cluster question

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

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

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

A

On 14 August 2017 at 16:47, 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.

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

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

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

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

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