From: Tom Lane <tgl@sss.pgh.pa.us>
To: Phoenix Kiula <phoenix.kiula@gmail.com>
Cc: PG-General Mailing List <pgsql-general@postgresql.org>
Subject: Re: Dumping/Restoring with constraints?
Date: Wed, 27 Aug 2008 09:54:02 -0400
Message-ID: <22708.1219845242@sss.pgh.pa.us> (raw)
In-Reply-To: <e373d31e0808270619uba31c8eodc2c85662b85140a@mail.gmail.com>
References: <e373d31e0808270619uba31c8eodc2c85662b85140a@mail.gmail.com>
"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> Hello. I have a database dump. With data and schema, which includes
> all the constraints and rules. But it seems the pg_dumpall command
> does not copy data in such a way that foreign keys are satisfied upon
> restoring. Because tables are inter-related, importing them keep
> giving errors and eventually no data is imported.
This shouldn't be possible in a regular dump, at least not with any
remotely modern version of PG. However, if you are trying to restore a
data-only dump into an already-created set of tables, it is possible
because pg_dump doesn't know how to order the data loads in that case.
(The problem may in fact be insoluble, since circular foreign key
relationships are allowed.)
The recommended solution is to use a regular schema-and-data dump.
If you really have to separate schema and data, the --disable-triggers
switch might help, though you open yourself to the possibility of
loading inconsistent data that way.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, phoenix.kiula@gmail.com, pgsql-general@postgresql.org
Subject: Re: Dumping/Restoring with constraints?
In-Reply-To: <22708.1219845242@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox