Subject: Re: PATCH: Exclude unlogged tables from base backups
To: Stephen Frost <sfrost@snowman.net>, Andres Freund <andres@anarazel.de>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Michael Paquier <michael.paquier@gmail.com>
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>
 <20171213014804.GH4628@tamriel.snowman.net>
From: David Steele <david@pgmasters.net>
Message-ID: <d3f60151-03b3-9443-e07c-aaaf42e9dd55@pgmasters.net>
Date: Wed, 13 Dec 2017 07:34:36 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.5.0
MIME-Version: 1.0
In-Reply-To: <20171213014804.GH4628@tamriel.snowman.net>
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature";
 boundary="7mc9LfKLideQRTE9bFrU2rbLlTMpXig6F"
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--7mc9LfKLideQRTE9bFrU2rbLlTMpXig6F
Content-Type: multipart/mixed; boundary="MX4SSF4JX1ACvfbpDOdL2QBcC3qcDkGjx";
 protected-headers="v1"
From: David Steele <david@pgmasters.net>
To: Stephen Frost <sfrost@snowman.net>, Andres Freund <andres@anarazel.de>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Michael Paquier <michael.paquier@gmail.com>
Message-ID: <d3f60151-03b3-9443-e07c-aaaf42e9dd55@pgmasters.net>
Subject: Re: PATCH: Exclude unlogged tables from base backups
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>
 <20171213014804.GH4628@tamriel.snowman.net>
In-Reply-To: <20171213014804.GH4628@tamriel.snowman.net>

--MX4SSF4JX1ACvfbpDOdL2QBcC3qcDkGjx
Content-Type: text/plain; charset=windows-1252
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable

On 12/12/17 8:48 PM, Stephen Frost wrote:
> Andres,
>=20
> * 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 ba=
cked up
>>> that don't need to be.  The main fork is unlikely to be very large at=
 that
>>> point so it doesn't seem like a big deal.
>>>
>>> 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.
>>
>> It's quite different - in the recovery case there's no other write
>> activity going on. But on a normally running cluster the persistence o=
f
>> existing tables can get changed, and oids can get recycled.  What
>> guarantees that between the time you checked for the init fork the tab=
le
>> hasn't been dropped, the oid reused and now a permanent relation is in=

>> its place?
>=20
> 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.
>=20
> 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.

Yes - that's the way I see it.  At least when I'm not tired from a day
of coding like I was last night...

> 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 an=
d
> will be replayed over top and will take care of this.

Files can be copied in any order, so if an OID is recycled the backup
could copy its first, second, or nth incarnation.  It doesn't really
matter since all of it will be clobbered by WAL replay.

The new base backup code just does the non-init fork removal in advance,
following the same rules that would apply on recovery given the same
file set.

--=20
-David
david@pgmasters.net


--MX4SSF4JX1ACvfbpDOdL2QBcC3qcDkGjx--

--7mc9LfKLideQRTE9bFrU2rbLlTMpXig6F
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - https://gpgtools.org

iQIcBAEBCgAGBQJaMR5dAAoJEIA2uIJQ5SFAv+8P/14wLP/gyqu5ZiSrSyURfSDj
0f0qthy01iGQj21eIu2nfWF1KEB8FuJLwi/H1EYrBEhIXgg3r19bJMlMl0a7YvTP
rREeCLMcch3goPnXOm7ZW+MSn5N92JBIu7LIUbh1PrxOGCRSQSicpAkO3VZfhW4f
NGel7wntcHQJmqCE87bhAiI6imQFnuFdQu10TUYzmMsunKF1QGpJ7iibaIDyhlvZ
l894eNN/yMFyUg1kwoVynnXVDAwAGHdMH5EMrOYzSvdN09JIGUhNLLiNcr0Iy68B
6cTnKhP9rm082l90hTwINh2iJ1mEEj03IRM7tftBB0+wC7+al0S9ETyCajQmbZ1l
EszUkzi4mSliivTuuR6vzk/xigXGZqNr5urTdF/ayKHKob51Mape030TlR56ON1h
AEfkjgKJTHvG5r7YQbEoNPNwrKC+UIv/Wc2ojVXnEMHb5of6q0Yg44N7Xp1LY0ke
0kxq2q0O+4Filb6CJEKtCPIsejdBhOaodMOjfb+6mTemtVLwMIkPrfYoulguf1ut
imDap9KSfBQubfBeimZs0Tz2mjj2FjIxA2jtDqrKxp3KlgRnSD8zycw1u6GbkV89
P+qzbTlFSiBmIALUzKbXsrSQwL5wwdVfeDRkO4qofRLzjZ8miCbgLzsesnVqBsg3
l3iqrI9j/nWwpQyTj9iK
=Zbre
-----END PGP SIGNATURE-----

--7mc9LfKLideQRTE9bFrU2rbLlTMpXig6F--