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.

Use postgres_fdw to set up link between databases. Create the new tables in the new database and then move the data from the old to new via queries. — Adrian Klaver
– Adrian Klaver, Commented May 12, 2022 at 15:59

Andronicus · Accepted Answer · 2022-05-13 05:00:21Z

2

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.

edited May 13, 2022 at 5:00

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

answered May 12, 2022 at 18:30

Jose Carvalho

692 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related