Home > mailing lists

Thread: [GENERAL] cluster question

[GENERAL] cluster question

From

Alex Samad

Date:

14 August 2017, 12:39:54

I have setup a streaming replicating cluster, with a hot standby.

Now I would like to change the RW to hot standby and change the hot standby to be the RW server.

Is it just a matter of updating recover.conf file ?

Alex

Re: [GENERAL] cluster question

From

Andreas Kretschmer

Date:

14 August 2017, 12:47:34

On 14 August 2017 08:39:54 GMT+02:00, Alex Samad <alex@samad.com.au> wrote:
>Hi
>
>I have setup a streaming replicating cluster, with a hot standby.
>
>Now I would like to change the RW to hot standby and change the hot
>standby
>to be the RW server.
>
>Is it just a matter of updating recover.conf file ?
>
>Alex

I would suggest you repmgr, with this tool you can do "repmgr standby switchover" to perform such tasks.

Regards, Andreas.


--
2ndQuadrant - The PostgreSQL Support Company

Re: [GENERAL] cluster question

From

Alex Samad

Date:

15 August 2017, 09:15:13

Quick question. I have a 2 node cluster - each node has its own ip.

But from reading this, I really need a 3rd ip, which potentially floats between the nodes to which ever is the master / rw node.

Is that right? Sort of makes sense I guess

On 14 August 2017 at 16:47, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:

On 14 August 2017 08:39:54 GMT+02:00, Alex Samad <alex@samad.com.au> wrote:
>Hi
>
>I have setup a streaming replicating cluster, with a hot standby.
>
>Now I would like to change the RW to hot standby and change the hot
>standby
>to be the RW server.
>
>Is it just a matter of updating recover.conf file ?
>
>Alex

I would suggest you repmgr, with this tool you can do "repmgr standby switchover" to perform such tasks.

Regards, Andreas.

--
2ndQuadrant - The PostgreSQL Support Company

Re: [GENERAL] cluster question

From

Andreas Kretschmer

Date:

15 August 2017, 12:35:38


Am 15.08.2017 um 05:15 schrieb Alex Samad:
> Hi
>
> Quick question.  I have a 2 node cluster - each node has its own ip.
>
> But from reading this, I really need a 3rd ip, which potentially
> floats between the nodes to which ever is the master / rw node.
>
> Is that right? Sort of makes sense I guess
>

That's one way to solve that problem. If you are using repmgr/repmgrd
you can call own commands on events like failover. And, if you are using
pgbouncer or connection-pooling you can change the config for pgbouncer
(redefine the databases and there connection strings) and reload
pgbouncer. You can do that with the event-notification commands defined
in your repmgr-config.
Other solution: with some Java-drivers you can define several databases
and checks (if the database read-only or rw), the next PostgreSQL 10
will offer a similar feature.

PS.: please don't top-posting.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: [GENERAL] cluster question

From

Alex Samad

Date:

16 August 2017, 11:41:57

On 15 August 2017 at 16:35, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:

Am 15.08.2017 um 05:15 schrieb Alex Samad:
Hi

Quick question. I have a 2 node cluster - each node has its own ip.

But from reading this, I really need a 3rd ip, which potentially floats between the nodes to which ever is the master / rw node.

Is that right? Sort of makes sense I guess

That's one way to solve that problem. If you are using repmgr/repmgrd you can call own commands on events like failover. And, if you are using pgbouncer or connection-pooling you can change the config for pgbouncer (redefine the databases and there connection strings) and reload pgbouncer. You can do that with the event-notification commands defined in your repmgr-config.
Other solution: with some Java-drivers you can define several databases and checks (if the database read-only or rw), the next PostgreSQL 10 will offer a similar feature.

okay think I have it setup, but when i do a switch over it gets stuck here.

NOTICE: STANDBY PROMOTE successful

NOTICE: Executing pg_rewind on old master server

