PostgreSQL (9.3) restore won’t finish

postgresqlpostgresql-9.3restore

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?

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

Related Question