Turn off streaming replication - leaving Master running

On Mon, Jun 29, 2015 at 2:42 PM, Andy Erskine <andy.erskine@jds.net.au> wrote:

I'd like to turn off the streaming on my postgresdb (9.2) It is currently
setup to stream from master to a single secondary.

I want to shutdown the secondary and turn it into another master and connect
it to another application for testing - after which i want to revert it back
to a streaming secondary.

What kind of tests? If this is read-only activity you could simply cut
the network connection between the master and the slave, or restart
the slave after updating recovery.conf such as it is still a standby
but has no primary_conninfo so as it is performing archive recovery,
or at least a fake one.

Is this possible while not touching the Master DB ? If so what are the
correct steps pls ?

Well, yes. It depends on what you want to do then. If this testing
really requires to promote the standby then you will need to take a
new fresh base backup knowing that you are using 9.2.
Regards,
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Cheers Micheal,

So i don't want to touch the file as this will promote it too a master - i
would just like it as a standalone db for this instance - i need to load a
db and do some testing.

After which i will delete the data directory and run the basebackup cmd and
pull the db back from the master and set up streaming again.

Just need to be certain of the right steps so i don't effect the Master in
any way.

On 29 June 2015 at 15:52, Michael Paquier <michael.paquier@gmail.com> wrote:

On Mon, Jun 29, 2015 at 2:42 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

I'd like to turn off the streaming on my postgresdb (9.2) It is currently
setup to stream from master to a single secondary.

I want to shutdown the secondary and turn it into another master and

connect

it to another application for testing - after which i want to revert it

back

to a streaming secondary.

What kind of tests? If this is read-only activity you could simply cut
the network connection between the master and the slave, or restart
the slave after updating recovery.conf such as it is still a standby
but has no primary_conninfo so as it is performing archive recovery,
or at least a fake one.

Is this possible while not touching the Master DB ? If so what are the
correct steps pls ?

Well, yes. It depends on what you want to do then. If this testing
really requires to promote the standby then you will need to take a
new fresh base backup knowing that you are using 9.2.
Regards,
--
Michael

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]

On 6/28/2015 10:52 PM, Michael Paquier wrote:

Well, yes. It depends on what you want to do then. If this testing
really requires to promote the standby then you will need to take a
new fresh base backup knowing that you are using 9.2.

if the standby is running on a file system with snapshotting (like zfs),
and the master is doing WAL archiving, you could, in theory, pause the
replication and snapshot the slave, do read/write tests on the slave,
then restore that snapshot and resume replication, pulling from the WAL
archive til it catches up.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

no snapshot available .. i don't mind running basebackup once i've finished
my test.

So if someone could help with the steps to turn off replication and bring
secondary up as a standalone db that would be great
thanks.

On 29 June 2015 at 15:58, John R Pierce <pierce@hogranch.com> wrote:

On 6/28/2015 10:52 PM, Michael Paquier wrote:

Well, yes. It depends on what you want to do then. If this testing
really requires to promote the standby then you will need to take a
new fresh base backup knowing that you are using 9.2.

if the standby is running on a file system with snapshotting (like zfs),
and the master is doing WAL archiving, you could, in theory, pause the
replication and snapshot the slave, do read/write tests on the slave, then
restore that snapshot and resume replication, pulling from the WAL archive
til it catches up.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]

On Mon, Jun 29, 2015 at 3:34 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

no snapshot available .. i don't mind running basebackup once i've
finished my test.

So if someone could help with the steps to turn off replication and bring
secondary up as a standalone db that would be great.

How to promote a node: pg_ctl promote or use a trigger_file:
http://www.postgresql.org/docs/devel/static/warm-standby.html#STANDBY-SERVER-OPERATION
Turning temporarily off replication has little meaning if you intend to
bring back a new standby afterwards.
My 2c.
--
Michael

If i touch my trigger file and promote my secondary to a master - what
effect will that have on the Master - will i need to make any changes on
that side ? Will it still try and stream data across to the promoted
secondary and just fill up the log files with error messages ?

On 29 June 2015 at 16:50, Michael Paquier <michael.paquier@gmail.com> wrote:

On Mon, Jun 29, 2015 at 3:34 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

no snapshot available .. i don't mind running basebackup once i've
finished my test.

So if someone could help with the steps to turn off replication and bring
secondary up as a standalone db that would be great.

How to promote a node: pg_ctl promote or use a trigger_file:

http://www.postgresql.org/docs/devel/static/warm-standby.html#STANDBY-SERVER-OPERATION
Turning temporarily off replication has little meaning if you intend to
bring back a new standby afterwards.
My 2c.
--
Michael

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]

On Sun, Jun 28, 2015 at 11:34 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

no snapshot available .. i don't mind running basebackup once i've
finished my test.

So if someone could help with the steps to turn off replication and bring
secondary up as a standalone db that would be great
thanks.

If people are used to connecting the standby in order to run production
queries (to spare load from the master), then make sure they know they
can't do that anymore. Configure the network to block them, change
pg_hba.conf, etc.

Make sure that it is not in archive mode, or at least that it isn't
archiving to the same directory as master is.

Shut it down, remove (or rename) the recovery.conf file, and start it back
up again. Instead of doing this you could use the trigger file (configured
in recovery.conf) or pg_ctl promote, but I wouldn't do that. You don't
want to make it look like you have promoted it to master, when that is not
what you have done.

But since you want a clone, what is the point of first setting up
streaming, and then breaking it? Just use pg_basebackup to set up a clone
directly, without ever having started streaming. It seems like you are
just going to confuse yourself about what is a standby meant for fail over,
and what is a clone meant for testing. With possibly disastrous
consequences.

Cheers,

Jeff

On 6/28/2015 11:58 PM, Andy Erskine wrote:

If i touch my trigger file and promote my secondary to a master - what
effect will that have on the Master - will i need to make any changes
on that side ? Will it still try and stream data across to the
promoted secondary and just fill up the log files with error messages ?

its not 'push', the slave pulls the data from the master. as long as
the master has WAL file archiving enabled (which is a separate but
related thing to streaming), the slave will catch up... if the slave is
understands where the WAL archive is, then when its woken back up after
being restored to how it was before your testing, it will query the
master, find out its way ahead of its timeline, and consult with the WAL
archives, fetching as many as are needed to catch up to the servers'
current timeline, then resume streaming ...

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 6/29/2015 12:06 AM, Jeff Janes wrote:

But since you want a clone, what is the point of first setting up
streaming, and then breaking it? Just use pg_basebackup to set up a
clone directly, without ever having started streaming. It seems like
you are just going to confuse yourself about what is a standby meant
for fail over, and what is a clone meant for testing. With possibly
disastrous consequences.

VERY good point!

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Jeff,

I don't want a clone - i want to temporaily turn off replication (and
therefore failover) and load a different db into the secondary which is now
writable and run some tests. Then i will remove this db and run a
basebackup to reinstate a copy of the master and turn on replication again.

So :

shutdown secondary
change hba_conf so primary cannot connect
rename recovery.conf
start db
load new db
run tests

shutdown db
basebackup db from master
revert streaming settings
startdb

thanks.

On 29 June 2015 at 17:12, John R Pierce <pierce@hogranch.com> wrote:

On 6/29/2015 12:06 AM, Jeff Janes wrote:

But since you want a clone, what is the point of first setting up
streaming, and then breaking it? Just use pg_basebackup to set up a clone
directly, without ever having started streaming. It seems like you are
just going to confuse yourself about what is a standby meant for fail over,
and what is a clone meant for testing. With possibly disastrous
consequences.

VERY good point!

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]

Hi,

On 06/29/2015 09:27 AM, Andy Erskine wrote:

Thanks Jeff,

I don't want a clone - i want to temporaily turn off replication
(and therefore failover) and load a different db into the secondary
which is now writable and run some tests. Then i will remove this db
and run a basebackup to reinstate a copy of the master and turn on
replication again.

So you want replica and you don't want replica at the same time?

Is there any reason why you simply don't want to either create a new
snapshot using pg_basebackup, or just simply shut down the standby,
create a copy of the data directory, remove the recovery conf and start
it again as a standalone database?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

No i don't want a replica.

I would like to reconfigure my streaming scenario into two standalone db's
i don't want the Master to be effected in anyway and i want it running
consistantly .. the secondary i want to reconfigure as a standalone to load
a different db and do some testing.

When i've finished testing - i want to reconfigure for streaming again.

On 30 June 2015 at 12:37, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:

Hi,

On 06/29/2015 09:27 AM, Andy Erskine wrote:

Thanks Jeff,

I don't want a clone - i want to temporaily turn off replication
(and therefore failover) and load a different db into the secondary
which is now writable and run some tests. Then i will remove this db
and run a basebackup to reinstate a copy of the master and turn on
replication again.

So you want replica and you don't want replica at the same time?

Is there any reason why you simply don't want to either create a new
snapshot using pg_basebackup, or just simply shut down the standby, create
a copy of the data directory, remove the recovery conf and start it again
as a standalone database?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]

On Tue, Jun 30, 2015 at 1:39 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

