0

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
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
EXK
  • 23
  • 4
  • I don't know enough about Patroni, but I doubt that it will be possible to reliably configure logical replication to survive a failover. – Laurenz Albe Mar 10 '23 at 02:38

0 Answers0