Thread: Disable Streaming Replication without restarting either master or slave

Is it possible stop/disable streaming replication without stopping or restarting either master or slave servers?

Since stopping or restarting the postgres servers would involve complete invalidation of the connection pool [Java/JEE app server pool] that may take a few minutes before the application becomes usable, it would be great if there is a way we can disable replication [for maintenance reasons like applying patches or upgrades, etc].

On Tue, May 29, 2012 at 7:18 PM, Samba <saasira@gmail.com> wrote:
> Hi,
>
>
> Is it possible stop/disable streaming replication without stopping or
> restarting either master or slave servers?
>
> Since stopping or restarting the postgres servers would involve complete
> invalidation of the connection pool [Java/JEE app server pool] that may take
> a few minutes before the application becomes usable, it would be great if
> there is a way we can disable replication [for maintenance reasons like
> applying patches or upgrades, etc].

There is no clean way to disable streaming replication. But you can do that
by the following steps:

1. change pg_hba.conf in the master so that the master does not accept new
    replication connection
2. reload pg_hba.conf in the master
3. send SIGTERM signal to currently-running walsender process, e.g., by
    "select pg_terminate_backend(pid) from pg_stat_replication".

Then replication connection will be terminated. The standby tries reconnecting
to the master, but which will continue failing until you'll change pg_hba.conf
again.

Regards,

--
Fujii Masao

On Tue, May 29, 2012 at 10:17 PM, François Beausoleil
<francois@teksol.info> wrote:
> Hi!
>
> Le 2012-05-29 à 06:18, Samba a écrit :
>
> Is it possible stop/disable streaming replication without stopping or
> restarting either master or slave servers?
>
> Since stopping or restarting the postgres servers would involve complete
> invalidation of the connection pool [Java/JEE app server pool] that may take
> a few minutes before the application becomes usable, it would be great if
> there is a way we can disable replication [for maintenance reasons like
> applying patches or upgrades, etc].
>
>
> Are per-chance looking for pg_xlog_replay_pause() and
> pg_xlog_replay_resume() ?

Those can pause and resume WAL replay in the standby, but not streaming
replication. Even while WAL replay is being paused, WAL can be streamed
from the master to the standby.

Regards,

--
Fujii Masao

On Tue, May 29, 2012 at 2:18 PM, Samba <saasira@gmail.com> wrote:
> Is it possible stop/disable streaming replication without stopping or
> restarting either master or slave servers?
>
> Since stopping or restarting the postgres servers would involve complete
> invalidation of the connection pool [Java/JEE app server pool] that may take
> a few minutes before the application becomes usable, it would be great if
> there is a way we can disable replication [for maintenance reasons like
> applying patches or upgrades, etc].

It is not really clear what is wrong with just stopping the
replication server for maintenance while keeping the master working?

>
>
> Thanks and Regards,
> Samba



--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204

On Wed, May 30, 2012 at 2:38 AM, Michael Nolan <htfoot@gmail.com> wrote:
>
>
> ---------- Forwarded message ----------
> From: Michael Nolan <htfoot@gmail.com>
> Date: Tue, May 29, 2012 at 1:37 PM
> Subject: Re: [GENERAL] Disable Streaming Replication without restarting
> either master or slave
> To: Fujii Masao <masao.fujii@gmail.com>
>
>
>
>
> On Tue, May 29, 2012 at 1:15 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>
>> On Tue, May 29, 2012 at 10:17 PM, François Beausoleil
>> <francois@teksol.info> wrote:
>
>
>>
>> > Are per-chance looking for pg_xlog_replay_pause() and
>> > pg_xlog_replay_resume() ?
>>
>> Those can pause and resume WAL replay in the standby, but not streaming
>> replication. Even while WAL replay is being paused, WAL can be streamed
>> from the master to the standby.
>>
>> Regards,
>>
>> --
>> Fujii Masao
>
>
> So, that means that the only ways to stop streaming replication are to stop
> the slave server, to disable access to the master via the pg_hba.conf file
> (requiring the master configs be reloaded) or to set the trigger file on the
> slave to tell it to stop replicating the master.
>
> And if the master/slave are set to synchronous streaming replication, your
> options are more limited, since the master has to know to stop waiting for
> the synchronous slave to respond.
>
> Once the slave has gone out of asynchronous replication mode, wuld it be
> possible to resume asynchronous replication by stopping the slave server,
> removing the trigger file, and restarting it in asynchronous streaming
> replication mode?  This would, at a minimum, depend on how many updates have
> occurred on the master during the time streaming replication was disabled
> and having all the WAL files available, right?

You'd like to restart the *promoted* standby server as the standby again?
To do this, a fresh base backup must be taken from the master onto
the standby before restarting it, even if there has been no update since
the standby had been promoted.

Regards,

--
Fujii Masao

On Wed, May 30, 2012 at 3:04 AM, Raghavendra
<raghavendra.rao@enterprisedb.com> wrote:
>> > Since stopping or restarting the postgres servers would involve complete
>> > invalidation of the connection pool [Java/JEE app server pool] that may
>> > take
>> > a few minutes before the application becomes usable, it would be great
>> > if
>> > there is a way we can disable replication [for maintenance reasons like
>> > applying patches or upgrades, etc].
>>
>
> I think even applying patches or upgrades needs restart.

Yep.

>> 3. send SIGTERM signal to currently-running walsender process, e.g., by
>>    "select pg_terminate_backend(pid) from pg_stat_replication".
>
>
> Will it be helpful here sending SIGINT instead of killing ?

No, walsender ignores SIGINT signal.

Regards,

--
Fujii Masao

select pg_pause_streaming_replication(slave_fqdn);
select pg_resume_streaming_replication(slave_fqdn);

select pg_pause_streaming_replication();
select pg_pause_streaming_replication();

select pg_start_streaming_replication(cascaded_slave_fqdn);
select pg_stop_streaming_replication(cascaded_slave_fqdn); 

pg_replication -p primary_fqdn -s slave_fqdn -a pause
pg_replication  -p primary_fqdn -s slave_fqdn -a resume
pg_replication  -p primary_fqdn -s slave_fqdn -a setup

On 2012-05-29, Fujii Masao <masao.fujii@gmail.com> wrote:
>
> You'd like to restart the *promoted* standby server as the standby again?
> To do this, a fresh base backup must be taken from the master onto
> the standby before restarting it, even if there has been no update since
> the standby had been promoted.
>

I'd like to add that for this purpose "rsync" will likely outperform "tar" by a
very large margin.

--
⚂⚃ 100% natural