Data Migration Between two databases with different structures (Postgresql)

Question

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.

It is too general a question as it stands. Can you provide a couple of real examples of the referencing between old and new that you actually want to do. I would be looking at 'views' with 'old' and 'new' references and using these 'views' to replace the 'old' tables? — Ryan Vincent, Jul 04 '15 at 17:40
Example: old_db(person_id,last_name,first_name) values (1234,Smith,John) | Now I want to move this data to new_db in a way a new id will be generated and the person_id will be moved in the ref_id column such as this: new_db(new_id,last_name,first_name,ref_id) values (1,Smith,John,1234) How will I do this?? @Ryan — john igneel, Jul 05 '15 at 00:33

score 5 · Accepted Answer · answered Jul 04 '15 at 17:44

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:

Thank you. But what I did is that I uploaded the old_db to new_db. I'm wondering if how can I use an UPDATE query to update the data from my old_db tables to new_db tables. @Kristjan — john igneel, Jul 05 '15 at 00:41
It sounds like you might actually be looking for [ALTER TABLE](http://www.postgresql.org/docs/9.4/static/sql-altertable.html). — Kristján, Jul 05 '15 at 09:00

Data Migration Between two databases with different structures (Postgresql)

1 Answers1