Use this quick start to create a highly available demo cluster using EDB Postgres Advanced Server or EDB Postgres Extended Server version 15 on Debian 11. The etcd hosts will be called etcd1, etcd2, and etcd3. The two database nodes in streaming replication are pg-patroni1 and pg-patroni2.
On both pg-patroni1 and pg-patroni2 hosts, install your preferred Postgres flavor. See EDB Postgres Advanced Server or EDB Postgres Extended Server for more information about installing these products using the EDB repository.
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. Make sure systemctl is disabled for this service, and remove the Postgres cluster created during the installation:
or
3. Watchdog
Patroni is the component interacting with the watchdog device. Set the permissions of the software watchdog on both pg-patroni1 and pg-patroni2 hosts:
4. Patroni
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:
Note
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 already have 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, and then start pg-patroni2.
To list the members of the cluster, use the patronictl command:
The Patroni REST API is available on port 8008.
With EDB Postgres Advanced Server, after the database cluster is running, some other systemd dependencies might need a reset. Use the following commands to perform the resets:
5. HAProxy
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:
Note
By using the /replica endpoint, Patroni redirects only to a standby server. Use /read-only to also include the primary.
You can now use port 5000 and port 5001 to choose between read-write or read-only connections: