X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A694F3A2BC2 for ; Tue, 23 Nov 2004 13:49:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53344-02 for ; Tue, 23 Nov 2004 13:49:10 +0000 (GMT) Received: from hm171.locaweb.com.br (hm171.locaweb.com.br [200.234.203.7]) by svr1.postgresql.org (Postfix) with SMTP id C3C673A3EAD for ; Tue, 23 Nov 2004 13:49:10 +0000 (GMT) Received: (qmail 23955 invoked by uid 0); 23 Nov 2004 13:49:11 -0000 Received: from unknown (10.1.10.47) by hm171.locaweb.com.br with QMTP; 23 Nov 2004 13:49:11 -0000 Received: (qmail 31720 invoked by uid 52787); 23 Nov 2004 13:49:08 -0000 Message-ID: <20041123134908.31719.qmail@hm47.locaweb.com.br> From: "Flavio Borup" Date: Tue, 23 Nov 2004 11:49:08 -0200 To: pgsql-admin@postgresql.org Subject: How to Restore a Database pg_dump - Help - S.O.S. References: In-Reply-To: X-Mailer: LocaMail X-IPAddress: 200.219.168.66 X-Sender: borup@running.com.br MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/170 X-Sequence-Number: 15554 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 } ============================================================================ ============================================================================ ============================================================================