Re: How safe is pg_basebackup + continuous archiving?

On 29.06.2016 17:51, Kaixi Luo wrote:
> Hello,
>
> We use PostgreSQL at work and we do daily backups with pg_dump. After
> that we pg_restore the dump and check the database that there isn't
> any data corruption. As the database grows, the whole pg_dump /
> pg_restore cycle time is quickly approaching 24h, so we need to change
> strategies.
>
> We've thought about using pg_basebackup + continuous archiving as an
> alternative backup process, but I have doubts regarding the safety of
> such procedure. As far as I know, pg_basebackup is done via rsync (and
> we also archive wals using rsync), so if by any chance disk
> corruption occurs on the master server, the corruption would be
> carried over to our backup server.
>
> How can we check for backup corruption in this case? Thanks you very much.
>
> Kaixi Luo
Hello!
Only pg_dump+ data checksums turned on can make you "sure" in absence
corruption in backup. But! If at any way you've lost some data file from
relation or it zeroed by say powerloss(there were some issue with xfs in
the past) even with pg_dump you will never know it.
But there is no any other method in PG to check database for
corruption. Also PG have no checksums on clog's file. So if any
corruption is happend in this file you also will never know it.
So at now pg_basebackup+ wal archiving is like walking on the minefield
with tightly closed eyes . You never know when it will make explode!

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company