Re: Upgrade time, dump+restore trouble. - Mailing list pgsql-admin

From Jesper Krogh
Subject Re: Upgrade time, dump+restore trouble.
Date
Msg-id 4716F4BE.8050809@krogh.cc
Whole thread Raw
In response to Re: Upgrade time, dump+restore trouble.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-admin
Tom Lane wrote:
> Jesper Krogh <jesper@krogh.cc> writes:
>> Tom Lane wrote:
>>> Drop the constraints in the source database.
>
>> That would be my workaround for the problem. But isn't it somehow
>> desirable that pg_dumpall | psql "allways" would work?
>
> Well, sure.  The reason why this sort of thing is deprecated is exactly
> that the database can't promise it will work all the time.  The DB has
> no way to know that your constraints do something they're not supposed
> to, and in particular no way to infer that there's a specific data
> loading order needed to keep the constraint from failing.

No, it should just prospone, both constraints and triggers to the end
of the dump (no magic here).

This is not different from triggers in general. When you need to dump
with --disable-triggers, you by yourself ensure that your contraint was
fulfilled before you dumped / disabled triggeres. Then load the data and
enabled the triggers again. The database cant enable usual foreing-key
constraints before it have the complete dataset either.

In this case the DB cant promise anything either. (unless it actually
runs the complete set of checks at "enable time", which it might as well
should be able to do with the stuff in the triggers.

The only difference is that this is per-table(disable-dump-enable) and
not dump-wide.

> We do allow you to do it, but if it breaks you get to keep both pieces.

What would be the preferred way of enabling some sort of timetravelling
on a database, where the usual foreing-key constraints (data-integrity)
should be preserved?

Our setup is inspired by:
http://www.varlena.com/GeneralBits/122.php
But it nicely jumps straight over foreing-keys.

--
Jesper Krogh, jesper@krogh.cc


pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: Upgrade time, dump+restore trouble.
Next
From: Kostis Mentzelos
Date:
Subject: convert char to varchar