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

[Tatsuo Ishii]

I am not familiar with repmgr but I guess pgpool-II is confused by
being taken over the task of failover and promotion by repmgr.

To let pgpool-II properly work, you need to let pgpool-II handle
faiover and promotion.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general