How can I migrate data from one PostgreSQL database to another (with slightly different table/column names)?

Question

I am trying to migrate a large portion of one postgres database to another postgres database that has a slightly different layout/table names/column names. But the data is the same. What is a good way to do this? All I can think of is using pg_dump and then manually changing the column names and table names in the dump file but there is a lot of data to work through and that way would also be very error prone.

Answer 1

Use dblink to add data to new tables in the new database.

Example:

INSERT into new_table ( cd_ace, no_desc )
SELECT cd_accessory, no_description
FROM DBLINK('host=ip_address_remote port=5470 dbname=database_name user=user password=password ',
'SELECT cd_acessorio, no_description from dbatez.acessorio')
  AS a ( cd_accessory character varying(4), no_description character varying(40));

I hope I helped you.