Cluster with 2 replicas and no primary anymore

[jose-joye]

** Which example are you working with? **
I have created a cluster with 1 primary and 1 replica and enabling the sync-replication (sync-level set to remote-apply).
I have setup this based on one of my previous questions: #2034.

The cluster has been working for some days. For any reason, the pods have been restarted and from that point we had no more primary but only 2 replicas:

$ pgo -n cfae-bit-cbcd show cluster pg-jose-benchmark-c4-remote-apply-35b24287

cluster : pg-jose-benchmark-c4-remote-apply-35b24287 (crunchy-postgres-ha:centos7-11.10-4.5.1)
        pod : pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-w4w7z (Running) on worker12.ccp02.adr.admin.ch (2/2) (replica)
                pvc: pg-jose-benchmark-c4-remote-apply-35b24287 (80Gi)
        pod : pg-jose-benchmark-c4-remote-apply-35b24287-ocul-6967cbf64dvsqgp (Running) on worker01.ccp02.adr.admin.ch (2/2) (replica)
                pvc: pg-jose-benchmark-c4-remote-apply-35b24287-ocul (80Gi)
        resources : CPU: 1 Memory: 4Gi
        limits : CPU: 1 Memory: 4Gi
        deployment : pg-jose-benchmark-c4-remote-apply-35b24287
        deployment : pg-jose-benchmark-c4-remote-apply-35b24287-backrest-shared-repo
        deployment : pg-jose-benchmark-c4-remote-apply-35b24287-ocul
        service : pg-jose-benchmark-c4-remote-apply-35b24287 - ClusterIP (10.98.71.131) - Ports (9187/TCP, 2022/TCP, 5432/TCP)
        service : pg-jose-benchmark-c4-remote-apply-35b24287-np - ClusterIP (10.102.204.208) - Ports (5432/TCP)
        service : pg-jose-benchmark-c4-remote-apply-35b24287-replica - ClusterIP (10.102.174.187) - Ports (9187/TCP, 2022/TCP, 5432/TCP)
        pgreplica : pg-jose-benchmark-c4-remote-apply-35b24287-ocul
        labels : cpu-in-milli-cpu=1000 pgo-version=4.5.1 name=pg-jose-benchmark-c4-remote-apply-35b24287 pg-cluster=pg-jose-benchmark-c4-remote-apply-35b24287 space-guid=guid-b1569ac2-a5bc-4d01-9037-b5e7dda1cb43 storage-config=storage-80 pgo-osb-instance=15b9d562-a960-4410-920a-ce484cda7c93 service-type=ClusterIP autofail=true crunchy-pgbadger=false crunchy-pgha-scope=pg-jose-benchmark-c4-remote-apply-35b24287 memory-in-gb=4 pgo-backrest=true pgouser=admin sync-replication=true workflowid=a392296e-1842-4e87-9b7d-910893dbd18b crunchy-postgres-exporter=true deployment-name=pg-jose-benchmark-c4-remote-apply-35b24287-ocul org-guid=guid-7751d33a-be22-4539-8629-231bb99ef686 pg-pod-anti-affinity=

The *-config configMap:

$ kubectl get configmap pg-jose-benchmark-c4-remote-apply-35b24287-config -o yaml
apiVersion: v1
kind: ConfigMap
metadata:
  annotations:
    config: '{"postgresql":{"parameters":{"archive_command":"source /opt/cpm/bin/pgbackrest/pgbackrest-set-env.sh
      && pgbackrest archive-push \"%p\"","archive_mode":true,"archive_timeout":"1800","checkpoint_completion_target":0.9,"checkpoint_timeout":"15min","effective_cache_size":"3GB","effective_io_concurrency":300,"huge_pages":"off","log_connections":"off","log_directory":"pg_log","log_disconnections":"off","log_duration":"off","log_min_duration_statement":60000,"log_min_messages":"WARNING","log_statement":"none","logging_collector":"off","maintenance_work_mem":"320MB","max_connections":100,"max_parallel_workers":1,"max_wal_senders":10,"max_wal_size":"1024MB","max_worker_processes":1,"min_wal_size":"512MB","shared_buffers":"1024MB","shared_preload_libraries":"pgaudit.so,pg_stat_statements.so,pgnodemx.so","superuser_reserved_connections":3,"synchronous_commit":"remote_apply","synchronous_standby_names":"*","temp_buffers":"8MB","unix_socket_directories":"/tmp,/crunchyadm","wal_keep_segments":130,"wal_level":"logical","work_mem":"32MB"},"recovery_conf":{"restore_command":"source
      /opt/cpm/bin/pgbackrest/pgbackrest-set-env.sh && pgbackrest archive-get %f \"%p\""},"use_pg_rewind":true},"synchronous_mode":true,"tags":{}}'
    history: '[[1,4596957336,"no recovery target specified","2020-12-15T10:49:37.113733+00:00"]]'
    initialize: "6904548279672279201"
  creationTimestamp: "2020-12-10T08:53:39Z"
  labels:
    crunchy-pgha-scope: pg-jose-benchmark-c4-remote-apply-35b24287
    vendor: crunchydata
  name: pg-jose-benchmark-c4-remote-apply-35b24287-config
  namespace: cfae-bit-cbcd
  resourceVersion: "23801950"
  selfLink: /api/v1/namespaces/cfae-bit-cbcd/configmaps/pg-jose-benchmark-c4-remote-apply-35b24287-config
  uid: 7d4844e5-92a5-45ff-a918-59dc0afc51f8

it contains the necessary information for the sync-commit:

• "synchronous_commit":"remote_apply"
• "synchronous_standby_names":"*"
• "synchronous_mode":true

By looking at the *-sync config map, we can see that the leader is pointing to a non-existing pod

$ kubectl describe  configmap pg-jose-benchmark-c4-remote-apply-35b24287-sync
Name:         pg-jose-benchmark-c4-remote-apply-35b24287-sync
Namespace:    cfae-bit-cbcd
Labels:       crunchy-pgha-scope=pg-jose-benchmark-c4-remote-apply-35b24287
              vendor=crunchydata
Annotations:  leader: pg-jose-benchmark-c4-remote-apply-35b24287-ocul-6967cbf64df2rp9

Data
====
Events:  <none>

I have followed the https://info.crunchydata.com/blog/synchronous-replication-in-the-postgresql-operator-for-kubernetes-guarding-against-transactions-loss documentation and created several clusters and performed same failover tests as described and was always ok. However, this case occured without manual intervention and I do not see how to proceed to get it up again.

Thanks,
José

Please tell us about your environment:

• Operating System: SUSE Linux Enterprise Server 15 SP1
• Where is this running ( Local)
• Storage being used (NFS)
• Container Image Tag: crunchy-postgres-ha:centos7-11.10-4.5.1
• PostgreSQL Version: 11.10
• Platform (kubernetes 1.17.13 )

[jkatz]

What do the PostgreSQL logs say?

[jose-joye]

Hi @jkatz,

Here is the information:

The initial replica has not been aware of anything and keep saying : following a different leader because i am not the healthiest node.

I restarted the pod with the deployment that was previously the primary:

