5

I am trying to backup our postgres database (8.4.17), which is about 25GB in size.

pg_dump database_name > database_db_dump_2014-05-05.sql

unfortunately, the backup stops after about 600MB in, and does not continue. It always stops when trying to dump a specific table (fb_crawler_event). I was able to successfully dump all the other tables using the --table swtich. I have already stopped any other process that could interact with the database.


server log after a restart of the DB (looks ok to me):

2014-05-05 14:34:46 CEST LOG:  all server processes terminated; reinitializing
2014-05-05 14:34:46 CEST LOG:  database system was interrupted; last known up at 2014-05-05 14:32:50 CEST
2014-05-05 14:34:46 CEST LOG:  database system was not properly shut down; automatic recovery in progress
2014-05-05 14:34:46 CEST LOG:  record with zero length at 1A1/AD6A78C0
2014-05-05 14:34:46 CEST LOG:  redo is not required
2014-05-05 14:34:47 CEST LOG:  database system is ready to accept connections
2014-05-05 14:34:47 CEST LOG:  autovacuum launcher started

verbose output from pg_dump (nothing suspicious, but the obvious table, which doesn't let the database continue to dump)

... lots of lines ... pg_dump: restoring data for table "django_site" pg_dump: dumping contents of table django_site pg_dump: restoring data for table "fb_crawler_event" pg_dump: dumping contents of table fb_crawler_event

And then it just halts.


output from the pg_locks table, which seems to be huge to me (294 entries all together):

  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      mode       | granted 
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------
 relation   |    16384 |     2674 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |    27367 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |   695092 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |     2675 |      |       |            |               |         |       |          | 2/7                | 18960 | AccessShareLock | t
 relation   |        0 |     2671 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 virtualxid |          |          |      |       | 2/7        |               |         |       |          | 2/7                | 18960 | ExclusiveLock   | t
...

I am no expert in postgres, so I am wondering if the database is corrupt?

How can I further debug this anomaly?

  • Could you try create table x as select * from fb_crawler_event and pg_dump that? – Colin 't Hart May 5 '14 at 13:41
  • 1
    I just tried, and the command is stuck just like pg_dump itself, so it seems I cannot select on some rows or something... – devsnd May 5 '14 at 13:55
  • That narrows it down a bit! Do you have an estimate of the number of rows in that table? I wonder if bisecting the table (using limit n and offset n+1) will work or if these commands will also get stuck? – Colin 't Hart May 5 '14 at 14:40
  • And, if you haven't already, read the PostgreSQL page on corruption: wiki.postgresql.org/wiki/Corruption – Colin 't Hart May 5 '14 at 14:41
  • Are there any rows in pg_locks where granted is not true? – jjanes May 5 '14 at 15:51
3

I was able to figure out which file in the database storage was the culprit, by copying all the files to /dev/null.

cp -vR /usr/lib/postgresql/8.4 /dev/null

(The path to your DB files might differ)

The currupt file could not be copied, but there was nothing I could do to change that. (so it was most probably a FS error or hardware failure)

So I restarted the server with a forced fsck (e.g. touch /forcefsck), to make sure the FS would do the best to fix itself. This might not be the way you'll want to go, since it is possible to have a total data loss afterwards, but I was able to preserve the most precious data already beforehand, so I took the risk.

After reboot I could finally access the inaccessable table again, but I am not sure, if the data contained is corrupted or not. Anyway, I do have a backup now, which I can disect to find out, and my server can go back online for now...

I recommend reading the wiki of postgres about corruption and the slides of this FOSDEM presentation for some more info on DB corruption

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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