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
1032 Views

Similar Articles

[PageSpeed] 26


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

DataBase DataBase DataBase DataBase
DataBase DataBase DataBase DataBase Porfessional Programmable Database Ver. 2.0 2.1 Million Record Capacity. Search Rate: 2000/Records/Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Easy to Learn (Typically 30 Seconds) Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). Easy Paypal Online Ordering. See Site Below. http://www.vehiclerepair.org/dbPro/dbpro.html Scott: #DataBase...

DataBase DataBase DataBase DataBase
DataBase DataBase DataBase DataBase Porfessional Programmable Database Ver. 2.0 2.1 Million Record Capacity. Search Rate: 2000/Records/Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Easy to Learn (Typically 30 Seconds) Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). Easy Paypal Online Ordering. See Site Below. http://www.vehiclerepair.org/dbPro/dbpro.html ...

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...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Em...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Em...

Database Database Database Database Software Cheap
Database Database Database Database Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://w...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Em...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Em...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Em...

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...

a database, is a database, is a err database
How many times can we see the same request from someone who wants to access data from a 'pick' database through what has come to be 'standard' practices (odbc, oledb) and still get the same old sloppy ' buy this proprietary utility (and above all, my services)' answer. I think most of these pick flavors should have some sort of layer (by now!) to handle this; If someone needs to do this, the service is really 'education' i.e to show them how. Lets cut the shit now and stop with this tired and silly BS and sad marketing schlock. Regards, -Jim ...

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...

o.O;
Looks like Juggles' Rainbow just got added recently to Asimov, like right out of the blue, along with Bumble Games. There's another couple old eduwarez to X off my list :P As expected, Juggles' Rainbow is written predominantly in BASIC, and was a cakewalk to get up on ProDOS. Next I'm gonna try, I think, Bumble. I wonder why IBM renamed it Juggles' Butterfly. -uso. On Tue, 8 Apr 2014, Steve Nickolas wrote: > Looks like Juggles' Rainbow just got added recently to Asimov, like right out > of the blue, along with Bumble Games. There's ano...