Sql-server – I was interrupted while transferring a very large table. How to pick it up from where it stopped

countpostgresqlsql server

I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the middle of it. I tried to select the rows with an offset as the number of rows already transferred. But count(*) takes up too much server resources and the server becomes unresponsive when I try. (memory usage rises from 100MB to ~8GB in a very short time)

I'm wondering if there's a way to pick up the transfer from where it stopped. It took around 3 days to transfer the number of rows I currently have in Postgres.

Thanks.

Also, I know the approximate number of the rows. So maybe I can do something like this: if there are approximately 12392320 rows, delete everything after the 12390000th row and just start from 12390001. Is something like that possible?

Best Answer

An alternative view:

Are you really using inserts for 2B rows? You might be better off bulk loading the data. 2B rows shouldn't take 3 days unless they are spectacularly wide.

Also, pg_bulkload might be of interest for this.

Related Question