Cross-site replication does not work with only 2 units in each cluster

[mastier]

Steps to reproduce

1. deploy 2 cluster in 2 models with just 2 postgresql units
2. Wait for them to establish sync replication
3. Setup cross-site replication as adviced in the docs

Expected behavior

Cluster sync configured

Actual behavior

The cluster to which the replication is assigned is stuck in waiting

postgresql/0*                         waiting   idle        0        10.46.16.24     5432/tcp  Still starting the database in the standby leader
postgresql/1                          waiting   idle        1        10.46.16.23     5432/tcp  Waiting for the standby leader start the database

Versions

24.04.5

Juju CLI:
3.6.11

Juju agent:
3.6.11

Charm revision:
rev952 (16/stable)

LXD:
5.21.6

Log output

==> /var/snap/charmed-postgresql/common/var/log/patroni/patroni.log.7 <==                           
2025-10-29 17:57:34 UTC [195876]: INFO: Lock owner: None; I am postgresql-0                         
2025-10-29 17:57:34 UTC [195876]: INFO: trying to bootstrap a new standby leader                    
2025-10-29 17:57:34 UTC [195876]: ERROR: Error when fetching backup: pg_basebackup exited with code=1
2025-10-29 17:57:34 UTC [195876]: WARNING: Trying again in 5 seconds                                
2025-10-29 17:57:39 UTC [195876]: ERROR: Error when fetching backup: pg_basebackup exited with code=1
2025-10-29 17:57:39 UTC [195876]: ERROR: failed to bootstrap clone from remote member postgresql://10.46.28.24:5432
                                                                                                    
==> /var/snap/charmed-postgresql/common/var/log/patroni/patroni.log.70 <==                          
2025-10-29 17:55:48 UTC [193558]: ERROR: Could not remove data directory /var/snap/charmed-postgresql/common/var/lib/postgresql
Traceback (most recent call last):                                                                  
  File "/usr/lib/python3/dist-packages/patroni/postgresql/__init__.py", line 1346, in remove_data_directory
    shutil.rmtree(self._data_dir)                                                                   
  File "/usr/lib/python3.12/shutil.py", line 796, in rmtree                                         
    onexc(os.rmdir, path, err)                                                                      
  File "/usr/lib/python3.12/shutil.py", line 794, in rmtree                                         
    os.rmdir(path, dir_fd=dir_fd)                                                                   
OSError: [Errno 16] Device or resource busy: '/var/snap/charmed-postgresql/common/var/lib/postgresql'
                                                                                                    
==> /var/snap/charmed-postgresql/common/var/log/patroni/patroni.log.73 <==                          
2025-10-29 17:55:40 UTC [193505]: ERROR: Could not rename data directory /var/snap/charmed-postgresql/common/var/lib/postgresql
Traceback (most recent call last):                                                                  
  File "/usr/lib/python3/dist-packages/patroni/postgresql/__init__.py", line 1319, in move_data_directory
    os.rename(self._data_dir, new_name)                                                             
OSError: [Errno 16] Device or resource busy: '/var/snap/charmed-postgresql/common/var/lib/postgresql' -> '/var/snap/charmed-postgresql/common/var/lib/postgresql.failed'
2025-10-29 17:55:43 UTC [193558]: INFO: No PostgreSQL configuration items changed, nothing to reload.

Additional context

We got internal discussion and seems that was already spotted in the internal Jira ticket DPE-8748

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-8781.

This message was autogenerated

[dragomirp]

Hi, @mastier, the underlying issue is pg_basebakup executed from Patroni being unable to make a copy from the primary cluster. There should be TLS errors in charmed-postgresql.patroni service's logs (from snap logs -n all charmed-postgresql.patroni or journalctl -u snap.charmed-postgresql.patroni) similar to:

