pg_auto_failover is an extension for PostgreSQL that monitors and manages failover for postgres clusters. It is optimized for simplicity and correctness. For detail, please refer to https://pg-auto-failover.readthedocs.io/en/main/
The below example shows how to configure pg_auto_failover on Postgres for single standby architecture.
The example is based on
· PostgreSQL 14
· 3 Linux machines with redhat linux 7.9
· pg_auto_failover version 1.6
# stop and disable firewall of the Linux machine sudo systemctl stop firewalld sudo systemctl disable firewalld sudo systemctl mask --now firewalld # add the required packages to your system curl https://install.citusdata.com/community/rpm.sh | sudo bash # install pg_auto_failover sudo yum install -y pg-auto-failover16_14 # confirm installation /usr/pgsql-14/bin/pg_autoctl --version # set postgres user password and switch to postgres passwd postgres su - postgres # export system variables export PATH=/usr/pgsql-14/bin:$PATH export PGDATA=/var/lib/pgsql/14/data # create monitor pg_autoctl create monitor \ --pgdata /var/lib/pgsql/14/data \ --pgport 5432 \ --hostname tec-l-1062609 \ --ssl-self-signed \ --auth trust #start pg_autoctl run # open another terminal, and check uri su - postgres export PATH=/usr/pgsql-14/bin:$PATH export PGDATA=/var/lib/pgsql/14/data pg_autoctl show uri # an example of uri is as below # postgres://autoctl_node@tec-l-106209:5432/pg_auto_failover?sslmode=prefer
#Add to systemd: -bash-4.2$ pg_autoctl show systemd 02:46:13 7256 INFO HINT: to complete a systemd integration, run the following commands (as root): 02:46:13 7256 INFO pg_autoctl -q show systemd --pgdata "/var/lib/pgsql/14/data" | tee /etc/systemd/system/pgautofailover.service 02:46:13 7256 INFO systemctl daemon-reload 02:46:13 7256 INFO systemctl enable pgautofailover 02:46:13 7256 INFO systemctl start pgautofailover
# stop and disable firewall of the Linux machine sudo systemctl stop firewalld sudo systemctl disable firewalld sudo systemctl mask --now firewalld # add the required packages to your system curl https://install.citusdata.com/community/rpm.sh | sudo bash # install pg_auto_failover sudo yum install -y pg-auto-failover16_14 # confirm installation /usr/pgsql-14/bin/pg_autoctl --version # set postgres user password and switch to postgres passwd postgres su - postgres # export system variables export PATH=/usr/pgsql-14/bin:$PATH export PGDATA=/var/lib/pgsql/14/data # create Primary node pg_autoctl create postgres \ --hostname tec-l-1062341 \ --auth trust \ --ssl-self-signed \ --monitor postgres://autoctl_node@tec-l-1062609:5432/pg_auto_failover?sslmode=require # run pg_autoctl run # verify from Monitor terminal # open Monitor termial su - postgres export PATH=/usr/pgsql-14/bin:$PATH export PGDATA=/var/lib/pgsql/14/data pg_autoctl show state
pg_ctl -D /var/lib/pgsql/14/data/ reload
# stop and disable firewall of the Linux machine sudo systemctl stop firewalld sudo systemctl disable firewalld sudo systemctl mask --now firewalld # add the required packages to your system curl https://install.citusdata.com/community/rpm.sh | sudo bash # install pg_auto_failover sudo yum install -y pg-auto-failover16_14 # confirm installation /usr/pgsql-14/bin/pg_autoctl --version # set postgres user password and switch to postgres passwd postgres su - postgres # export system variables export PATH=/usr/pgsql-14/bin:$PATH export PGDATA=/var/lib/pgsql/14/data # create standby node pg_autoctl create postgres \ --hostname tec-l-1062339 \ --auth trust \ --ssl-self-signed \ --monitor postgres://autoctl_node@tec-l-1062609:5432/pg_auto_failover?sslmode=require # run pg_autoctl run # verify from Monitor terminal # open Monitor termial su - postgres export PATH=/usr/pgsql-14/bin:$PATH export PGDATA=/var/lib/pgsql/14/data pg_autoctl show state
pg_ctl -D /var/lib/pgsql/14/data/ reload
multi-host and target_session_attrs is used for PostgreSQL Failover (https://www.postgresql.org/docs/current/libpq-connect.html ). With target_session_attrs=read-write ,and multi hosts specified in the connection string or DSN, it will connect to the read-write node, which is the primary node. As the standby node is read-only. After the Primary node fails, the standby node will be promoted to be the Primary one (read-write). And the connection string / DSN could still work without any change. Native PostgreSQL ODBC driver could support this.
[Postgres_Native]
Driver=/usr/pgsql-12/lib/psqlodbcw.so
Description=PostgreSQL Unicode(x64)
Database=<database name>
Servername=hostname1,hostname2 Port=5432,5432 Pqopt=target_session_attrs=read-write
Protocol=
ReadOnly=
RowVersioning=
ShowSystemTables=
ConnSettings=
DriverUnicodeType=
Progress’s PostgreSQL ODBC driver doesn’t support multihost and target_session_attrs, an enhancement request is submitted to them.
Progress's PostgreSQL ODBC driver has a failover implementation named AlternateServer which has limitations as below:
For a scenario, the Server is connects to the Primary Node A, and the AlternateServer is pointed to Standby Node B. When Node A fails, we could connect to Node B. And now, the Monitor promote Node B becomes the Primary node (read-write). However, after we bring up node A. The node A becomes standby node, which is read-only. But in the DSN, the Sever still prefer connects to Node A, and AlternateServer still connects to Node B. As node A is now read-only, it will trigger the write issue.
One workaround could be, perform a controlled switchover for Node A and Node B on the Monitor, so that Node A could still be Primary Node, Node B could still be the Standby Node.
