Обсуждение: Backup and Restore
I am using 7.2.x. When I run pg_dump on a database the objects are not loaded in the correct dependency order. Soooo.... when I run pg_restore it fails because objects that are needed have not been restored yet (ie. trieds to restore a view that points to a table that has not yet been restored). I then monkey around with the Table of Contents (TOC) and am able to get the restore to work. In the middle of a disaster I don't want to have to mess to the TOC. What are others doing who are running into this situation. Thanks, Dave Leedom The Hightower Group, Inc. Information Technology Consulting 165 West Airport Road/Lititz, PA 17543 V:717-560-4002, 877-560-4002 x: 114 F:717-560-2825 www.hightowergroup.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.483 / Virus Database: 279 - Release Date: 5/19/2003
This is a well known issue, you can change Postgress version to 7.3.3 and the pg_dump on that version is more smart. Gaetano ----- Original Message ----- From: "David A. Leedom" <daleedom@hightowergroup.com> To: <pgsql-admin@postgresql.org> Sent: Thursday, June 05, 2003 1:56 PM Subject: [ADMIN] Backup and Restore I am using 7.2.x. When I run pg_dump on a database the objects are not loaded in the correct dependency order. Soooo.... when I run pg_restore it fails because objects that are needed have not been restored yet (ie. trieds to restore a view that points to a table that has not yet been restored). I then monkey around with the Table of Contents (TOC) and am able to get the restore to work. In the middle of a disaster I don't want to have to mess to the TOC. What are others doing who are running into this situation. Thanks, Dave Leedom The Hightower Group, Inc. Information Technology Consulting 165 West Airport Road/Lititz, PA 17543 V:717-560-4002, 877-560-4002 x: 114 F:717-560-2825 www.hightowergroup.com ---------------------------------------------------------------------------- ---- > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.483 / Virus Database: 279 - Release Date: 5/19/2003 > ---------------------------------------------------------------------------- ---- ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
But I have blobs in my data so I am using -F c At 01:22 PM 6/5/2003 -0700, Jonathan Gardner wrote: >-----BEGIN PGP SIGNED MESSAGE----- >Hash: SHA1 > >On Thursday 05 June 2003 04:56, David A. Leedom wrote: > > I am using 7.2.x. When I run pg_dump on a database the objects are not > > loaded in the correct dependency order. > > > > Soooo.... when I run pg_restore it fails because objects that are needed > > have not been restored yet (ie. trieds to restore a view that points to a > > table that has not yet been restored). I then monkey around with the Table > > of Contents (TOC) and am able to get the restore to work. > > > >Don't use pg_restore. Use psql. Note that pg_dump dumps a bunch of SQL >commands. > >This is also a cheap way to "vacuum" the database: > ># pg_dump <dbname> | gzip > dump.gz ># dropdb <dbname> ># createdb <dbname> ># gunzip -c dump.gz > psql <dbname> > >- -- >Jonathan Gardner <jgardner@jonathangardner.net> >(was jgardn@alumni.washington.edu) >Live Free, Use Linux! >-----BEGIN PGP SIGNATURE----- >Version: GnuPG v1.2.1 (GNU/Linux) > >iD8DBQE+36aSWgwF3QvpWNwRAveXAJ4qiMWs7bAsAQoQYmUb0j1bkYOrjwCgwVt6 >Gkotl26Q7541ofhVDTP4lQk= >=U7V7 >-----END PGP SIGNATURE----- > > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.483 / Virus Database: 279 - Release Date: 5/19/2003 The Hightower Group, Inc. Information Technology Consulting 165 West Airport Road/Lititz, PA 17543 V:717-560-4002, 877-560-4002 x: 114 F:717-560-2825 www.hightowergroup.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.483 / Virus Database: 279 - Release Date: 5/19/2003
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thursday 05 June 2003 04:56, David A. Leedom wrote: > I am using 7.2.x. When I run pg_dump on a database the objects are not > loaded in the correct dependency order. > > Soooo.... when I run pg_restore it fails because objects that are needed > have not been restored yet (ie. trieds to restore a view that points to a > table that has not yet been restored). I then monkey around with the Table > of Contents (TOC) and am able to get the restore to work. > Don't use pg_restore. Use psql. Note that pg_dump dumps a bunch of SQL commands. This is also a cheap way to "vacuum" the database: # pg_dump <dbname> | gzip > dump.gz # dropdb <dbname> # createdb <dbname> # gunzip -c dump.gz > psql <dbname> - -- Jonathan Gardner <jgardner@jonathangardner.net> (was jgardn@alumni.washington.edu) Live Free, Use Linux! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+36aSWgwF3QvpWNwRAveXAJ4qiMWs7bAsAQoQYmUb0j1bkYOrjwCgwVt6 Gkotl26Q7541ofhVDTP4lQk= =U7V7 -----END PGP SIGNATURE-----
Dave- I ran into this recently & made a similar inquiry of the list. Apparently the pg_restore dependency problems are known issues, but not fixed in 7.2. I haven't looked to see if it is fixed in 7.3 yet. In our case, space was not an issue and backups run quickly enough that I just do two backups each night- one to tar format in case I want to restore individual tables, and one to text format so I can do a complete restore with ease. Before taking this approach I also played around a bit with both the TOC and creating a shell script with a bunch of pg_restores in the correct order. I quickly concluded that as long as space allows, I preferred the simpler approach of two backups since I don't have to remember to edit my shell script every time a make a change to the database. Like you, I prefer my tools for mid-disaster to be simple. -Nick > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of David A. Leedom > Sent: Thursday, June 05, 2003 6:56 AM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Backup and Restore > > > I am using 7.2.x. When I run pg_dump on a database the objects are not > loaded in the correct dependency order. > > Soooo.... when I run pg_restore it fails because objects that are needed > have not been restored yet (ie. trieds to restore a view that > points to a > table that has not yet been restored). I then monkey around with > the Table > of Contents (TOC) and am able to get the restore to work. > > In the middle of a disaster I don't want to have to mess to the TOC. > > What are others doing who are running into this situation. > > Thanks, > Dave Leedom > > > > The Hightower Group, Inc. > Information Technology Consulting > > 165 West Airport Road/Lititz, PA 17543 > V:717-560-4002, 877-560-4002 x: 114 > F:717-560-2825 > www.hightowergroup.com >
"Jonathan Gardner" <jgardner@jonathangardner.net> wrote: > This is also a cheap way to "vacuum" the database: > > # pg_dump <dbname> | gzip > dump.gz > # dropdb <dbname> > # createdb <dbname> > # gunzip -c dump.gz > psql <dbname> Yes specially when you have a dump of about 1 GB and you manage a service 24/24 7/7!! The only cheap way to vacuum is: #vacuumdb <dbname> if you want update the statistics too: #vacuumdb -z <dbname> and if you can handle to have an exclusive lock on tables: #vacuumdb -f -z <dbname> the only think that I suggest for bigs table with index is to reindex that table: #psql -t -c "reindex table <table_name>" <dbname> Regards Gaetano Mendola