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

Hi,

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.

Am I setting myself up for disaster with only a single dump as a backup?

Much thanks,
Peter

> 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 ;)

Sincerely,

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@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

"Anony Mous" <A.Mous@shaw.ca> writes:
> 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).

I don't know who recommended that, but I sure wouldn't do it that way.

If you do do it that way then the restore will be slow, at least if you
have any indexes or foreign keys.  It's better to load the data first
and then create indexes/verify foreign keys.  pg_dump does do things in
that order if you use a single dump, but obviously it cannot if you dump
schema and data separately.

What I *would* recommend is a single dump in either -Fc or -Ft mode.
The reason for this is that if needed, you can use pg_restore's options
to alter the restore order, which can get you out of trouble if you run
into one of the various pg_dump bugs about dumping related objects in
the wrong order.  (I think said bugs are finally all fixed for 8.0, but
they are a fact of life in released PG versions, so you should be
prepared to deal with 'em.)

If you use a text dump then you have to be willing to fix any ordering
problems by editing the dump file ... which can be a tad unwieldy if
it's a big dump.

            regards, tom lane