Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  FreshPhilOfSO Aug 3 '12 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. –  ologn Aug 3 '12 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. –  ologn Aug 3 '12 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 –  FreshPhilOfSO Aug 3 '12 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 '12 at 7:27
show 9 more comments

1 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.

share|improve this answer
    
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. –  ologn Aug 3 '12 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 '12 at 14:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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