Thread: Backup and Restore

Backup and Restore

From
"David A. Leedom"
Date:
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

Re: Backup and Restore

From
"Mendola Gaetano"
Date:
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



Re: Backup and Restore

From
"David A. Leedom"
Date:
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

Re: Backup and Restore

From
Jonathan Gardner
Date:
-----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-----

Re: Backup and Restore

From
"Nick Fankhauser"
Date:
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
>


Re: Backup and Restore

From
"Mendola Gaetano"
Date:
"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