[pgpool-general: 3043] Re: Last node in pgpool chain failover

[Long On]

Hi Yugo,

I am using postgresql streaming replication but not in master-slave mode.
The configuration I was aiming for is replication + load balancing
(select-only). I understand replication is available via pgpool but not
using it atm. Maybe this is the source of my troubles?

My recovery method is basically the same. I use pg_basebackup to restore
node01 as standby using node02 primary. My server is running Ubuntu 14.04
so recovery.conf can be found in /var/lib/postgresql/9.3/main/. Does pgpool
check if this file exist when searching for primary? If so is
/var/lib/postgresql/9.3/main/ the correct location?

When select pg_is_in_recovery() (as suggested by Tatsuo Ishii and Lazaro
Martinez) is run on each node, the result is always consistent: false for
primary, true for standby.

The issue seems to be that after node01 is recovered, pgpool still doesn't
know node01 belongs in the pool and as standby. It seems the node has to be
re-attached. However, pg_attach_node causes a failback_command and promotes
it to primary, even though node02 is currently primary. It feels like a
configuration is missing somewhere.

Perhaps pcp_recovery_node is a possible solution. I will take a look.

Thanks,
Long



On Thu, Jul 17, 2014 at 7:33 AM, Yugo Nagata <nagata at sraoss.co.jp> wrote:

> Hi,
>
> I looked at your pgpool.conf.If you use streaming-reprication,
> master_slave_mode should be on and masetr_slave_sub_mode = 'steam'.
>
>  master_slave_mode = on
>  master_slave_sub_mode = 'stream'
>
> And, when you recover node01 as standby, you have to backup data
> from the current primary (node02) and setup recovery.conf at node01
> before attaching this to pgpool. Otherwize, node01 would work still
> as standby but as primary, since recovery.conf doesn't exist.
>
> You can use "online recovery" functionality of pgpool to automate
> backup and recovery.conf configuration. In this case, pcp_recovery_node
> command is used rather than pcp_attach_node.
>
> The tutorial would be helpful for you to setup streaming-replication with
> watchdog.
>
> http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html
>
> There are sample conf files and scripts in the tutorial. Alhough
> pgpoolAdmin
> is used instead of pcp command, "Recovery" button of pgpool is equivalent
> to
> pcp_recovery_node.
>
>
> --
> Yugo Nagata <nagata at sraoss.co.jp>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140717/7e9b75b9/attachment.html>