title: Data Migration Between two databases with different structures (Postgresql)
body: <p>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.</p>
  
  <p>One of the tables in old_db goes like this:</p>
  
  <p>tbl_person:</p>
  
  <pre><code>person_id bigint,
  
  last_name text,
  
  first_name text,
  </code></pre>
  
  <p>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:</p>
  
  <p>tbl_person:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>