5

I have a postgres 9.3 db running on ubuntu server.
About a month ago we've had some problems with the hardware on the server which was resolved by our VPS hosting company.

The problem was resolved fairly quickly and everything seemed to be working fine.
We have backups running using barman that is on a different server - and backups and restores were working (I checked).

The first sign of data corruption was few days ago: I decided to do a full pg_dump on our DB as I do every once in a while, and it failed (invalid page header in block...) - The data seemed to have been corrupted a long time ago - around the time of the hardware problem (that was the date on the corrupted record). I resorted to finding the corrupted record and I deleted it and restored it manually.

After that I was able to do a full pg_dump.

In order to check for additional corruption - I've set up different db server from a backup and run pg_repack on all the tables to verify that I'm able to rebuild all the indexes and tables.

My questions are:
1. How can I be certain that I don't have any additional corruption in my DB?

2. What can I do to periodically check my data integrity?
3. What else can I do to verify the integrity of our DB besides dumping the whole DB and re-indexing it (which I already did)?

P.S - I don't have block checksums enabled.

  • 1
    If you can afford some downtime, and if you can pg_dump and later on pg_restore everything on a different server, I'd check that the SELECT count(*) FROM every table coincide with the source (and/or your exepctations)... If that's meaningful, Id' assume that the restored database is not corrupt. Use this new one... If that's not meaningful... – joanolo May 13 '17 at 20:25
  • What if I select count(*) from every table in my existing DB? Or do dump and restore to a different server and verify that it is working and that the restore DB count results make sense. Isn't that enough? I can afford some down time but I really prefer not to. – Rubinsh May 13 '17 at 20:48
  • 1
    Isn't that enough? Possibly yes. And most probably, if something went wrong with your disk, you might have a few blocks with some bad data, and the rest is ok... Your choice depends mostly on the level of safety that you need. – joanolo May 13 '17 at 21:16
  • I had the DB moved to a different storage when I found the first sign of corruption so I can say that the disk is OK. – Rubinsh May 14 '17 at 3:13
  • I also did a full pg_dump and pg_restore and everything worked. but is there any normal process for checking the DB? I find it really disturbing that I can't really automate some monitor to check this – Rubinsh May 14 '17 at 11:18
3

If you have initialized your cluster with checksum, you can control this while backuping with pg_backrest.

PostgreSQL has supported page-level checksums since 9.3. If page checksums are enabled pgBackRest will validate the checksums for every file that is copied during a backup.

You will find more information about cheksums here: https://www.postgresql.org/docs/current/static/app-initdb.html#APP-INITDB-DATA-CHECKSUMS

You can also make a pg_dump to /dev/null (if you don't have enough space to keep a logical dump). This should ensure you that you can read each data block.

PGDG had a discussion this year beacuse some wanted to activate checksum by default. You can find it there: http://www.postgresql-archive.org/Checksums-by-default-td5940158.html

3

As stated before, there are no corruption specific tools in core PostgreSQL similar to those in SQL Server (DBCC CHECKDB) or Oracle. Which does not mean that you cannot look after corruption and try to prevent it as much as possible. A few thoughts:

  • Use good hardware. This hardware should properly honor fsync. Not tunable in your case, but important nevertheless.
  • Use checksums when you initialize your cluster. Sure there is a little penalty for doing so, but it really is worth the hassle. You could create a new cluster, dump and reload your database in this new instance and enjoy a more robust environment. It is available since 9.3, so fine for you.
  • It's good to have an automated physical backup with Barman, but this will not check corruption. Only pg_dump (which will read all rows in all tables) will give you insurance against table corruption. This is something you should definetely automate. If you don't have the place, redirect pg_dump to /dev/null. Corrupted indexes can always be rebuilt if you have a correct table. I think there are some index corruption finding tools out there.
  • 1
    I also had a corrupted index which did not cause pg_dump to fail - only pg_repack/vacuum full where failing because of this - so I guess that just doing a pg_dump is not enough (maybe automate a pg_dump and a pg_restore on a different server?) I will most probably re-create the cluster with checksums and restore it with pg_restore - my main problem is the downtime that comes along with it. Is it possible to enable checksum only on a slave server with streaming replication? or will replication not work in that case? – Rubinsh May 15 '17 at 8:33
  • 1
    It's always good to have an automated backup/restore mechanism and I can only encourage you to have one. If you want to replicate between a cluster without checksum and one with checksum and minimize downtime, you will have to go with logical replication (Slony, Londiste, Bucardo, ...). It will be long to set up but the switch will be shorter. – KookieMonster May 15 '17 at 8:57
  • When you're "pg_dumping", you read only data, not indexes. With pg_restore, it will recreate your index from scratch and you still won't know if your index is corrupted on your production server. Of course, having an automated backup/restore mechanism is good, but I suggest to use physical backup instead of logical. – Arkhena May 16 '17 at 7:11
  • I agree with your first part, I even said "Only pg_dump [...] will give you insurance against table corruption". And I highly recommend having a physical backup solution such as the excellent barman, but please plan a logical backup from time to time too if you care about about corruption. – KookieMonster May 16 '17 at 7:25

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.