Re: suppress empty archive_command warning message

On Mon, Apr 26, 2021 at 7:35 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> Greetings,
>
> * Pavel Tide (paveltide(at)gmail(dot)com) wrote:
> > On Sun, Apr 25, 2021 at 8:08 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > > * Pavel Tide (paveltide(at)gmail(dot)com) wrote:
> > > > Right, but /bin/false will make pgsql to retry the command again and again
> > > > and that will throw warnings as well (AFAIK).
> > >
> > > It'll say it's retrying, sure.
> >
> > Right, but after 3 retries (1 second between them) it will give a
> > warning anyway:
> >
> > WARNING: archiving write-ahead log file "000000030000000500000084"
> > failed too many times, will try again later
> >
> > Which brings us back to the initial problem.
>
> I'm not entirely sure that I agree with it being a "problem", but, yes,
> that'll happen.
>
> > > > I would like to copy WALs once in every "X" minutes instead of copying them
> > > > continuously.
> > >
> > > PG is only going to call the archice command when you've written 16MB of
> > > WAL (unless you also have archive timeout set...).
> > >
> > > Maybe you can clarify your use-case as to why you only want to copy them
> > > once every 'X' minutes..?
> [...]
> > 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.

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:

i1 - wal1 - wal2 - wal3 - wal4 - i2 - wal5 - wal6 - wal7 - wal8 - i3 - wal9 ...

And so on.

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?

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

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.

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

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.

Thanks