Re: [GENERAL] Turn off streaming replication - leaving Master running
Cheers all. On 30 June 2015 at 15:58, John R Pierce pie...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
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
Re: [GENERAL] Turn off streaming replication - leaving Master running
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 pie...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
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 pie...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
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.paqu...@gmail.com wrote: On Mon, Jun 29, 2015 at 2:42 PM, Andy Erskine andy.ersk...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
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
Re: [GENERAL] Turn off streaming replication - leaving Master running
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
Re: [GENERAL] Turn off streaming replication - leaving Master running
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.paqu...@gmail.com wrote: On Mon, Jun 29, 2015 at 3:34 PM, Andy Erskine andy.ersk...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
On Sun, Jun 28, 2015 at 11:34 PM, Andy Erskine andy.ersk...@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
Re: [GENERAL] Turn off streaming replication - leaving Master running
On Mon, Jun 29, 2015 at 3:34 PM, Andy Erskine andy.ersk...@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
Re: [GENERAL] Turn off streaming replication - leaving Master running
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
Re: [GENERAL] Turn off streaming replication - leaving Master running
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.paqu...@gmail.com wrote: On Tue, Jun 30, 2015 at 2:29 PM, Andy Erskine andy.ersk...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
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.von...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
On Tue, Jun 30, 2015 at 1:39 PM, Andy Erskine andy.ersk...@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
Re: [GENERAL] Turn off streaming replication - leaving Master running
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.paqu...@gmail.com wrote: On Tue, Jun 30, 2015 at 1:39 PM, Andy Erskine andy.ersk...@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.ersk...@jds.net.au andy.ersk...@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]
Re: [GENERAL] Turn off streaming replication - leaving Master running
On Tue, Jun 30, 2015 at 2:29 PM, Andy Erskine andy.ersk...@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
[GENERAL] Turn off streaming replication - leaving Master running
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. Is this possible while not touching the Master DB ? If so what are the correct steps pls ?
Re: [GENERAL] Turn off streaming replication - leaving Master running
On Mon, Jun 29, 2015 at 2:42 PM, Andy Erskine andy.ersk...@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
Re: [GENERAL] Turn off streaming replication - leaving Master running
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
