6

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?

Improve this question
14
  • 2
    This will all depend on whether the rows were transferred in an ORDER. Any idea if they were? You'd probably be better off dumping the data to a .csv file and bulk loading that in, rather than doing a select/insert using python. A bulk load will be much faster.
    – Philᵀᴹ
    Aug 3, 2012 at 5:38
  • Phil: It was transferred without an ORDER. I might try the csv option, but I'm not sure if it works well with datetime objects, texts, unicode, nulls, and etc.
    – UXkQEZ7
    Aug 3, 2012 at 5:46
  • Phil: Also, the two databases are in a local network and can transfer around 5000 rows per second. That's fast enough for me although it can be faster. Thanks.
    – UXkQEZ7
    Aug 3, 2012 at 5:48
  • This looks like an OK way to push the data: postgresonline.com/journal/index.php?/archives/… - There might be a nice Postgres/ODBC way to do it too
    – Philᵀᴹ
    Aug 3, 2012 at 5:52
  • 2
    You cannot "delete everything after the 12390000th row" because the rows are not ordered. It might seem like they are ordered, but they are not, and a delete is precisely the sort of operation that will expose that underlying fact.
    – Jack Douglas
    Aug 3, 2012 at 7:27

1 Answer 1

Reset to default
8

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.

Improve this answer
2
  • Will it work with loading from a non-postgres DB? I have the db structure and keys converted from SQL Server to Postgres, so I just need to copy the contents.
    – UXkQEZ7
    Aug 3, 2012 at 13:20
  • 3
    You should be able to export to a series of .CSV files (or some other suitable format). Then import them using the bulk load facility. It will be some orders of magnitude faster than select/inserts.
    – ConcernedOfTunbridgeWells
    Aug 3, 2012 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.