View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000449Pgpool-IIEnhancementpublic2018-12-03 06:162018-12-07 17:32
ReporterAlfonso Assigned Topengbo  
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionopen 
Product Version4.0.2 
Summary0000449: Standby status recovery
DescriptionI believe this is a enhancement for pgpool
I'm using Streaming Replication with two nodes: vmpg1 (primary) and vmpg2 (standby), and implemented an automatic failover strategy with repmgr that works (repmgr promotes the standby when the primary fail). There is no pgpool installation in vmpg1 nor vmpg2.
In this scenario I'm configured a third node with pgpool: vmpool, just for monitoring these two backends and failover from 0 to 1 when the primary fail. This part is working.
But, if the *standby* database stops, pgpool sets these status as down and it appears there is no way to recover the standby status while keeping pgpool online (a way is to restart pgpool causing downtime).

Below is the show pool_nodes before and after a standby restart

Before standby restart:

-[ RECORD 1 ]------+--------------------
node_id | 0
hostname | vmpg1
port | 5432
status | up
lb_weight | 0.500000
role | primary
select_cnt | 0
load_balance_node | true
replication_delay | 0
last_status_change | 2018-12-02 15:30:14
-[ RECORD 2 ]------+--------------------
node_id | 1
hostname | vmpg2
port | 5432
status | up
lb_weight | 0.500000
role | standby
select_cnt | 0
load_balance_node | false
replication_delay | 0
last_status_change | 2018-12-02 15:33:46

After standby restart:

-[ RECORD 1 ]------+--------------------
node_id | 0
hostname | vmpg1
port | 5432
status | up
lb_weight | 0.500000
role | primary
select_cnt | 0
load_balance_node | true
replication_delay | 0
last_status_change | 2018-12-02 15:30:14
-[ RECORD 2 ]------+--------------------
node_id | 1
hostname | vmpg2
port | 5432
status | down
lb_weight | 0.500000
role | standby
select_cnt | 0
load_balance_node | false
replication_delay | 0
last_status_change | 2018-12-02 15:33:46

At this point, no downtime has occurred, but if primary stops and repmgr promotes the standby, there is no way to pgpool failover to standby without downtime. So, if I want to continue protected, I need to restart pgpool with -D causing a downtime.

I believe would be a good idea to give a way to recover the standby status
Steps To Reproduce1) Configure a PostgreSQL cluster and a standby with Streaming Replication
2) Configure pgpool in a third node in MASTER/SLAVE MODE with this Backend Connection Settings

backend_hostname0 = 'vmpg1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.6/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'vmpg2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.6/cont'
backend_flag1 = 'ALLOW_TO_FAILOVER'

master_slave_mode = on
master_slave_sub_mode = 'stream'

3) Stop the standby
4) Verify pgpool show pool_nodes shows the standby with status down
5) Start the standby

Now, my question is: how to get protected again without downtime ?
Additional InformationPostgreSQL 9.6
pgpool II 4.0.2
TagsNo tags attached.

Activities

pengbo

2018-12-03 12:38

developer   ~0002297

> 3) Stop the standby
> 4) Verify pgpool show pool_nodes shows the standby with status down
> 5) Start the standby

After you stopped standby, Pgpool-II detached this node from cluster.
So you have to execute pcp_attach_node command to attach this node to the cluster again if you restart standby.

# pcp_attach_node -p <pcp port> -n <node id> -U <user name>

[pcp_attach_node]
http://www.pgpool.net/docs/latest/en/html/pcp-attach-node.html

Alfonso

2018-12-05 21:28

reporter   ~0002305

Thanks, it works !
This issue can be closed

Issue History

Date Modified Username Field Change
2018-12-03 06:16 Alfonso New Issue
2018-12-03 12:38 pengbo Assigned To => pengbo
2018-12-03 12:38 pengbo Status new => assigned
2018-12-03 12:38 pengbo Note Added: 0002297
2018-12-04 10:55 pengbo Status assigned => feedback
2018-12-05 21:28 Alfonso Note Added: 0002305
2018-12-05 21:28 Alfonso Status feedback => assigned
2018-12-07 17:32 administrator Status assigned => closed