k logs pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c -c database
Fri Dec 18 13:57:54 UTC 2020 INFO: postgres-ha pre-bootstrap starting...
Fri Dec 18 13:57:54 UTC 2020 INFO: pgBackRest auto-config disabled
Fri Dec 18 13:57:54 UTC 2020 INFO: PGHA_PGBACKREST_LOCAL_S3_STORAGE and PGHA_PGBACKREST_INITIALIZE will be ignored if provided
Fri Dec 18 13:57:54 UTC 2020 INFO: Defaults have been set for the following postgres-ha auto-configuration env vars: PGHA_DEFAULT_CONFIG, PGHA_BASE_BOOTSTRAP_CONFIG, PGHA_BASE_PG_CONFIG
Fri Dec 18 13:57:54 UTC 2020 INFO: Defaults have been set for the following postgres-ha env vars: PGHA_PATRONI_PORT
Fri Dec 18 13:57:54 UTC 2020 INFO: Defaults have been set for the following Patroni env vars: PATRONI_NAME, PATRONI_RESTAPI_LISTEN, PATRONI_RESTAPI_CONNECT_ADDRESS, PATRONI_POSTGRESQL_LISTEN, PATRONI_POSTGRESQL_CONNECT_ADDRESS
Fri Dec 18 13:57:54 UTC 2020 INFO: Setting postgres-ha configuration for database user credentials
Fri Dec 18 13:57:54 UTC 2020 INFO: Setting 'pguser' credentials using file system
Fri Dec 18 13:57:58 UTC 2020 INFO: Setting 'superuser' credentials using file system
Fri Dec 18 13:57:58 UTC 2020 INFO: Setting 'replicator' credentials using file system
Fri Dec 18 13:57:58 UTC 2020 INFO: Applying base bootstrap config to postgres-ha configuration
Fri Dec 18 13:57:58 UTC 2020 INFO: Applying base postgres config to postgres-ha configuration
Fri Dec 18 13:57:58 UTC 2020 INFO: Applying pgbackrest config to postgres-ha configuration
Fri Dec 18 13:57:58 UTC 2020 INFO: Applying synchronous replication settings to postgres-ha configuration
Fri Dec 18 13:57:58 UTC 2020 INFO: Applying standard (non-TLS) remote connection configuration to pg_hba.conf
Fri Dec 18 13:57:58 UTC 2020 INFO: Custom postgres-ha configuration file not detected
Fri Dec 18 13:57:58 UTC 2020 INFO: Finished building postgres-ha configuration file '/tmp/postgres-ha-bootstrap.yaml'
Fri Dec 18 13:57:58 UTC 2020 INFO: postgres-ha pre-bootstrap complete!  The following configuration will be utilized to initialize
******************************
postgres-ha (PGHA) env vars:
******************************
PGHA_DEFAULT_CONFIG=true
PGHA_REPLICA_REINIT_ON_START_FAIL=true
PGHA_PGBACKREST_LOCAL_S3_STORAGE=false
PGHA_PGBACKREST=true
PGHA_TLS_ENABLED=false
PGHA_STANDBY=false
PGHA_PATRONI_PORT=8009
PGHA_TLS_ONLY=false
PGHA_USER=postgres
PGHA_PG_PORT=5432
PGHA_CRUNCHYADM=true
PGHA_BASE_BOOTSTRAP_CONFIG=true
PGHA_DATABASE=userdb
PGHA_BASE_PG_CONFIG=true
PGHA_INIT=false
PGHA_SYNC_REPLICATION=true
******************************
Patroni env vars:
******************************
PATRONI_LOG_LEVEL=INFO
PATRONI_KUBERNETES_SCOPE_LABEL=crunchy-pgha-scope
PATRONI_KUBERNETES_NAMESPACE=cfae-bit-cbcd
PATRONI_SCOPE=pg-jose-benchmark-c4-remote-apply-35b24287
PATRONI_POSTGRESQL_DATA_DIR=/pgdata/pg-jose-benchmark-c4-remote-apply-35b24287
PATRONI_POSTGRESQL_LISTEN=0.0.0.0:5432
PATRONI_RESTAPI_LISTEN=0.0.0.0:8009
PATRONI_KUBERNETES_LABELS={vendor: "crunchydata"}
PATRONI_POSTGRESQL_CONNECT_ADDRESS=10.244.12.213:5432
PATRONI_RESTAPI_CONNECT_ADDRESS=10.244.12.213:8009
PATRONI_NAME=pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c
******************************
Patroni bootstrap method: existing_init
******************************
Patroni configuration file:
******************************
bootstrap:
  method: existing_init
  pgbackrest_init:
    command: '/opt/cpm/bin/pgbackrest/pgbackrest-create-replica.sh primary'
    keep_existing_recovery_conf: true
  existing_init:
    command: '/opt/cpm/bin/bootstrap/create-from-existing.sh'
    keep_existing_recovery_conf: true
  dcs:
    postgresql:
      parameters:
        unix_socket_directories: /tmp,/crunchyadm
        wal_level: logical
        archive_mode: on
        archive_command: 'source /opt/cpm/bin/pgbackrest/pgbackrest-set-env.sh &&
          pgbackrest archive-push "%p"'
        synchronous_commit: "on"
        synchronous_standby_names: "*"
      use_slots: false
      recovery_conf:
        restore_command: 'source /opt/cpm/bin/pgbackrest/pgbackrest-set-env.sh &&
          pgbackrest archive-get %f "%p"'
    synchronous_mode: true
  post_bootstrap: /opt/cpm/bin/bootstrap/post-bootstrap.sh
postgresql:
  use_unix_socket: true
  pgpass: /tmp/.pgpass
  create_replica_methods:
  - pgbackrest
  - basebackup
  pgbackrest:
    command: '/opt/cpm/bin/pgbackrest/pgbackrest-create-replica.sh replica'
    keep_data: true
    no_params: true
  pgbackrest_standby:
    command: '/opt/cpm/bin/pgbackrest/pgbackrest-create-replica.sh standby'
    keep_data: true
    no_params: true
    no_master: 1
  remove_data_directory_on_rewind_failure: true
  callbacks:
    on_role_change: /opt/cpm/bin/callbacks/pgha-on-role-change.sh
  pg_hba:
  - local all postgres peer
  - local all crunchyadm peer
  - host replication primaryuser 0.0.0.0/0 md5
  - host all primaryuser 0.0.0.0/0 reject
  - host all all 0.0.0.0/0 md5
Fri Dec 18 13:57:58 UTC 2020 INFO: Applying SSHD..
Fri Dec 18 13:57:58 UTC 2020 INFO: Checking for SSH Host Keys in /sshd..
Fri Dec 18 13:57:58 UTC 2020 INFO: Checking for authorized_keys in /sshd
Fri Dec 18 13:57:58 UTC 2020 INFO: Checking for sshd_config in /sshd
Fri Dec 18 13:57:58 UTC 2020 INFO: setting up .ssh directory
Fri Dec 18 13:57:58 UTC 2020 INFO: Starting SSHD..
WARNING: 'UsePAM no' is not supported in Red Hat Enterprise Linux and may cause several problems.
Fri Dec 18 13:57:58 UTC 2020 INFO: Starting background process to monitor Patroni initization and restart the database if needed
Fri Dec 18 13:57:58 UTC 2020 INFO: Now removing "pause" key from patroni.dynamic.json configuration file if present
Fri Dec 18 13:57:58 UTC 2020 INFO: Initializing cluster bootstrap with command: '/usr/local/bin/patroni /tmp/postgres-ha-bootstrap.yaml'
Fri Dec 18 13:57:58 UTC 2020 INFO: Running Patroni as PID 1
2020-12-18 13:57:59,434 INFO: No PostgreSQL configuration items changed, nothing to reload.
2020-12-18 13:57:59,445 INFO: Reaped pid=144, exit status=0
2020-12-18 13:57:59,457 INFO: Reaped pid=147, exit status=0
2020-12-18 13:57:59,458 WARNING: Postgresql is not running.
2020-12-18 13:57:59,458 INFO: Lock owner: None; I am pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c
2020-12-18 13:57:59,465 INFO: Reaped pid=148, exit status=0
2020-12-18 13:57:59,466 INFO: pg_controldata:
  pg_control version number: 1100
  Catalog version number: 201809051
  Database system identifier: 6904548279672279201
  Database cluster state: shut down in recovery
  pg_control last modified: Fri Dec 18 13:57:47 2020
  Latest checkpoint location: 1/15000FC8
  Latest checkpoint's REDO location: 1/15000F90
  Latest checkpoint's REDO WAL file: 000000020000000100000015
  Latest checkpoint's TimeLineID: 2
  Latest checkpoint's PrevTimeLineID: 2
  Latest checkpoint's full_page_writes: on
  Latest checkpoint's NextXID: 0:424235
  Latest checkpoint's NextOID: 24988
  Latest checkpoint's NextMultiXactId: 1
  Latest checkpoint's NextMultiOffset: 0
  Latest checkpoint's oldestXID: 562
  Latest checkpoint's oldestXID's DB: 1
  Latest checkpoint's oldestActiveXID: 424235
  Latest checkpoint's oldestMultiXid: 1
  Latest checkpoint's oldestMulti's DB: 1
  Latest checkpoint's oldestCommitTsXid: 0
  Latest checkpoint's newestCommitTsXid: 0
  Time of latest checkpoint: Tue Dec 15 11:35:51 2020
  Fake LSN counter for unlogged rels: 0/1
  Minimum recovery ending location: 1/16000000
  Min recovery ending loc's timeline: 2
  Backup start location: 0/0
  Backup end location: 0/0
  End-of-backup record required: no
  wal_level setting: logical
  wal_log_hints setting: on
  max_connections setting: 100
  max_worker_processes setting: 8
  max_prepared_xacts setting: 0
  max_locks_per_xact setting: 64
  track_commit_timestamp setting: off
  Maximum data alignment: 8
  Database block size: 8192
  Blocks per segment of large relation: 131072
  WAL block size: 8192
  Bytes per WAL segment: 16777216
  Maximum length of identifiers: 64
  Maximum columns in an index: 32
  Maximum size of a TOAST chunk: 1996
  Size of a large-object chunk: 2048
  Date/time type storage: 64-bit integers
  Float4 argument passing: by value
  Float8 argument passing: by value
  Data page checksum version: 1
  Mock authentication nonce: 76351ab69d1ad074265db74a1564b5262fc2754e11b11c12cdb7307068e2b813

