Date: Tue, 2 Jan 2018 10:16:25 -0500
From: Stephen Frost <sfrost@snowman.net>
To: flumbador@virgilio.it
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Issue on  restore / recover
Message-ID: <20180102151625.GX2416@tamriel.snowman.net>
References: <524817289.230206.1514560886805@mail.virgilio.it>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Tln/wzp9jsNjmSUr"
Content-Disposition: inline
In-Reply-To: <524817289.230206.1514560886805@mail.virgilio.it>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--Tln/wzp9jsNjmSUr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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).
>=20
> During the restore 3 files were missing; these three files belong to a ta=
ble with high transaction workload, and for sure during the backup many tra=
nsactions had modified this table and those missing files. What is surprisi=
ng to me is that even if the files were missing the recover phase ended suc=
cessfully. I expect an error (for example file not found) raised when postg=
resql try to apply the wal entries related to this table and those files. A=
fter the recover I find that these three file has been created during recov=
er but when I try to query the table I get the error:
>=20
> db4=3D# select count(*) from pgbench_accounts ;
>=20
> ERROR: could not read block 1999996 in file "pg_tblspc/16471/PG_9.4_20140=
9291/16474/16593.15": read only 0 of 8192 bytes
>=20
> This error confirm that my database is corrupted! The question is: why du=
ring 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 corru=
pted backup rather then discover the issue after maybe a long time when a q=
uery 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

--Tln/wzp9jsNjmSUr
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJaS6JIAAoJEO1sijiDR2RV9zMP/iuLT+0dPTKYMgYwVn3yaQgV
35dqEJuObED++s0GpULPcvOilCS/tKZ78MHlR0tTmWst/0R5V6lBqw6Jy08KC3AL
buO9iK4GWfurr7F0rIGpHC+cyCkTTMXGaxpshxeMTULwQaMzNC/z5jwEoHvY+OtI
gVCVSB/4FraLwhpj2kxkFapHo8Ln1BIZLYxrexrl5TRfjCWUUNSF9GQ8gJNxjapA
htvJjQBsfLcaQd4Gli1u5hlFVgW/iBE9HVQax+FjW9sF5/V7OudfEoi+LmuXFdXV
ZoirIr7HoZSwLQcxiqCwwMr+kia3YbXtHlMXCz8QcPKDpW0mjVwNqtMJnAmnT3am
e64OGWY861u6rzY4bewq/9jXY88PoG1kUucBSqNWSDeoBAHCZJ9529DchhGRLkRY
nD66XFNVvy1+HqCN1/JXwHnWqgfgdXfPwlEPjgMu8iNRdPqbANdz255+XfBbsmNS
yzy7LFBQYmQXtU8h7gwBn2xTSkyBIDwHJZzYjy4QmWx225OuSvrJy5N4OMsqCW2n
bGLzgcialu4eAQFuCrlSZQhHA06imLf//sAvZiREQ3X3aV67RtRbXkqBEFiCwvcZ
REno7r4SqnwHIeUTOaM9riqbIt/6OMpcaqrYIbYj9zvI1+RdbGEp0ZNr+NyGbawL
2jlYgsZFjGqMdp3184+J
=AQua
-----END PGP SIGNATURE-----

--Tln/wzp9jsNjmSUr--