Re: Dumping a table from one database and adding it to another

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> "James B. Byrne" 12/03/10 12:56 PM >>>
I have read the documentation respecting backups but I cannot seem
to find any mention of the specific case that I wish performed.

Hi James,

pg_dump can take arguments to dump a specified table, the output can be filtered/edited in a pipe & passed back to psql accessing a different database on the same or another host:

You may need to set user & other parameters, but a quick example;

to dump a table, rename it & recreate in a different db on another server on the fly:

pgdump -h -t | sed 's/src table/target table/g' | psql -h -d

< dump named table in specified db >< change all occurences of >< run the SQL in the >
original table name to new one target db

By piping through sed you can also change column names, just as this does the table name.

But note that if you have users/tables/columns with the same name, this is not going to be straightforward...

HTH,

Brent Wood

I have a pair of tables in a production database that I wish to dump
and then restore to a new, different database. I can, and probably
will, recreate the tables and column layouts in the new database.

Is there a way to load the data dumped from a single table in one
database into a new, possibly differently named, table in a
different database, using PG utilities?

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Please consider the environment before printing this email.

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.