Copying data from a table to another database

Pedro Doria Meunier wrote:

Hi all,

This is perhaps a very 'newbie' question but for the life of me, I can't
see a way to do it without resorting to programming... (blush)

As far as I'm concerned writing SQL is programming. It's not (usually)
procedural, but neither is Prolog, ML, Haskell, or any other number of
programming languages.

Anyway ... your question doesn't seem to be all that specific, but it
sounds like you've done a database migration where the data from one
table was excluded from the migration. You now wish to copy that table's
contents to the new server as well, merging it with the data in the same
table that's been created since the migration.

If it's just a history table I don't really see what's wrong with doing
a data-only dump of just that table using pg_dump, removing any DELETE
or TRUNCATE statements from the dump script, then running the load
script on the new server. That does assume you reserved space in any
primary key ID sequence in the new table though.

If there are primary key ID conflicts and you don't actually care about
the primary key values you may be able to load the data into a temporary
table then do an INSERT...SELECT that generates new primary keys with
`nextval' / DEFAULT and copies all other fields.

Personally I don't bother putting a primary key on history tables unless
it needs to be accessed by an ORM layer or similar, but I realise lots
of people do.

If all the above completely misses the point then either I've totally
misread your question or you might want to explain it in a bit more detail.

--
Craig Ringer

You can do this with dblink
http://www.postgresql.org/docs/8.3/interactive/contrib-dblink.html
pretty easily.

Jon

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Pedro Doria Meunier
Sent: Sunday, May 25, 2008 6:25 AM
To: Postgresql Mailing List
Subject: [GENERAL] Copying data from a table to another database
Importance: High

Hi all,

This is perhaps a very 'newbie' question but for the life of me, I

can't

see a way to do it without resorting to programming... (blush)

The thing is:
I have a table that acts as a historic for some devices activity.
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

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

Pedro Doria Meunier wrote:

Hi all,

This is perhaps a very 'newbie' question but for the life of me, I can't
see a way to do it without resorting to programming... (blush)

something like -

pg_dump --data-only --table=myoldhistorytable | psql -h x.x.x.x mynewdb

The thing is:
I have a table that acts as a historic for some devices activity.
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.

If I get that right the old server has some of the new data (or more
importantly - updates to rows that were transferred) since the transfer
started?

Your best bet would be to pg_dump the data then change the table name at
the start of the file to match a temp table name that can take the old
history and then insert/update from the temp table to the new server
history table.

As the table on the new server grows exponentially (already nearing 1M
records) I need to do this asap unless I want the entire thing to take
forever... (sweat)

Could someone please lend me a hand here?

Already thankful for any insight,

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Dears
Craig, Roberts, Reece and Shane

Thank you very much for your thoughts. They're helpful! ;-)
I've chosen the dump approach to another table in the migration server
and then INSERTing the different recs with a WHERE clause for every
non-existing timestamp for a deviceID.

As far as the pkey was concerned (entry_nbr) it didn't cause me any
problems as what's important were the timestamps in the relevant column.
Again thanks!

Best regards,
--
Pedro Doria Meunier <pdoria@netmadeira.com>