From: Stephen Frost <sfrost@snowman.net>
To: flumbador@virgilio.it
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Issue on restore / recover
Date: Tue, 2 Jan 2018 10:16:25 -0500
Message-ID: <20180102151625.GX2416@tamriel.snowman.net> (raw)
In-Reply-To: <524817289.230206.1514560886805@mail.virgilio.it>
References: <524817289.230206.1514560886805@mail.virgilio.it>
Greetings,
* flumbador@virgilio.it (flumbador@virgilio.it) wrote:
> I have restored and recovered a Postgresql 9.4.9 from an hot backup. The backup is a filesystem copy taken while the Postgrsql is in backup mode (I mean between start and stop backup).
>
> During the restore 3 files were missing; these three files belong to a table with high transaction workload, and for sure during the backup many transactions had modified this table and those missing files. What is surprising to me is that even if the files were missing the recover phase ended successfully. I expect an error (for example file not found) raised when postgresql try to apply the wal entries related to this table and those files. After the recover I find that these three file has been created during recover but when I try to query the table I get the error:
>
> db4=# select count(*) from pgbench_accounts ;
>
> ERROR: could not read block 1999996 in file "pg_tblspc/16471/PG_9.4_201409291/16474/16593.15": read only 0 of 8192 bytes
>
> This error confirm that my database is corrupted! The question is: why during the recover phase Postgresql doesn't throw any errors? I think that is better to know immediately that we have restored our database from a corrupted backup rather then discover the issue after maybe a long time when a query is executed on the corrupted table.
How are you handling WAL archiving? Based on the symptoms, my first
guess is that you aren't putting the backup_label file in place (or
you're removing it) before PG starts doing WAL replay, which makes PG
think it's just doing crash recovery and so it does *some* WAL replay
but not *all* that it needs to do (it needs to start farther back, where
the backup started), which can certainly result in a corrupt backup.
I'd strongly suggest you look at the various backup tools which exist
for PostgreSQL and know how to properly do backups and restores instead
of trying to roll your own. Personally, I'd suggest pgBackRest but
there are other tools out there such as barman and WAL-E/G that you
might consider.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, flumbador@virgilio.it, pgsql-admin@lists.postgresql.org
Subject: Re: Issue on restore / recover
In-Reply-To: <20180102151625.GX2416@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox