Date: Mon, 26 Apr 2021 16:51:03 -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: <20210426205103.GE20766@tamriel.snowman.net>
References: 
 <CAAnkphW8RpmwodKyjQhT+6jaOSLG=ERd7n-9DHr0CLRSY2W+mg@mail.gmail.com>
 <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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="ukSGJKGEUVOMqgAT"
Content-Disposition: inline
In-Reply-To: 
 <CAAnkphXozg84YJS8HfweC+qAHDefeRym7mvPcr9v1HbS-T2jkA@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210426205103.GE20766%40tamriel.snowman.net>
Precedence: bulk


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

Greetings,

* Pavel Tide (paveltide@gmail.com) wrote:
> On Mon, Apr 26, 2021 at 7:35 PM Stephen Frost <sfrost@snowman.net> wrote:
> > * Pavel Tide (paveltide@gmail.com) wrote:
> > > We have many postgres servers so we would prefer our central server to
> > > collect logs in packs, all at once, instead of the servers streaming
> > > them continuously.
> >
> > That doesn't really provide any detail as to *why* that's sensible to
> > do.  The longer you wait for the WAL to actually be pushed, the more
> > data you're potentially going to lose if something bad happens.
>=20
> Well, our backup strategy involves having several image-based backups
> and a set of corresponding WAL segments connecting those images (i1,
> i2, i3). Something like this:
>=20
> i1 - wal1 - wal2 - wal3 - wal4 - i2 - wal5 - wal6 - wal7 - wal8 - i3 - wa=
l9 ...
>=20
> And so on.
>=20
> That way, if we wanted to restore to image2-wal7 point-in-time it
> would take us less time to spin up an instance using i2 + applying
> WALs 5,6,7 rather than having to restore image1 and applying WALs from
> 1 to 7, wouldn't it?

Uh, it's not that simple though.

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

Note also that it's not obvious where exactly that ending point in the
WAL is wrt when a snapshot is taken because PG recycles WAL files and
you can't tell just by looking at the files on the filesystem which are
necessary to get back to consistency and which are from after that
point.  PG also won't complain if you stop early, it'll just think that
it reached the end of WAL and will happily come up even though the
database will be corrupt.

In general, I discourage people from trying to build their own backup
system because it's simply not that easy to do correctly and you end up
with bad backups or corrupted databases when you restore...

> > > What if we put "sleep X" insude our archive command so that it will
> > > wait until we are ready to process the bunch of logs? Is there any
> > > limit for how long PG will wait for the command to return something?
> >
> > I hate to ask just what the archive command is that you're currently
> > using..
>=20
> It's not a command, in fact. We use an in-house-built utility that
> basically places itself as an archive command once in every X minutes
> and starts shipping logs to the central server.

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

> > Just adding a 'sleep' is certainly not what I'd recommend as
> > you could end up with a pile up of WAL that's slowly trickling out after
> > a large amount of writes.  Seems like what you'd really want is to
> > archive somewhere else locally and then have a cronjob or similar
> > periodically run and push whatever is in the WAL across to the central
> > server.
>=20
> That could be an option, but we would like to avoid any "intermediate
> landing" of WAL segments.
> Copying those segments onto the same machine imposes doubled IO,
> whereas saving them somewhere else would require us to have some
> additional storage.

None of this explains why you want to wait to ship WAL to the central
server...

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJghye3AAoJEO1sijiDR2RV6jAP/ihy229ABFBdYZb2ru5raf+v
WtJ5pzGe0ByT9qedfXc5rAP832VpHetes+cspexvMx9QmxrffY20bGuwx3xs09r3
t0ag9r78gC9EvMsuTie6HESAZSScCFL1wxl5zkYq0To3YmNXh2f5nO3xJB/IjH12
AEQPvcqOn5G4IpAftL0nphtwWfQAcLdKJDtgXSo1aHH1wzdVjjcLFC9Cja1AlGTp
WV2Yvhk3TfiLsYA0FR/j9rgdzj23TwUTStmxK60LkhPjq6kGyNJ2JTew3V6ScC6M
MJvTuLGgXEfPXzMKwnWw9aZsfSeJdZwDiMSO0/XeLyHB7+fJ04P3S480L0hY27JY
Qh/027uAy2DJF5jxT5HUYSsijraVaZX9r+Yl76C+AQZDPzFqI3LqZgKZ9d5RH/Wt
DrTZqE8tP3QUIDt18AVfv3AQXNEqqPphSj/6zMpN3RDpEoHZsz36/OElmTp9R4He
wlUCtuQwbXQnfeQDSUPKsM0evr7v6CDQ63ML8RFUUVVT9Ba7ALV0docUDMsDIMs/
vH4C2cflesYn+Th9/smMKX9+HtLUK1Yl9M3r6wFNmrRw/ZrIRNE7xREzI5/tmz8V
QM3VExEOJOoFK0dFXc9jTMvkk6NlwWulWn/iP6YnASV2n/19fB0WAG39iIcRzp2Z
hUIRzBjacLxlfVUM24xf
=sYuf
-----END PGP SIGNATURE-----

--ukSGJKGEUVOMqgAT--