Date: Tue, 27 Apr 2021 10:59:16 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Pavel Tide <paveltide@gmail.com>
Cc: Yogesh Jadhav <pgyogesh@outlook.com>, Bruce Momjian <bruce@momjian.us>,
	pgsql-novice@lists.postgresql.org
Subject: Re: suppress empty archive_command warning message
Message-ID: <20210427145916.GG20766@tamriel.snowman.net>
References: <20210423191750.GA7630@momjian.us>
 <20210423193809.GL20766@tamriel.snowman.net>
 <PS2PR03MB370260F10EA97D77B26FDE1AC4439@PS2PR03MB3702.apcprd03.prod.outlook.com>
 <CAAnkphU200qFbKLVOZnwccvOu8WSPUm5_32iuWTu-ZdPRQOkYw@mail.gmail.com>
 <20210425180804.GQ20766@tamriel.snowman.net>
 <CAAnkphUSwjBYfiZO2w5+AwPftuY-vWQr8Br1eaWbfuCh=caX6A@mail.gmail.com>
 <20210426173500.GX20766@tamriel.snowman.net>
 <CAAnkphXozg84YJS8HfweC+qAHDefeRym7mvPcr9v1HbS-T2jkA@mail.gmail.com>
 <20210426205103.GE20766@tamriel.snowman.net>
 <CAAnkphVmKzU2K6C6id3oHyS+Z5EUJx5kHs0PC954UdFcrMYHaA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="SXYo3yt1/j3u0sAZ"
Content-Disposition: inline
In-Reply-To: 
 <CAAnkphVmKzU2K6C6id3oHyS+Z5EUJx5kHs0PC954UdFcrMYHaA@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210427145916.GG20766%40tamriel.snowman.net>
Precedence: bulk


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

Greetings,

* Pavel Tide (paveltide@gmail.com) wrote:
> > You absolutely *must* replay all of the WAL that existed at the time
> > that the snapshot was taken and only after all of that WAL has been
> > replayed can you stop WAL replay at some later point.  There's
> > additional complexities if you have to deal with multiple storage
> > devices and tablespaces since, typically, snapshots are not guaranteed
> > across those and therefore you really need to actually do a
> > pg_start_backup and a pg_stop_backup (and save the backup label file..).
>=20
> We do trigger a pg_start_backup right before taking a snapshot
> (simultaneous across all devices), and once the snapshot has been
> taken we trigger pg_stop_backup.

Ok, good, and you collect the backup_label that's returned by
pg_stop_backup and make sure to store it with that snapshot, and ensure
that when the snapshot is used you put the backup_label into place?

> > What does that mean "places itself as an archive command"?  You
> > absolutely can not just start copying WAL files out of the pg_wal
> > directory independently because PG recycles WAL files and the writes
> > into them and you don't really "know" when a WAL file has been finished
> > without taking other steps or arranging to have WAL files archived
> > through calls to archive_command...
>=20
> I mean that we use it as an archive_command.
> Instead of placing some sort of "cp %p /mnt/nfs/%f" in
> archive_command, we use '/bin/paveltide_utility %p'.

Ok, that certainly wasn't clear from what you had written before, but if
you're at least archiving it through the archive_command then it should
be alright.

> > None of this explains why you want to wait to ship WAL to the central
> > server...
>=20
> The server does not just accept the segments, but also uses its own
> database to keep a note of the segments saved on the storage, LSNs,
> and whatnot.
> Continuous shipping means a higher load on the server, which we would
> like to avoid.

This doesn't make sense though- how would continuous shipping result in
a higher load?  The amount of WAL doesn't change as it's based on the
amount of data written to the database and so delaying it just means
you're going to have spikes of activity and then periods of downtime.
Generally speaking, it's better to have a continuous lower level of
activity rather than such spikes (which is why we actually just bumped
the default for checkpoint completion target to 0.9, as an example...).
It's also better to get the WAL off the system as quickly as possible,
to minimize the risk of commits being lost.

The way we'd probably want to actually implement this would be some kind
of "pause/resume" system for archiving of WAL rather than just
suppressing messages about pretty clear misconfigurations, or suppress
the message about retrying failed attempts to archive a WAL segment, and
that'd probably be a fair bit of code (and a potential foot-gun for
users..) and I'm having a pretty hard time seeing the justification for
it.

Thanks,

Stephen

--SXYo3yt1/j3u0sAZ
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJgiCbEAAoJEO1sijiDR2RVwmQP/2WbV/lTO6h2ho+93SQBww3O
yvXMtJe+A2z4B5SmuVxtJ+9HmAbxSio/rbps3phgKbCk4syz5iEu/++HR09zw1Re
7dl+ipFaNG3PcaVdkZForRwvDDol8AM0CBCvn6dWlhNM7seESuHLpjMwpxZm54Hi
MBSN3wRZIyyYYgMeC4Bb9/rGavVsQY8Ze5V4ZhHiEm/IilMybLLfvlXbE/VoHZrZ
t4t8q59GFd/VU6002OOvTpr/cw9IyVKmD/eFxE9pWKGxK71kfwkys4MS9NHXJ8i/
4z51R/jWj53uSpbuH9NF6IFK9CCScgtzVOzevEHPjP5orib+jPfhqDLLFgcvY5nv
SDBNFgK8afkGXAQ/G9cBBjdgU8u8vCecHHtJBpDMIjPFxJxtsz34Geyc2krhQWJe
gtEi65ppefF1FFVuhg37K4aLMddNw7k2EFcWVcrEVv7bf+QR16lEEOudhX2hYDZ5
LAawEMGT67i5AnfrvSh/yqdpbavnH+qNWtdPmK3Lw1wadNtxHfc4A6aTklUKsrlT
qX3UJc6rfZk7LChJb82WmOUcntrCvfwuuXdtZBp6rhxf27rH7eAjBx4ZQWCf2Ms3
XjpZ+4xaWAzcmua6G3LOqQ4MnYXh56Pn7BRM1CyiVKggOJtuPG0TxStkRQNbhJim
FDC1D1ce4VGVfySOhsIg
=KqHj
-----END PGP SIGNATURE-----

--SXYo3yt1/j3u0sAZ--