Date: Tue, 12 Dec 2017 20:48:05 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Andres Freund <andres@anarazel.de>
Cc: David Steele <david@pgmasters.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: PATCH: Exclude unlogged tables from base backups
Message-ID: <20171213014804.GH4628@tamriel.snowman.net>
References: <04791bab-cb04-ba43-e9c0-664a4c1ffb2c@pgmasters.net>
 <20171212225234.jqdwd4sp663l4znp@alap3.anarazel.de>
 <5e428ad7-f37f-6479-aabf-2c8b9318fb79@pgmasters.net>
 <20171212230740.evr7rvkzz5bztdg6@alap3.anarazel.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="hYO/T8b2nCy71qWY"
Content-Disposition: inline
In-Reply-To: <20171212230740.evr7rvkzz5bztdg6@alap3.anarazel.de>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk


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

Andres,

* Andres Freund (andres@anarazel.de) wrote:
> On 2017-12-12 18:04:44 -0500, David Steele wrote:
> > If the forks are written out of order (i.e. main before init), which is
> > definitely possible, then I think worst case is some files will be back=
ed up
> > that don't need to be.  The main fork is unlikely to be very large at t=
hat
> > point so it doesn't seem like a big deal.
> >=20
> > I don't see this as any different than what happens during recovery. The
> > unlogged forks are cleaned / re-inited before replay starts which is the
> > same thing we are doing here.
>=20
> It's quite different - in the recovery case there's no other write
> activity going on. But on a normally running cluster the persistence of
> existing tables can get changed, and oids can get recycled.  What
> guarantees that between the time you checked for the init fork the table
> hasn't been dropped, the oid reused and now a permanent relation is in
> its place?

We *are* actually talking about the recovery case here because this is a
backup that's happening and WAL replay will be happening after the
pg_basebackup is done and then the backup restored somewhere and PG
started up again.

If the persistence is changed then the table will be written into the
WAL, no?  All of the WAL generated during a backup (which is what we're
talking about here) has to be replayed after the restore is done and is
before the database is considered consistent, so none of this matters,
as far as I can see, because the drop table or alter table logged or
anything else will be in the WAL that ends up getting replayed.

If that's not correct, then isn't there a live issue here with how
backups are happening today with unlogged tables and online backups?

I don't think there is, because, as David points out, the unlogged
tables are cleaned up first and then WAL replay happens during recovery,
so the init fork will cause the relation to be overwritten, but then
later the logged 'drop table' and subsequent re-use of the relfilenode
to create a new table (or persistence change) will all be in the WAL and
will be replayed over top and will take care of this.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJaMIbUAAoJEO1sijiDR2RVW3UQAMgPkLf0+vrZToKID7c6xPFJ
rhKuWNP2nsuHE+3Zx4hQJjloXno6noj90nqOanwriNFlEuCtkuRSBum+1ayK+wCm
iqc9wJktVBtwWdK8hO2UWTeWkvXSsHEF9Ryqkbwh3WRUqgVWSCBBxffCxfM72x1a
/QWwDvgbLqB6wqI//0AD7N9kREauojGTvFakjWqMnZ60WA4RXFcorBVH+zHQxpV+
TWzKzHyERomEvnnHgViozwVwXJs2IWxspcqdNpF/I1JZq7IQ1ztKI+wJw/q+030P
I2b1A1e/qLjGoDkaOOuKcMXXjkUzm3FACkL2zRfJ5XkVuTRy1Nlk50CQDH+vjjh1
5wOal+NYIFqyuctRC3tCzzjBbEeGxlrVc2oOW6X09fdzv+yf1NTJN6PzYMJGam6i
n8RgF7oG6Hg+TG/IJjbNP0t4gK/SHVlWx+0pCXwDLCjjuSxETDqRXadmPDkXggJf
W/1WIVyFfZVoFdeeWgEDMpTntGlKP47EQO+EDo+PW7Vi2epIHkDhgqOZdTZcAZDZ
cAQZpVZy0LzjRt21LUGOPTIDGieWtA14d0VvPw3hX14xIMNqgvSh7TFvgDbpcfxS
OZIjcH0i30OottFRI8dHusEkOFF0gvzA0ypxoHWa7ztGqt1rWuyanOKdbj+qgCGU
EnX3AVBKXPxdpmCGm4re
=W6iq
-----END PGP SIGNATURE-----

--hYO/T8b2nCy71qWY--