Re: [GENERAL] Why does this hot standy archive_command work

"btober@computer.org" <btober@broadstripe.net> writes:

> ----- Original Message -----
>> From: "Jerry Sievers" <gsievers19@comcast.net>
>> To: "Steve Atkins" <steve@blighty.com>
>> Cc: "pgsql-general" <pgsql-general@postgresql.org>
>> Sent: Monday, January 23, 2017 12:52:46 PM
>> Subject: Re: [GENERAL] Why does this hot standy archive_command work
>>
>> Steve Atkins <steve@blighty.com> writes:
>>
>> >> On Jan 20, 2017, at 7:03 PM, btober@computer.org <btober@broadstripe.net>
>> >> wrote:
>> >>
>> >> While learning a bit about basic hot standby configuration, I was
>> >> reviewing an article that used these parameters
>> >>
>> >> wal_level = 'hot_standby'
>> >> archive_mode = on
>> >> archive_command = 'cd .'
>>
>> That's just a very silly way of making archive_command a true no-op...
>>
>> I would have set it to '/bin/true'
>
>
> Roger that.
>
> Also, I guess I should have maybe been more clear what I meant by "it works" ... the hot standby replication works,
whichis really what I was focusing on.  
>
> I think I might have previously been under the mis-impression that
> archiving the WAL to ... somewhere ... was required for hot stand-by,
> so the no-op threw me since the stand-by configuration does not seem
> to specify where to get the primary WAL from. It seems like what is
> happening here is that since the primary archive, as you point out, is
> no-op, and the standby server knows where to just grab the WAL
> directly.

Ummmm...

Well your master does not send WALs to any remote repo which means
backing it up with any intent of doing PITR is  not an option.

If your standby is streaming from a replication slot (only supported in
newer releases) vs standard streaming, then your master will retain WALs
indefinitely if your standby is down or paused... and it will always be
possible to catch up the standby no matter how long it's down.

OTOH, not using rep slot and *also* not using a remote archive repo and
once your standby is down long enough or falls behind for any reason
beyond what the master still has retained in WALs based upon settings
like wal_keep_segments and/or  min_wal_size...

You may lose your standby and have to refresh it from a basebackup since
the needed WALs won't exist anywhere.

There are a lot of configuration choices but perhaps the most common one
is a hybrid of streaming with fallback to WAL fetching from a remote
repo and indeed at my site this is required generally for robustness.

HTH

> Thanks for replying!
>
>
>>
>>
>>
>> >> max_wal_senders = 1
>> >> hot_standby = on
>> >>
>> >>
>> >> How or why that particular archive_command actually works (... and it does
>> >> ... I tried it ...) is not clear to me based on reading of the Postgresql
>> >> documentation on this topic. I would have expected to see an actual copy
>> >> or rsync command, as described in the fine manual at section 25.3.1.
>> >> "Setting Up WAL Archiving"
>> >>
>> >> The entire example appears at
>> >>
>> >>
https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps
>> >>
>> >> Can anyone enlighten on this topic, or provide a link to an existing
>> >> explanation?
>> >
>> > It's not archiving logs at all, instead relying on streaming them directly
>> > to the slave.
>> >
>> > Changing archive_mode requires a server restart, while changing
>> > archive_command from a command that does nothing, successfully, to a
>> > command that actually archives logs just requires a reload. So this lets
>> > you enable archiving without halting the server by changing the command.
>> >
>> > Or that's how I vaguely recall it working some years ago. Things may have
>> > changed now - you're following a very old tutorial.
>> >
>> > Cheers,
>> >   Steve
>>
>> --
>> Jerry Sievers
>> Postgres DBA/Development Consulting
>> e: postgres.consulting@comcast.net
>> p: 312.241.7800
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800