Python Postgres Best way to insert data from table on one DB to another table on another DB

bulkinsert plpgsql postgresql python-3.x sqlbulkcopy


The Python code below duplicates the contents of a table on PostgreSQL DB1 and INSERTS them into a related table on PostgreSQL DB2, respectively.

I want to use BULK INSERTS to speed things up. How can I do this?

import psycopg2
import sys
import os

all_data = []

    connec = psycopg2.connect("host = server1 dbname = DB1 ")
    connecc = psycopg2.connect("host = server2 dbname = DB2 ")
    connec.autocommit = True
    connecc.autocommit = True
    print("I am unable to connect to the database.")

cur = connec.cursor()

curr = connecc.cursor()

cur.execute("""SELECT * FROM TABLE1""")

curr.execute("TRUNCATE table TABLE2")

rows = cur.fetchall()

for row in rows:
    all_data = row

    curr.execute("INSERT INTO TABLE2 "
                 "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,     %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,"
             " %s)"
    ,(all_data[0],  all_data[1],all_data[2],all_data[3],all_data[4],     all_data[5], all_data[6], all_data[7],
      all_data[8], all_data[9], all_data[10], all_data[11],     all_data[12], all_data[13], all_data[14],all_data[15],
      all_data[16], all_data[17], all_data[18], all_data[19],
      all_data[20], all_data[21], all_data[22]))



1/31/2017 7:26:14 PM

Accepted Answer

The simplest method is to link the two servers using FDW (foreign data wrappers) ( and use a single server to manage both tables.

Use dblink as a second option (

In both cases, data is sent directly from one server to another (no via your program)

1/31/2017 7:50:01 PM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow