Обсуждение: 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 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

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

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

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

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

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

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