[bitnami/postgresql-repmgr] Cannot configure right sync state and synchronous_standby_names in postgres-repmgr

[luisxkimo]

Name and Version

bitnami/postgresql-repmgr:17.4.0

What architecture are you using?

amd64

What steps will reproduce the bug?

In a setup with 1 primary and 2 replicas, create several containers with bitnami/postgresql-repmgr:17.4.0 and this configurations:

Primary:

          POSTGRESQL_PASSWORD: "abc1234"
          POSTGRESQL_INITDB_WAL_DIR: "/mnt/waldir"
          POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS: "1"
          POSTGRESQL_SYNCHRONOUS_REPLICAS_MODE: "FIRST"
          POSTGRESQL_CLUSTER_APP_NAME: "*"

          POSTGRESQL_REPLICATION_MODE: "master"

          REPMGR_PARTNER_NODES: "hostname0.com,hostname1.com,hostname2.com"
          REPMGR_NODE_NAME: "pg-0"
          REPMGR_NODE_NETWORK_NAME: "hostname0.com"
          REPMGR_PRIMARY_HOST: "hostname0.com"
          REPMGR_PASSWORD: "abc12345"

Replica1:

          POSTGRESQL_PASSWORD: "abc1234"
          POSTGRESQL_INITDB_WAL_DIR: "/mnt/waldir"
          POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS: "1"
          POSTGRESQL_SYNCHRONOUS_REPLICAS_MODE: "FIRST"
          POSTGRESQL_CLUSTER_APP_NAME: "*"

          POSTGRESQL_REPLICATION_MODE: "slave"
          POSTGRESQL_MASTER_HOST:"hostname0.com"

          REPMGR_PARTNER_NODES: "hostname0.com,hostname1.com,hostname2.com"
          REPMGR_NODE_NAME: "pg-1"
          REPMGR_NODE_NETWORK_NAME: "hostname1.com"
          REPMGR_PRIMARY_HOST: "hostname1.com"
          REPMGR_PASSWORD: "abc12345"

Replica2:

          POSTGRESQL_PASSWORD: "abc1234"
          POSTGRESQL_INITDB_WAL_DIR: "/mnt/waldir"
          POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS: "1"
          POSTGRESQL_SYNCHRONOUS_REPLICAS_MODE: "FIRST"
          POSTGRESQL_CLUSTER_APP_NAME: "*"

          POSTGRESQL_REPLICATION_MODE: "slave"
          POSTGRESQL_MASTER_HOST:"hostname0.com"
          REPMGR_PARTNER_NODES: "hostname0.com,hostname1.com,hostname2.com"
          REPMGR_NODE_NAME: "pg-2"
          REPMGR_NODE_NETWORK_NAME: "hostname2.com"
          REPMGR_PRIMARY_HOST: "hostname2.com"
          REPMGR_PASSWORD: "abc12345"

What is the expected behavior?

Expected result of this query SHOW synchronous_standby_names;:

FIRST 1 ("*")

And about this query: SELECT sync_state FROM pg_stat_replication;: expected have "sync"

What do you see instead?

• result of this query SHOW synchronous_standby_names;: FIRST 1 ("hostname0","hostname1","hostname2")

• result And about this query: SELECT sync_state FROM pg_stat_replication;: "async"

Additional information

No response

[javsalgar]

Hi,

Could you show the generated postgresql.conf to see what the synchronous replication setting looks like? You can enter the container with docker exec

[luisxkimo]

Hi @javsalgar

Here is that I guess that is the relevant information of postgres.conf file. So simplify, I will put only configurations about primary and replica 1, because for replica 2 its same like first one.

---

PRIMARY

Environment variables:

REPMGR_PARTNER_NODES: "hostname0.com,hostname1.com,hostname2.com"
POSTGRESQL_CLUSTER_APP_NAME: "pg-0"
POSTGRESQL_SYNCHRONOUS_REPLICAS_MODE: "FIRST"
POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS: "1"

postgres.conf file

synchronous_standby_names = 'FIRST 1 ("hostname0","hostname1")'                          
                                # method to choose sync standbys, number of sync standbys,   
                                # and comma-separated list of application_name               
                                # from standby(s); '*' = all                                 
hot_standby = 'on'

---

REPLICA

REPMGR_PARTNER_NODES: "hostname0.com,hostname1.com,hostname2.com"
POSTGRESQL_CLUSTER_APP_NAME: "pg-1"
POSTGRESQL_SYNCHRONOUS_REPLICAS_MODE: "FIRST"
POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS: "1"

postgres.conf file

# synchronous_standby_names = ''
primary_conninfo = 'host=hostname0.com port=5432 user=repmgr password=xxx application_name=pg-1'
hot_standby = 'on'

---

Configuration in repmgr.conf file seems to have right values in general:

# FIXME: these 2 parameter should work                                                       
node_id=1001                                                                                 
node_name='pg-1'                                                                             
location='default'                                                                           
conninfo='user=repmgr password=xxx host=hostname1.com dbname=repmgr port=54
32 connect_timeout=5'                                                                        
failover='automatic'                                                                         
promote_command='PGPASSWORD=xxx repmgr standby promote -f "/opt/bitnami/repmgr/conf/
repmgr.conf" --log-level DEBUG --verbose'                                                    
follow_command='PGPASSWORD=xxx repmgr standby follow -f "/opt/bitnami/repmgr/conf/re
pmgr.conf" -W --log-level DEBUG --verbose'                                                   
reconnect_attempts='3'                                                                       
reconnect_interval='5'                                                                       
log_level='NOTICE'                                                                           
priority='100'                                                                               
monitoring_history='no'                                                                      
monitor_interval_secs='2'

And new behaviour detected: after restart container in primary the configuration in postgres.conf change to:

# synchronous_standby_names = ''

So in summary I guess that there are 3 issues:

1. Configuration of synchronous_standby_names is reset after restart container in primary
2. synchronous_standby_names first configuration is based on values of REPMGR_PARTNER_NODES. This is not expected because at the application_name in replicas are configured from other way and values could not match.
3. The value of application_name stored in table pg_stat_replication not match with the value stored in the postgres.conf (parameter primary_conninfo) for the replicas. This value is obtained from REPMGR_NODE_NAME container variable, and is not expected

[luisxkimo]

I update my previous comment about issue number 1.

I guess that is expected behavior to avoid to have 2 primary replicas, when a failover process starts after the first primary is down. The repmgr process starts to choose new primary between all available replicas in the cluster.

If original primary comes back to online again, won't be part of the cluster until is added manually (as new primary or new replica)

[github-actions]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[luisxkimo]

Ping to keep alive