Search code examples
postgresqldatabase-migration

Data Migration Between two databases with different structures (Postgresql)

I have two databases, old_db and new_db, what I wanted to do is to transfer the data records from old_db to new-db but with different structures or different columns. I am creating a sql script that will upload the old_db into new_db and from there I can get the data from the old_db to new_db.

One of the tables in old_db goes like this:

tbl_person:

person_id bigint,

last_name text,

first_name text,

Now I want to transfer the data into new_db with structure like this where the new_id column will generate new id number and the person_id will be referenced or tranferred to ref_id column:

tbl_person:

new_id bigint, ---this column is where the new id will be generated

last_name text,

first_name text,

ref_id bigint; ---this column is where the person_id will be copied

How can I create an sql script in such a way that this data will be referenced properly from old_db to new_db??? I'm not asking for a tool or GUI instead an sql script that I will be executing in a shell script. I'm using postgresql as my DBMS so I also need help regarding pg_dump or pg_restore for uploading the old_db in the new_db. TIA.

Solution

  • The root of this will be to insert data to your new table directly from your old table. NB: I have not run this code.

    INSERT INTO new_db.tbl_person (last_name, first_name, ref_id)
(SELECT last_name, first_name, person_id FROM old_db.tbl_person)

    If both DBs are running in the same Postgres instance, this will work on its own. If they're in different instances on hosts that are visible to each other, you can use dblink, which makes the SELECT query something like:

    SELECT * FROM
dblink(
  'host=otherhost user=me password=pass dbname=old_db',
  'SELECT last_name, first_name, person_id FROM tbl_person'
) AS tbl_old_person(last_name text, first_name test, person_id integer)

    If the hosts can't see each other, there's lots of help around StackOverflow on pg_dump and pg_restore: