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've been running a restore job on 38m row table for over 24 hours that doesn't seem to want to come to an end. When I check pg_stat_activity it tells me that the restore job is still running, and the query column shows valid insert queries slotting data into the database.

However, when I run a count(*) on the table, I've been getting the same number just shy of 38m for the last 15 hours.

I don't think the issue is a hardware one, as previously a restore of this table took just 3 hours (it had around 25m rows at that stage).

Has anyone else experienced anything similar?

Also, is there any way to determine the number of rows in a .backup file? Perhaps, then I can know if it has captured all of the data.

Or is the only way to be sure to ignore the backup and run a complete insert all of the data again?

share|improve this question
    
What is a .backup file? And does it really contain 38M INSERT statements? If yes, how often do you commit? –  dezso Dec 22 '13 at 18:56
    
@dezso .backup is the poorly-chosen file extension PgAdmin-III uses for pg_dump -Fc style database backups. –  Craig Ringer Dec 23 '13 at 4:39
add comment

1 Answer

However, when I run a count(*) on the table, I've been getting the same number just shy of 38m for the last 15 hours

The restore will be running in a single transaction - or, at least, that particular table will be being loaded by a COPY command that runs in one transaction. If that's the case you won't see the new rows until the transaction commits.

You haven't shown the contents you mention from pg_stat_activity so it's hard to do more than guess here.

Also, is there any way to determine the number of rows in a .backup file? Perhaps, then I can know if it has captured all of the data.

Use pg_restore without a target database. It can extract a pg_dump custom-format file, dumping the whole file or individual tables as SQL, as CSV, etc. You can examine that dump contents that way.

I don't think the issue is a hardware one, as previously a restore of this table took just 3 hours (it had around 25m rows at that stage).

This could be a locking issue - if something else is still connected to the database it might be preventing a table truncation, preventing inserts, etc.

Check pg_locks; see Lock monitoring.

share|improve this answer
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.