Skip site navigation (1) Skip section navigation (2)

Re: pg_dump: what is advantage with schema and data dumps?

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Anony Mous <A(dot)Mous(at)shaw(dot)ca>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: pg_dump: what is advantage with schema and data dumps?
Date: 2004-09-24 17:40:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
> I've seen in previous posts that recommended practice for database backup is
> to run the dump utility twice.   Once for schema only and a second time for
> data only (I think).  Up to know, I've only ever dumped both, and have never
> had a problem restoring into older/newer versions of postgres, nor with
> restoring into other commercial versions of postgres.  This is a testament
> of the robustness of pg_dump!  Of course, there are always a series of error
> messages at the start of each restore when duplicate/unknown template DB
> functions are recreated, but these have never prevented the core data from
> being properly copied into the database.

pg_dump is not always smart about ordering of objects when there are 
custom items in play. For example, pg_dump does not restore correctly if 
you use TSearch (although I believe there is a patch).

So, if you dump the schema separately you can fix any ordering problems, 
or data type problems (from version to version) that you may encounter. 
Which in turn will allow you to restore your data safely.

Also there are times when you only want to restore the structure and not 
the constraints or indexes things like that. Dumping the database in two 
parts allows you to edit the schema without having to open a 50gig file 
in joe or vi ;)


Joshua D. Drake

> Am I setting myself up for disaster with only a single dump as a backup?
> Much thanks,
> Peter
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com -
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachment: jd.vcf
Description: text/x-vcard (640 bytes)

In response to

pgsql-general by date

Next:From: Ed L.Date: 2004-09-24 17:52:59
Subject: Re: 8.0 logging question
Previous:From: Vivek KheraDate: 2004-09-24 17:27:35
Subject: Re: converting FK's to "DEFERRABLE"

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group