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

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