pg_auto_failover Tutorial
In this guide we’ll create a primary and secondary Postgres node and set up pg_auto_failover to replicate data between them. We’ll simulate failure in the primary node and see how the system smoothly switches (fails over) to the secondary.
For illustration, we’ll run our databases on virtual machines in the Azure platform, but the techniques here are relevant to any cloud provider or on-premise network. We’ll use four virtual machines: a primary database, a secondary database, a monitor, and an “application.” The monitor watches the other nodes’ health, manages global state, and assigns nodes their roles.
Create virtual network
Our database machines need to talk to each other and to the monitor node, so let’s create a virtual network.
az group create \
--name ha-demo \
--location eastus
az network vnet create \
--resource-group ha-demo \
--name ha-demo-net \
--address-prefix 10.0.0.0/16
We need to open ports 5432 (Postgres) and 22 (SSH) between the machines, and also give ourselves access from our remote IP. We’ll do this with a network security group and a subnet.
az network nsg create \
--resource-group ha-demo \
--name ha-demo-nsg
az network nsg rule create \
--resource-group ha-demo \
--nsg-name ha-demo-nsg \
--name ha-demo-ssh-and-pg \
--access allow \
--protocol Tcp \
--direction Inbound \
--priority 100 \
--source-address-prefixes `curl ifconfig.me` 10.0.1.0/24 \
--source-port-range "*" \
--destination-address-prefix "*" \
--destination-port-ranges 22 5432
az network vnet subnet create \
--resource-group ha-demo \
--vnet-name ha-demo-net \
--name ha-demo-subnet \
--address-prefixes 10.0.1.0/24 \
--network-security-group ha-demo-nsg
Finally add four virtual machines (ha-demo-a, ha-demo-b, ha-demo-monitor, and
ha-demo-app). For speed we background the az vm create
processes and run
them in parallel:
# create VMs in parallel
for node in monitor a b app
do
az vm create \
--resource-group ha-demo \
--name ha-demo-${node} \
--vnet-name ha-demo-net \
--subnet ha-demo-subnet \
--nsg ha-demo-nsg \
--public-ip-address ha-demo-${node}-ip \
--image debian \
--admin-username ha-admin \
--generate-ssh-keys &
done
wait
To make it easier to SSH into these VMs in future steps, let’s make a shell function to retrieve their IP addresses:
# run this in your local shell as well
vm_ip () {
az vm list-ip-addresses -g ha-demo -n ha-demo-$1 -o tsv \
--query '[] [] .virtualMachine.network.publicIpAddresses[0].ipAddress'
}
# for convenience with ssh
for node in monitor a b app
do
ssh-keyscan -H `vm_ip $node` >> ~/.ssh/known_hosts
done
Let’s review what we created so far.
az resource list --output table --query \
"[?resourceGroup=='ha-demo'].{ name: name, flavor: kind, resourceType: type, region: location }"
This shows the following resources:
Name ResourceType Region
------------------------------- ----------------------------------------------------- --------
ha-demo-a Microsoft.Compute/virtualMachines eastus
ha-demo-app Microsoft.Compute/virtualMachines eastus
ha-demo-b Microsoft.Compute/virtualMachines eastus
ha-demo-monitor Microsoft.Compute/virtualMachines eastus
ha-demo-appVMNic Microsoft.Network/networkInterfaces eastus
ha-demo-aVMNic Microsoft.Network/networkInterfaces eastus
ha-demo-bVMNic Microsoft.Network/networkInterfaces eastus
ha-demo-monitorVMNic Microsoft.Network/networkInterfaces eastus
ha-demo-nsg Microsoft.Network/networkSecurityGroups eastus
ha-demo-a-ip Microsoft.Network/publicIPAddresses eastus
ha-demo-app-ip Microsoft.Network/publicIPAddresses eastus
ha-demo-b-ip Microsoft.Network/publicIPAddresses eastus
ha-demo-monitor-ip Microsoft.Network/publicIPAddresses eastus
ha-demo-net Microsoft.Network/virtualNetworks eastus
Install the “pg_autoctl” executable
This guide uses Debian Linux, but similar steps will work on other distributions. All that differs are the packages and paths. See Installing pg_auto_failover.
The pg_auto_failover system is distributed as a single pg_autoctl
binary
with subcommands to initialize and manage a replicated PostgreSQL service.
We’ll install the binary with the operating system package manager on all
nodes. It will help us run and observe PostgreSQL.
for node in monitor a b app
do
az vm run-command invoke \
--resource-group ha-demo \
--name ha-demo-${node} \
--command-id RunShellScript \
--scripts \
"sudo touch /home/ha-admin/.hushlogin" \
"curl https://install.citusdata.com/community/deb.sh | sudo bash" \
"sudo DEBIAN_FRONTEND=noninteractive apt-get install -q -y postgresql-common" \
"echo 'create_main_cluster = false' | sudo tee -a /etc/postgresql-common/createcluster.conf" \
"sudo DEBIAN_FRONTEND=noninteractive apt-get install -q -y postgresql-11-auto-failover-1.4" \
"sudo usermod -a -G postgres ha-admin" &
done
wait
Run a monitor
The pg_auto_failover monitor is the first component to run. It periodically attempts to contact the other nodes and watches their health. It also maintains global state that “keepers” on each node consult to determine their own roles in the system.
# on the monitor virtual machine
ssh -l ha-admin `vm_ip monitor` -- \
pg_autoctl create monitor \
--auth trust \
--ssl-self-signed \
--pgdata monitor \
--pgctl /usr/lib/postgresql/11/bin/pg_ctl
This command initializes a PostgreSQL cluster at the location pointed
by the --pgdata
option. When --pgdata
is omitted, pg_autoctl
attempts to use the PGDATA
environment variable. If a PostgreSQL
instance had already existing in the destination directory, this command
would have configured it to serve as a monitor.
pg_auto_failover
, installs the pgautofailover
Postgres extension, and
grants access to a new autoctl_node
user.
In the Quick Start we use --auth trust
to avoid complex security settings.
The Postgres trust authentication method is not considered a reasonable
choice for production environments. Consider either using the --skip-pg-hba
option or --auth scram-sha-256
and then setting up passwords yourself.
At this point the monitor is created. Now we’ll install it as a service with systemd so that it will resume if the VM restarts.
ssh -T -l ha-admin `vm_ip monitor` << CMD
pg_autoctl -q show systemd --pgdata ~ha-admin/monitor > pgautofailover.service
sudo mv pgautofailover.service /etc/systemd/system
sudo systemctl daemon-reload
sudo systemctl enable pgautofailover
sudo systemctl start pgautofailover
CMD
Bring up the nodes
We’ll create the primary database using the pg_autoctl create
subcommand.
ssh -l ha-admin `vm_ip a` -- \
pg_autoctl create postgres \
--pgdata ha \
--auth trust \
--ssl-self-signed \
--username ha-admin \
--dbname appdb \
--hostname ha-demo-a.internal.cloudapp.net \
--pgctl /usr/lib/postgresql/11/bin/pg_ctl \
--monitor 'postgres://autoctl_node@ha-demo-monitor.internal.cloudapp.net/pg_auto_failover?sslmode=require'
Notice the user and database name in the monitor connection string – these are what monitor init created. We also give it the path to pg_ctl so that the keeper will use the correct version of pg_ctl in future even if other versions of postgres are installed on the system.
In the example above, the keeper creates a primary database. It chooses to set up node A as primary because the monitor reports there are no other nodes in the system yet. This is one example of how the keeper is state-based: it makes observations and then adjusts its state, in this case from “init” to “single.”
Also add a setting to trust connections from our “application” VM:
ssh -T -l ha-admin `vm_ip a` << CMD
echo 'hostssl "appdb" "ha-admin" ha-demo-app.internal.cloudapp.net trust' \
>> ~ha-admin/ha/pg_hba.conf
CMD
At this point the monitor and primary node are created and running. Next we need to run the keeper. It’s an independent process so that it can continue operating even if the PostgreSQL process goes terminates on the node. We’ll install it as a service with systemd so that it will resume if the VM restarts.
ssh -T -l ha-admin `vm_ip a` << CMD
pg_autoctl -q show systemd --pgdata ~ha-admin/ha > pgautofailover.service
sudo mv pgautofailover.service /etc/systemd/system
sudo systemctl daemon-reload
sudo systemctl enable pgautofailover
sudo systemctl start pgautofailover
CMD
Next connect to node B and do the same process. We’ll do both steps at once:
ssh -l ha-admin `vm_ip b` -- \
pg_autoctl create postgres \
--pgdata ha \
--auth trust \
--ssl-self-signed \
--username ha-admin \
--dbname appdb \
--hostname ha-demo-b.internal.cloudapp.net \
--pgctl /usr/lib/postgresql/11/bin/pg_ctl \
--monitor 'postgres://autoctl_node@ha-demo-monitor.internal.cloudapp.net/pg_auto_failover?sslmode=require'
ssh -T -l ha-admin `vm_ip b` << CMD
pg_autoctl -q show systemd --pgdata ~ha-admin/ha > pgautofailover.service
sudo mv pgautofailover.service /etc/systemd/system
sudo systemctl daemon-reload
sudo systemctl enable pgautofailover
sudo systemctl start pgautofailover
CMD
It discovers from the monitor that a primary exists, and then switches its own state to be a hot standby and begins streaming WAL contents from the primary.
Node communication
For convenience, pg_autoctl modifies each node’s pg_hba.conf
file to allow
the nodes to connect to one another. For instance, pg_autoctl added the
following lines to node A:
# automatically added to node A
hostssl "appdb" "ha-admin" ha-demo-a.internal.cloudapp.net trust
hostssl replication "pgautofailover_replicator" ha-demo-b.internal.cloudapp.net trust
hostssl "appdb" "pgautofailover_replicator" ha-demo-b.internal.cloudapp.net trust
For pg_hba.conf
on the monitor node pg_autoctl inspects the local network
and makes its best guess about the subnet to allow. In our case it guessed
correctly:
# automatically added to the monitor
hostssl "pg_auto_failover" "autoctl_node" 10.0.1.0/24 trust
If worker nodes have more ad-hoc addresses and are not in the same subnet, it’s
better to disable pg_autoctl’s automatic modification of pg_hba using the
--skip-pg-hba
command line option during creation. You will then need to
edit the hba file by hand. Another reason for manual edits would be to use
special authentication methods.
Watch the replication
First let’s verify that the monitor knows about our nodes, and see what states it has assigned them:
ssh -l ha-admin `vm_ip monitor` pg_autoctl show state --pgdata monitor
Name | Node | Host:Port | LSN | Reachable | Current State | Assigned State
-------+-------+--------------------------------------+-----------+-----------+---------------------+--------------------
node_1 | 1 | ha-demo-a.internal.cloudapp.net:5432 | 0/3000060 | yes | primary | primary
node_2 | 2 | ha-demo-b.internal.cloudapp.net:5432 | 0/3000060 | yes | secondary | secondary
This looks good. We can add data to the primary, and later see it appear in the secondary. We’ll connect to the database from inside our “app” virtual machine, using a connection string obtained from the monitor.
ssh -l ha-admin `vm_ip monitor` pg_autoctl show uri --pgdata monitor
Type | Name | Connection String
-----------+---------+-------------------------------
monitor | monitor | postgres://autoctl_node@ha-demo-monitor.internal.cloudapp.net:5432/pg_auto_failover?sslmode=require
formation | default | postgres://ha-demo-b.internal.cloudapp.net:5432,ha-demo-a.internal.cloudapp.net:5432/appdb?target_session_attrs=read-write&sslmode=require
Now we’ll get the connection string and store it in a local environment variable:
APP_DB_URI=$( \
ssh -l ha-admin `vm_ip monitor` \
pg_autoctl show uri --formation default --pgdata monitor \
)
The connection string contains both our nodes, comma separated, and includes
the url parameter ?target_session_attrs=read-write
telling psql that we
want to connect to whichever of these servers supports reads and writes.
That will be the primary server.
# connect to database via psql on the app vm and
# create a table with a million rows
ssh -l ha-admin -t `vm_ip app` -- \
psql "'$APP_DB_URI'" \
-c "'CREATE TABLE foo AS SELECT generate_series(1,1000000) bar;'"
Cause a failover
Now that we’ve added data to node A, let’s switch which is considered the primary and which the secondary. After the switch we’ll connect again and query the data, this time from node B.
# initiate failover to node B
ssh -l ha-admin -t `vm_ip monitor` \
pg_autoctl perform switchover --pgdata monitor
Once node B is marked “primary” (or “wait_primary”) we can connect and verify that the data is still present:
# connect to database via psql on the app vm
ssh -l ha-admin -t `vm_ip app` -- \
psql "'$APP_DB_URI'" \
-c "'SELECT count(*) FROM foo;'"
It shows
count
---------
1000000
Cause a node failure
This plot is too boring, time to introduce a problem. We’ll turn off VM for node B (currently the primary after our previous failover) and watch node A get promoted.
In one terminal let’s keep an eye on events:
ssh -t -l ha-admin `vm_ip monitor` -- \
watch -n 1 -d pg_autoctl show state --pgdata monitor
In another terminal we’ll turn off the virtual server.
az vm stop \
--resource-group ha-demo \
--name ha-demo-b
After a number of failed attempts to talk to node B, the monitor determines the node is unhealthy and puts it into the “demoted” state. The monitor promotes node A to be the new primary.
Name | Node | Host:Port | LSN | Reachable | Current State | Assigned State
-------+-------+--------------------------------------+-----------+-----------+---------------------+--------------------
node_1 | 1 | ha-demo-a.internal.cloudapp.net:5432 | 0/6D4E068 | yes | wait_primary | wait_primary
node_2 | 2 | ha-demo-b.internal.cloudapp.net:5432 | 0/6D4E000 | yes | demoted | catchingup
Node A cannot be considered in full “primary” state since there is no secondary present, but it can still serve client requests. It is marked as “wait_primary” until a secondary appears, to indicate that it’s running without a backup.
Let’s add some data while B is offline.
# notice how $APP_DB_URI continues to work no matter which node
# is serving as primary
ssh -l ha-admin -t `vm_ip app` -- \
psql "'$APP_DB_URI'" \
-c "'INSERT INTO foo SELECT generate_series(1000001, 2000000);'"
Resurrect node B
Run this command to bring node B back online:
az vm start \
--resource-group ha-demo \
--name ha-demo-b
Now the next time the keeper retries its health check, it brings the node back. Node B goes through the state “catchingup” while it updates its data to match A. Once that’s done, B becomes a secondary, and A is now a full primary again.
Name | Node | Host:Port | LSN | Reachable | Current State | Assigned State
-------+-------+--------------------------------------+------------+-----------+---------------------+--------------------
node_1 | 1 | ha-demo-a.internal.cloudapp.net:5432 | 0/12000738 | yes | primary | primary
node_2 | 2 | ha-demo-b.internal.cloudapp.net:5432 | 0/12000738 | yes | secondary | secondary
What’s more, if we connect directly to the database again, all two million rows are still present.
ssh -l ha-admin -t `vm_ip app` -- \
psql "'$APP_DB_URI'" \
-c "'SELECT count(*) FROM foo;'"
It shows
count
---------
2000000