How can data be transferred between two databases in PostgreSQL?

There are several approaches to transferring data between two PostgreSQL databases from Python:

1. Using the psycopg2 library: connect to both databases, read data from the first, then insert into the second.

2. Using the dblink or postgres_fdw tool: if the databases are accessible from one, you can set up external connections and execute SQL queries directly.

3. Export and import via files: export data from the first database to CSV, then load into the second.

Example with psycopg2:

import psycopg2

# Connect to the first database
conn_src = psycopg2.connect(dbname='db1', user='user', password='pass', host='host1')
cursor_src = conn_src.cursor()

# Connect to the second database
conn_dst = psycopg2.connect(dbname='db2', user='user', password='pass', host='host2')
cursor_dst = conn_dst.cursor()

# Read data from the first database
cursor_src.execute('SELECT id, name FROM table1')
rows = cursor_src.fetchall()

# Insert data into the second database
for row in rows:
    cursor_dst.execute('INSERT INTO table2 (id, name) VALUES (%s, %s)', row)

conn_dst.commit()

cursor_src.close()
cursor_dst.close()
conn_src.close()
conn_dst.close()

This approach is suitable for small volumes of data. For larger volumes, it is better to use specialized tools or streaming transfer.