I am trying to implement logical replication in a postgres cluster, where cluster switchover/failover is managed by patroni. I am trying to leverage the permanent replication slot feature. Using patroni version 2.1.1 on Postgres version 13.5. I have included my patroni config file below. After stopping and starting patroni, I notice that 2 physical replication slots called host_1 and host_2 are created even though patroni config defines implementation of logical replication. Patroni seems to be creating them by default.
postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------
host_2 | | physical | | | f | t | 23825 | | | B5/2602F808 | | reserved |
host_0 | | physical | | | f | t | 23838 | | | B5/2602F808 | | reserved |
(2 rows)
#Patroni configuration
loop_wait: 10
master_start_timeout: 300
maximum_lag_on_failover: 1048576
postgresql:
callbacks:
on_role_change: /etc/patroni/patroni_callback.sh
on_start: /etc/patroni/patroni_callback.sh
on_stop: /etc/patroni/patroni_callback.sh
parameters:
archive_command: sh /etc/patroni/cfg/archive_command_pgbackrest.sh %p %f
archive_mode: 'on'
archive_timeout: 0
config_file: /data/postgresql.conf
hot_standby: 'on'
log_directory: /log
max_connections: 210
max_locks_per_transaction: 64
max_prepared_transactions: 0
max_replication_slots: 10
max_wal_senders: 10
max_worker_processes: 4
password_encryption: trust
pg_stat_statements.max: 1000
pg_stat_statements.track: top
shared_preload_libraries: pg_stat_statements,pg_buffercache,pg_stat_kcache,wal2json
track_commit_timestamp: false
wal_keep_size: 11200
wal_level: logical
wal_log_hints: true
recovery_conf:
restore_command: sh /etc/patroni/cfg/restore_command_pgbackrest.sh %p %f
slots:
db_slot:
database: driver_test
plugin: wal2json
type: logical
use_pg_rewind: true
use_slots: true
retry_timeout: 10
synchronous_mode: 'off'
synchronous_mode_strict: 'off'
ttl: 30
The only changes to the working patroni config was to change the following
slots:
db_slot:
database: driver_test
plugin: wal2json
type: logical
use_slots: true