WAL archive (archive_mode = always) ?

On Fri, Oct 19, 2018 at 03:00:15PM +0100, Adelino Silva wrote:

What is the advantage to use archive_mode = always in a slave server
compared to archive_mode = on (shared WAL archive) ?
I only see duplication of Wal files, what is the purpose of this
feature?

Some users like having redundancy in their backups and archives, so as
all things are on multiple location. archive_mode = always helps in
leveraging these needs.
--
Michael

On Fri, Oct 19, 2018 at 10:00 AM Adelino Silva <
adelino.j.silva@googlemail.com> wrote:

Hi,

What is the advantage to use archive_mode = always in a slave server
compared to archive_mode = on (shared WAL archive) ?
I only see duplication of Wal files, what is the purpose of this feature ?

You might not have a shared wal archive in the first place. For example,
your only access to the master is through the streaming replication
protocol, but you want to maintain a local WAL archive anyway so you can
PITR locally for testing or debugging purposes.

Cheers,

Jeff

From: Adelino Silva [mailto:adelino.j.silva@googlemail.com]

What is the advantage to use archive_mode = always in a slave server compared
to archive_mode = on (shared WAL archive) ?

I only see duplication of Wal files, what is the purpose of this feature ?

This also saves you the network bandwidth by not sending the WAL archive from the primary to the standby(s). The network bandwidth can be costly between remote regions for disaster recovery.

Regards
Takayuki Tsunakawa

Hello Takayuki,

Sorry can you explain how we can same network bandwidth by not sending the
WAL archive from the primary to the standby(s).
I possible scenario is have to multiple standby servers in same host for
same master. or other scenarios exists ?

Many thanks in advance,
Adelino.

On Mon, Oct 22, 2018 at 1:47 AM Tsunakawa, Takayuki <
tsunakawa.takay@jp.fujitsu.com> wrote:

Hi,

I think what Takayuki is trying to say is that streaming replication works
by sending the contents of the WAL archives to the standbys. If
archive_mode was NOT set to always, and if you wanted to archive WAL logs
in the standby you would need to rely on the process_command and make it
ship the WAL logs (to the standby). This causes the same WAL log
information to be shipped in two places,

1. Through Streaming Replication
2. By the process_command

This redundant shipping of the same information is expensive and consumes
network bandwidth. This can be avoided with the use of archive_mode=always.

archive_mode=always makes the standby archive the WAL logs it receives,
thus avoiding the requirement of having to ship it separately.

Thank you,
Narayanan

On Mon, Oct 22, 2018 at 2:36 PM Adelino Silva <
adelino.j.silva@googlemail.com> wrote:

From: Narayanan V [mailto:vnarayanan.email@gmail.com]

I think what Takayuki is trying to say is that streaming replication works
by sending the contents of the WAL archives to the standbys. If archive_mode
was NOT set to always, and if you wanted to archive WAL logs in the standby
you would need to rely on the process_command and make it ship the WAL logs
(to the standby). This causes the same WAL log information to be shipped
in two places,

1. Through Streaming Replication
2. By the process_command

This redundant shipping of the same information is expensive and consumes
network bandwidth. This can be avoided with the use of archive_mode=always.

archive_mode=always makes the standby archive the WAL logs it receives,
thus avoiding the requirement of having to ship it separately.

Exactly. (archive_command, not process_command)

Thanks, Narayanan.

Regards
Takayuki Tsunakawa

On Mon, Oct 22, 2018 at 5:06 AM Adelino Silva <
adelino.j.silva@googlemail.com> wrote:

Hello Takayuki,

Sorry can you explain how we can same network bandwidth by not sending the
WAL archive from the primary to the standby(s).
I possible scenario is have to multiple standby servers in same host for
same master. or other scenarios exists ?

Before archive_mode = always became available, we've had to stream the WAL
twice, once to the hot standby for immediate application, and once to
pg_receivexlog for archival purposes. So it doubled the bandwidth usage.

Cheers,

Jeff