Use this quick start to create a highly available demo cluster using EDB Postgres Advanced Server or EDB Postgres Extended Server version 15 on RHEL 8. The etcd hosts will be called etcd1, etcd2, and etcd3. The two database nodes in streaming replication are pg-patroni1 and pg-patroni2.
The Postgres flavor you choose determines your environment variables.
For EDB Postgres Advanced Server, use the following settings:
For EDB Postgres Extended Server, use the following settings:
Patroni bootstraps (creates) the initial Postgres cluster and is in charge of starting the service, so make sure systemctl is disabled for this service:
Configure the firewall on each database node so they accept connections to the Postgres service:
Patroni is the component interacting with the watchdog device. Set the permissions of the software watchdog on both pg-patroni1 and pg-patroni2 hosts:
On both pg-patroni1 and pg-patroni2 hosts, install Patroni and its dependencies for etcd. See Installing Patroni.
Define the Patroni configuration in /etc/patroni.yml:
In the postgresql: authentication: section in the example, a superuser is created during initialization (initdb) and later used by Patroni to connect to the database. The replication user is created and used by the standby servers to access the replication source by way of streaming replication. Finally, a rewind user is created and is used with pg_rewind when needed. We then recommend changing those passwords in the example configuration to match your password policy.
$MY_IP and $MY_NAME are specific to the local host. Otherwise, the patroni.yml configuration is the same on all Patroni nodes.
Patroni expects to find the postgres binary in the bin_dir location, while the EDB Postgres Advanced Server binary is called edb-postgres. The postgresql.bin_name setting does not exist in Patroni prior to the 3.0.3 release and will be silently ignored by older versions. For these versions, create an appropriately named symbolic link that points to the relevant binary:
Depending on the Patroni installation source, a systemd file might have already been created. Define your own file with the accurate system user, group, and configuration path:
Start the Patroni service first on pg-patroni1 so it becomes the leader of your Postgres cluster. Then start pg-patroni2.
To list the members of the cluster, use the patronictl command:
The Patroni REST API is available on port 8008. Configure the firewall if needed:
The PGDG yum extras repositories contain haproxy packages.
For the purpose of this example, install HAProxy on both pg-patroni1 and pg-patroni2 hosts:
There are two sections: read-write, using port 5000, and read-only, using port 5001. All Postgres hosts are included in both sections because they are all potential candidates to be either primary or standby. For HAproxy to know the role each host currently has, it queries the Patroni REST API:
By using the /replica endpoint, Patroni redirects only to a standby server. Use /read-only to also include the primary.
Configure the firewall if needed to access the ports for HAProxy to listen on:
You can now use port 5000 and port 5001 to choose between read-write or read-only connections: