Re: Does PostgreSQL check database integrity at startup?

Greetings,

* Edson Carlos Ericksson Richter (richter(at)simkorp(dot)com(dot)br) wrote:
> Right now, I do prefer to use pg_basebackup instead - but sometimes
> (when database is just too large), rsync seems more reliable (maybe
> it is the cause of the problem).

I don't generally recommend rsync for various reasons, but if you make
sure to call pg_start/stop_backup(), keep track of all your WAL and
verify everything ends up written to disk then it should work. Don't
use it for incrementals though- there are cases where that can fail.

> Anyway, instead digging into rsync functionality (or bugs - I doubt,
> but who knows?), I do prefer to have a script I can run to check if
> there is obvious failures in standby servers.

As mentioned, zero-byte files can be perfectly valid. PostgreSQL does
have page-level CRCs, if you initialized your database with them (which
I would strongly recommend). There are also backup tools which will
verify those checksums when performing a backup of the system. In
addition to that, you can do parallel backup and restore which can
reduce the downtime for doing restores quite a bit (though this will
depend on what you're bottleneck is, of course). I'd suggest you take a
look at pgBackRest, though I think that other solutions now also have
all of these features (though they all have their own features too).

Thanks!

Stephen