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

Re: pg_dump | pg_sql: insert commands and foreign key constraints

From: Tomasz Ostrowski <tometzky(at)batory(dot)org(dot)pl>
To: William Garrison <postgres(at)mobydisk(dot)com>
Cc: Postgres General List <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_dump | pg_sql: insert commands and foreign key constraints
Date: 2008-09-24 07:40:37
Message-ID: 48D9EEF5.6010708@batory.org.pl (view raw or flat)
Thread:
Lists: pgsql-general
On 2008-09-23 19:03, William Garrison wrote:
> I have several .SQL files created from pg_dump, and I find that when I 
> feed them into psql that I get tons of foreign key errors because the 
> INSERT statements in the dump are not in the correct order.  After 
> reading the docs, mailing lists, and googling, I see posts saying this 
> problem was fixed back in the 7.x days.

It is not fixed and is sometimes not possible to fix for data only dumps.

> Since I did a data only dump, I think my only option is to create the 
> schema, manually disable all the constraints, then restore, then 
> re-enable the constraints.

Much easier:

1. Create a schema with all constraints etc.
2. Dump this empty database with pg_dump with default options to
empty_database.sql.
3. Split empty_database.sql file to 2 files - tables.sql and
constraints.sql - all constraints will be at the end of empty_database.sql
4. drop database, create empty one, import tables.sql, import your
data-only backup, import constraints.sql.

Regards
Tometzky
-- 
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2008-09-24 08:19:13
Subject: Re: Slony vs Longiste
Previous:From: Volkan YAZICIDate: 2008-09-24 05:43:48
Subject: Re: Slony vs Longiste

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