PostgreSQL 14 Streaming Replication (Sync / Async) on CentOS 7

Using High Availability is a requirement for companies all over the world.

Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they’re generated, without waiting for the WAL file to be filled.

Stream Replication as the replication method is asynchronous by default, so it is possible to do some operations on the primary node that have not yet been copied to the standby server. This means there is some potential possibility of data loss. If the standby server is strong enough to keep up with the load, this delay in the commit process should be very small. If this small risk of data loss is not acceptable at the company, you can also use simultaneous replication instead of the default.

In synchronous replication, each record of a write operation awaits confirmation that the recording was written to both the primary and standby server’s write-from-log disk. This method minimizes the possibility of data loss. For data loss to occur, both primary and standby must fail at the same time.

The disadvantage of this method is the same for all synchronous methods because the response time for each write operation increases with this method. This is because you wait for all confirmations that the transaction is complete. Fortunately, read-only transactions will not be affected.

Master/Primary: 10.165.191.43

Slave/Standby: 10.165.191.44

Install Postgresql-14 on both master/slave servers.

Enabling remote Database connections
Set Listen address to your server IP address or “*” for all interfaces.

$sudo vi /var/lib/pgsql/14/data/postgresql.conf

Set PostgreSQL to accept remote connections

vi /var/lib/pgsql/14/data/pg_hba-conf
#host all all 0.0.0.0/0 scram-sha-256 (Accept from anywhere)
host all all 10.165.191.1/24 scram-sha-256 (Accept from trusted subnet)

$ firewall-cmd --add-service=postgresql --permanent
$ firewall-cmd --reload
$ vi /etc/selinux/config -> Disabled
$ su - postgres
postgres $ createuser -P -e replicator --replication

vi /var/lib/pgsql/14/data/pg_hba.conf
host replication replicator 10.165.191.44/32 scram-sha-256

Slave:

systemctl restart postgresql-14.service

↑ Till here everything is going to be applied for both master/primary and slave/standby servers ↑

From this part on to Slave/Standby Server:

$systemctl stop postgresql-14.service $cp -R /var/lib/pgsql/14/data /var/lib/pgsql/14/data_orjinal $su - postgres $rm -rf /var/lib/pgsql/14/data/* $exit $ls -ltr /var/lib/pgsql/14/data/*

pg_basebackup -h 10.165.191.43 -D /var/lib/pgsql/14/data -U replicator -P -v -R -X stream -C -S pgstandby

replicator passwd

-h host –host=host
Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for a Unix domain socket. The default is taken from the PGHOST environment variable, if set, else a Unix domain socket connection is attempted.

-D directory –pgdata=directory
Sets the target directory to write the output to. pg_basebackup will create this directory (and any missing parent directories) if it does not exist. If it already exists, it must be empty.
When the backup is in tar format, the target directory may be specified as – (dash), causing the tar file to be written to stdout.
This option is required.

-U username –username=username
Specifies the user name to connect as.

-P –progress
Enables progress reporting. Turning this on will deliver an approximate progress report during the backup. Since the database may change during the backup, this is only an approximation and may not end at exactly 100%. In particular, when WAL log is included in the backup, the total amount of data cannot be estimated in advance, and in this case the estimated target size will increase once it passes the total estimate without WAL.

-v –verbose
Enables verbose mode. Will output some extra steps during startup and shutdown, as well as show the exact file name that is currently being processed if progress reporting is also enabled.

-R –write-recovery-conf
Creates a standby.signal file and appends connection settings to the postgresql.auto.conf file in the target directory (or within the base archive file when using tar format). This eases setting up a standby server using the results of the backup.
The postgresql.auto.conf file will record the connection settings and, if specified, the replication slot that pg_basebackup is using, so that streaming replication will use the same settings later on.

-X method –wal-method=method
Includes the required WAL (write-ahead log) files in the backup. This will include all write-ahead logs generated during the backup. Unless the method none is specified, it is possible to start a postmaster in the target directory without the need to consult the log archive, thus making the output a completely standalone backup.
The following methods for collecting the write-ahead logs are supported:

n none
Don’t include write-ahead logs in the backup.

f fetch
The write-ahead log files are collected at the end of the backup. Therefore, it is necessary for the source server’s wal_keep_size parameter to be set high enough that the required log data is not removed before the end of the backup. If the required log data has been recycled before it’s time to transfer it, the backup will fail and be unusable.
When tar format is used, the write-ahead log files will be included in the base.tar file.

s stream (This value is the default.)
Stream write-ahead log data while the backup is being taken. This method will open a second connection to the server and start streaming the write-ahead log in parallel while running the backup. Therefore, it will require two replication connections not just one. As long as the client can keep up with the write-ahead log data, using this method requires no extra write-ahead logs to be saved on the source server.
When tar format is used, the write-ahead log files will be written to a separate file named pg_wal.tar (if the server is a version earlier than 10, the file will be named pg_xlog.tar).

-C –create-slot
Specifies that the replication slot named by the –slot option should be created before starting the backup. An error is raised if the slot already exists.

-S slotname –slot=slotname
This option can only be used together with -X stream. It causes WAL streaming to use the specified replication slot. If the base backup is intended to be used as a streaming-replication standby using a replication slot, the standby should then use the same replication slot name as primary_slot_name. This ensures that the primary server does not remove any necessary WAL data in the time between the end of the base backup and the start of streaming replication on the new standby.
The specified replication slot has to exist unless the option -C is also used.
If this option is not specified and the server supports temporary replication slots (version 10 and later), then a temporary replication slot is automatically used for WAL streaming.

chown -R postgres:postgres /var/lib/pgsql/14/data