Hi Team,
I'm evaluating pg_auto_failover since a while, so far soo good. Thanks very much for this wonderful solution.
Currently I'm unable to change the pgautofailover.* configuration settings
I tried editing the postgresql.conf and using "ALTER DATABASE"
pg_auto_failover=# ALTER DATABASE pg_auto_failover SET pgautofailover.promote_wal_log_threshold=0;
ERROR: parameter "pgautofailover.promote_wal_log_threshold" cannot be changed now
Please let me know, if I'm missing something
Environment
postgres@host280[DEV][~] $ pg_autoctl --version
pg_autoctl version 1.6.4
pg_autoctl extension version 1.6
compiled with PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
compatible with Postgres 10, 11, 12, 13, and 14
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $ uname -a
Linux host280 4.18.0-348.20.1.el8_5.x86_64 #1 SMP Tue Mar 8 12:56:54 EST 2022 x86_64 x86_64 x86_64 GNU/Linux
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $ pg_autoctl show state
Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State
-----+-------+------------+-----------+--------------+---------------------+--------------------
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $ pg_autoctl show uri
Type | Name | Connection String
-------------+---------+-------------------------------
monitor | monitor | postgres://autoctl_node@host280.uk.dev.net:6524/pg_auto_failover?sslmode=verify-full&sslrootcert=/var/lib/pgsql/.certs/root.crt
formation | default |
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $ cat /postgres/data/14/postgresql.conf | grep pgautofailover
shared_preload_libraries='pg_stat_statements,passwordcheck,pgaudit,auth_delay,pgautofailover'
pgautofailover.health_check_period=10000
pgautofailover.health_check_retry_delay=5000
pgautofailover.health_check_max_retries=120
pgautofailover.health_check_timeout=5000
pgautofailover.node_considered_unhealthy_timeout=600000
pgautofailover.promote_wal_log_threshold=0
pgautofailover.startup_grace_period=60000
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $ sudo systemctl stop pgautofailover
postgres@host280[DEV][~] $ sudo systemctl start pgautofailover
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $
postgres@host280[DEV][~] $ sudo systemctl status pgautofailover
● pgautofailover.service - pg_auto_failover
Loaded: loaded (/etc/systemd/system/pgautofailover.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2022-05-05 10:24:05 GMT; 5s ago
Main PID: 531587 (pg_autoctl)
Tasks: 16 (limit: 23704)
Memory: 19.6M
CGroup: /system.slice/pgautofailover.service
├─531587 /usr/pgsql-14/bin/pg_autoctl run
├─531590 pg_autoctl: start/stop postgres
├─531591 pg_autoctl: monitor listener
├─531600 /usr/pgsql-14/bin/postgres -D /postgres/data/14 -p 6524 -h *
├─531601 postgres: host280_pgopnsrc_inst01: logger
├─531606 postgres: host280_pgopnsrc_inst01: checkpointer
├─531607 postgres: host280_pgopnsrc_inst01: background writer
├─531608 postgres: host280_pgopnsrc_inst01: walwriter
├─531609 postgres: host280_pgopnsrc_inst01: autovacuum launcher
├─531610 postgres: host280_pgopnsrc_inst01: archiver last was 000000010000000000000007
├─531611 postgres: host280_pgopnsrc_inst01: stats collector
├─531612 postgres: host280_pgopnsrc_inst01: pg_auto_failover monitor
├─531613 postgres: host280_pgopnsrc_inst01: logical replication launcher
├─531615 postgres: host280_pgopnsrc_inst01: pg_auto_failover monitor healthcheck worker postgres
├─531616 postgres: host280_pgopnsrc_inst01: pg_auto_failover monitor healthcheck worker pg_auto_failover
└─531618 postgres: host280_pgopnsrc_inst01: autoctl_node pg_auto_failover [local] idle
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531587 INFO Started pg_autoctl postgres service with pid 531590
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531590 INFO /usr/pgsql-14/bin/pg_autoctl do service postgres --pgdata /postgres/data/14 -v
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531587 INFO Started pg_autoctl listener service with pid 531591
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531591 INFO /usr/pgsql-14/bin/pg_autoctl do service listener --pgdata /postgres/data/14 -v
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531591 INFO Managing the monitor at postgres://autoctl_node@host280.uk.dev.net:6524/pg_auto_failover?sslmode=verify-full&sslrootcert=/var/lib>
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531591 INFO Reloaded the new configuration from "/var/lib/pgsql/.config/pg_autoctl/postgres/data/14/pg_autoctl.cfg"
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531600 INFO /usr/pgsql-14/bin/postgres -D /postgres/data/14 -p 6524 -h *
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531590 INFO Postgres is now serving PGDATA "/postgres/data/14" on port 6524 with pid 531600
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531591 INFO The version of extension "pgautofailover" is "1.6" on the monitor
May 05 10:24:05 host280 pg_autoctl[531587]: 10:24:05 531591 INFO Contacting the monitor to LISTEN to its events.
postgres@host280[DEV][~] $
default settings still remain the same post pgautofailover service restart.
postgres=# select name, setting, unit, short_desc from pg_settings where name ~ 'pgautofailover.';
name | setting | unit | short_desc
--------------------------------------------------+----------+------+------------------------------------------------------------------------------------------------------
pgautofailover.enable_sync_wal_log_threshold | 16777216 | | Don't enable synchronous replication until secondary xlog is within this many bytes of the primary's
pgautofailover.health_check_max_retries | 2 | | Maximum number of re-tries before marking a node as failed.
pgautofailover.health_check_period | 10000 | ms | Duration between each check (in milliseconds).
pgautofailover.health_check_retry_delay | 5000 | ms | Delay between consecutive retries.
pgautofailover.health_check_timeout | 5000 | ms | Connect timeout (in milliseconds).
pgautofailover.node_considered_unhealthy_timeout | 600000 | ms | Mark node unhealthy if last ping was over this long ago
pgautofailover.primary_demote_timeout | 30000 | ms | Give the primary this long to drain before promoting the secondary
pgautofailover.promote_wal_log_threshold | 16777216 | | Don't promote secondary unless xlog is with this many bytes of the master
pgautofailover.startup_grace_period | 60000 | ms | Wait for at least this much time after startup before initiating a failover.
(9 rows)
tried to change the setting using ALTER DATABASE
postgres=# \c pg_auto_failover
You are now connected to database "pg_auto_failover" as user "postgres".
pg_auto_failover=# ALTER DATABASE pg_auto_failover SET pgautofailover.promote_wal_log_threshold=0;
ERROR: parameter "pgautofailover.promote_wal_log_threshold" cannot be changed now
pg_auto_failover=#
Hi Team, I'm evaluating pg_auto_failover since a while, so far soo good. Thanks very much for this wonderful solution.
Currently I'm unable to change the pgautofailover.* configuration settings
I tried editing the postgresql.conf and using "ALTER DATABASE"
pg_auto_failover=# ALTER DATABASE pg_auto_failover SET pgautofailover.promote_wal_log_threshold=0; ERROR: parameter "pgautofailover.promote_wal_log_threshold" cannot be changed now
Please let me know, if I'm missing something
Environment
default settings still remain the same post pgautofailover service restart.
tried to change the setting using ALTER DATABASE