Disclaimer: These notes give an idea of what is involved in setting up ‘hot standby’ HA mode in PostgreSQL. The below procedures are not regularly tested. Please use the PostgreSQL documentation when setting up HA on PostgreSQL.
The following variables are used on this page:
- PRIMARY_PG_IP: The IP address of the host that contains the primary database.
- SECONDARY_PG_IP: The IP address of the host that contains the secondary database.
- REP_USER: The internal database user that manages replication on the primary.
- REP_USER_PW: The password of the replication user.
- REP_ARCHIVE: The directory where the database will place files to be replicated.
Their values are represented when they are preceded with the ‘$’ character (e.g. $REP_USER_PW). These are to be replaced with actual values in the commands and files below.
Primary
Perform these actions on the primary host.
Create internal database user
Create an internal database user to manage replication. You will be prompted to supply a password ($REP_USER_PW) for this new user:
sudo -u postgres createuser -U postgres $REP_USER -P -c 5 --replication
Set up replication file storage
Set up a place to store replication files:
REP_ARCHIVE=/var/backups/pgsql/archive
sudo mkdir -p $REP_ARCHIVE
sudo chown postgres $REP_ARCHIVE
Allow secondary host to connect
Edit /etc/postgresql/9.5/main/pg_hba.conf to allow the secondary host to contact this primary host.
host replication $REP_USER $SECONDARY_PG_IP/32 md5
Configure for replication
Edit /etc/postgresql/9.5/main/postgresql.conf to listen on more than just its localhost interface, turn on replication, and point to the archive directory:
listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f $REP_ARCHIVE/%f && cp %p $REP_ARCHIVE/%f'
max_wal_senders = 3
Restart the database
Restart the database to apply the above changes:
sudo systemctl restart postgresql
Check log file /var/log/postgresql/postgresql-9.5-main.log on this primary host for any errors.
The primary database is now ready to accept replication requests from the secondary database (that will be set up below).
Secondary
Perform these actions on the secondary host.
This host should ideally match the primary host in terms of:
- CPU architecture
- OS type and version
- PostgreSQL version
Replication has been known to fail due to an architecture mismatch.
Install PostgreSQL and stop the service
Install PostgreSQL and stop the service:
sudo apt install postgresql
sudo systemctl stop postgresql
Copy over primary database files
Move the default database files out of the way and replace them with a copy of the primary database files. You will be prompted for the password of the remote replication user.
sudo mv /var/lib/postgresql/9.5/main /var/lib/postgresql/9.5/main.old
sudo -u postgres pg_basebackup -h $PRIMARY_PG_IP -D /var/lib/postgresql/9.5/main -U $REP_USER -v -P --xlog-method=stream
Password:
Once a copy of the primary database is transferred, proceed to configure actual replication.
Place database in hot standby mode
Edit /etc/postgresql/9.5/main/postgresql.conf and put this secondary host in hot standby mode:
hot_standby = on
Set up recovery configuration file
Copy a sample recovery configuration file into place:
sudo cp /usr/share/postgresql/9.5/recovery.conf.sample /var/lib/postgresql/9.5/main/recovery.conf
Configure for recovery
Edit /var/lib/postgresql/9.5/main/recovery.conf. Specify hot standby mode and enter the information necessary for contacting the primary:
standby_mode = on
primary_conninfo = 'host=$PRIMARY_PG_IP port=5432 user=$REP_USER password=$REP_USER_PW'
Start the database
Start the database:
sudo systemctl start postgresql
Check log file /var/log/postgresql/postgresql-9.5-main.log on this secondary host for any errors.
The secondary database is now replicating the primary database.
Verification of replication
This section includes a raw test that will show whether replication is functioning.
On the secondary database host, perform a query on the ‘maasserver_node’ table in the ‘maasdb’ database:
sudo -u postgres psql maasdb -c 'SELECT hostname,status,power_state FROM maasserver_node'
The output will look something like:
hostname | status | power_state
-------------------+--------+-------------
pmatulis-imp-maas | 0 | unknown
node3 | 4 | off
node1 | 6 | on
node2 | 4 | off
node4 | 6 | on
(5 rows)
This includes any hosts that are being used for API servers or rack controllers (‘pmatulis-imp-maas’ in this example). There are 4 regular MAAS nodes.
To quickly check that replication is working simply (temporarily) rename a node’s hostname in the web UI and re-invoke the above command to see if the change is reflected.
Another test could be to change the status of a node, for example, by Commissioning or Deploying (a status of ‘4’ is ‘Ready’ and a status of ‘6’ is ‘Deployed’).
