How can I migrate data from one PostgreSQL database to another (with slightly different table/column-CodePudding

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.

CodePudding user response：

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.