Re: fine tuned database dump/reload? - Mailing list pgsql-general

From Douglas McNaught
Subject Re: fine tuned database dump/reload?
Date
Msg-id m2r7ast339.fsf@Douglas-McNaughts-Powerbook.local
Whole thread Raw
In response to fine tuned database dump/reload?  (Dan Armbrust <daniel.armbrust.list@gmail.com>)
List pgsql-general
Dan Armbrust <daniel.armbrust.list@gmail.com> writes:

> Does postgresql have any facility to dump anything more fine grained
> than a database to a text file?

You can dump a single table with the -t option.  Right now you can't
specify an arbitrary set of tables to dump.

> For example, to mention a bad word, MySQL's dump command allows you to
> specify individual tables to dump - and not only that - you can also
> specify a SQL query that must be satisfied for a row to make it into
> the dump file - this is extremely useful for the way that we use our
> database.

You'd have to write custom client code for this, currently (or use
'psql', see below).  People have been talking about making COPY able
to read from views (and maybe from SELECT statements as well), so you
might have more flexibility in a later version.

> Also - how does postgresql handle foreign keys if you dump an entire
> database along with the create table statements?  Because my foreign
> keys require that the tables be created in a certain order - MySQL
> fails on this front - but since it allows me to specify the tables to
> dump, and it dumps them in that order, I can specify the correct
> order.

You don't have to worry about this.  The SQL created by the dump adds
all the foreign key constraints after populating the tables.

> PostgreSQL's pg_dump command seems rather limited in its
> abilities. Maybe I'm missing the command I'm looking for.

Its primary purpose historically has been to back up whole databases,
and it does that very well.  You might look at what 'psql' can do for
you--it's pretty powerful and can be controlled from a script.

-Doug

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Cluster/redundancy question
Next
From: Gregory Youngblood
Date:
Subject: Re: PostgreSQL missing in SuSE 10?