Thread: question about pg_dump -a

I have a database which I create using dia and tedia2sql.

I developed another version with more tables, without changing anything
that was already present in the first version.

Now I want to copy the data from one database to another, so I thought
about pg_dump -a, assuming that since there is no change in the
structure and I can freely  and and reload the information.

My problem is that when I reload the data into the new database, I have
several error about foreign keys violation. For what I've been able to
understand, it seems to be a problem of loading order and tables
referring to others are loaded earlier than those.

Is there a way to export tables in order, so that dependencies are
always met? reading the manpage of pg_dump I found the -Fc flag, but I
haven't understood if it is good for me and how it works. Or is there a
way to relax constraints while loading data?

Ottavio Campana wrote:
>
> Is there a way to export tables in order, so that dependencies are
> always met? reading the manpage of pg_dump I found the -Fc flag, but I
> haven't understood if it is good for me and how it works. Or is there a
> way to relax constraints while loading data?

Try a pg_dump with -Fc and then pg_restore --data-only.

If all else fails, you can control item-by-item what gets restored by
producing a list from pg_restore (--list), commenting out lines and then
using it as a specification with (--use-list). See manuals for full details.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton ha scritto:
> Ottavio Campana wrote:
>>
>> Is there a way to export tables in order, so that dependencies are
>> always met? reading the manpage of pg_dump I found the -Fc flag, but I
>> haven't understood if it is good for me and how it works. Or is there a
>> way to relax constraints while loading data?
>
> Try a pg_dump with -Fc and then pg_restore --data-only.
>
> If all else fails, you can control item-by-item what gets restored by
> producing a list from pg_restore (--list), commenting out lines and then
> using it as a specification with (--use-list). See manuals for full
> details.

with -L I was able to solve it, thanks.

But why does pg_dump does not already exports data such that previous
tables do not depend on successive ones?

Ottavio Campana wrote:
> Richard Huxton ha scritto:
> > Ottavio Campana wrote:
> >>
> >> Is there a way to export tables in order, so that dependencies are
> >> always met? reading the manpage of pg_dump I found the -Fc flag, but I
> >> haven't understood if it is good for me and how it works. Or is there a
> >> way to relax constraints while loading data?
> >
> > Try a pg_dump with -Fc and then pg_restore --data-only.
> >
> > If all else fails, you can control item-by-item what gets restored by
> > producing a list from pg_restore (--list), commenting out lines and then
> > using it as a specification with (--use-list). See manuals for full
> > details.
>
> with -L I was able to solve it, thanks.
>
> But why does pg_dump does not already exports data such that previous
> tables do not depend on successive ones?

It does -- but it can't with data-only dumps.  (I think that it just
punts and doesn't care.  Maybe we could improve it to do a "best
effort").  The current suggested usage is to avoid using data-only
dumps.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

I don't think this would be too hard to effect:

When pg_dumping a schema, have an additional flag -m <newschemaname>,
that would convert all references in the dump from the original
schema to the new schema name.

Thus the command:

pg_dump -c -s myoldschemaname -m mynewschemaname mydatabase -f foo

would generate a dump file such that all the data that was in
myoldschemaname would, upon psql mydatabase < foo would be in a new
(or replaced) schema mynewschemaname.

The present strategy is to go through the dump and manually change
the schema names in the dump text.  This is not a reliable mechanism,
as there may be name collisions with the schema name and other names,
and there's always the possibility that you might miss one when
you're hand modifying the code.

I'd be happy to help on the effort if that makes sense.  I don't know
what the code to pg_dump is like, though.

-Owen

On Sep 28, 2007, at 9:07 AM, Ottavio Campana wrote:

> But why does pg_dump does not already exports data such that previous
> tables do not depend on successive ones?

Because you can't always sort your tables that way.  The restore
procedure is responsible for either sorting or disabling the FK
checks during bulk load.  The latter is more efficient, especially if
there are no indexes yet, as in a full restore from dump.

Vivek Khera ha scritto:
>
> On Sep 28, 2007, at 9:07 AM, Ottavio Campana wrote:
>
>> But why does pg_dump does not already exports data such that previous
>> tables do not depend on successive ones?
>
> Because you can't always sort your tables that way.  The restore
> procedure is responsible for either sorting or disabling the FK checks
> during bulk load.  The latter is more efficient, especially if there are
> no indexes yet, as in a full restore from dump.

how can FK checks be disabled? is there a command?