[pgpool-general: 8731] Re: Postgres 14 / Patroni 3.01 / PGPool 4.3.3..."play nice" together settings
Dave Sisk
dave.sisk at bullhorn.com
Tue Apr 11 22:30:41 JST 2023
Tatsuo,
Thank you so much for the response to this. This config isn't working as
we would expect though. (Answering your question: Yes, Patroni does a
pg_rewind on any orphaned replicas that are still up.)
We get the behavior shown below. Node1 was former primary, we stopped that
node, node 0 became the new primary, and Patroni executed pg_rewind on
node1 and node2. In the show pool_nodes output, *the pg_role is correct,
but the role is not*:
postgres at mrkpgdb3:~$ psql -h mrkpgdb3 -p 5432 -c 'show pool_nodes;'
Pager usage is off.
node_id | hostname | port | status | pg_status | lb_weight | *role*
| *pg_role* | select_cnt | load_balance_node | replication_delay |
replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | mrkpgdb1 | 5433 | up | up | 0.333333 | standby
| *primary* | 0 | true | 0 |
| | 2023-04-11 09:02:18
1 | mrkpgdb2 | 5433 | up | up | 0.333333 |
*primary* | standby | 0 | false | 0
| | | 2023-04-11 09:02:18
2 | mrkpgdb3 | 5433 | up | up | 0.333333 | standby
| standby | 0 | false | 0 |
| | 2023-04-11 09:02:18
(3 rows)
Here is our PGPool configuration:
root at mrkpgdb3:~# cat /etc/pgpool2/pgpool.conf
### On-Prem PGPool config parameters
# Static config parameters
listen_addresses = '*'
port = 5432
serialize_accept = on
num_init_children = 1048
max_pool = 1
listen_backlog_multiplier = 1
log_destination = 'syslog,stderr'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
pid_file_name = '/var/run/postgresql/pgpool.pid'
logdir = '/tmp'
connection_life_time = 300
connection_cache = OFF
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
child_life_time = 300
client_idle_limit = 0
memory_cache_enabled = off
sr_check_user = 'xxx'
sr_check_password = 'xxx'
sr_check_database = 'postgres'
sr_check_period = 1
delay_threshold = 1
log_standby_delay = 'if_over_threshold'
failover_on_backend_error = FALSE
allow_multiple_failover_requests_from_node = OFF
health_check_user = 'xxx'
health_check_password = 'xxx'
health_check_period = 1
health_check_timeout = 1
health_check_max_retries = 3
health_check_retry_delay = 1
connect_timeout = 800
master_slave_mode = on
master_slave_sub_mode = 'stream'
failover_command = ''
auto_failback = ON
detach_false_primary = ON
#failback_command = '/etc/pgpool2/failback.sh %d %h %p'
#follow_master_command = '/etc/pgpool2/follow_master.sh %d %h %p postgres'
recovery_user = 'xxx'
recovery_password = 'xxx'
#recovery_1st_stage_command = 'recovery_1st_stage.sh'
recovery_timeout = 5
ssl = OFF
ssl_cert = '/etc/pgpool2/server.crt'
ssl_key = '/etc/pgpool2/server.key'
ssl_prefer_server_ciphers = ON
ssl_ciphers = 'HIGH:!MEDIUM:!LOW:!SSLv2:!SSLv3:!TLSv1:!TLSv1.1:+TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA256:DHE-RSA-AES256-SHA::DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA:!3DES'
use_watchdog = OFF
failover_when_quorum_exists = on
failover_require_consensus = on
#wd_port = 9000
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 15
heartbeat_destination_port0 = 9694
heartbeat_destination_port1 = 9694
other_wd_port0 = 9000
other_wd_port1 = 9000
other_pgpool_port0 = 5432
other_pgpool_port1 = 5432
#wd_escalation_command = '/etc/pgpool2/escalate.sh -e'
#wd_de_escalation_command = '/etc/pgpool2/escalate.sh -d'
if_cmd_path = '/etc/pgpool2'
if_up_cmd = 'ip addr add $_IP_$/24 dev ens3 label ens3:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev ens3'
arping_path = '/etc/pgpool2'
arping_cmd = 'arping -U $_IP_$ -w 1'
# Host and Cluster specific parameters
backend_hostname0 = 'mrkpgdb1'
backend_data_directory0 = '/var/lib/postgresql/14/main'
backend_port0 = 5433
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'mrkpgdb1'
backend_hostname1 = 'mrkpgdb2'
backend_data_directory1 = '/var/lib/postgresql/14/main'
backend_port1 = 5433
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'mrkpgdb2'
backend_hostname2 = 'mrkpgdb3'
backend_data_directory2 = '/var/lib/postgresql/14/main'
backend_port2 = 5433
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'mrkpgdb3'
load_balance_mode = ON
delegate_IP = '172.19.7.240'
wd_hostname = 'mrkpgdb3'
wd_priority = 3
heartbeat_destination0 = 'mrkpgdb1'
other_pgpool_hostname0 = 'mrkpgdb1'
heartbeat_destination1 = 'mrkpgdb2'
other_pgpool_hostname1 = 'mrkpgdb2'
trusted_servers = '172.19.7.1'
Here's the PGPool version:
postgres at mrkpgdb3:~$ pgpool -v
pgpool-II version 4.3.5 (tamahomeboshi)
Are there other parameters we need to unset in PGPool.conf?
On Tue, Apr 4, 2023 at 11:12 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> Hi,
>
> > We want to examine using PGPool as an intelligent proxy only, while
> letting
> > Patronic orchestrate failover/switcher and rewind/recovery.
> >
> > Obviously, our failover_command and other similar settings should just be
> > blank. What other settings should I make to allow PGPool to recognize
> that
> > the primary might have changed when PGPool is not executing that change?
> >
> > We've tried setting backend_flag0/1/2 = 'DISALLOW_TO_FAILOVER', but
> PGPool
> > did not recognize the change in primary.
>
> You should not set DISALLOW_TO_FAILOVER. Even if you want setup
> Pgpool-II as a proxy, you should not turn off failover because
> Pgpool-II recognizes the primary change at failover. You just need to
> disable failover_command and follow_primary_command. Also I recommend
> to disable failover_on_backend_error to avoid accidental failover.
> Thus followings are necessary set up:
>
> failover_command = ''
> follow_primary_command = ''
> failover_on_backend_error = off
>
> With this settings the actual operation looks like this (suppose we
> have 3 PostgreSQL servers).
>
> # The Initial state: node 0 is primary.
>
> $ pcp_node_info -w -p 11001
> localhost 11002 1 0.333333 waiting up primary primary 0 none none
> 2023-04-05 11:54:15
> localhost 11003 1 0.333333 waiting up standby standby 0 streaming async
> 2023-04-05 11:54:15
> localhost 11004 1 0.333333 waiting up standby standby 0 streaming async
> 2023-04-05 11:54:15
>
> # Stop the primary
>
> $ pg_ctl -D data0 stop
> waiting for server to shut down.... done
> server stopped
>
> # node 0 is surely down.
>
> $ pcp_node_info -w -p 11001
> localhost 11002 1 0.333333 waiting down primary unknown 0 none none
> 2023-04-05 11:54:15
> localhost 11003 1 0.333333 waiting up standby standby 0 streaming async
> 2023-04-05 11:54:15
> localhost 11004 1 0.333333 waiting up standby standby 0 streaming async
> 2023-04-05 11:54:15
>
> # make node 1 to new primary.
>
> $ pg_ctl -D data1 promote
> waiting for server to promote.... done
> server promoted
>
> # As you can see, pgpool reconizes new primary.
>
> $ pcp_node_info -w -p 11001
> localhost 11002 3 0.333333 down down standby unknown 0 none none
> 2023-04-05 11:55:16
> localhost 11003 1 0.333333 waiting up primary primary 0 none none
> 2023-04-05 11:55:16
> localhost 11004 1 0.333333 waiting up standby standby 104 none none
> 2023-04-05 11:54:15
>
> Note that node 2 is not following new primary. Maybe Patroni takes care of
> this?
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> <http://www.sraoss.co.jp/index_en>
> Japanese:http://www.sraoss.co.jp
> <http://www.sraoss.co.jp>
>
--
*Dave Sisk*
*Data Architect*
T:
--
<https://www.linkedin.com/company/bullhorn>
<https://twitter.com/Bullhorn> <https://www.youtube.com/@BULLHORNTV>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230411/07669501/attachment.htm>
More information about the pgpool-general
mailing list