search

patroni / patroni

A template for PostgreSQL High Availability with Etcd, Consul, ZooKeeper, or Kubernetes
MIT License
6.77k stars 843 forks source link

Patroni in 2-node suggestion and notice #2953

Closed yinan8128 closed 11 months ago

yinan8128 commented 11 months ago

What happened?

Our existing patroni cluster are configured in 3 nodes work well, now as storage limitation, we consider to change to patroni 2 nodes, after research there are less patroni 2-node doc, so create this ticket for suggestion and notice

How can we reproduce it (as minimally and precisely as possible)?

N.A

What did you expect to happen?

Based on our existing configuration, please provide suggestion and if there is any item we need to notice, thank you

Patroni/PostgreSQL/DCS version

Patroni configuration file

scope: pgcluster01
name: patroni_01-1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.10.10.1:8008
  authentication:
    username: patroni_api
    password: ccc
  allowlist:
    - localhost
    - 127.0.0.1/32
    - 10.10.10.0/32
    - 10.10.10.1/32
    - 10.10.10.2/32
    - consul_ip_0/32
    - consul_ip_1/32
    - consul_ip_2/32

consul:
  host: 127.0.0.1:8500
  checks: []
  register_service: true

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    failsafe_mode: true
    synchronous_mode: true
    postgresql:
      use_pg_rewind: true
      use_slots: false
      parameters:
        archive_mode: "on"
        archive_command: /bin/true
        archive_timeout: 1800s
        autovacuum: "on"
        autovacuum_work_mem: 1GB
        autovacuum_vacuum_threshold: 50
        autovacuum_vacuum_cost_limit: 8000
        autovacuum_vacuum_scale_factor: 0.01
        autovacuum_analyze_threshold: 50
        autovacuum_analyze_scale_factor: 0.05
        autovacuum_max_workers: 6
        auto_explain.log_min_duration: 60000
        auto_explain.log_nested_statements: true
        bgwriter_delay: 50ms
        bgwriter_lru_maxpages: 4000
        bgwriter_lru_multiplier: 4
        checkpoint_completion_target: 0.7
        checkpoint_timeout: 30min
        config_file: /var/lib/pgsql/14/data/postgresql.conf
        constraint_exclusion: partition
        cron.database_name: postgres
        datestyle: "ISO, MDY"
        default_statistics_target: 100
        default_text_search_config: "pg_catalog.english"
        effective_cache_size: "2880MB"
        hot_standby: "on"
        idle_in_transaction_session_timeout: 10min
        jit: "off"
        lc_messages: 'C'
        lc_monetary: 'C'
        lc_numeric: 'C'
        lc_time: 'C'
        lock_timeout: 30s
        log_autovacuum_min_duration: 500
        log_checkpoints: "off"
        log_connections: "off"
        log_destination: "stderr"
        log_directory: "/var/log/postgresql"
        log_disconnections: "off"
        log_duration: "off"
        log_filename: 'postgresql.log'
        log_line_prefix: '[%m] p=%p:%l@%v c=%u@%h/%d:%a '
        log_lock_waits: "on"
        log_min_duration_statement: "5s"
        log_recovery_conflict_waits: "on"
        log_rotation_age: 0
        log_rotation_size: 0
        log_statement: "ddl"
        log_temp_files: 1MB
        log_timezone: "UTC"
        log_truncate_on_rotation: "off"
        logging_collector: "on"
        maintenance_work_mem: 2GB
        max_connections: 1000
        max_replication_slots: 10
        max_wal_senders: 10
        max_wal_size: 160
        min_wal_size: 1024
        pg_stat_statements.max: 10000
        pg_stat_statements.track: all
        pg_stat_statements.track_utility: 'off'
        random_page_cost: 1.1
        shared_buffers: 960MB
        shared_preload_libraries: 'pg_stat_statements,pg_stat_kcache,auto_explain,pg_cron,timescaledb'
        statement_timeout: 10min
        temp_file_limit: 5GB
        timezone: "UTC"
        track_io_timing: "on"
        unix_socket_directories: '/var/run/postgresql'
        wal_buffers: -1
        wal_compression: "on"
        wal_keep_size: "160MB"
        wal_level: replica
        wal_log_hints: "on"
        work_mem: "1MB"
      recovery_conf:
        restore_command: /bin/true
  initdb:
    - encoding: UTF8
    - data-checksums
    - locale: en_US.UTF-8
    - lc-collate: en_US.UTF-8
    - lc-ctype: en_US.UTF-8

  users:
    replicator:
      password: bbb
      options:
        - replication

postgresql:
  listen: '*'
  connect_address: 10.10.10.1:5432
  data_dir: /var/lib/pgsql/14/data
  bin_dir: /usr/pgsql-14/bin
  config_dir: /var/lib/pgsql/14/data
  hba_file: /var/lib/pgsql/14/data/pg_hba.conf
  restore: /usr/bin/patroni_wale_restore
  pgpass: /tmp/pgpass
  authentication:
    pg_rewind:
      username: postgres
      password: aaa
    replication:
      username: replicator
      password: bbb
      network: 0.0.0.0/0
    superuser:
      username: postgres
      password: aaa
  basebackup:
    - verbose
    - checkpoint: 'fast'

watchdog:
  mode: automatic
  device: /dev/watchdog
  safety_margin: 5

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

log:
    format: '{"time": "%(asctime)s", "name": "%(name)s", "process": %(process)d, "thread": %(thread)d, "level": "%(levelname)s", "message": "%(message)s"}'

patronictl show-config

# patronictl -c /etc/patroni/postgresql.yml show-config                                                                                                                   [35/1871]
failsafe_mode: true                                                                                                                                                                            [34/1978]
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    archive_command: /bin/true
    archive_mode: 'on'
    archive_timeout: 1800s
    auto_explain.log_min_duration: 60000
    auto_explain.log_nested_statements: true
    autovacuum: 'on'
    autovacuum_analyze_scale_factor: 0.05
    autovacuum_analyze_threshold: 50
    autovacuum_max_workers: 6
    autovacuum_vacuum_cost_limit: 8000
    autovacuum_vacuum_scale_factor: 0.01
    autovacuum_vacuum_threshold: 50
    autovacuum_work_mem: 1GB
    bgwriter_delay: 50ms
    bgwriter_lru_maxpages: 4000
    bgwriter_lru_multiplier: 4
    checkpoint_completion_target: 0.7
    checkpoint_timeout: 30min
    config_file: /var/lib/pgsql/14/data/postgresql.conf
    constraint_exclusion: partition
    cron.database_name: postgres
    datestyle: ISO, MDY
    default_statistics_target: 100
    default_text_search_config: pg_catalog.english
    effective_cache_size: 2880MB
    hot_standby: 'on'
    idle_in_transaction_session_timeout: 10min
    jit: 'off'
    lc_messages: C
    lc_monetary: C
    lc_numeric: C
    lc_time: C
    lock_timeout: 30s
    log_autovacuum_min_duration: 500
    log_checkpoints: 'off'
    log_connections: 'off'
    log_destination: stderr
    log_directory: /var/log/postgresql
    log_disconnections: 'off'
    log_duration: 'off'
    log_filename: postgresql.log
    log_line_prefix: '[%m] p=%p:%l@%v c=%u@%h/%d:%a '
    log_lock_waits: 'on'
    log_min_duration_statement: 5s
    log_recovery_conflict_waits: 'on'
    log_rotation_age: 0
    log_rotation_size: 0
    log_statement: ddl
    log_temp_files: 1MB
    log_timezone: UTC
    log_truncate_on_rotation: 'off'
    logging_collector: 'on'
    maintenance_work_mem: 2GB
    max_connections: 1000
    max_replication_slots: 10
    max_wal_senders: 10
    max_wal_size: 160
    min_wal_size: 1024
    pg_stat_statements.max: 10000
    pg_stat_statements.track: all
    pg_stat_statements.track_utility: 'off'
    random_page_cost: 1.1
    shared_buffers: 960MB
    shared_preload_libraries: pg_stat_statements,pg_stat_kcache,auto_explain,pg_cron,timescaledb
    statement_timeout: 10min
    temp_file_limit: 5GB
    timezone: UTC
    track_io_timing: 'on'
    unix_socket_directories: /var/run/postgresql
    wal_buffers: -1
    wal_compression: 'on'
    wal_keep_size: 160MB
    wal_level: replica
    wal_log_hints: 'on'
    work_mem: 1MB
  recovery_conf:
    restore_command: /bin/true
  use_pg_rewind: true
  use_slots: false
retry_timeout: 10
synchronous_mode: true
ttl: 30

Patroni log files

N.A

PostgreSQL log files

N.A

Have you tried to use GitHub issue search?

Anything else we need to know?

Based on our existing configuration, please provide suggestion and if there is any item we need to notice, thank you

CyberDem0n commented 11 months ago

I'll answer is with the link to the F.A.Q. page: https://patroni.readthedocs.io/en/latest/faq.html#concepts-and-requirements

Does Patroni require a minimum number of Postgres nodes in the cluster? No, you can run Patroni with any number of Postgres nodes. Remember: Patroni is decoupled from the DCS.

And for the future - please use Slack for questions.