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 367B23A3F0B for ; Tue, 23 Nov 2004 15:02:32 +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 76475-09 for ; Tue, 23 Nov 2004 15:02:26 +0000 (GMT) Received: from mail1.cc.huji.ac.il (real-outmail.cc.huji.ac.il [132.64.1.17]) by svr1.postgresql.org (Postfix) with ESMTP id CAD4F3A3E55 for ; Tue, 23 Nov 2004 15:02:27 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by mail1.cc.huji.ac.il (Postfix) with ESMTP id 9AEC2F063D; Tue, 23 Nov 2004 17:02:23 +0200 (IST) Received: from mail1.cc.huji.ac.il (localhost [127.0.0.1]) by localhost (VaMailArmor-2.0.1.16) id 29529-398A55E5; Tue, 23 Nov 2004 17:02:23 +0200 Received: by mail1.cc.huji.ac.il (Postfix, from userid 31998) id 781B0F0626; Tue, 23 Nov 2004 17:02:23 +0200 (IST) Received: from [132.64.52.127] (evegeni.mm.huji.ac.il [132.64.52.127]) by mail1.cc.huji.ac.il (Postfix) with ESMTP id 052BEF0626; Tue, 23 Nov 2004 17:02:23 +0200 (IST) Message-ID: <41A350FF.6000809@mail.jct.ac.il> Date: Tue, 23 Nov 2004 17:02:23 +0200 From: Tsirkin Evgeny User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Flavio Borup Cc: pgsql-admin@postgresql.org Subject: Re: How to Restore a Database pg_dump - Help - S.O.S. References: <20041123134908.31719.qmail@hm47.locaweb.com.br> In-Reply-To: <20041123134908.31719.qmail@hm47.locaweb.com.br> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable 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/171 X-Sequence-Number: 15555 Flavio Borup wrote: >I=B4m not DBA. I=B4m a infrastruture professional, please someone help m= e > >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 > 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 > 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 ># | 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 ># ># 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 > "$location_binaries/vacuumdb" -z -h $postgresql_hostname -U $postgresq= l_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" =3D "yes" ]; then > gzip "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-pos= tgresql_database-$i-backup"=20 > chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$d= ate_info/$current_time-postgresql_database-$i-backup.gz" > else > chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$d= ate_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)= at $timeinfo for schedule $current_time on database: $i, format: $backup= _type" >> $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 >