2020-12-18 13:57:59,466 INFO: Lock owner: None; I am pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c
2020-12-18 13:57:59,479 INFO: Reaped pid=150, exit status=0
2020-12-18 13:57:59,480 INFO: Lock owner: None; I am pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c
2020-12-18 13:57:59,539 INFO: starting as a secondary
2020-12-18 13:57:59,798 INFO: postmaster pid=156
/tmp:5432 - no response
2020-12-18 13:57:59.814 UTC [156] LOG:  pgaudit extension initialized
2020-12-18 13:57:59.817 UTC [156] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-12-18 13:57:59.819 UTC [156] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-18 13:57:59.825 UTC [156] LOG:  listening on Unix socket "/crunchyadm/.s.PGSQL.5432"
2020-12-18 13:57:59.881 UTC [161] LOG:  database system was shut down in recovery at 2020-12-18 13:57:47 UTC
2020-12-18 13:57:59.881 UTC [162] FATAL:  the database system is starting up
2020-12-18 13:58:00.147 UTC [161] LOG:  entering standby mode
2020-12-18 13:58:00.413 UTC [161] LOG:  restored log file "00000002.history" from archive
2020-12-18 13:58:00.782 UTC [161] LOG:  restored log file "000000020000000100000015" from archive
2020-12-18 13:58:00.806 UTC [161] LOG:  redo starts at 1/15000F90
2020-12-18 13:58:00.814 UTC [161] LOG:  consistent recovery state reached at 1/16000000
2020-12-18 13:58:00.815 UTC [156] LOG:  database system is ready to accept read only connections
2020-12-18 13:58:00.815 UTC [176] FATAL:  the database system is starting up
/tmp:5432 - rejecting connections
/tmp:5432 - accepting connections
2020-12-18 13:58:00,862 INFO: establishing a new patroni connection to the postgres cluster
Fri Dec 18 13:58:00 UTC 2020 INFO: Node pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c fully initialized for cluster pg-jose-benchmark-c4-remote-apply-35b24287 and is ready for use
2020-12-18 13:58:01,004 INFO: following a different leader because i am not the healthiest node
2020-12-18 13:58:11,368 INFO: following a different leader because i am not the healthiest node
2020-12-18 13:58:21,368 INFO: following a different leader because i am not the healthiest node
2020-12-18 13:58:31,368 INFO: following a different leader because i am not the healthiest node
2020-12-18 13:58:41,370 INFO: following a different leader because i am not the healthiest node

[jose-joye]

@jkatz
I tried in many ways to reproduce the issue.

In fact, I believe the following occurred:

