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

Oh good. That's almost what I did:

I made a schema only dump, then a data only dump with --inserts. Then I
commented-out the constraints from the schema. Then I loaded the data.
Unfortunately, the INSERT statements take 24 hours instead of 4 hours to
restore. When you say the "default" options - what format does that
write? Should I have used -Fp to make a plain text backup but not
--inserts? Then it would be doing a COPY instead of an INSERT and maybe
that would be faster. Oh well.

Tomasz Ostrowski wrote:
> 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
>