Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 






Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand

>>> "James B. Byrne" <byrnejb@xxxxxxxxxxxxx> 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 <host1> -t <src table> <db1>  |   sed 's/src table/target table/g'   |   psql -h <host2> -d <db2>

<   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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxx)
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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux