Hello Postgres Admins, We have a PostgreSQL setup with a primary and 2 standby nodes, with synchronous_commit on and sync_standby_names = ANY 1(standby1, standby2). Assuming we use 'archive_mode = on' on all replicas, in the event of a primary failure and subsequent failover to a standby node, there is a risk of the primary having failed to upload some WALs to archive storage before the failover. On the other hand, setting 'archive_mode = always' ensures WAL retention on standbys but introduces additional inefficiencies - this would likely require trying to upload each WAL segment from each standby, or at the very least making additional calls to check for WAL presence in the archive. This can be costly and may not scale well. Is there a recommended and optimal way to ensure all WAL files are always uploaded, even across failovers? Here's how we ended up addressing this: we set 'archive_mode = always' on all replicas, but in our archive_command on standby nodes, we connect to the primary server and query `pg_stat_archiver` to determine the last successfully archived WAL file. Based on this information, the we either returns an exit code 0 (if primary confirms WAL was archived) or a non-zero value (primary has not, or fails to respond). This enables us to retain all WALs on the standby that have not so far been archived by the primary, and prevents redundant uploads - but it involves additional communication between standbys and the primary. My idea for a more robust solution is to include the last successfully archived WAL file information in the keep-alive messages sent from the primary to standby nodes. This would allow standby servers to use this data directly, eliminating the need for extra queries to the primary. If the community finds this approach useful, I would be interested in contributing a patch to implement this improvement. Please let us know your thoughts! Thanks, Best, Harinath
