Migrating Data Between Two Databases with Different Structures (Postgresql)
I have two databases, old_db and new_db, what I wanted to do was move the data records from old_db to new-db, but with different structures or different columns. I am creating a sql script that will load old_db to new_db and from there I can get data from old_db to new_db.
One of the tables in old_db looks like this:
tbl_person:
person_id bigint,
last_name text,
first_name text,
Now I want to transfer data to new_db with a structure like this, where the new_id column will generate a new id number and the person_id will reference or wrap to the 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 a sql script such that this data is correctly referenced from old_db to new_db ??? I am not asking for a tool or GUI instead of a sql script that I will be executing in a shell script. I am using postgresql as my DBMS, so I also need help regarding pg_dump or pg_restore to load old_db into new_db. TIA.
source to share
The root of this would be to insert data into your new table directly from the old table. NB: I am not running 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 databases are running on the same Postgres instance, this will work on its own. If they are in different instances on nodes that are visible to each other, you can use dblink , which makes the query SELECT
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 hosts can't see each other, there is a lot of help around StackOverflow on pg_dump
and pg_restore
:
source to share