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

Re: Upgrade time, dump+restore trouble.

From: Jesper Krogh <jesper(at)krogh(dot)cc>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Upgrade time, dump+restore trouble.
Date: 2007-10-18 05:53:02
Message-ID: 4716F4BE.8050809@krogh.cc (view raw or flat)
Thread:
Lists: pgsql-admin
Tom Lane wrote:
> Jesper Krogh <jesper(at)krogh(dot)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(at)krogh(dot)cc


In response to

pgsql-admin by date

Next:From: yogeshDate: 2007-10-18 06:01:50
Subject: Drop Not Null
Previous:From: Kostis MentzelosDate: 2007-10-18 05:23:35
Subject: convert char to varchar

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