Enable WAL archiving even in standby - Mailing list pgsql-hackers

Hi,

I'd propose the attached WIP patch which allows us to enable WAL archiving
even in standby. The patch adds "always" as the valid value of archive_mode.
If it's set to "always", the archiver is started when the server is in standby
mode and all the WAL files that walreceiver wrote to the disk are archived by
using archive_command. Then, even after the server is promoted to master,
the archiver keeps archiving WAL files. The patch doesn't change the meanings
of the setting values "on" and "off" of archive_mode.

I think that this feature is useful for the case, e.g., where large database
needs to be replicated between remote servers. Imagine the situation where
the replicated database gets corrupted completely in the remote standby.
How should we address this problematic situation and restart the standby?

One approach is to take a fresh backup from the master and restore it onto
the standby. But since the database is large and there is long distance
between two servers, this approach might take a surprisingly long time.

Another approach is to restore the backup which was taken from the standby
before. But most of many WAL files which the backup needs might exist only
in the master (because WAL archiving cannot be enabled in the standby) and
they need to be transfered from the master to the standby via long-distance
network. So I think that this approach also would take a fairly long time.
To shorten that time, you may think that archive_command in the master can
be set so that it transfers WAL files from the master to the standby's
archival storage. I agree that this setting can accelerate the database restore
process. But this causes every WAL files to be transfered between remote
servers twice (one is by streaming replication, another is by archive_command),
and which is a waste of network bandwidth.

Enabling WAL archiving in standby is one of solutions for this situation. We
can expect that most of WAL files that are required for the backup taken from
the standby would exist in the standby's archival storage.

Back to the patch. If archive_mode is set to "always", archive_command is
always used to archive WAL files even during recovery. Do we need to separate
the command into two for master and standby, respectively? We can add
something like standby_archive_command parameter which is used to archive
only WAL files walreceiver writes. The other WAL files are archived by
archive_command. I'm not sure if it's really worth separating the command
that way. Is there any use case?

The patch doesn't allow us to enable WAL archiving *only* during recovery.
Should we support yet another archive_mode like "standby" which allows
the archiver to be running only during recovery, but makes it end just after
the server is promoted to master? I'm not sure if there is really use case for
that.

I've not included the update of document in the patch yet. If we agree to
support this feature, I will do the remaining work.

Regards,

--
Fujii Masao