# PostgreSQL cluster

> [HTML Version](configure-postgresql.html)

This article describes how to install PostgreSQL 13 for Ubuntu Linux 20.04 and 22.04. See the supported PostgreSQL versions for correct system operation in the [System requirements for BRIX On-Premises](elma365-enterprise-on-premises.md#postgresql) article. You can also refer to the [official PostgreSQL documentation](https://www.postgresql.org/docs/13/high-availability.html).

The installation consists of 10 steps:

1. [Prepare nodes (servers)](#preparation-node).

2. [Prepare the etcd cluster](#preparation-etcd).

3. [Install PostgreSQL](#install-postgresql).

4. [Configure PostgreSQL](#setting-postgresql).

5. [Install Patroni](#install-patroni).

6. [Configure Patroni](#setting-patroni).

7. [Prepare the PostgreSQL+Patroni cluster](#preparation-postgresql-patroni).

8. [Prepare PGBouncer (optional)](#prepare-pgbouncer).

9. [Configure HAProxy (postgres block)](#haproxy-configuration).

10. [Connect to PostgreSQL](#connecting-to-postgresql).

## Step 1: Prepare nodes (servers)

Create three nodes (servers) with sequentially numbered host names.

````
начало внимание

````
The minimum number of servers for organizing a cluster is three.

````
конец внимание

````
In this example, three nodes with the following hostnames and IP addresses are used:

- **postgres-server1.your\_domain, 192.168.1.1**

- **postgres-server2.your\_domain, 192.168.1.2**

- **postgres-server3.your\_domain, 192.168.1.3**

Create the necessary host name mappings in DNS. If not possible, add the required entries in \[OBJECT\].

## Step 2: Prepare the etcd cluster

1. Install \[OBJECT\] on all nodes:

````
sudo apt-get install etcd -y

2. ````
Stop \[OBJECT\] on all nodes:

````
sudo systemctl stop etcd

3. ````
Remove the data directory:

````
sudo rm -rf /var/lib/etcd/\*

4. ````
Move the default configuration file:

````
sudo mv /etc/default/etcd\{,.original\}

5. ````
Create and open a new configuration file for editing:

````
sudo nano /etc/default/etcd

6. ````
Add the example configurations for the** postgres-server1.your\_domain** node:

````
ETCD\_NAME="postgres-server1"  
ETCD\_DATA\_DIR="/var/lib/etcd/default"  
ETCD\_HEARTBEAT\_INTERVAL="1000"  
ETCD\_ELECTION\_TIMEOUT="5000"  
ETCD\_LISTEN\_PEER\_URLS="http://192.168.1.1:2380"  
ETCD\_LISTEN\_CLIENT\_URLS="http://192.168.1.1:2379,http://localhost:2379"  
ETCD\_INITIAL\_ADVERTISE\_PEER\_URLS="http://192.168.1.1:2380"  
ETCD\_INITIAL\_CLUSTER="postgres-server1=http://192.168.1.1:2380,postgres-server2=http://192.168.1.2:2380,postgres-server3=http://192.168.1.3:2380"  
ETCD\_INITIAL\_CLUSTER\_STATE="new"  
ETCD\_INITIAL\_CLUSTER\_TOKEN="etcd-postgres-cluster"  
ETCD\_ADVERTISE\_CLIENT\_URLS="http://192.168.1.1:2379"  
ETCD\_ENABLE\_V2="true"  
ETCD\_INITIAL\_ELECTION\_TICK\_ADVANCE="false"

````
Example configurations with enabling TLS/SSL for the postgres-server1.your\_domain node:

````
ETCD\_NAME="postgres-server1"  
ETCD\_DATA\_DIR="/var/lib/etcd/default"  
ETCD\_HEARTBEAT\_INTERVAL="1000"  
ETCD\_ELECTION\_TIMEOUT="5000"  
ETCD\_LISTEN\_PEER\_URLS="https://192.168.1.1:2380"  
ETCD\_LISTEN\_CLIENT\_URLS="https://192.168.1.1:2379,https://localhost:2379"  
ETCD\_INITIAL\_ADVERTISE\_PEER\_URLS="https://postgres-server1.your\_domain:2380"  
ETCD\_INITIAL\_CLUSTER="postgres-server1=https://postgres-server1.your\_domain:2380,postgres-server2=https://postgres-server2.your\_domain:2380,postgres-server3=https://postgres-server3.your\_domain:2380"  
ETCD\_INITIAL\_CLUSTER\_STATE="new"  
ETCD\_INITIAL\_CLUSTER\_TOKEN="etcd-postgres-cluster"  
ETCD\_ADVERTISE\_CLIENT\_URLS="https://postgres-server1.your\_domain:2379"  
ETCD\_ENABLE\_V2="true"  
ETCD\_CERT\_FILE="/path/to/public.crt"  
ETCD\_KEY\_FILE="/path/to/private.key"  
ETCD\_CLIENT\_CERT\_AUTH="true"  
ETCD\_TRUSTED\_CA\_FILE="/path/to/certCA.pem"  
ETCD\_PEER\_CERT\_FILE="/path/to/public.crt"  
ETCD\_PEER\_KEY\_FILE="/path/to/private.key"  
ETCD\_PEER\_CLIENT\_CERT\_AUTH="true"  
ETCD\_PEER\_TRUSTED\_CA\_FILE="/path/to/certCA.pem"  
ETCD\_INITIAL\_ELECTION\_TICK\_ADVANCE="false"
````

7. ````
Add the example configurations to the file for the **postgres-server2.your\_domain **node:

````
ETCD\_NAME="postgres-server2"  
ETCD\_DATA\_DIR="/var/lib/etcd/default"  
ETCD\_HEARTBEAT\_INTERVAL="1000"  
ETCD\_ELECTION\_TIMEOUT="5000"  
ETCD\_LISTEN\_PEER\_URLS="http://192.168.1.2:2380"  
ETCD\_LISTEN\_CLIENT\_URLS="http://192.168.1.2:2379,http://127.0.0.1:2379"  
ETCD\_INITIAL\_ADVERTISE\_PEER\_URLS="http://192.168.1.2:12380"  
ETCD\_INITIAL\_CLUSTER="postgres-server1=http://192.168.1.1:12380,postgres-server2=http://192.168.1.2:12380,postgres-server3=http://192.168.1.3:2380"  
ETCD\_INITIAL\_CLUSTER\_STATE="new"  
ETCD\_INITIAL\_CLUSTER\_TOKEN="etcd-postgres-cluster"  
ETCD\_ADVERTISE\_CLIENT\_URLS="http://192.168.1.2:2379"  
ETCD\_ENABLE\_V2="true"  
ETCD\_INITIAL\_ELECTION\_TICK\_ADVANCE="false"

````
Example configurations with enabling TLS/SSL for the postgres-server2.your\_domain:

````
ETCD\_NAME="postgres-server2"  
ETCD\_DATA\_DIR="/var/lib/etcd/default"  
ETCD\_HEARTBEAT\_INTERVAL="1000"  
ETCD\_ELECTION\_TIMEOUT="5000"  
ETCD\_LISTEN\_PEER\_URLS="https://192.168.1.2:2380"  
ETCD\_LISTEN\_CLIENT\_URLS="https://192.168.1.2:2379,https://localhost:2379"  
ETCD\_INITIAL\_ADVERTISE\_PEER\_URLS="https://postgres-server2.your\_domain:2380"  
ETCD\_INITIAL\_CLUSTER="postgres-server1=https://postgres-server1.your\_domain:2380,postgres-server2=https://postgres-server2.your\_domain:2380,postgres-server3=https://postgres-server3.your\_domain:2380"  
ETCD\_INITIAL\_CLUSTER\_STATE="new"  
ETCD\_INITIAL\_CLUSTER\_TOKEN="etcd-postgres-cluster"  
ETCD\_ADVERTISE\_CLIENT\_URLS="https://postgres-server2.your\_domain:2379"  
ETCD\_ENABLE\_V2="true"  
ETCD\_CERT\_FILE="/path/to/public.crt"  
ETCD\_KEY\_FILE="/path/to/private.key"  
ETCD\_CLIENT\_CERT\_AUTH="true"  
ETCD\_TRUSTED\_CA\_FILE="/path/to/certCA.pem"  
ETCD\_PEER\_CERT\_FILE="/path/to/public.crt"  
ETCD\_PEER\_KEY\_FILE="/path/to/private.key"  
ETCD\_PEER\_CLIENT\_CERT\_AUTH="true"  
ETCD\_PEER\_TRUSTED\_CA\_FILE="/path/to/certCA.pem"  
ETCD\_INITIAL\_ELECTION\_TICK\_ADVANCE="false"
````

8. ````
Add the example configurations to the file for the **postgres-server3.your\_domain **node:

````
ETCD\_NAME="postgres-server3"  
ETCD\_DATA\_DIR="/var/lib/etcd/default"  
ETCD\_HEARTBEAT\_INTERVAL="1000"  
ETCD\_ELECTION\_TIMEOUT="5000"  
ETCD\_LISTEN\_PEER\_URLS="http://192.168.1.3:2380"  
ETCD\_LISTEN\_CLIENT\_URLS="http://192.168.1.3:2379,http://localhost:2379"  
ETCD\_INITIAL\_ADVERTISE\_PEER\_URLS="http://192.168.1.3:2380"  
ETCD\_INITIAL\_CLUSTER="postgres-server1=http://192.168.1.1:2380,postgres-server2=http://192.168.1.2:2380,postgres-server3=http://192.168.1.3:2380"  
ETCD\_INITIAL\_CLUSTER\_STATE="new"  
ETCD\_INITIAL\_CLUSTER\_TOKEN="etcd-postgres-cluster"  
ETCD\_ADVERTISE\_CLIENT\_URLS="http://192.168.1.3:2379"  
ETCD\_ENABLE\_V2="true"  
ETCD\_INITIAL\_ELECTION\_TICK\_ADVANCE="false"

````
Example configurations with enabling TLS/SSL for the postgres-server3.your\_domain:

````
ETCD\_NAME="postgres-server3"  
ETCD\_DATA\_DIR="/var/lib/etcd/default"  
ETCD\_HEARTBEAT\_INTERVAL="1000"  
ETCD\_ELECTION\_TIMEOUT="5000"  
ETCD\_LISTEN\_PEER\_URLS="https://192.168.1.3:2380"  
ETCD\_LISTEN\_CLIENT\_URLS="https://192.168.1.3:2379,https://localhost:2379"  
ETCD\_INITIAL\_ADVERTISE\_PEER\_URLS="https://postgres-server3.your\_domain:2380"  
ETCD\_INITIAL\_CLUSTER="postgres-server1=https://postgres-server1.your\_domain:2380,postgres-server2=https://postgres-server2.your\_domain:2380,postgres-server3=https://postgres-server3.your\_domain:2380"  
ETCD\_INITIAL\_CLUSTER\_STATE="new"  
ETCD\_INITIAL\_CLUSTER\_TOKEN="etcd-postgres-cluster"  
ETCD\_ADVERTISE\_CLIENT\_URLS="https://postgres-server3.your\_domain:2379"  
ETCD\_ENABLE\_V2="true"  
ETCD\_CERT\_FILE="/path/to/public.crt"  
ETCD\_KEY\_FILE="/path/to/private.key"  
ETCD\_CLIENT\_CERT\_AUTH="true"  
ETCD\_TRUSTED\_CA\_FILE="/path/to/certCA.pem"  
ETCD\_PEER\_CERT\_FILE="/path/to/public.crt"  
ETCD\_PEER\_KEY\_FILE="/path/to/private.key"  
ETCD\_PEER\_CLIENT\_CERT\_AUTH="true"  
ETCD\_PEER\_TRUSTED\_CA\_FILE="/path/to/certCA.pem"  
ETCD\_INITIAL\_ELECTION\_TICK\_ADVANCE="false"
````

````
Let's review the provided parameters:

- \[OBJECT\]: Unique name within the cluster;

- \[OBJECT\]: Connection point for clients in the cluster;

- \[OBJECT\]: URLs through which other cluster nodes can be found.;

- \[OBJECT\]: Connection point for other nodes in the cluster;

- \[OBJECT\]: Initial URLs through which other nodes can find it;

- \[OBJECT\]: Token for the cluster, must be the same on all nodes.;

- \[OBJECT\]: List of nodes in the cluster at startup;

- \[OBJECT\]: Can take two values: \[OBJECT\] and \[OBJECT\];

- \[OBJECT\]: Location of the cluster's data directory;

- \[OBJECT\]: Time in milliseconds between the last received notification from the cluster leader and attempting to take over the leader role on the follower node;

- \[OBJECT\]: Time in milliseconds between leader notifications sent by the leader node to inform others that it is still the leader;

- \[OBJECT\]: Path to the server certificate file;

- \[OBJECT\]: Path to the private key file;

- \[OBJECT\]: Path to the root CA file;

- \[OBJECT\]: Can take two values: \[OBJECT\] and \[OBJECT\];

- \[OBJECT\]: Path to the server certificate file;

- \[OBJECT\]: Path to the private key file;

- \[OBJECT\]: Path to the root CA file;

- \[OBJECT\]: Can take two values: \[OBJECT\] and \[OBJECT\];

9. Restart \[OBJECT\] on all nodes:

````
sudo systemctl restart etcd

10. ````
Check the cluster status.

For a cluster without TLS:

````
sudo etcdctl cluster-health

````
For a cluster with TLS:

````
sudo etcdctl -C https://postgres-server1.your\_domain:2379 --key-file /path/to/private.key --cert-file /path/to/public.crt --ca-file /path/to/certCA.pem cluster-health

## ````
Step 3: Install PostgreSQL

````
начало внимание

````
Hardware requirements for BRIX on PostgreSQL can be found in [System requirements for BRIX On-Premises](elma365-enterprise-on-premises.md#postgresql).

````
конец внимание

1. ````
To install PostgreSQL, add the official repository \[OBJECT\]:

````
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt \$(lsb\_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'  
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

2. ````
Update package cache:

````
sudo apt update

3. ````
Install PostgreSQL on all nodes:

````
sudo apt install postgresql-13 -y

## ````
Step 4: Configure PostgreSQL

````
начало примечание

````
**Note**

For the password, the following characters are allowed:

- Uppercase Latin letters: A to Z

- Lowercase Latin letters: a to z

- Digits: 0 to 9

- Symbols: -\_

Reserved (invalid) symbols:

\! \* ' ( ) ; : @ \& = + \$ , / ? % # \[ \]

````
конец примечание

#### ````
Actions for the node postgres-server1.your\_domain:

1. Create a new role \[OBJECT\] with the password **SecretPassword**:

````
sudo -u postgres psql -c \\  
"CREATE ROLE brix365 WITH login password 'SecretPassword';"

2. ````
Create a database \[OBJECT\] with owner **brix365**:

````
sudo -u postgres psql -c \\  
"CREATE DATABASE brix365 WITH OWNER brix365;"

3. ````
Add necessary extensions for the database \[OBJECT\]:

````
sudo -u postgres psql -d brix365 -c \\  
"CREATE EXTENSION \\"uuid-ossp\\"; CREATE EXTENSION pg\_trgm;"

4. ````
Create a new role \[OBJECT\] with the password **ReplicatorPassword** for working with replicas. It must match the Patroni settings in the block \[OBJECT\] and the list of allowed PostgreSQL hosts in the file \[OBJECT\]:

````
sudo -u postgres psql -c \\  
"CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'ReplicatorPassword';"

5. ````
Set a password for the **postgres** user:

````
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'PostgresPassword';"

6. ````
Stop PostgreSQL:

````
systemctl stop postgresql

#### ````
Actions for the nodes postgres-server2.your\_domain and postgres-server3.your\_domain:

1.  Stop PostgreSQL:

````
systemctl stop postgresql

2. ````
Remove the data directory on the nodes **postgres-server2.your\_domain** and **postgres-server3.your\_domain**:

````
rm -rf /var/lib/postgresql/13/main

## ````
Step 5: Install Patroni

1. Install Patroni and PIP on all nodes:

````
sudo apt-get install python3-pip python3-dev libpq-dev -y  
sudo apt-get install patroni -y

2. ````
Install dependencies for Patroni to work on all nodes:

````
pip3 install psycopg2-binary  
pip3 install wheel  
pip3 install python-etcd

## ````
Step 6: Configure Patroni

1. Create a configuration file:

````
sudo nano /etc/patroni/config.yml

2. ````
Place an example initial configuration in the created \[OBJECT\] file, changing the IP addresses for each node in the cluster. Pay attention to the comments in this file.

Example initial configuration 

````
scope: postgres-cluster # the same value on all nodes  
name: postgresql-server1 # different values on all nodes  
namespace: /service/ # the same value on all nodes  
  
restapi:  
  listen: postgres-server1.your\_domain:8008 # address of the node where the file is  
  connect\_address: postgres-server1.your\_domain:8008 # address of the node where the file is  
  
etcd:  
  hosts: postgres-server1.your\_domain:2379,postgres-server2.your\_domain:2379,postgres-server3.your\_domain:2379 # list of all the nodes with installed etcd  
  
bootstrap:  
  method: initdb  
  dcs:  
    ttl: 30  
    loop\_wait: 10  
    retry\_timeout: 10  
    maximum\_lag\_on\_failover: 1048576  
    master\_start\_timeout: 300  
    synchronous\_mode: false  
    synchronous\_mode\_strict: false  
    synchronous\_node\_count: 1  
    postgresql:  
      use\_pg\_rewind: true  
      use\_slots: true  
      parameters:  
        max\_connections: 2000  
        superuser\_reserved\_connections: 5  
        max\_locks\_per\_transaction: 64  
        max\_prepared\_transactions: 0  
        huge\_pages: try  
        shared\_buffers: 512MB  
        work\_mem: 128MB  
        maintenance\_work\_mem: 256MB  
        effective\_cache\_size: 4GB  
        checkpoint\_timeout: 15min  
        checkpoint\_completion\_target: 0.9  
        wal\_compression: on  
        min\_wal\_size: 2GB  
        max\_wal\_size: 4GB  
        wal\_buffers: 32MB  
        default\_statistics\_target: 1000  
        seq\_page\_cost: 1  
        random\_page\_cost: 4  
        effective\_io\_concurrency: 2  
        synchronous\_commit: on  
        autovacuum: on  
        autovacuum\_max\_workers: 5  
        autovacuum\_vacuum\_scale\_factor: 0.1  
        autovacuum\_analyze\_scale\_factor: 0.05  
        autovacuum\_vacuum\_cost\_limit: 200  
        autovacuum\_vacuum\_cost\_delay: 20  
        autovacuum\_naptime: 15s  
        max\_files\_per\_process: 4096  
        archive\_mode: on  
        archive\_timeout: 1800s  
        archive\_command: cd .  
        wal\_level: replica  
        wal\_keep\_segments: 130  
        max\_wal\_senders: 10  
        max\_replication\_slots: 10  
        hot\_standby: on  
        hot\_standby\_feedback: True  
        wal\_log\_hints: on  
        shared\_preload\_libraries: pg\_stat\_statements,auto\_explain  
        pg\_stat\_statements.max: 10000  
        pg\_stat\_statements.track: all  
        pg\_stat\_statements.save: off  
        auto\_explain.log\_min\_duration: 10s  
        auto\_explain.log\_analyze: true  
        auto\_explain.log\_buffers: true  
        auto\_explain.log\_timing: false  
        auto\_explain.log\_triggers: true  
        auto\_explain.log\_verbose: true  
        auto\_explain.log\_nested\_statements: true  
        standard\_conforming\_strings: true  
        track\_io\_timing: on  
        log\_lock\_waits: on  
        log\_temp\_files: 3  
        track\_activities: on  
        track\_counts: on  
        track\_functions: all  
        log\_checkpoints: on  
        logging\_collector: on  
        log\_truncate\_on\_rotation: on  
        log\_rotation\_age: 1d  
        log\_rotation\_size: 0  
        log\_line\_prefix: '%t \[%p-%l\] %r %q%u@%d '  
        log\_filename: 'postgresql-%a.log'  
        log\_directory: /var/log/postgresql  
  
  initdb: # List options to be passed on to initdb  
    - encoding: UTF8  
    - locale: en\_US.UTF-8  
    - data-checksums  
  
  pg\_hba: # must contain addresses of ALL the machines used in the cluster  
    - local all postgres peer  
    - local all all peer  
    - host all all 0.0.0.0/0 md5  
    - host replication replicator localhost trust  
    - host replication replicator 192.168.1.1/32 md5  
    - host replication replicator 192.168.1.2/32 md5  
    - host replication replicator 192.168.1.3/32 md5  
  
postgresql:  
  listen: 192.168.1.1,127.0.0.1:5432 # address of the node where the file is  
  connect\_address: 192.168.1.1:5432 # address of the node where the file is  
  use\_unix\_socket: true  
  data\_dir: /var/lib/postgresql/13/main # data directory  
  bin\_dir: /usr/lib/postgresql/13/bin  
  config\_dir: /etc/postgresql/13/main  
  pgpass: /var/lib/postgresql/.pgpass\_patroni  
  authentication:  
    replication:  
      username: replicator  
      password: ReplicatorPassword  
    superuser:  
      username: postgres  
      password: PostgresPassword  
  parameters:  
    unix\_socket\_directories: /var/run/postgresql  
  pg\_hba: # must contain addresses of ALL the machines used in the cluster  
    - local all postgres peer  
    - local all all peer  
    - host all all 0.0.0.0/0 md5  
    - host replication replicator localhost trust  
    - host replication replicator 192.168.1.1/32 md5  
    - host replication replicator 192.168.1.2/32 md5  
    - host replication replicator 192.168.1.3/32 md5  
  
  remove\_data\_directory\_on\_rewind\_failure: false  
  remove\_data\_directory\_on\_diverged\_timelines: false  
  
  create\_replica\_methods:  
    - basebackup  
  basebackup:  
    max-rate: '100M'  
    checkpoint: 'fast'  
  
watchdog:  
  mode: off # Allowed values: off, automatic, required  
  device: /dev/watchdog  
  safety\_margin: 5  
  
tags:  
  nofailover: false  
  noloadbalance: false  
  clonefrom: false  
  nosync: false
````

````
Example initial configuration to enable TLS/SSL support in Patroni

Place the example initial configuration with TLS/SSL in the file \[OBJECT\]. Pay attention to the comments in this file:

````
scope: postgres-cluster # the same value on all nodes  
name: postgresql-server1 # different values on all nodes  
namespace: /service/ # the same value on all nodes  
  
restapi:  
  listen: postgres-server1.your\_domain:8008 # address of the node where the file is  
  connect\_address: postgres-server1.your\_domain:8008 # address of the node where the file is  
  cafile: /path/to/pgCA.pem  
  certfile: /path/to/pg.crt # path to the server certificate file  
  keyfile: /path/to/pg.key # path to the private key file  
  verify\_client: required # path to the root CA file  
  
etcd:  
  protocol: https  
  cert: /path/to/public.crt # path to the server certificate file  
  key: /path/to/private.key # path to the private key file  
  cacert: /path/to/certCA.pem # path to the root CA file  
  hosts: postgres-server1.your\_domain:2379,postgres-server2.your\_domain:2379,postgres-server3.your\_domain:2379 # list of all the nodes with installed etcd  
  
ctl:  
  insecure: false # Allow connections to SSL sites without certs  
  certfile: /path/to/pg.crt # path to the server certificate file  
  keyfile: /path/to/pg.key # path to the private key file  
  cacert: /path/to/pgCA.pem # path to the root CA file  
  
bootstrap:  
  method: initdb  
  dcs:  
    ttl: 30  
    loop\_wait: 10  
    retry\_timeout: 10  
    maximum\_lag\_on\_failover: 1048576  
    master\_start\_timeout: 300  
    synchronous\_mode: false  
    synchronous\_mode\_strict: false  
    synchronous\_node\_count: 1  
    postgresql:  
      use\_pg\_rewind: true  
      use\_slots: true  
      parameters:  
        max\_connections: 2000  
        superuser\_reserved\_connections: 5  
        max\_locks\_per\_transaction: 64  
        max\_prepared\_transactions: 0  
        huge\_pages: try  
        shared\_buffers: 512MB  
        work\_mem: 128MB  
        maintenance\_work\_mem: 256MB  
        effective\_cache\_size: 4GB  
        checkpoint\_timeout: 15min  
        checkpoint\_completion\_target: 0.9  
        wal\_compression: on  
        min\_wal\_size: 2GB  
        max\_wal\_size: 4GB  
        wal\_buffers: 32MB  
        default\_statistics\_target: 1000  
        seq\_page\_cost: 1  
        random\_page\_cost: 4  
        effective\_io\_concurrency: 2  
        synchronous\_commit: on  
        autovacuum: on  
        autovacuum\_max\_workers: 5  
        autovacuum\_vacuum\_scale\_factor: 0.1  
        autovacuum\_analyze\_scale\_factor: 0.05  
        autovacuum\_vacuum\_cost\_limit: 200  
        autovacuum\_vacuum\_cost\_delay: 20  
        autovacuum\_naptime: 15s  
        max\_files\_per\_process: 4096  
        archive\_mode: on  
        archive\_timeout: 1800s  
        archive\_command: cd .  
        wal\_level: replica  
        wal\_keep\_segments: 130  
        max\_wal\_senders: 10  
        max\_replication\_slots: 10  
        hot\_standby: on  
        hot\_standby\_feedback: True  
        wal\_log\_hints: on  
        shared\_preload\_libraries: pg\_stat\_statements,auto\_explain  
        pg\_stat\_statements.max: 10000  
        pg\_stat\_statements.track: all  
        pg\_stat\_statements.save: off  
        auto\_explain.log\_min\_duration: 10s  
        auto\_explain.log\_analyze: true  
        auto\_explain.log\_buffers: true  
        auto\_explain.log\_timing: false  
        auto\_explain.log\_triggers: true  
        auto\_explain.log\_verbose: true  
        auto\_explain.log\_nested\_statements: true  
        standard\_conforming\_strings: true  
        track\_io\_timing: on  
        log\_lock\_waits: on  
        log\_temp\_files: 3  
        track\_activities: on  
        track\_counts: on  
        track\_functions: all  
        log\_checkpoints: on  
        logging\_collector: on  
        log\_truncate\_on\_rotation: on  
        log\_rotation\_age: 1d  
        log\_rotation\_size: 0  
        log\_line\_prefix: '%t \[%p-%l\] %r %q%u@%d '  
        log\_filename: 'postgresql-%a.log'  
        log\_directory: /var/log/postgresql  
        ssl: on  
        ssl\_ca\_file: '/path/to/pgCA.pem'  
        ssl\_cert\_file: '/path/to/pg.crt'  
        ssl\_key\_file: '/path/to/pg.key'  
  
  initdb: # List options to be passed on to initdb  
    - encoding: UTF8  
    - locale: en\_US.UTF-8  
    - data-checksums  
  
  pg\_hba: # must contain addresses of ALL the machines used in the cluster  
    - local all postgres peer  
    - local all all peer  
    - hostssl all all 0.0.0.0/0 md5  
    - hostssl replication replicator localhost trust  
    - hostssl replication replicator 192.168.1.1/32 md5  
    - hostssl replication replicator 192.168.1.2/32 md5  
    - hostssl replication replicator 192.168.1.3/32 md5  
  
postgresql:  
  listen: 192.168.1.1,127.0.0.1:5432 # address of the node where the file is  
  connect\_address: 192.168.1.1:5432 # address of the node where the file is  
  use\_unix\_socket: true  
  data\_dir: /var/lib/postgresql/13/main # data directory  
  bin\_dir: /usr/lib/postgresql/13/bin  
  config\_dir: /etc/postgresql/13/main  
  pgpass: /var/lib/postgresql/.pgpass\_patroni  
  authentication:  
    replication:  
      username: replicator  
      password: ReplicatorPassword  
      sslcert: /path/to/pg.crt # path to the server certificate file  
      sslkey: /path/to/pg.key # path to the private key file  
      sslrootcert: /path/to/pgCA.pem # path to the root CA file  
    superuser:  
      username: postgres  
      password: PostgresPassword  
      sslcert: /path/to/pg.crt # path to the server certificate file  
      sslkey: /path/to/pg.key # path to the private key file  
      sslrootcert: /path/to/pgCA.pem # path to the root CA file  
  parameters:  
    unix\_socket\_directories: /var/run/postgresql  
  pg\_hba: # must contain addresses of ALL the machines used in the cluster  
    - local all postgres peer  
    - local all all peer  
    - hostssl all all 0.0.0.0/0 md5  
    - hostssl replication replicator localhost trust  
    - hostssl replication replicator 192.168.1.1/32 md5  
    - hostssl replication replicator 192.168.1.2/32 md5  
    - hostssl replication replicator 192.168.1.3/32 md5  
  
  remove\_data\_directory\_on\_rewind\_failure: false  
  remove\_data\_directory\_on\_diverged\_timelines: false  
  
  create\_replica\_methods:  
    - basebackup  
  basebackup:  
    max-rate: '100M'  
    checkpoint: 'fast'  
  
watchdog:  
  mode: off # Allowed values: off, automatic, required  
  device: /dev/watchdog  
  safety\_margin: 5  
  
tags:  
  nofailover: false  
  noloadbalance: false  
  clonefrom: false  
  nosync: false

````
Make the **postgres** user the owner of the private key file \[OBJECT\]:

````
Начало внимание

````
The private key file \[OBJECT\] specified in the section \[OBJECT\] must have permissions of u=rw (0600) or less if it belongs to the PostgreSQL database user, or permissions of u=rw, g=r (0640) or less if it belongs to the root .

````
Конец внимание

sudo chown postgres:postgres -R /path/to/pg.key  
sudo chmod 600 /path/to/pg.key

````
For more details on TLS/SSL configuration in Patroni, refer to the [official Patroni documentation](https://patroni.readthedocs.io/en/latest/yaml_configuration.html).

3. Make the **postgres** user the owner of the configuration directory:

````
sudo chown postgres:postgres -R /etc/patroni  
sudo chmod 700 /etc/patroni

## ````
Step 7: Prepare PostgreSQL+Patroni cluster

1. Start the Patroni service on the **postgres-server1.your\_domain** node, and then on the nodes **postgres-server2.your\_domain** and **postgres-server3.your\_domain**:

````
sudo systemctl enable --now patroni.service

2. ````
Check the cluster status:

````
patronictl -c /etc/patroni/config.yml list

## ````
Step 8: Prepare PGBouncer (optional)

PGBouncer is designed to manage a connection pool to PostgreSQL, minimizing the overhead associated with establishing new connections to PostgreSQL. For information on installing and configuring PGBouncer, refer to [Install PGBouncer](pgbouncer-installation.md).

## Step 9: Configure HAProxy (postgres block)

Configure a [high availablility HAProxy](fail-safe-haproxy.md)  to handle requests to PostgreSQL and load balance between the nodes of the PostgreSQL cluster. For more details, refer to [Configure HAProxy for PostgreSQL](haproxy-postgresql.md).

## Step 10: Connecto to PostgreSQL

Connection string to connect to the PostgreSQL cluster via Haproxy:

````
psqlUrl: postgresql://brix365:SecretPassword@haproxy-server.your\_domain:5000/brix365?sslmode=prefer

````
Connection string to connect to the PostgreSQL cluster for read-only access via Haproxy:

````
psqlUrl: postgresql://brix365:SecretPassword@haproxy-server.your\_domain:5001/brix365?sslmode=prefer

````
Connection string to connect to the PostgreSQL cluster with replica support:

````
psqlUrl: postgresql://brix365:SecretPassword@postgresql-server1.your\_domain:5432,postgresql-server2.your\_domain:5432,postgresql-server3.your\_domain:5432/brix365?sslmode=prefer\&target\_session\_attrs=primary

````
Connection string for read-only PostgreSQL cluster access with replica support:

````
roPsqlUrl: postgresql://brix365:SecretPassword@postgresql-server1.your\_domain:5432,postgresql-server2.your\_domain:5432,postgresql-server3.your\_domain:5432/brix365?sslmode=prefer\&target\_session\_attrs=prefer-standby

````
If an external secrets storage, such as Vault, is configured, you should [set](install-vault.md#secrets) the \[OBJECT\] and \[OBJECT\] environment variables to connect to the read-only PostgreSQL replica.

## Prepare a PostgreSQL database for restoration

If you need to restore a database from backup, first prepare the database for restoration. For more details, refer to the article [PostgreSQL](postgresql.md#prepare-to-restore).