No i don't want a replica.

I would like to reconfigure my streaming scenario into two standalone db's
i don't want the Master to be effected in anyway and i want it running
consistantly .. the secondary i want to reconfigure as a standalone to load
a different db and do some testing.

When i've finished testing - i want to reconfigure for streaming again.

Well, in this case:
1) promote your standby
2) Run your tests on it.
3) Recreate a new standby
The documentation online, as well as the PostgreSQL wiki have all the
documentation to help you achieve those steps.
Regards,
--
Michael

Ok so a question i should have asked at the very beginning ..

If i touch my trigger file - promoting the secondary to a master - will
that in anyway effect the master thats already running ?

IE no files on the master will change ?

Then all i'll have to do is shutdown the secondary when i've finished and
run the basebackup process again to restore replication (and change
recovery.done - conf again.

thanks.

On 30 June 2015 at 15:22, Michael Paquier <michael.paquier@gmail.com> wrote:

On Tue, Jun 30, 2015 at 1:39 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

No i don't want a replica.

I would like to reconfigure my streaming scenario into two standalone
db's i don't want the Master to be effected in anyway and i want it running
consistantly .. the secondary i want to reconfigure as a standalone to load
a different db and do some testing.

When i've finished testing - i want to reconfigure for streaming again.

Well, in this case:
1) promote your standby
2) Run your tests on it.
3) Recreate a new standby
The documentation online, as well as the PostgreSQL wiki have all the
documentation to help you achieve those steps.
Regards,
--
Michael

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]

On Tue, Jun 30, 2015 at 2:29 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

Ok so a question i should have asked at the very beginning ..

If i touch my trigger file - promoting the secondary to a master - will
that in anyway effect the master thats already running ?
IE no files on the master will change ?

Streaming replication is designed to let the master run properly when a
standby disconnects.

Then all i'll have to do is shutdown the secondary when i've finished and
run the basebackup process again to restore replication (and change
recovery.done - conf again.

Yes.

Now something that has been mentioned by the others: isn't your standby
here for a reason? Like in case of failure don't you have a process to
failover automatically? Perhaps you are shooting yourself in the foot by
unplugging this standby, hence you should, and other recommend the same,
simply let the existing standby alone and create a new instance by taking a
new base backup from either the master or the standby and use it for your
tests. Then eliminate the node you created. In short: avoid doing stupid
things...
--
Michael

agreed there is an element of risk.

however a backup of the master will be carried out prior (ok there could
potentially be a gap of data during downtime)

unfortunately i have nothing else big enough (diskwise) to run my tests on.

On 30 June 2015 at 15:47, Michael Paquier <michael.paquier@gmail.com> wrote:

On Tue, Jun 30, 2015 at 2:29 PM, Andy Erskine <andy.erskine@jds.net.au>
wrote:

Ok so a question i should have asked at the very beginning ..

If i touch my trigger file - promoting the secondary to a master - will
that in anyway effect the master thats already running ?
IE no files on the master will change ?

Streaming replication is designed to let the master run properly when a
standby disconnects.

Then all i'll have to do is shutdown the secondary when i've finished and
run the basebackup process again to restore replication (and change
recovery.done - conf again.

Yes.

Now something that has been mentioned by the others: isn't your standby
here for a reason? Like in case of failure don't you have a process to
failover automatically? Perhaps you are shooting yourself in the foot by
unplugging this standby, hence you should, and other recommend the same,
simply let the existing standby alone and create a new instance by taking a
new base backup from either the master or the standby and use it for your
tests. Then eliminate the node you created. In short: avoid doing stupid
things...
--
Michael

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]

On 6/29/2015 10:55 PM, Andy Erskine wrote:

agreed there is an element of risk.

however a backup of the master will be carried out prior (ok there
could potentially be a gap of data during downtime)

unfortunately i have nothing else big enough (diskwise) to run my
tests on.

rent a virtual server for a few days from Amazon or someone.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Cheers all.

On 30 June 2015 at 15:58, John R Pierce <pierce@hogranch.com> wrote:

On 6/29/2015 10:55 PM, Andy Erskine wrote:

agreed there is an element of risk.

however a backup of the master will be carried out prior (ok there could
potentially be a gap of data during downtime)

unfortunately i have nothing else big enough (diskwise) to run my tests
on.

rent a virtual server for a few days from Amazon or someone.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

*Andy Erskine*

*JDS Australia*
*P* 1300 780 432 | *M* 0431919301 | *F* 03 90124578

*E:* *andy.erskine@jds.net.au <andy.erskine@jds.net.au>*

*W*: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001

[image: JDS Signature v1]