Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres Dump out of order

Postgres Dump out of order

From
Helio Campos Mello de Andrade
Date:
Hi guys,

 - I'm having a problem when i want to make a backup of my system.
 - Postgres generated dump was created out of "foreing key" order and when i try to recreate my database structures, data and functions. Does someone have this same issue? Someone knows of some app that do the job?

Regards...

--
Helio Campos Mello de Andrade

Re: Postgres Dump out of order

From
Rikard Bosnjakovic
Date:
On Wed, Dec 2, 2009 at 08:16, Helio Campos Mello de Andrade
<helio.campos@gmail.com> wrote:

>  - Postgres generated dump was created out of "foreing key" order and when i
> try to recreate my database structures, data and functions.

I think there's some information missing here.

What happens when you recreate your database structure?


--
- Rikard - http://bos.hack.org/cv/

Re: Postgres Dump out of order

From
silly8888
Date:
pg_dump dumps data first and then the constraints (including FK) so
there shouldn't be any problems when you import the dump.



On Wed, Dec 2, 2009 at 2:16 AM, Helio Campos Mello de Andrade
<helio.campos@gmail.com> wrote:
> Hi guys,
>
>  - I'm having a problem when i want to make a backup of my system.
>  - Postgres generated dump was created out of "foreing key" order and when i
> try to recreate my database structures, data and functions. Does someone
> have this same issue? Someone knows of some app that do the job?
>
> Regards...
>
> --
> Helio Campos Mello de Andrade

Re: Postgres Dump out of order

From
Craig Ringer
Date:
On 2/12/2009 3:41 PM, silly8888 wrote:
> pg_dump dumps data first and then the constraints (including FK) so
> there shouldn't be any problems when you import the dump.

... assuming you're using a sufficiently recent version of pg_dump.
Wasn't that added fairly recently?

--
Craig Ringer

Re: Postgres Dump out of order

From
"A. Kretschmer"
Date:
In response to Helio Campos Mello de Andrade :
> Hi guys,
>
>  - I'm having a problem when i want to make a backup of my system.
>  - Postgres generated dump was created out of "foreing key" order and when i
> try to recreate my database structures, data and functions. Does someone have
> this same issue? Someone knows of some app that do the job?

Which PG-Version?

Wild guess: not a 8.x, or?


And yes, provide more informations, for instance the table-definition
and the error-message you got.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Re: Postgres Dump out of order

From
Tom Lane
Date:
Craig Ringer <craig@postnewspapers.com.au> writes:
> On 2/12/2009 3:41 PM, silly8888 wrote:
>> pg_dump dumps data first and then the constraints (including FK) so
>> there shouldn't be any problems when you import the dump.

> ... assuming you're using a sufficiently recent version of pg_dump.
> Wasn't that added fairly recently?

Depends on context, which the OP provided none of.

pg_dump has handled FK dependencies -- even circular ones -- correctly
for a long time, given that you're doing a full schema+data dump.

If you ask it for a data-only dump, there is no way to handle circular
dependencies, so until recently it just threw up its hands and dumped
the tables in an arbitrary order.  Recent versions (I think probably
only 8.4.x) will order a data-only dump correctly for FK considerations
so long as there are no circular dependencies.

If you must use a data-only dump pre-8.4, I'd suggest using pg_restore's
-L switch to manually control the restore order.

            regards, tom lane