f



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 1725 articles. 0 followers. ashok (20) is leader. Post Follow

1 Replies
1290 Views

Similar Articles

[PageSpeed] 59

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: