How to check if Postgresql files are OK

Nikhil,

* Nikhil G Daddikar (ngd@celoxis.com) wrote:

We use PostgreSQL 9 on our production server and I was wondering if
there there is a way to know when pages get corrupted.

It's not great, but there are a few options. First is to use pg_dump
across the entire database and monitor the PG logs to see if it barfs
about anything. Another thing that you can do is to write a script
which pulls out all of the data from each table using an ORDER BY which
matches some index on the table- PG will, generally, use an in-order
index traversal, which will validate the index and the heap, again, to
some extent.

I see that
there is some kind of checksum maintained from 9.3 but till then is
there a way to be notified quickly when such a thing happens? I use
a basebackup+rsync of WAL files as a disaster recovery solution.
Will this be useful when such a scenario occurs?

It really depends. Having multiple backups over time will limit the
risk that corruption gets propagated to a slave system. Also, there is
a CRC on the WAL records which are shipped, which helps a bit, but there
are still cases where corruption can get you. The best thing is to have
frequent, tested, backups.

Thanks,

Stephen

there is a wiki page aouble corruption detection：

http://wiki.postgresql.org/index.php?title=Corruption_detection

but I think avoid corruption is more important and practical than try to
check corruption：

http://blog.ringerc.id.au/2012/10/avoiding-postgresql-database-corruption.html

Jov
blog: http:amutu.com/blog <http://amutu.com/blog&gt;

2013/5/28 Nikhil G Daddikar <ngd@celoxis.com>

It really depends. Having multiple backups over time will limit the
risk that corruption gets propagated to a slave system. Also, there is
a CRC on the WAL records which are shipped, which helps a bit, but there
are still cases where corruption can get you. The best thing is to have
frequent, tested, backups.

How does one validate a backup? Is there any generally practiced way
of doing that? Or what do you mean when you say "tested" backups?

--
Amit Langote

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

* Amit Langote (amitlangote09@gmail.com) wrote:

How does one validate a backup? Is there any generally practiced way
of doing that? Or what do you mean when you say "tested" backups?

You restore from it and then query the restored database for expected
contents, at least.

Thanks,

Stephen