Date: Fri, 20 Jan 2017 11:59:22 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Vladimir Borodin <root@simply.name>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
	Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>,
	"Madusudanan.B.N" <b.n.madusudanan@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	David Steele <david@pgmasters.net>
Subject: Re: Backup taking long time !!!
Message-ID: <20170120165922.GS18360@tamriel.snowman.net>
References: <23dc5b1ea9604ec3afdaabdd15491085@cyient.com>
 <CAFj8pRAHx-Wt4fyJmF=YH-F2_S=mO2E+=JDx+qnunkDP5_FPGA@mail.gmail.com>
 <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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="+solAT/muKCA45Gw"
Content-Disposition: inline
In-Reply-To: <17D95770-42B6-4FF5-80FF-465C77119A27@simply.name>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--+solAT/muKCA45Gw
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 18:06, Stephen Frost <sfros=
t@snowman.net> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> >=20
> > Right, without incremental or compressed backups, you'd have to have
> > room for 7 full copies of your database.  Have you looked at what your
> > incrementals would be like with file-level incrementals and compression?
>=20
> Most of our DBs can=E2=80=99t use partitioning over time-series fields, s=
o we have a lot of datafiles in which only a few pages have been modified. =
So file-level increments didn=E2=80=99t really work for us. And we didn=E2=
=80=99t use compression in barman before patching it because single-threade=
d compression sucks.

Interesting.  That's certainly the kind of use-case we are thinking
about for pgbackrest's page-level incremental support.  Hopefully it
won't be too much longer before we add support for it.

> > 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).

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.

> > pgbackrest recently added the ability to
> > check PG page-level checksums during a backup and report issues.
>=20
> Sounds interesting, should take a look.

It's done with a C library that's optional and not yet included in the
packages on apt/yum.p.o, though we hope it will be soon.  The C library
is based, unsurprisingly, on the PG backend code and so should be pretty
fast.  All of the checking is done on whole pgbackrest blocks, in
stream, so it doesn't slow down the backup process too much.

Thanks!

Stephen

--+solAT/muKCA45Gw
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

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

iQIcBAEBCgAGBQJYgkHqAAoJEO1sijiDR2RVnwMP/2MikVucA57ybHHDk7BoXJkW
vtI18rKfErjlNDz/Y8rpBLeohxzcuPhcy3qXdqnGTKvv7GpkyMfRSmaTFvNA9MgS
JKQcB4tPiT5ksb5hY3tTvKcceEG1ufgxu/quZYpY79kyKXejCzUaR4izfvMH9O9n
mvEeYwTj/3+geF7uFlnGBceyd5HBYYBEuiwEZFXUTMOpEixidZWxx8efbD1kYovT
roFL5I9zwyTaENz6uhlVGD58Vp4iFQYAHpSdcDo4LpppQO4dAHxEZAEZ88bIeKjZ
kHRrFfyLURfCuRbNt0NXwLI3zcIPW3MojPV7ovYiJ9V0NXqSiYAerMR4IwYfQnMn
6sxVgxpKpdtDsMMpGagV9hMroHLrvrtFUFWwLPDB4rB+fMcSrKbnD32hWv2KW7Rc
HGJMRZNkmPESx0eWopqlAdXGK9wdRQ8v1MhSj/+GAesMcE3EbW2xnJAMRjGP/ylH
FNyvccyEgClK1Tx6TsKT/OUT48G4Yz6vgz1be+zvBlMAYxJQhK5/4Y6zU3agQvx9
IXZnVxQfr41DQ5Df2gyLzm+BSdyouroTfP+QtiAUCkGv1HCyiR8g2qzun1vye6EY
Kt3Pe1XTgHgsymOs/ONvWsMtjxRydnZnSGEiAKgPr1KGOgv9NSO4jhGrhTeQVehz
CdGZ4FbqnE3od4gus2sr
=huEr
-----END PGP SIGNATURE-----

--+solAT/muKCA45Gw--