NOTICE: 3 files copied to /var/lib/pgsql/9.6/data

NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl -w -D /var/lib/pgsql/9.6/data -m fast restart'

pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist

Is server running?

starting server anyway

NOTICE: STANDBY FOLLOW successful

doesn't look like the output from https://github.com/2ndQuadrant/repmgr/blob/master/README.md

NOTICE: STANDBY PROMOTE successful
NOTICE: Executing pg_rewind on old master server
NOTICE: 5 files copied to /var/lib/postgresql/9.5/data
NOTICE: restarting server using '/usr/local/bin/pg_ctl -w -D /var/lib/postgresql/9.5/node_1/data -m fast restart'
pg_ctl: PID file "/var/lib/postgresql/9.5/node_1/data/postmaster.pid" does not exist
Is server running?
starting server anyway
NOTICE: node 1 is replicating in state "streaming"
NOTICE: switchover was successful

PS.: please don't top-posting.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

--
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] cluster question

From

Ian Barwick

Date:

17 August 2017, 06:51:38

On 08/16/2017 02:41 PM, Alex Samad wrote:
(...)
 >
 > okay think I have it setup, but when i do a switch over it gets stuck here.
 >
 >
 >
 > NOTICE: STANDBY PROMOTE successful
 > NOTICE: Executing pg_rewind on old master server
 > NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
 > NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl  -w -D /var/lib/pgsql/9.6/data -m fast restart'
 > pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
 > Is server running?
 > starting server anyway
 > NOTICE: STANDBY FOLLOW successful

 From the repmgr README:

 >> You must ensure that following a server start using `pg_ctl`, log output
 >> is not send to STDERR (the default behaviour). If logging is not configured,
 >> we recommend setting `logging_collector=on` in `postgresql.conf` and
 >> providing an explicit `-l/--log` setting in `repmgr.conf`'s `pg_ctl_options`
 >> parameter.

i.e. when the old primary is restarted with:

     /usr/pgsql-9.6/bin/pg_ctl  -w -D /var/lib/pgsql/9.6/data -m fast restart

the calling process hangs, waiting for logging output from pg_ctl.
In "repmgr.conf" set "pg_ctl_options" to something like:

     pg_ctl_options='-l /path/to/log'


Regards

Ian Barwick

--
  Ian Barwick                   http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] cluster question

From

Alex Samad

Date:

17 August 2017, 10:42:14

On 17 August 2017 at 10:51, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:

On 08/16/2017 02:41 PM, Alex Samad wrote:
(...)
>
> okay think I have it setup, but when i do a switch over it gets stuck here.
>
>
>
> NOTICE: STANDBY PROMOTE successful
> NOTICE: Executing pg_rewind on old master server
> NOTICE: 3 files copied to /var/lib/pgsql/9.6/data
> NOTICE: restarting server using '/usr/pgsql-9.6/bin/pg_ctl -w -D /var/lib/pgsql/9.6/data -m fast restart'
> pg_ctl: PID file "/var/lib/pgsql/9.6/data/postmaster.pid" does not exist
> Is server running?
> starting server anyway
> NOTICE: STANDBY FOLLOW successful

From the repmgr README:

>> You must ensure that following a server start using `pg_ctl`, log output
>> is not send to STDERR (the default behaviour). If logging is not configured,
>> we recommend setting `logging_collector=on` in `postgresql.conf` and
>> providing an explicit `-l/--log` setting in `repmgr.conf`'s `pg_ctl_options`
>> parameter.

i.e. when the old primary is restarted with:

/usr/pgsql-9.6/bin/pg_ctl -w -D /var/lib/pgsql/9.6/data -m fast restart

the calling process hangs, waiting for logging output from pg_ctl.
In "repmgr.conf" set "pg_ctl_options" to something like:

pg_ctl_options='-l /path/to/log'

Regards

Thanks, simple when you know, too many new things to look at

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services