[pgpool-general: 3454] Questions about getting pgpool to handle failover more smoothly

[Reuven M. Lerner]

Hi there!  I am a longtime user of PostgreSQL, but new to pgpool.

I have set up master-slave replication, with repmgr in place.  The 
master-slave failover is working perfectly; if the master dies, the 
slave self-promotes (thanks to repmgrd) to be a new master.  I can then 
run a shell script that turns the old master into the new slave, 
swapping the original roles.

This is the functionality that I'm looking for: Two nodes, with one 
master and one slave, that will cover for each other if and when there 
is a problem.

My goal in using pgpool is to provide our application with a single 
address and port for accessing the database.  By connecting to pgpool, 
it doesn't matter which is the primary (master) and which is the 
secondary (slave); pgpool will route the query to the right place.

I have configured pgpool in the following way:

# Host number 1 (original master)
backend_hostname0 = '10.50.1.153'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/home/postgres/databases/fb'
backend_flag0 = 'ALLOW_TO_FAILOVER'

# Host number 2 (original slave)
backend_hostname1 = '10.50.1.154'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/home/postgres/databases/fb'
backend_flag1 = 'ALLOW_TO_FAILOVER'

master_slave_mode = on
master_slave_sub_mode = 'stream'

I have three questions, all having to do with detection and 
promotion/failover of the nodes:

(1) pgpool doesn't seem to notice that the standby has become a master

Given the above configuration, I ask pgpool to tell me what's running, 
and I get:

postgres=# show pool_nodes;
  node_id |  hostname   | port | status | lb_weight |  role
---------+-------------+------+--------+-----------+---------
  0       | 10.50.1.153 | 5432 | 2      | 0.500000  | primary
  1       | 10.50.1.154 | 5432 | 2      | 0.500000  | standby
(2 rows)


I then kill off the master.  repmgrd promotes the old slave to be the 
new master.  But pgpool doesn't seem to notice:

postgres=# show pool_nodes;
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# show pool_nodes;
  node_id |  hostname   | port | status | lb_weight |  role
---------+-------------+------+--------+-----------+---------
  0       | 10.50.1.153 | 5432 | 3      | 0.500000  | standby
  1       | 10.50.1.154 | 5432 | 2      | 0.500000  | standby
(2 rows)

Notice that even though node 1 is now a master (primary), pgpool doesn't 
seem to notice this.  It sees both as "standby" nodes.  However, even in 
this state, I'm able to write to the database; pgpool does seem to know 
something about what's happening, even if that isn't being reflected.

I am still experimenting with the various failover scripts, seeing which 
one gets executed when, and how I can use pcp_attach_node and 
pcp_detach_node to get things working correctly.

I know that when my old master comes up as the new slave, performing a 
pcp_attach_node gets everything to work correctly, with pool_nodes 
showing the right values.  But until then, pgpool seems confused.

(2) my psql client's connection to pgpool is reset when the master goes down

Is this normal?  I thought that pgpool should be insulating me from any 
problems on the nodes.  Would it help for me to run pcp_detach_node in a 
shell script, if and when the primary goes down?

Thanks in advance!

Reuven

-- 
Reuven M. Lerner, PhD -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
http://lerner.co.il * Skype / WeChat: ReuvenLerner * @reuvenmlerner