2

We're running a backup process based on pg_basebackup of a Postgres 14.5 database that creates a lot of WAL archive files and using up huge amounts of disk space. As we don't really need the WAL archive, I thought to disable WAL archiving and I changed these values (everything else is pure default):

archive_mode = off     # was 'on' before
wal_level = minimal    # was 'replica' before (there's only a single db instance running)
max_wal_senders = 0    # was '10' before (but wal_level = minimal seems to require '0')

Maybe there's no real need to lower the wal_level from replica to minimal, but the WAL documentation states what seems exactly sufficient for my less than critical database:

minimal removes all logging except the information required to recover from a crash or immediate shutdown.

Unfortunately, after the above configuration, the backup process now fails with this error:

FATAL:  number of requested standby connections exceeds max_wal_senders (currently 0)

In other words, max_wal_senders must be zero and also greater than zero at the same time! What can I do to get out of this deadlock?

dokaspar
  • 123
  • 5

1 Answers1

2

What can I do to get out of this deadlock?

Don't set wal_level = minimal, don't set max_wal_senders = 0, and leave wal_level = replica. pg_basebackup uses the built-in replication mechanism to perform a consistent backup while the server is running (and potentially modifying data).

The backup is made over a regular PostgreSQL connection that uses the replication protocol. [...] The server must also be configured with max_wal_senders set high enough to provide at least one walsender for the backup

mustaccio
  • 28,207
  • 24
  • 60
  • 76