Skip site navigation (1) Skip section navigation (2)

Re: Copying data from a table to another database

From: Reece Hart <reece(at)harts(dot)net>
To: Pedro Doria Meunier <pdoria(at)netmadeira(dot)com>
Cc: Postgresql Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Copying data from a table to another database
Date: 2008-05-25 19:38:23
Message-ID: 1211744303.6958.183.camel@snafu (view raw or flat)
Thread:
Lists: pgsql-general
On Sun, 2008-05-25 at 12:24 +0100, Pedro Doria Meunier wrote:

> Now I've migrated the entire 'shebang' to another server and need to
> update the history table on the new server with data from the old
> server
> for every different record.


I'm not sure I understand this completely. Would COPY work? For example:

$ psql -h <oldhost> -d <olddb> -c 'copy <schema>.<table> to stdout' |
psql -h <newhost> -d <newdb> -c 'copy <schema>.<table> from stdin'

This presumes that a table with the same structure already exists in the
new database.

If you have made schema changes, or you need only a subset of rows, you
can specify an appropriate select statement to the copy command on "old"
database. See documentation for COPY.

Also consider a statement like this:
=> INSERT INTO newtable SELECT * FROM oldtable EXCEPT SELECT * FROM
newtable;
I'm assuming that you populate a temporary oldtable in the new db
(perhaps using the COPY method above). This won't work if there are
intentional identical rows in your table.

The pipe assumes a Unix-ish box.

-Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

In response to

pgsql-general by date

Next:From: Shane AmblerDate: 2008-05-25 19:51:58
Subject: Re: Copying data from a table to another database
Previous:From: Roberts, JonDate: 2008-05-25 18:15:49
Subject: Re: Copying data from a table to another database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group