How to Restore a Database pg_dump - Help - S.O.S.

  • Permalink
  • submit to reddit
  • Email
  • Follow


I�m not DBA. I�m a infrastruture professional, please someone help me

In my company we have a PGSQL Linux Red Hat 9 Database
The relevant portions of my DB Backup Script follows
I can send too, the whole script with other parts of the code

The backup is beeing made with the �bva� parameters
How can i restore?
I have to do a su - postgresql?
i have to drop and recrate something?







============================================================================
============================================================================
============================================================================
#!/bin/sh
#
#  +-- Restoration can be performed by using psql or pg_restore.
#  |   Here are two examples:
#  |          
#  |   a) If the backup is plain text:
#  |
#  |   Firstly gunzip your backup file (if it was gzipped).
#  |
#  |   gunzip backup_file.gz
#  |   psql -U postgres database < backup_file
#  |
#  |   b) If the backup is not plain text:
#  |
#  |   Firstly gunzip your backup file (if it was gzipped).
#  |
#  |   gunzip backup_file
#  |   pg_restore -d database -F {c|t} backup_file
#  |
#  |   Note: {c|t} is the format the database was backed up as.
#  |
#  |   pg_restore -d database -F t backup_file_tar
#  |
#  +-- Refer to the following url for more pg_restore help:  
#
#      http://www.postgresql.org/idocs/index.php?app-pgrestore.html
#


# Run backup, vacuum and analyze
run_bva() {
	for i in $databases; do
		start_time=`date '+%s'`
		timeinfo=`date '+%T %x'`
		
		"$location_binaries/vacuumdb" -z -h $postgresql_hostname -U $postgresql_username $i >/dev/null 2>&1
		"$location_binaries/pg_dump" $backup_args -h $postgresql_hostname $i > "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
		if [ "$backup_gzip" = "yes" ]; then
			gzip "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup" 
			chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup.gz"
		else
			chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
		fi
		finish_time=`date '+%s'`
		duration=`expr $finish_time - $start_time`
		echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $location_logfile
	done
	exit 1
}

============================================================================
============================================================================
============================================================================

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

0
Reply borup (1) 11/23/2004 1:49:08 PM

See related articles to this posting


Flavio Borup wrote:

>I=B4m not DBA. I=B4m a infrastruture professional, please someone help me
>
>In my company we have a PGSQL Linux Red Hat 9 Database
>The relevant portions of my DB Backup Script follows
>I can send too, the whole script with other parts of the code
>
>The backup is beeing made with the =B4bva=B4 parameters
>How can i restore?
>I have to do a su - postgresql?
>=20=20
>
no .you already have the instractions in the script you posted:

#  |   a) If the backup is plain text:
#  |
#  |   Firstly gunzip your backup file (if it was gzipped).
#  |
#  |   gunzip backup_file.gz
#  |   psql -U postgres database < backup_file
#  |
#  |   b) If the backup is not plain text:
#  |
#  |   Firstly gunzip your backup file (if it was gzipped).
#  |
#  |   gunzip backup_file
#  |   pg_restore -d database -F {c|t} backup_file
#  |
#  |   Note: {c|t} is the format the database was backed up as.
#  |
#  |   pg_restore -d database -F t backup_file_tar

bva options means - you have the plain text format.

>i have to drop and recrate something?
>
>=20=20
>
you should delete all the data from the tables
(not drop tables) .
running the
psql -U postgres database < backup_file
will add the data to database.
if you still have data in tables i suggest running backup befor you try=20
this(if the data is meaningful).
However please note all this is not so simple: if you have=20
keys/triggers/sequences the data have to be loaded
in some order ,so this maybe tricky .if you don't know what you are=20
doing ,better don't do it.
And follow the link in the script!
http://www.postgresql.org/idocs/index.php?app-pgrestore.html
Evgeny.

