Date: Mon, 23 Jan 2017 01:48:39 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Vladimir Borodin <root@simply.name>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Backup taking long time !!!
Message-ID: <20170123064839.GT18360@tamriel.snowman.net>
References: <b0748fe40949446283068a4d2d06c158@cyient.com>
 <CAFj8pRBFDh=hsghE8v5F71KztyczR7iWBXGJBOLLhUQDwUh8MQ@mail.gmail.com>
 <20170120122224.GF18360@tamriel.snowman.net>
 <DE553F58-646E-49E8-94D1-31794C3E9040@simply.name>
 <20170120134054.GH18360@tamriel.snowman.net>
 <7696A57C-C871-4A45-9141-FFD5F75CCB65@simply.name>
 <20170120150646.GP18360@tamriel.snowman.net>
 <17D95770-42B6-4FF5-80FF-465C77119A27@simply.name>
 <20170120165922.GS18360@tamriel.snowman.net>
 <D60EACF0-5D9B-4CCF-B526-A645319E7AF4@simply.name>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Xm1wk/WBwRlpvc4I"
Content-Disposition: inline
In-Reply-To: <D60EACF0-5D9B-4CCF-B526-A645319E7AF4@simply.name>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--Xm1wk/WBwRlpvc4I
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Vladimir,

* Vladimir Borodin (root@simply.name) wrote:
> > 20 =D1=8F=D0=BD=D0=B2. 2017 =D0=B3., =D0=B2 19:59, Stephen Frost <sfros=
t@snowman.net> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> >>> How are you testing your backups..?  Do you have page-level checksums
> >>> enabled on your database? =20
> >>=20
> >> Yep, we use checksums. We restore latest backup with recovery_target =
=3D 'immediate' and do COPY tablename TO '/dev/null=E2=80=99 with checking =
exit code for each table in each database (in several threads, of course).
> >=20
> > Right, unfortunately that only checks the heap pages, it won't help with
> > corruption happening in an index file or other files which have a
> > checksum.
>=20
> That=E2=80=99s fine for us because indexes could be rebuilt. The main ide=
a is the guarantee that data would not be lost.

Fair enough, however, if you don't check that the indexes are valid then
you could end up with corruption in the database if you start operating
against such a recovered database.

Consider what happens on a 'primary key' lookup or insert- if the index
doesn't find a conflicting tuple (perhaps because the index is corrupt),
then it will happily allow the INSERT to go through, even though it
should have been prevented.

Indexes are a *really* important component to having a valid database.
If you aren't checking the validity of them, then you're running the
risk of being exposed to corruption in them, either ongoing or when
restoring.

If you *always* rebuild your indexes when restoring from a backup, then
you should be fine, of course, but if you're going to do that then you
might consider using pg_dump instead, which would do that and validate
all foreign key references too.

Thanks!

Stephen

--Xm1wk/WBwRlpvc4I
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

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

iQIcBAEBCgAGBQJYhadHAAoJEO1sijiDR2RVNPAP/3hXQjZtbKrB1DW3CfMxugu2
16WtKNN0g1JU3YX+FddcihXVC0duPIGa2bHbh2VwTVOHQlJbkY3Jw6GU9q5K8GeE
7lVVzVmJjbKMpsiWmWCImIJ5C4eOlc6R175y9Bac7L1XbX+Yqm3Dtx+o4dd2LFZd
PzsSIcq9ngiKiTkjouZmhcXcSpavjwbFoMLGBqnKsxnqu/n0IfvfB0KeqFD8P0J/
Qe3jKeONA+RZkeU9HakFWSHJP1iQWnBITTGf0OvMZXADwJSSBooycdyZk9Z+pFQo
qaSV2zri8sQwsoCY8lJjHtYnslW0XHOvcmbL5hPazxOGlS6DJGb/dsohpr/ZCQ71
XX/iPvLLyeg/XyEn9Fs47EMKdSC8OkW1UsS8avTWwLMLNKp4tL5fL06k+B3uBYqE
dbORkRjvFChIgrbmgaFpLQfNIA8AUfeZmdrJZqs3LfL0h+CmvcsUaMiseh7MQAkI
AJK1lfj7ZPBArviGb3+z7ExUhMmMl23XxnzTgCJVo/lPBZOJX1ic4JwIqC/V8J9B
jERw6WmIWIPhvno09rAD0hokdha91iA1o8UFum0lCqxYVjSKT4rAVyIFD1TRzeh0
K8Z7yrtTOr84k60CULXykofTWdVU0cCBXvng9Rb2Kjky2q5VVyNuGnRpFuQIOCzH
8zXPwA8EPPmKLV6j0or/
=EDNA
-----END PGP SIGNATURE-----

--Xm1wk/WBwRlpvc4I--