psycopg2 transfer data from one database to another

Question

I am trying to move data from one database (db1) to another (db2).

After a few attempts, I came up with the code below, but it is too slow. According to my estimations, data transferring will take +7k hours.

Is there any way to make it faster? Can we process more than 1 row from cursor at once?

batch_size=10
transfer_table_name='my_table'

with db1.cursor(name=’my_cursor') as cursor:

    cursor.itersize=batch_size
    cursor.execute(‘select * from schema.table’)

    for row in cursor:
        csv_io = io.StringIO()
        writer=csv.writer(csv_io)
        writer.writerow(row)

        csv_io.seek(0)
        db2.cursor().copy_expert(
                        'COPY '+
                        transfer_table_name+
                        """ FROM STDIN WITH (NULL '', DELIMITER ',', FORMAT CSV)""",
                        csv_io
                     )

        db2.commit()

Are you allowed to use psycopg3, instead? That driver allows binary copying: https://www.psycopg.org/articles/2020/11/15/psycopg3-copy/ — Mike Organek, Aug 14 '23 at 19:35
This `for row in cursor:` is your issue. 1) How much data are you dealing with? 2) Why not `COPY` to pull from the source database? — Adrian Klaver, Aug 14 '23 at 19:39
@AdrianKlaver, yes, but idk how to make it without it, 1) 180+ millions rows and ~60 columns 2) idk what it is — Иван Иваныч, Aug 14 '23 at 19:51
@MikeOrganek, unfortunately we have only psycopg2 at my workplace, is there a way to duplicate that functionality in psycopg2? — Иван Иваныч, Aug 14 '23 at 19:51
If you cannot use psycopg3, then have a look at this: https://stackoverflow.com/a/8150329/13808319 — Mike Organek, Aug 14 '23 at 19:59
Do you have to use Python.psycopg2? Or could you set up [Logical Replication](https://www.postgresql.org/docs/15/logical-replication.html) and have the databases do the work? — Adrian Klaver, Aug 14 '23 at 22:20

score 1 · Accepted Answer · answered Aug 14 '23 at 19:53

It'll be much faster if you transfer rows in larger chunks

batch_size = 1000
transfer_table_name = 'my_table'

with db1.cursor(name='my_cursor') as cursor:
    cursor.itersize = batch_size
    cursor.execute('SELECT * FROM schema.table')

    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        
        csv_io = io.StringIO()
        writer = csv.writer(csv_io)
        writer.writerows(rows)
        
        csv_io.seek(0)
        
        with db2.cursor() as db2_cursor:
            db2_cursor.copy_expert(
                f'COPY {transfer_table_name} FROM STDIN WITH (NULL \'\', DELIMITER \',\', FORMAT CSV)',
                csv_io
            )
            db2.commit()

psycopg2 transfer data from one database to another

1 Answers1