FOPENP

PostgreSQL Replication on Debian - The easy way

I managed to replicate the PostgreSQL 16 database on two hosts: the primary and the standby. This is a note I made for future use.

The purpose of “primary” server is to allow read and write access to data. “Standby” is a server that is kept synchronized by the primary and that allows access to data only by reading. In the event of a primary server failure, a standby server can be promoted to a primary one.

First of all you need to install postgresql on both machines:

su
apt install postgresql-16 postgresql-client

On Debian you shall remember that postgres commands are in /usr/lib/postgresql/16/bin/ .

You need to edit the configuration file /etc/postgresql/16/main/postgresql.conf of both machines like this:

listen_addresses = '*'
wal_level = replica
wal_log_hints = on
max_wal_senders = 8
hot_standby = on

also the /etc/postgresql/16/main/pb_hba.conf file must be edited on both machines:

host all all 127.0.0.1/32 trust
host all all 192.168.56.0/24 trust
host replication all 127.0.0.1/32 trust
host replication all 192.168.56.0/24 trust

Instead of 192.168.56.0/24 you need to use the machines' sub-network.

If the primary server is dirty, execute (as root):

systemctl stop postgresql
su postgres
rm -rf /var/lib/postgresql/16/main/*
/usr/lib/postgresql/16/bin/initdb /var/lib/postgresql/16/main/
exit
systemctl start postgresql

Then you need to stop the postgresql standby instance (as root) and execute the pg_basebackup command:

systemctl stop postgresql
su postgres
rm -rf /var/lib/postgresql/16/main/*
pg_basebackup -h primary_ip_server -p 5432 -U postgres -c fast -D /var/lib/postgresql/16/main/ --slot=myslot1 -R -C
exit
systemctl start postgresql

At this point, the primary server will be able to perform read and write operations, while standby will perform read-only operations.

(OPTIONAL) Check the replication statistics on the primary server:

psql
\x
SELECT * FROM pg_stat_replication;

(OPTIONAL) Check the replication statistics on the standby server:

psql
\x
SELECT * FROM pg_stat_wal_receiver;

2023
Dec, 13