Disable Streaming Replication without restarting either master or slave

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() ?

http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE

Hope that helps!
François Beausoleil

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

---------- 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?
--
Mike Nolan

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.

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 ?

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

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

I understand the way things work currently, but would it not be more
convenient to let the replication be stopped and started from SQL commands
like:

On Master:

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

On Slave:

select pg_pause_streaming_replication();
select pg_pause_streaming_replication();

If Cascaded Streaming Replication is confugured, then

On Slave:

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

Or an OS executable [ either in the core or a contrib module] like

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

[ the last one would awesome; and i do not think that it is impossible, and
would be loved by one and all ]

Regards,
Samba

========================================================

On Wed, May 30, 2012 at 12:07 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

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