Subject: suppress empty archive_command warning message

From: Pavel Tide <[email-masked]>
Date: Fri, 23 Apr 2021 20:48:39 +0200

Hello everyone,

Is there a way to suppress the WARNING that is thrown in PG logs if
archive_mode is enabled but the archive command is empty? Maybe some
configuration key which I have overlooked?

Thanks!

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 23 Apr 2021 15:17:50 -0400

> configuration key which I have overlooked?

Have you tried putting "/bin/true" in there, for non-Windows?

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

---

From: Keith <[email-masked]>
Date: Fri, 23 Apr 2021 15:18:14 -0400

> configuration key which I have overlooked?

If you're on linux, set the archive_command to "/bin/true"

Keith

---

From: Stephen Frost <[email-masked]>
Date: Fri, 23 Apr 2021 15:38:09 -0400

Greetings,

> Have you tried putting "/bin/true" in there, for non-Windows?

Erm, that would cause all WAL to just be silently thrown away..  To have
the same behavior as having it be unset, you'd set it to '/bin/false'.

Of course, which you want depends, but generally you should be using a
real archive command to archive your WAL, not throwing it away.

Thanks,

Stephen

---

From: Yogesh Jadhav <[email-masked]>
Date: Sun, 25 Apr 2021 13:41:58 +0530

Greetings,

Why would anyone want to set archive_mode on and empty archive_command?

Thanks,
Yogesh

> On 24-Apr-2021, at 1:08 AM, Stephen Frost <[email-masked]> wrote:
> 
> Greetings,
>

---

From: Pavel Tide <[email-masked]>
Date: Sun, 25 Apr 2021 15:33:54 +0200

Hi,

Thank you for your suggestions

Stephen,

Right, but /bin/false will make pgsql to retry the command again and again
and that will throw warnings as well (AFAIK).

Yogesh,

I would like to copy WALs once in every "X" minutes instead of copying them
continuously.

Cheers

---

From: Stephen Frost <[email-masked]>
Date: Sun, 25 Apr 2021 13:26:22 -0400

Greetings,

Please don't top-post on these lists.

> Why would anyone want to set archive_mode on and empty archive_command?

Well, not very likely, which is why there's a warning sent out about it.

That said, when doing things like promoting a new node for a failover,
or maybe doing a restore to a new system for some kind of testing or
development work, etc, there could be reasons that you'd want archiving
enabled but not want the archive_command to actually be set (yet).

Thanks,

Stephen

---

From: Stephen Frost <[email-masked]>
Date: Sun, 25 Apr 2021 14:08:04 -0400

Greetings,

> and that will throw warnings as well (AFAIK).

It'll say it's retrying, sure.

> I would like to copy WALs once in every "X" minutes instead of copying them

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

Thanks,

Stephen

---

From: Pavel Tide <[email-masked]>
Date: Mon, 26 Apr 2021 19:29:16 +0200

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

> once every 'X' minutes..?

Sure.

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.

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?

Thanks

---

From: Stephen Frost <[email-masked]>
Date: Mon, 26 Apr 2021 13:35:00 -0400

Greetings,

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

> > once every 'X' minutes..?
[...]
> collect logs in packs, all at once, instead of the servers streaming

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.

> 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..  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.  Still don't entirely understand why this makes sense to do
though.

Thanks,

Stephen

---

From: Pavel Tide <[email-masked]>
Date: Mon, 26 Apr 2021 22:34:20 +0200

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

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

---

From: Stephen Frost <[email-masked]>
Date: Mon, 26 Apr 2021 16:51:03 -0400

Greetings,

> WALs 5,6,7 rather than having to restore image1 and applying WALs from

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

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

> whereas saving them somewhere else would require us to have some

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

Thanks,

Stephen

---

From: Pavel Tide <[email-masked]>
Date: Tue, 27 Apr 2021 00:30:34 +0200

> pg_start_backup and a pg_stop_backup (and save the backup label file..).

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.

> through calls to archive_command...

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

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

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.

Thanks!

---

From: Stephen Frost <[email-masked]>
Date: Tue, 27 Apr 2021 10:59:16 -0400

Greetings,

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

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

> Continuous shipping means a higher load on the server, which we would

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

---

From: Pavel Tide <[email-masked]>
Date: Thu, 29 Apr 2021 19:34:40 +0200

> that when the snapshot is used you put the backup_label into place?

Not that I've implemented that part by myself, but I will double-check
with my team, thank you for the advice.

> to minimize the risk of commits being lost.

Well, writing WAL-records in bunches instead of writing them one by
one puts less stress onto our database.
Also it might be tricky to make it so that the utility that we use
will be able to tell the server that it's ready to ship something so
that the server will come over and fetch the logs.
Therefore, the server should come and ask once in a while (depending
on how often a new segment is created, which is practically an
unpredictable thing because it depends on many factors), and that
would require the server to generate requests quite frequently.

> users..) and I'm having a pretty hard time seeing the justification for

I think that pause/resume would work too.
While I agree with a foot-gun argument in general, I think that users
who subconsciously want to shoot themselves in a foot they, uh, find a
way no matter what : )

I also understand your concerns regarding the proposed design, so
thank you for your insights,

Cheers