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

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
borup (1)
11/23/2004 1:49:08 PM
comp.postgresql.admin 1726 articles. 0 followers. ashok (20) is leader. Post Follow

1 Replies
1124 Views

Similar Articles

[PageSpeed] 17
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
tsurkin
11/23/2004 3:02:23 PM
Reply:
Similar Artilces:

System Restore, Virus Restore=?ISO-8859-1?B?ig==?=Same Thing
http://www.mcpmag.com/columns/article.asp?EditorialsID=760 ----- Start Quotes ----- Windows ME and Windows XP have a handy System Restore feature, which periodically make backups of key system files, especially when you install new software or hardware. Unfortunately, virus-infected files can get backed up by System Restore. So, you could clean your system and then roll back to a restore checkpoint and poof! The virus is back. .... Depending on how you feel about System Restore's benefits vs. the risks of bringing a virus back, you might want to disable System Restore ----- End Quotes...

__Shortcut to KILL battery / restore hotsync M500 ?
I've searched the groups and can't find the posts about the graffiti shortcut that will lock the Palm ON, with backlight, so that it will DIE, and thus reset whatever has rendered the USB connection dead. (hoping it's not a static discharge issue) It belongs to a friend, and everything works, including beaming, and a hard reset didn't cure it. This Palm tries to do a hotsync, but the desktop end of it doesn't launch, so the Palm times out with the 'connection could not be established' message on screen. My M505 syncs perfectly on the same cradle. Any...

Cannot restore after Veritas Netbackup restores
Hi There, We have an issue on our db2 restores. We do database dumps every night, and restore them to development servers every morning. This works fine with no errors. However if we backup those images to tape using Veritas netbackup, and then restore those images and try to restore them to the database, we get "The backup image is corrupt". If we run db2ckbkp against that image we get "Unable to decompress image from different platform". This is really urgent as our backups are currently useless to us if we cannot restore them properly. If anyone has even a smal...

trigger database changes to java beans
Hello, I am working on a client server application involving TOMCAT as Web server, MySQl as the DB and combination of JSP, Servlet, and JavaBeans to access and write Data back. There is another piece of the project (out side my scope of development) that writes to the Same MySQL tables and make changes to the data. How do I write a Trigger/Store Procedure or any such mechanism that would throw an event that the external Java Beans would catch and take action like, fetch the changed data.... I am sure this is a very commom issue in application development and there maybe some...

database toolbox driver setup
using the database toolbox on linux, i'm trying to access mysql using the jdbc driver connector/j. when i do: conn = database('test', '','', 'com.mysql.jdbc.Driver', 'jdbc:mysql://localhost') i get the following error in conn.Message: 'JDBC Driver Error: com.mysql.jdbc.Driver. Driver not Found/Loaded' i unarchived and installed connector/j (mysql-connector-java-3.0.11-stable-bin.jar), added the full path to the jar file in CLASSPATH variable (echoed back to verify), added the full path to the jar file in $matlabroot/toolbox/local/classpath...

Restore program
Is there a way to write a program that would backup all home directory objects that had been modified within say, the last 14 days, prior to restoring your home directory backup object? On Tue, 15 Aug 2006 06:07:51 -0500, <rschapin@comcast.net> wrote: > Is there a way to write a program that would backup all home directory > objects that had been modified within say, the last 14 days, > prior to restoring your home directory backup object? There are no time stamps on internal calc objects (only SD card files have modification times). It might be easiest to just make a new c...

Restoring from mksysb
I have an RS6000 F50-7025 that currently has a 4GB SCSI boot disk (rootvg). I'm going to replace it with a 9GB disk. I plan to restore from an mksysb tape. I've never done this before. How difficult is the restoration process from mksysb? Is there anything I should know before diving in? I'm making the tape with the command: mkszfile -f'' && mksysb '/dev/rmt0.1' What's the best procedure for booting off this tape? By using bootlist(1)? What exactly happens when you boot off an mksysb tape? Does it just ask you if you want to restore, and then...

To restore information after a hard reset
In PC, after one has reformatted the hard disk many times by wiping out the entire Windows and reinstalling it, there is still a method to restore and retrieve the supposedly deleted data. For a Palm, if one perform a hard reset to it several times, is it possible to use some advance technology to restore and retrieve the data that were gone? I appreciate an answer to this question. On Tue, 9 Sep 2003 23:33:33 +0800, "tom" <tom@nospam.com> wrote: >In PC, after one has reformatted the hard disk many times by wiping out the >entire Windows and reinstalling i...

Kingston PS/2 Memory and Upgrade Database
Before going to the Web, Kingston freely offered a parts database, a FoxPro app for Win 3.11 which also runs on Win98SE. For some time, updates were available but I didn't follow. I just remembered that I had an earlier version, from 1996, which was quite handy in looking up PS/2 memory schemas, upgrade options and short sysinfo. This database has been now integrated in the web site of Kingston, their so-called configurator. The app, "Kepler for Windows", consists of 2 diskettes and is by license freely distributable. I have zipped the diskettes, they can be obtained t...

Disk utility
I run Jaguar. I need the 'restore' ability that the Disk Utility from Panther has. I don't want to upgrad emy system, want to wait for Tiger. What can I do? Thanks! -- gipsy boy In article <39Sae.143741$Uh3.14053@biebel.telenet-ops.be>, gipsy boy <x@x.pi> wrote: > I run Jaguar. I need the 'restore' ability that the Disk Utility from > Panther has. I don't want to upgrad emy system, want to wait for Tiger. > What can I do? The first thing would be to describe the problem you're trying to solve rather than what you think the solution is. ...

need matlab code and algorithms for image restoration
hey anybody help me to give matlab code and algorithms for image restoration and image enhancement. thanks. roksy rosky wrote: > > > hey anybody help me to give matlab code and algorithms for image > restoration and image enhancement. > thanks. > roksy Why? /PB ...

Eastern Australia: Informix Database Engine requires restart after patching the OS for Daylight Savings Extension
If you use IDS (or XPS, SE, OnLine, RedBrick, Cloudscape, ...) and your system is based in southern or eastern Australia, you are probably already aware that the switch from summer time back to winter time (daylight saving to standard time) has been delayed from 2006-03-26 to 2006-04-02 because of the Commonwealth Games. What you may not have realized is that IDS uses the local tim to determine CURRENT (and TODAY). This is determined by the TZ variable when the server is started. All the major vendors have released o/s patches to modify the handling of the revised Australian rule...

Same Broker for multiple oracle databases
Hi, I am developing a java web application and i am using a broker to access to my oracle 8i Database on linux. I want to use the same broker to access more than one Database. Is it possible? if yes how ? Thank you in advance Ramzi ...

Zapping PRAM in OS X (to restore Appletalk?)
I got a strange problem at work. The Macs in the Graphics dept. running OS 10.2.x can see the Appletalk Zones and have no problem printing to two Xerox DC 12s running off Fiery servers and an HP 5si/MX. If you put OS 10.3.x on one of the Macs and print through IP printing to the Xerox DC 12s, they either spew out pages of Postscript code, or have the print jobs continuously stall in the Printer Setup Utility no matter how many times you restart them, or else give the message "will retry connection in 10 seconds". About 10% of the time you actually print what you want. I hav...

want to know the growth of my oracle database
Hi, I am trying to predict the growth of my database every month. Is there any tool or method which I can use to predict the growth. Wanted to know whether oracle database only maintains any of these statistics in the data dictionary. thanks and regards RAMESH. This has been multi-posted. You will find answers close to the other posts. PLEASE don't waste everyone's time like this in the future - asking in one comp.database.oracle.* newsgroup is sufficient. -- Hans Forbrich Canada-wide Oracle training and consulting mailto: Fuzz...

Restoring bad pics...
http://cjoint.com/data/lAvdvKQpea.htm The whole image is 1,4 MB, it's a pic from Flickr, a painting from de Heem; http://www.insecula.com/PhotosNew/00/00/05/89/ME0000058932_3.JPG This is what it should look like. Next i will buy the DVD with all that stuff, but i was trying to make it watchable... 1) Use of the perspective tool, cropping... 2) Applying some gaussian blur, then the smart sharpening script. On the sample, because else it takes too much time. 3) adding some contrast, remove yellow (all less than 10%) Anyway it seems that i can't get it sharp and...

Newbie @ Databases
Hi all, Here's my situation: I have an Access Database v. 7.0 and there is only one table, "Market". There are three fields in the Market table, Price, Product, and Number. What I want to be able to do is to access the database and enter in a number (ISBN) which then checks the database for that number. If the number exists, I want all three fields to be displayed into three different text boxes... but if the number doesn't exist, then it will add the number to the database. How can I go about searching the database and displaying the correct information when the nu...

Ruby Database Tutorials ( Oracle).
[Note: parts of this message were removed to make it a legal post.] Dear all, Can you tell me a good Ruby Database Tutorials site? I want to learn with Oracle Database. many thank in advance, regards, salai. On Thu, Jan 17, 2008 at 10:22:43PM +0900, Salai Khine wrote: > Dear all, > Can you tell me a good Ruby Database Tutorials site? I want to learn with > Oracle Database. > > many thank in advance, On that subject . . . Something similar for PostgreSQL would be handy for me right about now. -- CCD CopyWrite Chad Perrin [ http://ccd.apotheon.org ] Larry Wall: &quo...

NT Backup restores directories but not files
Hello Why is it that NT Backup restores directories but not files. All the directories are empty. I have had this on a few occasions at different sites I work at. I tried searching for an answer... not much found. I heard mention of incremental backups being problem, but am sure I haven't used that option anyway. Anyone one help please? Please dont answer if you dont know... rather not have the thread hijacked as sometimes happens :D Thanks. ...

US-TX-San Antonio: SQL Server Database Admin, UNIX scripting, VB, .NET, XML (45286457621)
US-TX-San Antonio: SQL Server Database Admin, UNIX scripting, VB, .NET, XML (45286457621) ========================================================================================= Position: SQL Server Database Admin Reference: SMC01150 Location: San Antonio TX Duration: C-P Skills: 5 + years IT experience, 3 + years Database Administration and Architecture with SQL Server. Unix scripting, data warehouse, data modeling, data standards, development and management, data dictionary maintenance and data admin...

Question: drop database problem
Dear list, I'm using Postgresql 7.2 on Solaris. I have this error message when I try to drop a database. I couldn't find a mention anywhere on this error. Could someone advise how to circumvent this error? pgsql=# drop database mydb; ERROR: Postmaster environment variable 'PGDATA3' not set The database owner is pgsql, and if I echo $PGDATA3 as the owner, it returns a valid directory. Also, the datpath is correct. select * from pg_database; shows datname ... datpath -----------------+...+--------- mydb PGDATA3 In the $PGDATA3/data/base/ directory,...

GRANT TEMPORARY ON DATABASE to Public
This is standard when just create a db, but how long do these temporary objects survive? (eg public user creates a table) It's longer than the session, contrary to create temporary table x.. tnx, ...

RE: Moving Informix TableSpaces between databases #3
Hi Simmon and Superboer thanks you for your suggestions. I tried out the RAW table and it works like a charm, it was exactly what I was looking for. Thanks a lot. Regards, Aash -----Original Message----- From: owner-informix-list@iiug.org [ mailto:owner-informix-list@iiug.org <mailto:owner-informix-list@iiug.org> ]On Behalf Of Simmons, Keith Sent: Friday, December 09, 2005 7:41 PM To: informix-list@iiug.org Subject: RE: Moving Informix TableSpaces between databases Aash You don't tell us which version of IDS you are using, however have you in...

Restore Mac Mini with Restore Disk
Hi there folks. I'm trying to restore my Mac Mini. FIrst time MAC user here. I played around with my mini for a bit but i want to start fresh and reinstall. I popped in teh reinstall disk, clicked the install OS X and Applications and it prompts to restart. I restart it, it reboots to the OS then pops the disk out and I am right where I started. What am I doing wrong? Help! THanks. In article <1136231016.508197.173330@g49g2000cwa.googlegroups.com>, ridergroov <ridergroov1@comcast.net> wrote: > Hi there folks. I'm trying to restore my Mac Mini. I'm not sure why...

Restore from Tape
Hi, I had performed a full backup of my Unix system on a Tape Disk through the cpio utility. It had taken 3 tapes to perform the backup. Recently I had deleted an directory on the system and wanted to restore from the tape. Accidently I kept the tape onto the drive and an automatic backup program overwrote the contents of the tape. My question is : a) Is it possible to recover the data on the first tape? b) Is it possible to list the contents of the 2nd and 3rd tapes and recover the directory from these tapes? I had issued the following command to write onto the tape : "find . -print ...