1. The cluster was running.
2. The node where the primary was running disappeared.
3. The replica becomes the primary.
4. The node where the "new primary" was running also disappeared (At that time the second instance was scheduled on a new node. But it was not ready yet and therefore could not become the primary.
5. The 2nd instance has been scheduled onto a new node has also become a replica.
6. None of them could become primary.

I was able to reproduce this sequence by issuing the following (without wait time in between)

1. kubectl delete pod "primary pod"
2. kubectl delete pod "secondary pod"

In fact, using the information found in *-sync config map, I should be able to properly find out which deployment/pvc hold the up to date information and therefore, set it as primary.

However, my questions are :
What is the proper way to fix it and bring the cluster back to work?

• This situation can also occur if we drain the 2 nodes 1 after the other without allowing enough time to stabilize. Should we have an automated way to bring it back to again in such a situation?

Thanks a lot for any hints
José

[jkatz]

This situation can also occur if we drain the 2 nodes 1 after the other without allowing enough time to stabilize. Should we have an automated way to bring it back to again in such a situation?

Run more replicas? 😉

For the actual failure scenario, there's only so much you can do if the nodes of both instances fail. Since a proper leader election cannot be held, the safe measure is not to promote anything and to let the administrator decide what to do. I think the question I have is what made it such that a leader election could not occur? Was Patroni unable to reach a quorum, and if so, why?

So, while the tongue-in-cheek response is to "run more replicas" to mitigate the risk of this scenario, that is part of the solution. "High availability" really is about "mitigating the risk of downtime" -- the more options you have for failover, the more likely the system will tolerate fault.

What is the proper way to fix it and bring the cluster back to work?

Inspect the instances, determine which one you want to promote, and promote that one. If the combo of pgo failover --query and pgo failover --target don't work, you may need to exec into one of the Pods and perform a patronictl switchover.

Based on the state of the data, you may need to reprovision the replicas, though a pgBackRest delta restore should be performed (and definitely in 4.6 as we made some changes where for anything working with existing data sets, a delta restore will be attempted first).

[jose-joye]

@jkatz ,
Running more replicas is always a solution :-) . However, I'm still not fully convinced if I refer to the https://info.crunchydata.com/blog/synchronous-replication-in-the-postgresql-operator-for-kubernetes-guarding-against-transactions-loss post. Actually, this part makes me unsure:

This means that in the event that neither the original primary nor the current synchronous replica can be promoted (e.g. if both are unhealthy), the database will remain in a read-only state, even though other healthy (asynchronous) replicas might be present in the cluster.

If I'm correct, this means that we will still be in a state where all replicas will be in "replica" state.

The solution with pgo failover --target works fine and based on the value we have in the <cluster-name>-sync I can retarget the primary and the information within the config map is correct:

$ kubectl describe  configmap pg-jose-benchmark-c4-remote-apply-35b24287-sync
Name:         pg-jose-benchmark-c4-remote-apply-35b24287-sync
Namespace:    cfae-bit-cbcd
Labels:       crunchy-pgha-scope=pg-jose-benchmark-c4-remote-apply-35b24287
              vendor=crunchydata
Annotations:  leader: pg-jose-benchmark-c4-remote-apply-35b24287-ocul-6967cbf64dvsqgp
              sync_standby: pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c

$ pgo -n cfae-bit-cbcd show cluster  pg-jose-benchmark-c4-remote-apply-35b24287

cluster : pg-jose-benchmark-c4-remote-apply-35b24287 (crunchy-postgres-ha:centos7-11.10-4.5.1)
        pod : pg-jose-benchmark-c4-remote-apply-35b24287-869c9986fb-htd4c (Running) on worker12.ccp02.adr.admin.ch (2/2) (replica)
                pvc: pg-jose-benchmark-c4-remote-apply-35b24287 (80Gi)
        pod : pg-jose-benchmark-c4-remote-apply-35b24287-ocul-6967cbf64dvsqgp (Running) on worker01.ccp02.adr.admin.ch (2/2) (primary)
                pvc: pg-jose-benchmark-c4-remote-apply-35b24287-ocul (80Gi)
        resources : CPU: 1 Memory: 4Gi
        limits : CPU: 1 Memory: 4Gi
        deployment : pg-jose-benchmark-c4-remote-apply-35b24287
        deployment : pg-jose-benchmark-c4-remote-apply-35b24287-backrest-shared-repo
        deployment : pg-jose-benchmark-c4-remote-apply-35b24287-ocul
        service : pg-jose-benchmark-c4-remote-apply-35b24287 - ClusterIP (10.98.71.131) - Ports (9187/TCP, 2022/TCP, 5432/TCP)
        service : pg-jose-benchmark-c4-remote-apply-35b24287-np - ClusterIP (10.102.204.208) - Ports (5432/TCP)
        service : pg-jose-benchmark-c4-remote-apply-35b24287-replica - ClusterIP (10.102.174.187) - Ports (9187/TCP, 2022/TCP, 5432/TCP)
        pgreplica : pg-jose-benchmark-c4-remote-apply-35b24287-ocul
        labels : space-guid=guid-b1569ac2-a5bc-4d01-9037-b5e7dda1cb43 crunchy-pgbadger=false crunchy-pgha-scope=pg-jose-benchmark-c4-remote-apply-35b24287 crunchy-postgres-exporter=true deployment-name=pg-jose-benchmark-c4-remote-apply-35b24287-ocul memory-in-gb=4 name=pg-jose-benchmark-c4-remote-apply-35b24287 pg-cluster=pg-jose-benchmark-c4-remote-apply-35b24287 org-guid=guid-7751d33a-be22-4539-8629-231bb99ef686 storage-config=storage-80 pg-pod-anti-affinity= pgo-osb-instance=15b9d562-a960-4410-920a-ce484cda7c93 pgouser=admin service-type=ClusterIP sync-replication=true autofail=true cpu-in-milli-cpu=1000 pgo-backrest=true pgo-version=4.5.1 workflowid=a392296e-1842-4e87-9b7d-910893dbd18b

