How to setup streaming replication in Postgresql

How does it works:

PostgreSQL streaming replication is a process that replicates data from a primary PostgreSQL database to one or more standby databases in real-time. The key aspects are:

1. The primary server writes changes to its write-ahead log (WAL), which contains all the data modifications.

2. The primary server's WAL sender process streams these WAL records to the standby server's WAL receiver process over a network connection.

3. The standby server applies these WAL records as they are received, keeping its database contents synchronized with the primary in near real-time.

4. This allows the standby server(s) to quickly take over as the new primary if the original primary fails, providing high availability and disaster recovery.

5. Streaming replication can be configured to operate in either asynchronous or synchronous mode. In synchronous mode, the primary waits for confirmation that the WAL has been written to at least one standby before committing a transaction.

6. Streaming replication requires the primary and standby servers to have matching OS and PostgreSQL versions, and proper authentication and network connectivity configured between them.

The key benefit of PostgreSQL streaming replication is that it enables fast, continuous data replication with minimal lag, supporting high availability and disaster recovery scenarios.




archive_mode : Must be set to ON to enable archiving of WALs.
wal_level : Must be at least set to hot_standby until version 9.5 or replica in the later versions.
max_wal_senders : Must be set to 3 if you are starting with one slave. For every slave, you may add 2 wal senders.
wal_keep_segments : Set the WAL retention in pg_xlog (until PostgreSQL 9.x) and pg_wal (from PostgreSQL 10). Every WAL requires 16MB of space unless you have explicitly modified the WAL segment size. You may start with 100 or more depending on the space and the amount of WAL that could be generated during a backup.
archive_command : This parameter takes a shell command or external programs. It can be a simple copy command to copy the WAL segments to another location or a script that has the logic to archive the WALs to S3 or a remote backup server.
listen_addresses : Specifies which IP interfaces could accept connections. You could specify all the TCP/IP addresses on which the server could listen to connections from client. ‘*’ means all available IP interfaces. The default : localhost allows only local TCP/IP connections to be made to the postgres server.
hot_standby : Must be set to ON on standby/replica and has no effect on the master. However, when you setup your replication, parameters set on the master are automatically copied. This parameter is important to enable READS on slave.


Here is an explanation of the key parameters for configuring PostgreSQL streaming replication:

Primary Server Parameters

*listen_addresses*
- Specifies the IP address(es) on which the PostgreSQL server will listen for connections from client applications.
- Set this to '*' to listen on all available interfaces.

*wal_level*
- Controls the amount of information written to the write-ahead log (WAL).
- For streaming replication, this should be set to 'replica' or higher.

*max_wal_senders*
- Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients.
- Set this to at least the number of standby servers plus 1.



*wal_keep_size*
The wal_keep_size parameter specifies the minimum size of past WAL (Write-Ahead Log) files that should be kept in the pg_wal directory, in case a standby server needs to fetch them for streaming replication

*wal_sender_timeout*
- Sets the maximum time to wait for a WAL sender to send a message.
- Helps detect abnormal conditions in the WAL sender process.


*archive_mode*
- Enables archiving of WAL files.
- Must be 'on' for streaming replication.

*archive_command*
- Specifies the shell command to execute to archive a WAL file segment.
- For example: 'cp %p /mnt/server/archive_dir/%f'

*pg_hba.conf*
- Configure replication user access in the 'pg_hba.conf' file.



*hot_standby*
- Allows read-only queries on the standby while it is in recovery.


*****

pg_basebackup -h 172.18.0.2 -D /var/lib/pgsql/14/data1/ -Xs -R -p


select * from pg_stat_replication

select * from pg_stat_wal_receiver