pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to Restore a Database pg_dump -  Help - S.O.S.
2+ messages / 2 participants
[nested] [flat]

* How to Restore a Database pg_dump -  Help - S.O.S.
@ 2004-11-23 13:49  Flavio Borup <borup@running.com.br>
  0 siblings, 1 reply; 2+ messages in thread

From: Flavio Borup @ 2004-11-23 13:49 UTC (permalink / raw)
  To: pgsql-admin


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
}

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: How to Restore a Database pg_dump -  Help - S.O.S.
@ 2004-11-23 15:02  Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
  parent: Flavio Borup <borup@running.com.br>
  0 siblings, 0 replies; 2+ messages in thread

From: Tsirkin Evgeny @ 2004-11-23 15:02 UTC (permalink / raw)
  To: Flavio Borup <borup@running.com.br>; +Cc: pgsql-admin

Flavio Borup wrote:

>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?
>  
>
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?
>
>  
>
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 
this(if the data is meaningful).
However please note all this is not so simple: if you have 
keys/triggers/sequences the data have to be loaded
in some order ,so this maybe tricky .if you don't know what you are 
doing ,better don't do it.
And follow the link in the script!
http://www.postgresql.org/idocs/index.php?app-pgrestore.html
Evgeny.

>
>
>
>
>
>============================================================================
>============================================================================
>============================================================================
>#!/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
>  
>




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-11-23 15:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-23 13:49 How to Restore a Database pg_dump -  Help - S.O.S. Flavio Borup <borup@running.com.br>
2004-11-23 15:02 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox