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

"Anony Mous" <A(dot)Mous(at)shaw(dot)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