Обсуждение: Upgrade time, dump+restore trouble.
We have a quite large (in our measurements) database, currently around 150GB of data. Time has come to upgrade to 8.2.5 from 8.1.10. Going to the fine manual.. having a system where both databases are installed PG8.1 on port 5432 and PG8.2 on port 5433, logging in as the "postgres" user and running: pg_dumpall | psql -p 5433 This fails due to constraints on the tables. Typical error messages look like: ERROR: new row for relation "box_full" violates check constraint "tower_id_ref" The tables are running a "home-made" timetravelling feature where a contraint on the table implements the foreing keys on the table. How can I instruct pg_dumpall to turn off these constriants during dump/restore? Jesper -- Jesper Krogh, jesper@krogh.cc
Jesper Krogh <jesper@krogh.cc> writes:
> The tables are running a "home-made" timetravelling feature where a
> contraint on the table implements the foreing keys on the table.
You mean you have check constraints that do selects on other tables?
You have just found out (one reason) why that's a bad idea.
> How can I instruct pg_dumpall to turn off these constriants during
> dump/restore?
Drop the constraints in the source database.
regards, tom lane
Tom Lane wrote: > Jesper Krogh <jesper@krogh.cc> writes: >> The tables are running a "home-made" timetravelling feature where a >> contraint on the table implements the foreing keys on the table. > > You mean you have check constraints that do selects on other tables? Yes. We do.. we have to .. in order to perserve the foreing-keys. What we have in short is: Added two timestamps on each columns, starttime,endtime. When endtime is "null" the record is "current" and a view exposes this from the table. The foreing keys are then implemented using contraints that look up the id in the foreing table and ensures that there is a record where the endtime is null present > You have just found out (one reason) why that's a bad idea. Do you have any other reasons? >> How can I instruct pg_dumpall to turn off these constriants during >> dump/restore? > > 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? Jesper -- Jesper Krogh, jesper@krogh.cc
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.
We do allow you to do it, but if it breaks you get to keep both pieces.
regards, tom lane
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