So, as always, thanks for the more-than-great support!
José

[jkatz]

So, I. missed the very first line for some reason where you said synchronous replication, as that will change my answer just slightly.

Synchronous replication is there to [guard against transaction loss[(https://access.crunchydata.com/documentation/postgres-operator/latest/architecture/high-availability/#synchronous-replication-guarding-against-transactions-loss), not ensure high availability -- you need for both instances to be healthy. In fact, the documentation is pretty clear on this:

Additionally, there is a potential impact to availability: if a synchronous replica crashes, any writes to the primary will be blocked until a replica is promoted to become a new synchronous replica of the primary.

So yes, the "adding more replicas" solution is wrong in this case. The solution is what I did ultimately suggest, which is to force the failover and election once both nodes are healthy.

The longer term solution is to support quorum commit which will be more HA friendly for workloads requiring synchronous replication. patroni/patroni#672 will ultimately add quorum commit support to Patroni.

[jose-joye]

@jkatz,

Looking forward for the quorum commit feature!

I may wrongly interpret the doc at https://info.crunchydata.com/blog/synchronous-replication-in-the-postgresql-operator-for-kubernetes-guarding-against-transactions-loss

However, it says the following:

Finally, availability is still ultimately favored over durability. More specifically, if a valid synchronous replica is not available in the PostgreSQL cluster (e.g. if no replicas exist), then synchronous replication will be disabled, ensuring the database is still able to accept writes. This effectively allows two node clusters to be supported, since the database can continue to accept writes even after the primary is lost (in other words, assuming the original is primary lost and the only replica available has been promoted to be the new primary, there will be no longer be another replica available to act as the synchronous replica).

This does not apply to my situation. But according to what is said above, if the replica is lost the primary will continue working.

José

[jkatz]

I did manage to reproduce your scenario on my own by doing the following. I provide analysis at the bottom.

tl;dr: In sync replication mode, Patroni will only promote the last known primary and replica. As Patroni keeps track of that via the Pod name and the Pods disappear, if the last known primary/replica disappear at the same time, you will have to manually promote a cluster.

1. Create a 2-node PostgreSQL cluster with synchronous replication.

pgo create cluster rhino --replica-count=1 --sync-replication

2. Manually scale down the replica Deployment. So the replica Pod is destroyed

When I did this, I received this message in the primary:

2020-12-21 01:54:05,524 INFO: Lock owner: rhino-5c688596db-gfx48; I am rhino-5c688596db-gfx48
2020-12-21 01:54:05,575 INFO: no action.  i am the leader with the lock
2020-12-21 01:54:15,523 INFO: Lock owner: rhino-5c688596db-gfx48; I am rhino-5c688596db-gfx48
2020-12-21 01:54:15,571 INFO: Updating synchronous privilege temporarily from ['rhino-ahdh-694666695-nsz6x'] to []
2020-12-21 01:54:15,622 INFO: Assigning synchronous standby status to []
server signaled
2020-12-21 01:54:15,634 INFO: no action.  i am the leader with the lock
2020-12-21 01:54:25,526 INFO: Lock owner: rhino-5c688596db-gfx48; I am rhino-5c688596db-gfx48
2020-12-21 01:54:25,575 INFO: no action.  i am the leader with the lock

Cool, so that supports what Andrew had written in that blog post.

3. Now, I scaled down the primary Deployment. Now there are no Pods running. This is the scenario that you had faced.
4. I brought back up both Deployments. Lo and behold:

2020-12-21 01:55:15,346 INFO: Lock owner: None; I am rhino-5c688596db-tcw4s
2020-12-21 01:55:15,350 INFO: Reaped pid=140, exit status=0
...
/tmp:5432 - rejecting connections
/tmp:5432 - rejecting connections
/tmp:5432 - accepting connections
2020-12-21 01:55:16,672 INFO: establishing a new patroni connection to the postgres cluster
2020-12-21 01:55:16,763 INFO: following a different leader because i am not the healthiest node
Mon Dec 21 01:55:17 UTC 2020 INFO: Node rhino-5c688596db-tcw4s fully initialized for cluster rhino and is ready for use
2020-12-21 01:55:27,186 INFO: following a different leader because i am not the healthiest node

A few observations:

• As all the nodes were down, there was no lock owner
• It does not appear that Patroni can initialize the election as none of the nodes are taking a lock.

It appears in this state it's necessary to trigger a manual failover as the election cannot be held. In my case, I ran pgo failover rhino --target=rhino

(I'm going to discuss this scenario with a few folks to see if there are any potential upstream behavior changes that are required, but going to go forward with the next experiment).

5. For argument sake, I am going to add an additional node to the cluster:

pgo scale rhino

6. I scale down the Deployment of the instance that was acting as the synchronous replica. Patroni swapped the other replica to act as the synchronous one:

2020-12-21 02:04:15,084 INFO: Updating synchronous privilege temporarily from ['rhino-ahdh-694666695-rn5l2'] to []
2020-12-21 02:04:15,137 INFO: Assigning synchronous standby status to ['rhino-rcyg-54675df69c-bp8d4']
server signaled
2020-12-21 02:04:17,226 INFO: Synchronous standby status assigned to ['rhino-rcyg-54675df69c-bp8d4']

I will note that the other replica dropped out of Patroni's purview, i.e. it was not coming up in patronictl list

7. I scaled down the second replica Deployment. Same behavior as step 2:

2020-12-21 02:06:05,036 INFO: Lock owner: rhino-5c688596db-tcw4s; I am rhino-5c688596db-tcw4s
2020-12-21 02:06:05,089 INFO: Updating synchronous privilege temporarily from ['rhino-rcyg-54675df69c-bp8d4'] to []
2020-12-21 02:06:05,135 INFO: Assigning synchronous standby status to []
server signaled

8. I scale down the cluster completely. Then scaled everything back up. Same scenario: every node came up as a replica as it could not establish a lock. Raw output:

+ Cluster: rhino (6908520796655591572) -------+---------+---------+----+-----------+
| Member                      | Host          | Role    | State   | TL | Lag in MB |
+-----------------------------+---------------+---------+---------+----+-----------+
| rhino-5c688596db-dfr59      | 10.85.196.174 | Replica | running |  2 |         0 |
| rhino-ahdh-694666695-lrgwl  | 10.85.196.156 | Replica | running |  2 |         0 |
| rhino-rcyg-54675df69c-px2wk | 10.85.196.159 | Replica | running |  2 |         0 |
+-----------------------------+---------------+---------+---------+----+-----------+

Anyway, poking around the Patroni code, I believe this behavior can be traced to here:

https://github.com/zalando/patroni/blob/311e5ccc5bdf43177a815eac4bf5f6afc3a40da8/patroni/ha.py#L787-L796

As all of the synchronous instances went down, including the last known primary and replica, and because Patroni keeps track of this by Pod name, Patroni won't automatically promote, so you have to do it manually.

I'm going to discuss with a few folks about this. In synchronous replication mode, this does sound like a safe option, and as mentioned, synchronous mode does favor durability over availability (in this case -- Andrew is correct that if a replica goes down, the primary will still exist. Unless you go into strict mode 😉).

[jose-joye]

@jkatz,

Thanks for the deep analysis.
At least, I understand fully what occurred and can now correctly react in such a situation

José

[jkatz]

Reopening as I plan to add some documentation around this in one of the troubleshooting sections.