Date: Mon, 23 Jan 2017 12:47:37 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Jim Nasby <Jim.Nasby@bluetreble.com>,
	julyanto SUTANDANG <julyanto@equnix.co.id>,
	Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Backup taking long time !!!
Message-ID: <20170123174737.GK18360@tamriel.snowman.net>
References: <0fb0c8c3f69340a3a7d70b1c39fa2b2c@cyient.com>
 <CAGu3fER+B6dFk=7F+DEMPPOSerYZs0HZMOfVAkbKs5Gdi-B-mg@mail.gmail.com>
 <20170122145511.GD18360@tamriel.snowman.net>
 <CAGu3fESxPSSqbKrV-UHdVnWnLK0vYk2fTmxjNOoEtevW39j1uw@mail.gmail.com>
 <20170122163748.GG18360@tamriel.snowman.net>
 <CAGu3fEREP2Vg_n6nqOoOg0Rv_XeHYFrHVKQUQXWj6TxVFUNZ=w@mail.gmail.com>
 <20170122173245.GH18360@tamriel.snowman.net>
 <e71cdceb-779c-498d-f6ce-66b9002b54cf@BlueTreble.com>
 <CAMkU=1xn5LRC8=XPB7bucy0SpGPqh0UuMEARfipC+E985-6czg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="xpH2Lusg6ToNDEFe"
Content-Disposition: inline
In-Reply-To: 
 <CAMkU=1xn5LRC8=XPB7bucy0SpGPqh0UuMEARfipC+E985-6czg@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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

* Jeff Janes (jeff.janes@gmail.com) wrote:
> On Mon, Jan 23, 2017 at 7:28 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wro=
te:
> > On 1/22/17 11:32 AM, Stephen Frost wrote:
> >> The 1-second window concern is regarding the validity of a subsequent
> >> incremental backup.
> >
> > BTW, there's a simpler scenario here:
> >
> > Postgres touches file.
> > rsync notices file has different timestamp, starts copying.
> > Postgres touches file again.
> >
> > If those 3 steps happen in the same second, you now have an invalid
> > backup. There's probably other scenarios as well.

Ah, yeah, I think the outline I had was why we decided that even a file
with the same timestamp as the start of the backup couldn't be trusted.

> To be clear, you don't have an invalid backup *now*, as replay of the WAL
> will fix it up.  You will have an invalid backup next time you take a
> backup, using a copy of the backup you just took now as the rsync
> destination of that future backup.

Correct.

> If you were to actually fire up a copy of the backup and go through
> recovery, then shut it down, and then use that post-recovery copy as the
> destination of the rsync, would that eliminate the risk (barring clock sk=
ew
> between systems)?

I believe it would *change* things, but not eliminate the risk- consider
this: what's the timestamp going to be on the files that were modified
through WAL recovery?  It would be *after* the backup was done.  I
believe (but not sure) that rsync will still copy the file if there's
any difference in timestamp, but it's technically possible that you
could get really unlikely and have the same post-backup timestamp as the
file ends up having when the following backup is done, meaning that the
file isn't copied even though its contents are no longer the same (the
primary server's copy has whatever was written to that file in the same
second that the restored server was writing the WAL replay into the
file).

Admittedly, that's pretty unlikely, but it's not impossible and that's
where you can get into *serious* trouble because it becomes darn near
impossible to figure out what the heck went wrong, and that's just not
cool with backups.

Do it properly, or use something that does.  This isn't where you want
to be playing fast-and-loose.

> > In short, if you're using rsync, it's *critical* that you give it the
> > --checksum option, which tells rsync to ignore file size and timestamp.
>=20
> Which unfortunately obliterates much of the point of using rsync for many
> people.  You can still save on bandwidth, but not on local IO on each end.

No, it means that rsync is *not* a good tool for doing incremental
backups of PG.  Would be great if we could get more people to understand
that.

'cp' is an equally inappropriate and bad tool for doing WAL archiving,
btw.  Would be great if our docs were clear on that.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJYhkG5AAoJEO1sijiDR2RVqVEQAIBEW4ptXn3irjB7eAbXFjV3
JGByTotbfy6iTMeGaJrefLbYtcDvzPiAXYnGFwNeWqh6hg7FLhTt1T/cuU92JqXH
2ZTV0ry7Sc1bb0OaALgTky9wCUpGQb9SyicgjPMhB1JVVi0St+P3sVZSc9wamNfi
W+7dBS7JVHqIjj0lPvoHaIHaBJtpzZQbQ4si/fVmRULu5JIwql01gDfN3496n3uE
r5vkPVEhuzgRkXu6cl233Wp8kdCf9eBJfuNF1fsry5Z7D1T8dkI3HX6dXLJNHaGu
+H7h1VfAJvQx1XvUo5CsIrSEDqOaVbaNY5CkHwurNpMDGiCXyk0Uy5/Nkej+Y251
u/jwzyRfCU+0gp1o6nlSgV2Dlie8wGKMipRpigoVXlus4H+TdAPKuvE37jGPJQv9
S2ccMykQ/DvxTCaic0mYFHY+vLTLHVGkTi4biFrgqLjgN37029bSdH0l4T7AltwR
cd9yot75iGyyof0/15jqHLFD8lb7hswniswc85OYcAPNym9oqIvoTTSXDy50GvAC
3EvUcZJZkPl6QOQpOfdR7m+DnKwT00qxOAb/NeuPp9kVrL0tA4k3VoTTrWpQSIg7
XuQ7Blx8va1/CAg/H4ArTNIkl1f0nYwOSVnnqB/GUvwR93GqHoWEiJ00FWFQ//hd
mrJYGlYTeP/gf6dH0gi+
=2uF7
-----END PGP SIGNATURE-----

--xpH2Lusg6ToNDEFe--