Oct 30 16:21:05 juju-6515c0-0 charmed-postgresql.patroni[108068]: pg_basebackup: error: connection to server at "10.10.10.221", port 5432 failed: SSL error: certificate verify failed
Oct 30 16:21:05 juju-6515c0-0 charmed-postgresql.patroni[108068]: connection to server at "10.10.10.221", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "10.10.10.248", user "replication", no encryption
Oct 30 16:21:10 juju-6515c0-0 charmed-postgresql.patroni[108074]: pg_basebackup: error: connection to server at "10.10.10.221", port 5432 failed: SSL error: certificate verify failed
Oct 30 16:21:10 juju-6515c0-0 charmed-postgresql.patroni[108074]: connection to server at "10.10.10.221", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "10.10.10.248", user "replication", no encryption

It seems to be related to using spaces and TLS, not two unit clusters. It seems related to the ca provided to the replication user. I'm still investigating potential fix.

[taurus-forever]

For the history: it looks like it is an issue in the charm in case IF
a) juju spaces used
b) cluster A and cluster B uses different CAs on each side for client-interfaces endpoints.

This is not something we tested initially and keep testing on CI. We will keep the bugreport opened to check/fix this case.
However, for now, the client-certificates interface should use common CA on both sides (cluster A and cluster B).
In this case the Primary switchover A=>B will NOT force CA change for PostgreSQL clients.

[ggouzi]

Hi folks,

Indeed the async-replication fails because of a SSL verification error in Patroni.
Using the same self-signed-certificate app for both sites seems to work. I didn't fully try async-replication tests but all units were active/idle and no SSL error anymore.

However, I cannot make it work using Vault. We need to use Vault for production-grade environments.

• What works: Relating both postgresql:client-certificates (on both sites) to the same self-signed-certificates (cross-model relation)
• What doesn't work: Relating both postgresql:client-certificates (on both sites) to the same vault:vault-pki (cross-model relation). Vault itself being related to a unique self-signed-certificates application

Here are some logs when it fails with Vault:

• juju show-unit postgresql --endpoint certificates: https://pastebin.ubuntu.com/p/rMy9wVcyJX/
• patroni logs: https://pastebin.ubuntu.com/p/TxGJY6CJyz/
• Site2 (replica) status with relations: https://pastebin.ubuntu.com/p/PgFMVFB2rx/
• Site1 (main) status with relations: https://pastebin.ubuntu.com/p/cTpGm638sk/
• Site2 (replica) bundle: https://pastebin.ubuntu.com/p/qHSbr8qB3Q/
• Site1 (replica) bundle: https://pastebin.ubuntu.com/p/khXnknTbdd/
• Vault model bundle: https://pastebin.ubuntu.com/p/XhWVMfh7KX/

When using Vault, I confirm the same error logs:

pg_basebackup: error: connection to server at "10.46.28.23", port 5432 failed: SSL error: certificate verify failed
connection to server at "10.46.28.23", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "10.46.18.23", user "replication", no encryption

• Site1 and site2 have the same CN:

Site 2 (replica)
openssl x509 -in /var/snap/charmed-postgresql/current/etc/patroni/ca.pem -noout -subject -issuer
subject=CN = dev.XXX, x500UniqueIdentifier = f8fea6ed-8982-4335-808d-c020131f4217, O = XX, OU = XX, C = XX
issuer=CN = dev.XXX

Site 1 (main)
openssl x509 -in /var/snap/charmed-postgresql/current/etc/patroni/ca.pem -noout -subject -issuer
subject=CN = dev.XXX, x500UniqueIdentifier = f8fea6ed-8982-4335-808d-c020131f4217, O = XX, OU = XX, C = XX
issuer=CN = dev.XXX

Here are some openssl command when using Vault:

echo | openssl s_client -starttls postgres -connect 10.46.28.23:5432 -showcerts | openssl x509 -noout -subject -ext subjectAltName
Can't use SSL_get_servername
depth=1 CN = dev.XXX, x500UniqueIdentifier = f8fea6ed-8982-4335-808d-c020131f4217, O = XXX, OU = XXX, C = XXX
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 CN = 10.46.27.23
verify return:1
DONE
subject=CN = 10.46.27.23
X509v3 Subject Alternative Name: 
    DNS:10.46.27.23, DNS:dpshdev-postgresql-1.dev.XXX DNS:postgresql-0, IP Address:10.46.27.23

