PostgreSQL Data Transfer – Pull Data from One Database and Insert to Another with Same Schema

postgresqlpostgresql-9.5

I am currently working on moving data from one database to another in PostgreSQL. I am studying python with psycopg2 to connect and run SQL queries in one database. My problem is how can I pull and insert the data that I queried from one database to another. Most of the posts I found here talk about database from local server to a remote server. Like:

This suggests to use dblink or FDW. My situation is that both databases are in the same schema.

I am really new to this kind of task. Any suggestions?

Here's sample code in python that I am working with:

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
conn_t = p.connect("dbname='t_mig1' user='postgres' password='postgres' host='localhost'")

cur_p = conn_p.cursor()
cur_t = conn_t.cursor()

cur_t.execute("SELECT CAST(REGEXP_REPLACE(studentnumber, ' ', '') as integer), firstname, middlename, lastname FROM sprofile")
rows = cur_t.fetchall()

for row in rows:
    print "Inserting ", row[0], row[1], row[2], row[3]
    cur_p.execute(""" INSERT INTO "a_recipient" (id, first_name, middle_name, last_name) VALUES ('%s', '%s', '%s', '%s') """ % (row[0], row[1], row[2], row[3]))

cur_p.commit()
cur_pl.close()
cur_t.close()

Best Answer

FDW and dblink have the advantage that they make a direct postgreql to postgresql connection which means that there will be no processing needed in your python code. here's an example that should work much like your example code, except faster.

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
strt="dbname='t_mig1' user='postgres' password='postgres' host='localhost'"
cur_p = conn_p.cursor();

cur_p.execute("select dblink_connect('tmpdb',%s);",(strt,));
cur_p.execute("""
  insert into "a_recipient" (id, first_name, middle_name, last_name)
  SELECT * FROM dblink('tmpdb',
    $$
    SELECT CAST(REGEXP_REPLACE(studentnumber, '[^0-9]', '','g') as integer), firstname, middlename, lastname FROM sprofile 
    $$ ) as x( a int,b text, c text,d text);
""");

cur_p.execute("SELECT dblink_disconnect('tmpdb');");

unfortunately I was not able to test this code. feel free to make any correction needed. I also changed the regex to remove all non digits.

Related Question