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?