>
>
>
>
>
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>#!/bin/sh
>#
>#  +-- Restoration can be performed by using psql or pg_restore.
>#  |   Here are two examples:
>#  |=20=20=20=20=20=20=20=20=20=20
>#  |   a) If the backup is plain text:
>#  |
>#  |   Firstly gunzip your backup file (if it was gzipped).
>#  |
>#  |   gunzip backup_file.gz
>#  |   psql -U postgres database < backup_file
>#  |
>#  |   b) If the backup is not plain text:
>#  |
>#  |   Firstly gunzip your backup file (if it was gzipped).
>#  |
>#  |   gunzip backup_file
>#  |   pg_restore -d database -F {c|t} backup_file
>#  |
>#  |   Note: {c|t} is the format the database was backed up as.
>#  |
>#  |   pg_restore -d database -F t backup_file_tar
>#  |
>#  +-- Refer to the following url for more pg_restore help:=20=20
>#
>#      http://www.postgresql.org/idocs/index.php?app-pgrestore.html
>#
>
>
># Run backup, vacuum and analyze
>run_bva() {
>	for i in $databases; do
>		start_time=3D`date '+%s'`
>		timeinfo=3D`date '+%T %x'`
>=09=09
>		"$location_binaries/vacuumdb" -z -h $postgresql_hostname -U $postgresql_=
username $i >/dev/null 2>&1
>		"$location_binaries/pg_dump" $backup_args -h $postgresql_hostname $i > "=
$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_data=
base-$i-backup"
>		if [ "$backup_gzip" =3D "yes" ]; then
>			gzip "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postg=
resql_database-$i-backup"=20
>			chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$dat=
e_info/$current_time-postgresql_database-$i-backup.gz"
>		else
>			chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$dat=
e_info/$current_time-postgresql_database-$i-backup"
>		fi
>		finish_time=3D`date '+%s'`
>		duration=3D`expr $finish_time - $start_time`
>		echo "Backup, Vacuum and Analyze complete (duration $duration seconds) a=
t $timeinfo for schedule $current_time on database: $i, format: $backup_typ=
e" >> $location_logfile
>	done
>	exit 1
>}
>
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>=20=20
>


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

0
Reply tsurkin 11/23/2004 3:02:23 PM
comp.postgresql.admin 1726 articles. 1 followers. Post

1 Replies
494 Views

Similar Articles

[PageSpeed] 13


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

Restore database fails because database is in use.
I'm a newbie so please be gentle. In attempting to run a restore I get the following error message: "Exclusive access could not be obtained because the database is in use." However, it doesn't appear that there are any connections to the database. At the suggestion of another thread, I ran sp_who2 and there are no connections to the database in question. I've been running the same restore for months and all has been well, until yesterday.... I wrote an .asp page (below) to test connectivity to this database. The vb code ran successfully and soon thereafter, my nightl...

Restore database Vs Restore tablespace.....
How are the following two commands different as far as the 'end result' of the restores is concerned: 1. db2 RESTORE DB mydb 2. db2 RESTORE DB mydb TABLESPACE In other words, in the above case, what is the difference between 'database restore (performed in point 1 above) Vs the 'tablespace restore' (performed in point 2 above). TIA Raquel. "Raquel" <raquel_rodriguezus@yahoo.com> wrote in message news:9a73b58d.0405132324.3229db68@posting.google.com... > How are the following two commands different as far as the 'end > result'...

Unable to restore a database image into a replicated database.
Hello. Can somebody help me out here? I have a web server sitting on a DMZ that has DB2 UDB version 8 installed running on it. I would like this server to be completely independent of our internal network. If possible, I would like to be able to each morning FTP a copy of the backup image from our production database, which is created from the previous night's db2 backup, to the web server. I am hoping that I can then simply perform a restore from that image into the database on the DMZ. This will allow us to make daily updates to our database on the web server. When I try...

Rman
Hello all! I'm testing ability to restore database after total loosing of datafiles and controlfiles. I have Rman backup with controlfile. I restored controlfile from backup, but I cannot restore database using the same backup. I'm getting error "RMAN-06023: no backup or copy of datafile..." when I try to "restore database". I don't use catalog database. Is it possible to re-catalog backup? How? Thank you for help Lech In message <1166427629.304419.243970@73g2000cwn.googlegroups.com>, Lech <leszek.adamek@gmail.com> writes >Hello all...

Opening one database always restores a minimised database
Hi I have an Access database that I use to keep track of hours I work on development. The database is always open and minimised. An annoying problem is that whenever I open a database I am working on through a shortcut, the time tracking database window gets restored before the one I am opening actually opens. It does stay behind the one I am working on but I have to minimise it again if I want to keep the desktop uncluttered. Is there any way of stopping the minimised one restoring? Jeff When I set the target property of the shortcut to: "C:\Program Files\Microsoft Office\O...

Difference between Restoring to a new database / using redirect restore?
Hello, I am little confused and puzzled the real difference between Restoring to a new database and using redirect restore? I want to copy the production databases to test databases in DB2 v8.2/ AIX environment. Please share your opinion. Regards, Myjish. On 16 Apr, 15:26, DB2-v789 <tojigneshs...@gmail.com> wrote: > Hello, > > I am little confused and puzzled the real difference between Restoring > to a new database and using redirect restore? > http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/r0006254.htm?resulto...

