Moving information between databases

Question

I'm on Postgres, and have two databases on the same machine, and I'd like to move some data from database Source to database Dest.

Database Source:
Table User has a primary key
Table Comments has a primary key
Table UserComments is a join table with two foreign keys for User and Comments

Dest looks just like Source in structure, but already has information in User and Comments tables that needs to be retained.

I'm thinking I'll probably have to do this in a few steps.

Step 1, I would dump Source into a file using the Postgres Copy command.

Step 2, In Dest I would add a temporary second_key column to both User and Comments, and a new SecondUserComments join table.

Step 3, I would import the dumped file into Dest using Copy again, with the keys input into the second_key columns.

Step 4, I would add rows to UserComments in Dest based on the contents of SecondUserComments, only using the real primary keys this time. Could this be done with a SQL command or would I need a script?

Step 5, delete the SecondUserComments table and remove the second_key columns.

Does this sound like the best way to do this, or is there a better way I'm overlooking?

Answer 1

You could use dblink to connect from one database to the other and just insert the records and columns you need. Then you don't have to change the datamodel, don't have to do a pg_dump, just some good old SQL.