Thread: 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
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
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
Attachment
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
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
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
Attachment
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:
On Mon, Jun 29, 2015 at 3:34 PM, Andy Erskine <andy.erskine@jds.net.au> wrote:Turning temporarily off replication has little meaning if you intend to bring back a new standby afterwards.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:My 2c.--Michael
Andy Erskine
JDS Australia
P 1300 780 432 | M 0431919301 | F 03 90124578
E: andy.erskine@jds.net.au
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
Attachment
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 greatthanks.
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
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
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
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
Attachment
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
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
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
Attachment
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 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 standby2) Run your tests on it.3) Recreate a new standbyThe 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
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
Attachment
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.
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...
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
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001
Attachment
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
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
W: www.jds.net.au
Level 8, 2 Russell Street, Melbourne, VIC 3000
GPO Box 4777, Melbourne VIC 3001