[pgpool-general: 8732] Re: Postgres 14 / Patroni 3.01 / PGPool 4.3.3..."play nice" together settings
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Apr 11 22:57:28 JST 2023
Hi,
I noticed there's no
backend_clustering_mode = 'streaming_replication'
parameter. Instead you have:
> master_slave_mode = on
> master_slave_sub_mode = 'stream'
They are old format parameter and not necessary in 4.3.
Can you fix and try again?
> 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>
>
More information about the pgpool-general
mailing list