Hello,
We have a full working primary cluster (A) with pg_tde encyption at DBs and WALs.
We want to replicate its data to a different standby (B) cluster , but still without any success. Fortunately we are on a Lab ,trying to have a working proof of concept . Any help is welcomed. Our cluster B /etc/patroni/postgresql.yml is the following
name : pgb1
scope : percona_b_scope
namespace : /pg_cluster
etcd3 : { hosts : 192.168.116.135:2379, 192.168.116.136:2379, 192.168.116.137:2379 }
restapi:
listen : 192.168.116.135:8008
connect_address: 192.168.116.135:8008
postgresql:
cluster_name : percona_b_cluster
listen : 192.168.116.135:5432
connect_address: 192.168.116.135:5432
data_dir : /var/lib/pgsql/18/data
bin_dir : /usr/pgsql-18/bin
pgpass : /tmp/pgpass
parameters : { unix_socket_directories: /run/postgresql }
basebackup : { checkpoint: fast }
watchdog : { safety_margin: 5, device: /dev/watchdog, mode: required } # mode values: off, automatic, required
authentication :
replication : { username: replicator , password: SomePassword }
superuser : { username: postgres , password: SomePassword }
rewind : { username: pgrewind , password: SomePassword }
create_replica_methods:
- basebackup
bootstrap:
dcs:
standby_cluster:
host : pga1.local.lan,pga2.local.lan,pga3.local.lan
port : 5432
primary_slot_name : percona_b_slot
create_replica_methods : [ basebackup ]
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
maximum_lag_on_syncnode: 15000000
synchronous_mode: false
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
default_table_access_method: tde_heap
pg_tde.wal_encrypt: 'on'
shared_preload_libraries: pg_tde
listen_addresses : '*'
ssl : true
ssl_key_file : /etc/patroni/ssl/server.key
ssl_cert_file : /etc/patroni/ssl/server.crt
ssl_ca_file : /etc/patroni/ssl/ca.crt
ssl_min_protocol_version : TLSv1.3
ssl_groups : prime256v1
max_connections : 2000
password_encryption : scram-sha-256
synchronous_standby_names: ''
hot_standby : on
wal_level : replica
wal_keep_segments : 10
max_wal_senders : 5
max_replication_slots : 10
wal_buffers : 64MB
min_wal_size : 1GB
wal_log_hints : 'on'
archive_mode : 'on'
logging_collector : 'on'
log_rotation_size : 100MB
max_wal_size : 2GB
archive_timeout : 600s
archive_command : pgbackrest --stanza=pg_ha archive-push /var/lib/pgsql/18/data/pg_wal/%f
shared_buffers : 2GB
work_mem : 16MB
maintenance_work_mem : 2GB
max_worker_processes : 16
effective_cache_size : 64GB
fsync : on
temp_buffers : 4MB
checkpoint_completion_target: 0.9
recovery_conf:
recovery_target_timeline: latest
restore_command: pgbackrest --config=/etc/pgbackrest.conf --stanza=pg_ha archive-get %f "%p"
initdb:
- auth-host : scram-sha-256
- auth-local: scram-sha-256
- auth-local: trust
- encoding : UTF8
- data-checksums
pg_hba:
- local all all trust
- local replication all trust
- host all all 127.0.0.1/32 scram-sha-256
- host all all ::1/128 scram-sha-256
- host replication all ::1/128 scram-sha-256
- host replication all 127.0.0.1/32 scram-sha-256
- host replication replicator 127.0.0.1/32 scram-sha-256
- host replication replicator 192.168.116.131/32 scram-sha-256
- host replication replicator 192.168.116.129/32 scram-sha-256
- host replication replicator 192.168.116.128/32 scram-sha-256
- host replication replicator 192.168.116.135/32 scram-sha-256
- host replication replicator 192.168.116.136/32 scram-sha-256
- host replication replicator 192.168.116.137/32 scram-sha-256
- host replication replicator 172.168.196.253/32 scram-sha-256
- host replication replicator 172.168.196.254/32 scram-sha-256
- hostssl all all samenet scram-sha-256
users:
admin:
password: admin
options:
- createrole
- createdb
slots:
permanent_logical_slot_name:
type : logical
database : postgres
plugin : wal2json
tags:
nofailover : false
noloadbalance: false
clonefrom : false
nosync : true
the A configuration is quite similar . thanks