• And same openssl command when using self-signed-certificates directly

echo | openssl s_client -starttls postgres -connect 10.46.28.23:5432 -showcerts | openssl x509 -noout -subject -ext subjectAltName
Can't use SSL_get_servername
depth=1 CN = dev.XXX
verify error:num=19:self-signed certificate in certificate chain
verify return:1
depth=1 CN = dev.XXX
verify return:1
depth=0 CN = 10.46.27.23, x500UniqueIdentifier = bd699e67-2de4-49f2-b900-94c9145bf81d
verify return:1
DONE
subject=CN = 10.46.27.23, x500UniqueIdentifier = bd699e67-2de4-49f2-b900-94c9145bf81d
X509v3 Subject Alternative Name: 
    DNS:postgresql-0, DNS:dpshdev-postgresql-1.dev.XXX DNS:10.46.27.23, IP Address:10.46.27.23

However, when using Vault, the Authority Key Identifier (AKI) and Subject Key Identifier (SKI) do not match. It means ca.pem was issued by a different CA key (even though it uses the same CN = dev.XXX).
Using self-signed-certificates, the AKI and SKI match. Maybe this is the causing the issue ?

Using vault
openssl x509 -in /var/snap/charmed-postgresql/current/etc/patroni/ca.pem -text -noout | grep -A1 'Key Identifier'
            X509v3 Authority Key Identifier: 
                04:14:C9:FA:08:42:59:80:9B:2F:EE:D5:D8:59:85:9C:80:02:F1:46:C7:46
            X509v3 Subject Key Identifier: 
                08:54:73:64:FC:49:BE:78:DF:96:AA:FD:3B:C0:03:E9:B4:25:0E:9A

Using self-signed-certificates:

openssl x509 -in /var/snap/charmed-postgresql/current/etc/patroni/ca.pem -text -noout | grep -A1 'Key Identifier'
            X509v3 Subject Key Identifier: 
                04:14:C9:FA:08:42:59:80:9B:2F:EE:D5:D8:59:85:9C:80:02:F1:46:C7:46
            X509v3 Authority Key Identifier: 
                04:14:C9:FA:08:42:59:80:9B:2F:EE:D5:D8:59:85:9C:80:02:F1:46:C7:46

Vault is configured with the following options:

      pki_allow_any_name: true # Workaround for https://github.com/canonical/postgresql-operator/issues/1276
      pki_allow_ip_sans: true
      pki_ca_common_name: dev.XXX
      pki_ca_country_name: "XX"
      pki_ca_organization: "XX"
      pki_ca_organizational_unit: "XX"
      pki_allow_ip_sans: true
      pki_allow_subdomains: true
      pki_allow_wildcard_certificates: true
      pki_allowed_domains: true

I also tried to use the postgresql:receive-ca-cert endpoint to "upload" the vault certificate through send-ca-cert into each cluster. No success

[ggouzi]

As suggested by @taurus-forever, configuring postgresql:receive-ca-cert to receive a certificate from self-signed-certificates:send-ca-cert (instead of vault:send-ca-cert) helps.
No patroni error. All units are active/idle after running create-replication.
Still, I doubt this is a suitable workaround

[ggouzi]

[nobuto-m]

I really don't see a point why vault is necessary in the diagram above. But in any case, if this step in the doc doesn't populate the root CA data in the relation, it sounds like a bug.

https://charmhub.io/vault/docs/h-use-vault-as-intermediate-ca

[ggouzi]

As discussed offline @nobuto-m, using Vault is indeed not mandatory.
It can help for a few cases:

• To issue certs with a different validity period
• If we replace self-signed-certificates with manual-tls-certificates (and a manually-generated rootCA), then we can relate manual-tls-certificates to vault and sign CSRs only once instead of directly relating manual-tls-certificates to each application model.

Still checking internally what our managed services team prefers.
To come back to the original issue, this is indeed a problem if the rootCA needs to be manually related to the application model.