Restoring databases
Guys, I need to restore a database which is 400 GB plus. Usually a normal restore from a backup file takes about 40 minutes. I have a database Company on a server A. I need to use the backup of database company to restore as company1 on the same box. At the end, company and company1 would reside on the same box. Is there any way to do it faster than a normal restore??? Thanks in advance. Regards Jaideep jai (dbasybase@gmail.com) writes: > I need to restore a database which is 400 GB plus. Usually a normal > restore from a backup file takes about 40 minutes. I have a database > Co...

Database restore
Hi All, I am trying to restore a database from backup on another system [my client's] but I had to follow some weired steps. Step 1. Restore the database from EM 's GUI. [Users as on My machine get restored but their logins are not activated. So I have to follow the next steps] sp_helpuser shows me the userName but null LoginName and DefaultDatabase Step 2. Create a new login , grant access to the DB Step 3. Then run the script to change the ownership from earlier DB user to new user. Step 4. In EM make new user as dbo. Really weired steps. There should be easier way to restore...

Restoring database
Hello, due to some testing my SQL Server crashed. I have to reinstall it and ... For now I only have .mdf file and nothing else. My question is: is there any possibility to restore this .mdf file for SQL 2000? I have some useful data in this file and there will be nice to use it. Best Regards MarciN "Snick" <butcher@gold.pl> wrote in message news:btueb9$bho$1@news2.ipartners.pl... > Hello, > due to some testing my SQL Server crashed. I have to reinstall it and ... > For now I only have .mdf file and nothing else. My question is: is there any > possibility to re...

Restore Database
Hi, I have a db-backup and i'd like to restore on another pc... No problem with the same.. backup and restore on same machine.... anyone can help? tnx David This should just work. Can you give more info on what kind of error you got? -- Wei Xiao [MSFT] SQL Server Storage Engine Development http://weblogs.asp.net/weix This posting is provided "AS IS" with no warranties, and confers no rights. "gonne" <gonne@undernetshop.it> wrote in message news:cnt10d$2m8t$1@newsreader2.mclink.it... > Hi, > I have a db-backup and i'd like to restore on another p...

Database Restore
I have two 9.2.0.4 databases on the same Solaris machine. They reside on the same filesystem. I would like to Restore db "A" to db "B". Different SIDS and different datafile locations. Is the correct way to perform this to use RMAN to duplicate to an auxiliary database? Thanks > That is one way, yes Could you list the others please? Ryan S wrote: > I have two 9.2.0.4 databases on the same Solaris machine. They reside > on the same filesystem. I would like to Restore db "A" to db "B". > Different SIDS and different datafile location...

restoring database
Hi everybody ... i need to restore only one database from a pg_dumpall backup file... how can i do it? Thanks a lot ! ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend As I am not someone with a lot of experience.... (depending on the size of the 'dumpall') I would create a new database to suck up the 'all' then pg_dump the table I really cared about. Obviously, if the dump_all is a terabyte database this method is inconvenient.... (to say the least). However it will work. Ted --- Lucas L...

Restoring the database
Hi, Im trying to restore a postgresql database from a backup that was done a few days ago. The backup is on CD and was a straight copy of everything in the /$postgresql_install_path/ The problem is i want to restore a certain database and not every database. How would i know what files correspond to which database ? eg. I've got 3 databases called 'books', 'dvds' and 'videos'. I only want to restore 'dvds' from the CD. ---------- Regards Cody Phanekham ************************************************************************************...

restore database
Hi all I have files from Oracle database (8.1.6.3) from AIX. This is: control0x.ctl *.dbf Is it possible to restore this files to Oracle on Windows XP running any other version of Oracle Database when backup was made as copy of dabatase files. Is following errors caused by version difference ? I tried to restore it to 10.1 When I tried to make restore i got following error: Error in Restoring Control File Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connected to target database: orcl (not mounted...

restoring database
Hi everybody ... i need to restore only one database from a pg_dumpall backup file... how can i do it? Thanks a lot ! ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org On Mon, 2003-12-22 at 14:51, Lucas Lain wrote: > Hi everybody ... i need to restore only one database from a pg_dumpall backup > file... how can i do it? If it's a plain text dump, make a copy of the backup file. Edit the copy and delete everything that doesn't relate to the datab...

I want to revert back to original state of database before i performed restore database
Hello, i am in great trouble. I want to revert back to original state of database before i performed restore database on my sql server 2K Database. Accidently i didn't take backup of my Database and i did restore, so is there any way to get the original state back of my Database? Any suggestion will be highly appriciated. Regards, S. Domadia. "san" <shdomadia@gmail.com> wrote in message news:1163592758.514890.277030@h48g2000cwc.googlegroups.com... > Hello, > i am in great trouble. I want to revert back to original state of > database before i performed res...

need to restore old database (database server 4.01 AIX)
hi. i need to restore an old database. The db was backed up using a DLT drive, using 2 volumes. The content for the tapes was copied to file onto Solaris machine using rsh and dd (for backup purposes). Now, the drive is defective and can't read the tapes anymore. Server is AIX 4.3.2 and database is IBM DB2 Server (DB2 for AIX Version 2.1.2) I tried doing an NFS export from solaris machine. and doing restore database ..... from /mnt but i got the following message: SQL2542N No match for a database image file was found based on the source database alias and timestamp ...

Restore of Case Insensitive Database to a Case Sensitive Database
Yesterday I received a response to my CI/CS Collation problem and the recommendation was to try and restore a CI Collation database to a CS Collation database. After creating a blank CS database a full restore (Force restore over existing database) does change the Collation to CI. I'm unsure as to how I can restore without changing the Collation. Any suggestions? Hi. Check out this article: http://www.sql-server-performance.com/vg_database_comparison_sp.asp If appears that if you can put the CI db on another box and create a linked server to it you should be able to bring over every...

Trouble restoring 7.2.1 database into 7.4.2 database
This is a multi-part message in MIME format. ------=_NextPart_000_0047_01C41DC9.42906D90 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have table defined as follows: create table vm_emailjob ( JobId serial not null, MessageId varchar(128) not null, Recipients bytea not null, SendTime bigint not null, QueuedTime bigint not null, RetryCount integer ...

Database Restore Error
Hello, I am trying to recover a database with a RMAN backup. I only had control files, i lost all my dbf and redo. When i try to recover i got this error message: ORA-00283: recovery session canceled due to errors ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/dbs1/oradata/db/redo01.log' ORA-27037: unable to obtain file status Error: 2: No such file or directory I then did this: alter database open; and recived the following message: RMAN-03002: failure of alter db command at 11/07/2005 14:54:30 ORA-01113: file 1 needs media recovery O...

How to restore to a new database
Hi All, I have a cold RMAN backup (2 pieces - the main body and the controlfile piece) from another server. My mission is to recreate / restore it to a new server under new db name. Connectivity to the other server does not exists as this is from a customer's site. Environment is: Oracle 9i, Solaris 10. noarchivelog mode (for now). I read and tried for last 4 days no success. The instructions I researched involved: 1. get the rman cold backup from customer, put on my machine 2. create a backup control file - got the 'trace' file from customer, modified it for create ...

Restoring Numeric Databases
Hi, I'm developing a database application that uses numeric databases. At this point not using numeric databases is not an option. Doing a mysqldump works. However when trying to restore the database with "mysql -u root -psecret < backup.sql" gives the following error message: ERROR 1064 at line 11: You have an error in your SQL syntax near '123456789' at line 1 Line 1 has the following line: CREATE DATABASE /*!32312 IF NOT EXISTS*/ 123456789; I know this will generate an error. The correct syntax would be: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `123456789`; H...

Effects of a Database restoration.
Hi, Does a database restoration perform any update-statistics/defragmentation by default. We observed a marked improvement in performance when we restored the a database from an high end machine to a low machine. Could someone shed some light on this. Regards, Thyagarajan Delli. Thyagu (tdelli@gmail.com) writes: > Does a database restoration perform any > update-statistics/defragmentation by default. As far as I know, no. Except, that if the MDF is very fragmented on the source machine, and the target machine has space to accept it as contiguous, you will see defragmentation on th...

Problem restoring database ...
Dear Newsgroup, I am using sql server 2000 over win 2000 server with service pack 4. I have been given a back up of a database (I have tried both from T-SQL and Enterprise Manager) T-SQL RESTORE FILELISTONLY FROM DISK = 'c:\A.bck' RESTORE DATABASE B FROM DISK = 'c:\A.bck' WITH MOVE 'A_Data' TO 'c:\test\B.mdf', MOVE 'A_Log' TO 'c:\test\B.ldf' and as I try to restore I get the following error : Server: Msg 3154, Level 16, State 2, Line 1 The backup set holds a backup of a database other than the existing 'B' database. ...

Restore Database Window
I have a large relational database which has attached to one of my forms at startup; a macro which assigns the current time and date. I must have inadvertently changed the macro and have somehow caused a block to the tools menu and I have lost the database window. Is there a way to recover the database window or at least the tools menu so I can access the startup options and correct the problem? So far I have (1) Tried Shift then opening the database - tools option display for a second but cannot be invoked (2) I have attempted to import all components from the effected database...