Hello group,

I just found something extremely annoying: I backup my DB with pg_dump
to extract the data. Then I drop the schema public in the backupped
database and restore it (a little bit modified, but compatible with the
backupped data). As third step I try to restore the data my doing a psql
<backup.sql. But because the order of the backupped tables is arbitraty
(or maybe alphabetically?) and there are foreign key contraints in
there, the restore fails because of the violated foreign key constraints.

So I have to open up vi, go into the data and rearrange the INSERT
operations just so they don't have dependencies on each other - so
finally it works. The whole backup is enclosed in a "START TRANSACTION;"
- "COMMIT;" part, so shouldn't it just try to insert ALL insert
statements and *then* check the foreign key constraints? Is my PG-server
too old (8.1)?

Regards,
Johannes

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Johannes Bauer <dfnsonfsduifb (AT) gmx (DOT) de> wrote:
No, this has not changed.

This is because pg_dump dumps constraints *after* the COPY (or INSERT)
statements.

So if you restore a complete dump, you'll have the CREATE TABLE first,
then the data, and then the constraints.
That is because this is the most efficient way to do it.

The problem you experience happens because you dump only the data.

But I can see several options how you could solve your problem,
apart from major editing:

- You can dump all tables individually and restore them in the correct
order.
- You can dump the schema defiition with the data and just edit the
CREATE TABLE or CREATE SCHEMA statements to reflect the changes you want.
Then import the dump which will recreate the objects with your changes.
- You can define the constraints on your tables as
DEFERRABLE INITIALLY DEFERRED. That way you should be able to import
your dump if it is within one transaction.

Yours,
Laurenz Albe

Laurenz Albe schrieb:

Ah, okay.


The last one I like best, this is exactly what I want. Thanks a lot for
your suggestion (again!).

Kind regards,
Johannes