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

Yugo Nagata nagata at sraoss.co.jp
Fri Jul 18 17:46:57 JST 2014


Hi Long,

On Thu, 17 Jul 2014 22:39:23 -0400
Long On <on.long.on at gmail.com> wrote:

> 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?

Yes, you have to use master-slave mode. 

> 
> 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

No. pgpool use pg_is_in_recovery(). However, this is only when 
master_slave_mode is on and master_slave_sub_mode is 'sterem'.
Otherwize, find_parimary_node (function to find primary) always fails 
and returning -1 and the log says a message like following;

2014-07-17 02:27:12 LOG:   pid 18498: failover: set new primary node: -1

You said node01 is regared as primary when recovered. However, to be exact,
this is not "primary" but "master". Master means the first alive node in
backends list regardless of whethere this is primary or not. Primary is
the exact priamry server of streaming replication, which is detected by 
using pg_is_in_recovery() only when master-slave/stream mode is used.

> /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>
> >


-- 
Yugo Nagata <nagata at sraoss.co.jp>


More information about the pgpool-general mailing list