PostgreSQL Streaming Replication (WAL); What It Is And How To Configure One

What is Streaming replication in PostgreSQL?

Streaming replication in PostgreSQL is an efficient method for maintaining a near real-time replica of a primary database on one or more standby servers. The primary server continuously sends Write-Ahead Log (WAL) records to standby servers as they are generated, ensuring minimal latency in the replication process. This approach is designed to enhance high availability and scalability, allowing read queries to be offloaded to standby servers, thereby reducing the load on the primary server. Streaming replication supports both synchronous and asynchronous modes, enabling flexibility in balancing data consistency with performance. The process involves the standby server connecting to the primary, requesting WAL streaming, and applying the received records to its own copy of the database. This method provides faster failover and reduces the risk of data loss compared to file-based log shipping, making it ideal for geographically distributed environments.

How Is Streaming Replication Done?

Streaming replication works by continuously transferring Write-Ahead Log (WAL) data from the primary server to the standby server in real-time, keeping the standby's database nearly identical to the primary. This can be used for master failover or for using replicas to handle read operations, allowing your system to scale by orders of magnitude.

Lets Start With PostgreSQL Configuration Files And Where To Find Them?

PostgreSQL configuration files play a crucial role in managing the settings and behavior of the database server. The primary configuration file, `postgresql.conf`, contains most server settings and can be found in different locations depending on the operating system, such as `/etc/postgresql/<version>/main/postgresql.conf` on Debian/Ubuntu and `/var/lib/pgsql/<version>/data/postgresql.conf` on Red Hat/CentOS. Another key file is `pg_hba.conf`, which controls client authentication by defining how clients can connect to the server.

This file is typically located in the same directory as `postgresql.conf`. Additionally, `pg_ident.conf` is used for username mapping but is less commonly utilized. For versions prior to PostgreSQL 12, `recovery.conf` was used for configuring standby servers, but its contents have since been moved into `postgresql.conf` and `postgresql.auto.conf`, which stores configuration parameters set by `ALTER SYSTEM` commands.

The exact locations of these files can vary based on the operating system, installation method, and PostgreSQL version. To locate these files, the SQL command `SHOW config_file;` can be used within a PostgreSQL instance.

postgres=# SHOW config_file;
               config_file                
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf

List Of Configuration Files And For What They Are Used

postgresql.conf

• Primary configuration file
• Contains most server settings
• Typical locations:
  • /etc/postgresql/<version>/main/
postgresql.conf (Debian/Ubuntu)
  • /var/lib/pgsql/<version>/data/
postgresql.conf (Red Hat/CentOS)
  • C:\Program Files\PostgreSQL<version>\data
\postgresql.conf (Windows)

pg_hba.conf

• Controls client authentication
• Defines how clients are allowed to connect to the server
• Usually in the same directory as postgresql.conf


pg_ident.conf

• Used for user name mapping
• Often in the same directory as postgresql.conf
• Less commonly used
  

recovery.conf (for versions prior to 12)

• Used to configure standby servers and recovery settings
• In PostgreSQL 12 and later, its contents are moved into postgresql.conf and postgresql.auto.conf
• postgresql.auto.conf
  
• Stores configuration parameters set by ALTER SYSTEM commands
• Automatically managed by PostgreSQL
• In the same directory as postgresql.conf


The exact locations can vary depending on:

• Operating system
• Installation method
• PostgreSQL version

Examples of WAL (Write ahead logs) And How They Look Like

you can see them using pg_waldump command:

pg_waldump /var/lib/postgresql/data/pg_wal/000000010000000000000001

...
rmgr: Standby     len (rec/tot):     42/    42, tx:       1738, lsn: 0/01938698, prev 0/01938668, desc: LOCK xid 1738 db 5 rel 18065 
rmgr: Heap        len (rec/tot):    203/   203, tx:       1738, lsn: 0/019386C8, prev 0/01938698, desc: INSERT off: 12, flags: 0x00, blkref #0: rel 1663/5/1259 blk 25
rmgr: Btree       len (rec/tot):     64/    64, tx:       1738, lsn: 0/01938798, prev 0/019386C8, desc: INSERT_LEAF off: 394, blkref #0: rel 1663/5/2662 blk 4
rmgr: Btree       len (rec/tot):    112/   112, tx:       1738, lsn: 0/019387D8, prev 0/01938798, desc: INSERT_LEAF off: 110, blkref #0: rel 1663/5/2663 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:       1738, lsn: 0/01938848, prev 0/019387D8, desc: INSERT_LEAF off: 229, blkref #0: rel 1663/5/3455 blk 4
rmgr: Heap2       len (rec/tot):    176/   176, tx:       1738, lsn: 0/01938888, prev 0/01938848, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [36], blkref #0: rel 1663/5/1249 blk 113
rmgr: Btree       len (rec/tot):     80/    80, tx:       1738, lsn: 0/01938938, prev 0/01938888, desc: INSERT_LEAF off: 61, blkref #0: rel 1663/5/2658 blk 28
rmgr: Btree       len (rec/tot):     64/    64, tx:       1738, lsn: 0/01938988, prev 0/01938938, desc: INSERT_LEAF off: 319, blkref #0: rel 1663/5/2659 blk 18
rmgr: Heap        len (rec/tot):    197/   197, tx:       1738, lsn: 0/019389C8, prev 0/01938988, desc: INSERT off: 23, flags: 0x00, blkref #0: rel 1663/5/2610 blk 11
rmgr: Btree       len (rec/tot):     64/    64, tx:       1738, lsn: 0/01938A90, prev 0/019389C8, desc: INSERT_LEAF off: 200, blkref #0: rel 1663/5/2678 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:       1738, lsn: 0/01938AD0, prev 0/01938A90, desc: INSERT_LEAF off: 180, blkref #0: rel 1663/5/2679 blk 2
rmgr: Heap2       len (rec/tot):     85/    85, tx:       1738, lsn: 0/01938B10, prev 0/01938AD0, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [132], blkref #0: rel 1663/5/2608 blk 26
rmgr: Btree       len (rec/tot):     72/    72, tx:       1738, lsn: 0/01938B68, prev 0/01938B10, desc: INSERT_LEAF off: 233, blkref #0: rel 1663/5/2673 blk 20
rmgr: Btree       len (rec/tot):     72/    72, tx:       1738, lsn: 0/01938BB0, prev 0/01938B68, desc: INSERT_LEAF off: 43, blkref #0: rel 1663/5/2674 blk 12
rmgr: XLOG        len (rec/tot):     49/   209, tx:       1738, lsn: 0/01938BF8, prev 0/01938BB0, desc: FPI , blkref #0: rel 1663/5/18065 blk 1 FPW
rmgr: XLOG        len (rec/tot):     49/   137, tx:       1738, lsn: 0/01938CD0, prev 0/01938BF8, desc: FPI , blkref #0: rel 1663/5/18065 blk 0 FPW
rmgr: Heap        len (rec/tot):    188/   188, tx:       1738, lsn: 0/01938D60, prev 0/01938CD0, desc: INPLACE off: 12, blkref #0: rel 1663/5/1259 blk 25
rmgr: Transaction len (rec/tot):    242/   242, tx:       1738, lsn: 0/01938E20, prev 0/01938D60, desc: COMMIT 2024-10-02 21:47:28.453226 UTC; inval msgs: catcache 55 catcache 54 catcache 7 catcache 6 catcache 32 catcache 55 catcache 54 relcache 18065 relcache 17640 snapshot 2608 relcache 17640 relcache 18065
rmgr: Storage     len (rec/tot):     42/    42, tx:          0, lsn: 0/01938F18, prev 0/01938E20, desc: CREATE base/5/18066
rmgr: Standby     len (rec/tot):     42/    42, tx:       1739, lsn: 0/01938F48, prev 0/01938F18, desc: LOCK xid 1739 db 5 rel 18066 
rmgr: Heap        len (rec/tot):    203/   203, tx:       1739, lsn: 0/01938F78, prev 0/01938F48, desc: INSERT off: 13, flags: 0x00, blkref #0: rel 1663/5/1259 blk 25
rmgr: Btree       len (rec/tot):     64/    64, tx:       1739, lsn: 0/01939048, prev 0/01938F78, desc: INSERT_LEAF off: 395, blkref #0: rel 1663/5/2662 blk 4
rmgr: Btree       len (rec/tot):    104/   104, tx:       1739, lsn: 0/01939088, prev 0/01939048, desc: INSERT_LEAF off: 116, blkref #0: rel 1663/5/2663 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:       1739, lsn: 0/019390F0, prev 0/01939088, desc: INSERT_LEAF off: 230, blkref #0: rel 1663/5/3455 blk 4
rmgr: Heap2       len (rec/tot):    176/   176, tx:       1739, lsn: 0/01939130, prev 0/019390F0, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [37], blkref #0: rel 1663/5/1249 blk 113
rmgr: Btree       len (rec/tot):     72/    72, tx:       1739, lsn: 0/019391E0, prev 0/01939130, desc: INSERT_LEAF off: 62, blkref #0: rel 1663/5/2658 blk 28
rmgr: Btree       len (rec/tot):     64/    64, tx:       1739, lsn: 0/01939228, prev 0/019391E0, desc: INSERT_LEAF off: 320, blkref #0: rel 1663/5/2659 blk 18
rmgr: Heap        len (rec/tot):    197/   197, tx:       1739, lsn: 0/01939268, prev 0/01939228, desc: INSERT off: 24, flags: 0x00, blkref #0: rel 1663/5/2610 blk 11
rmgr: Btree       len (rec/tot):     64/    64, tx:       1739, lsn: 0/01939330, prev 0/01939268, desc: INSERT_LEAF off: 201, blkref #0: rel 1663/5/2678 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:       1739, lsn: 0/01939370, prev 0/01939330, desc: INSERT_LEAF off: 181, blkref #0: rel 1663/5/2679 blk 2
rmgr: Heap2       len (rec/tot):     85/    85, tx:       1739, lsn: 0/019393B0, prev 0/01939370, desc: MULTI_INSERT ntuples: 1, flags: 0x02, offsets: [133], blkref #0: rel 1663/5/2608 blk 26
rmgr: Btree       len (rec/tot):     72/    72, tx:       1739, lsn: 0/01939408, prev 0/019393B0, desc: INSERT_LEAF off: 234, blkref #0: rel 1663/5/2673 blk 20
rmgr: Btree       len (rec/tot):     72/    72, tx:       1739, lsn: 0/01939450, prev 0/01939408, desc: INSERT_LEAF off: 44, blkref #0: rel 1663/5/2674 blk 12
rmgr: XLOG        len (rec/tot):     49/   205, tx:       1739, lsn: 0/01939498, prev 0/01939450, desc: FPI , blkref #0: rel 1663/5/18066 blk 1 FPW
rmgr: XLOG        len (rec/tot):     49/   137, tx:       1739, lsn: 0/01939568, prev 0/01939498, desc: FPI , blkref #0: rel 1663/5/18066 blk 0 FPW
rmgr: Heap        len (rec/tot):    188/   188, tx:       1739, lsn: 0/019395F8, prev 0/01939568, desc: INPLACE off: 13, blkref #0: rel 1663/5/1259 blk 25
rmgr: Transaction len (rec/tot):    242/   242, tx:       1739, lsn: 0/019396B8, prev 0/019395F8, desc: COMMIT 2024-10-02 21:47:28.457807 UTC; inval msgs: catcache 55 catcache 54 catcache 7 catcache 6 catcache 32 catcache 55 catcache 54 relcache 18066 relcache 17640 snapshot 2608 relcache 17640 relcache 18066
...

Let me break down some key aspects of this WAL output

- Structure: Each line represents a WAL record, containing information about database operations.

- Components of each record:

• - rmgr: Resource manager (e.g., Heap, Btree, Transaction)
• - len: Length of the record
• - tx: Transaction ID
• - lsn: Log Sequence Number
• - prev: Previous LSN
• - desc: Description of the operation

- Types of operations visible:

• - INSERT operations (Heap and Btree)
• - MULTI_INSERT operations (Heap2)
• - COMMIT transactions
• - File operations (CREATE)
• - Full Page Writes (FPW)

- Specific examples:

• - Table inserts: `rmgr: Heap len (rec/tot): 203/203, tx: 1738, lsn: 0/019386C8, prev 0/01938698, desc: INSERT off: 12, flags: 0x00, blkref #0: rel 1663/5/1259 blk 25`
• - Index updates: `rmgr: Btree len (rec/tot): 64/ 64, tx: 1738, lsn: 0/01938798, prev 0/019386C8, desc: INSERT_LEAF off: 394, blkref #0: rel 1663/5/2662 blk 4`
• - Transaction commit: `rmgr: Transaction len (rec/tot): 242/ 242, tx: 1738, lsn: 0/01938E20, prev 0/01938D60, desc: COMMIT 2024-10-02 21:47:28.453226 UTC;`

This WAL output provides a detailed view of the database operations, allowing for analysis of transaction flow, data modifications, and system activities. It's particularly useful for understanding database behavior, troubleshooting, and in some cases, for point-in-time recovery.

How to work with them using docker

In postgresql.conf, specific settings are crucial for enabling streaming replication. These settings control how the master and replica instances communicate and synchronize. You will need to modify this configuration for both the master and the replica. Here is the snippet from postgresql.conf related to streaming replication:

...
# REPLICATION
#------------------------------------------------------------------------------

# - Sending Servers -

# Set these on the primary and on any standby that will send replication data.

#max_wal_senders = 10           # max number of walsender processes
                                # (change requires restart)
#max_replication_slots = 10     # max number of replication slots
                                # (change requires restart)
#wal_keep_size = 0              # in megabytes; 0 disables
#max_slot_wal_keep_size = -1    # in megabytes; -1 disables
#wal_sender_timeout = 60s       # in milliseconds; 0 disables
#track_commit_timestamp = off   # collect timestamp of transaction commit
                                # (change requires restart)

# - Primary Server -

# These settings are ignored on a standby server.

#synchronous_standby_names = '' # standby servers that provide sync rep
                                # method to choose sync standbys, number of sync standbys,
                                # and comma-separated list of application_name
                                # from standby(s); '*' = all

# - Standby Servers -

# These settings are ignored on a primary server.

#primary_conninfo = ''                  # connection string to sending server
#primary_slot_name = ''                 # replication slot on sending server
#hot_standby = on                       # "off" disallows queries during recovery
                                        # (change requires restart)
#max_standby_archive_delay = 30s        # max delay before canceling queries
                                        # when reading WAL from archive;
                                        # -1 allows indefinite delay
#max_standby_streaming_delay = 30s      # max delay before canceling queries
                                        # when reading streaming WAL;
                                        # -1 allows indefinite delay
#wal_receiver_create_temp_slot = off    # create temp slot if primary_slot_name
                                        # is not set
#wal_receiver_status_interval = 10s     # send replies at least this often
                                        # 0 disables
#hot_standby_feedback = off             # send info from standby to prevent
                                        # query conflicts
#wal_receiver_timeout = 60s             # time that receiver waits for
                                        # communication from primary
                                        # in milliseconds; 0 disables
#wal_retrieve_retry_interval = 5s       # time to wait before retrying to
                                        # retrieve WAL after a failed attempt
#recovery_min_apply_delay = 0           # minimum delay for applying changes during recovery

# - Subscribers -
# These settings are ignored on a publisher.

#max_logical_replication_workers = 4    # taken from max_worker_processes
                                        # (change requires restart)
#max_sync_workers_per_subscription = 2  # taken from max_logical_replication_workers
#max_parallel_apply_workers_per_subscription = 2        # taken from max_logical_replication_workers
...

Let's Dive Into Docker Compose Example

What you will need is:

• init-master.sh:
• init-replica.sh
• start-replica.sh
• docker-compose.yml

init-master.sh:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicatorpass';
  SELECT pg_create_physical_replication_slot('replica_slot');
EOSQL

cat >> ${PGDATA}/postgresql.conf <<EOF
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
EOF

cat >> ${PGDATA}/pg_hba.conf <<EOF
host replicat

sets up a PostgreSQL master for replication. It connects to the PostgreSQL server using the provided environment variables for username and database, then creates a replication user (`replicator`) with the necessary permissions. It also creates a replication slot (`replica_slot`) to track the replication process. Next, the script configures PostgreSQL by appending settings to `postgresql.conf`, enabling WAL (Write-Ahead Logging) for replication, setting up replication slots, and enabling hot standby for read-only queries. It also updates `pg_hba.conf` to allow the replication user to connect.

init-replica.sh

#!/bin/bash
set -e

echo "Waiting for master to be ready..."
until PGPASSWORD=mypassword psql -h postgres_master -U myuser -d mydb -c '\l' >/dev/null 2>&1; do
  echo "Still waiting..."
  sleep 1
done
echo "Master is ready. Attempting to take base backup..."

# Stop PostgreSQL if it's running
pg_ctl -D "$PGDATA" -m fast -w stop || true

# Remove existing data directory contents
rm -rf $PGDATA/*

PGPASSWORD=replicatorpass pg_basebackup -h postgres_master -D ${PGDATA} -U replicator -v -P --wal-method=stream

echo "Base backup completed. Configuring replica..."

cat >> ${PGDATA}/postgresql.conf <<EOF
hot_standby = on
EOF

cat > ${PGDATA}/postgresql.auto.conf <<EOF
primary_conninfo = 'host=postgres_master port=5432 user=replicator password=replicatorpass application_name=replica1'
EOF

touch ${PGDATA}/standby.signal

echo "Replica configuration complete."

prepares a PostgreSQL replica to connect to the master and start replication. It waits until the master is fully up and running by continuously checking the connection to the master database. Once the master is ready, it stops the local PostgreSQL instance (if running), removes existing data, and performs a base backup from the master using `pg_basebackup`. After the backup is complete, it configures the replica by enabling `hot_standby` for read-only queries and setting the connection info for the master (including host, user, and password). Finally, it signals PostgreSQL that this instance should act as a standby by creating the `standby.signal` file.

start-replica.sh

#!/bin/bash
set -e

# Run the init-replica.sh script
/docker-entrypoint-initdb.d/init-replica.sh

# Start PostgreSQL
exec docker-entrypoint.sh postgres

is used to start a PostgreSQL replica in a Docker container. It first runs the `init-replica.sh` script to handle the initialization and setup of the replication process. After initializing the replica, the script starts PostgreSQL using the default Docker entrypoint (`docker-entrypoint.sh`) with `postgres` as the argument to run the PostgreSQL service inside the container. The `set -e` ensures that the script exits immediately if any command returns a non-zero status (i.e., on error).

docker-compose.yml

services:
  postgres_master:
    image: postgres:14
    container_name: postgres_master
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    volumes:
      - ./pgdata_master:/var/lib/postgresql/data
      - ./init-master.sh:/docker-entrypoint-initdb.d/init-master.sh
    ports:
      - "5432:5432"

  postgres_replica:
    image: postgres:14
    container_name: postgres_replica
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    volumes:
      - ./pgdata_replica:/var/lib/postgresql/data
      - ./init-replica.sh:/docker-entrypoint-initdb.d/init-replica.sh
      - ./start-replica.sh:/start-replica.sh
    ports:
      - "5433:5432"
    depends_on:
      - postgres_master
    command: ["/start-replica.sh"]

Make the scripts executable (init-master.sh, init-replica.sh, start-replica.sh) with:

chmod +x ....scripts

And when you run

docker-compose up -d

You should see this output:

Attaching to postgres_master, postgres_replica
postgres_master   | The files belonging to this database system will be owned by user "postgres".
postgres_master   | This user must also own the server process.
postgres_master   | 
postgres_master   | The database cluster will be initialized with locale "en_US.utf8".
postgres_master   | The default database encoding has accordingly been set to "UTF8".
postgres_master   | The default text search configuration will be set to "english".
postgres_master   | 
postgres_master   | Data page checksums are disabled.
postgres_master   | 
postgres_master   | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres_master   | creating subdirectories ... ok
postgres_master   | selecting dynamic shared memory implementation ... posix
postgres_master   | selecting default max_connections ... 100
postgres_master   | selecting default shared_buffers ... 128MB
postgres_master   | selecting default time zone ... Etc/UTC
postgres_master   | creating configuration files ... ok
postgres_master   | running bootstrap script ... ok
postgres_replica  | Waiting for master to be ready...
postgres_replica  | Still waiting...
postgres_master   | performing post-bootstrap initialization ... ok
postgres_master   | initdb: warning: enabling "trust" authentication for local connections
postgres_master   | You can change this by editing pg_hba.conf or using the option -A, or
postgres_master   | --auth-local and --auth-host, the next time you run initdb.
postgres_master   | syncing data to disk ... ok
postgres_master   | 
postgres_master   | 
postgres_master   | Success. You can now start the database server using:
postgres_master   | 
postgres_master   |     pg_ctl -D /var/lib/postgresql/data -l logfile start
postgres_master   | 
postgres_master   | waiting for server to start....2024-10-10 19:12:07.091 UTC [49] LOG:  starting PostgreSQL 14.9 (Debian 14.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres_master   | 2024-10-10 19:12:07.095 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_master   | 2024-10-10 19:12:07.106 UTC [50] LOG:  database system was shut down at 2024-10-10 19:12:06 UTC
postgres_master   | 2024-10-10 19:12:07.114 UTC [49] LOG:  database system is ready to accept connections
postgres_master   |  done
postgres_master   | server started
postgres_master   | CREATE DATABASE
postgres_master   | 
postgres_master   | 
postgres_master   | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init-master.sh
postgres_master   | CREATE ROLE
postgres_master   |  pg_create_physical_replication_slot 
postgres_master   | -------------------------------------
postgres_master   |  (replica_slot,)
postgres_master   | (1 row)
postgres_master   | 
postgres_master   | 
postgres_master   | waiting for server to shut down....2024-10-10 19:12:07.384 UTC [49] LOG:  received fast shutdown request
postgres_master   | 2024-10-10 19:12:07.389 UTC [49] LOG:  aborting any active transactions
postgres_master   | 2024-10-10 19:12:07.392 UTC [49] LOG:  background worker "logical replication launcher" (PID 56) exited with exit code 1
postgres_master   | 2024-10-10 19:12:07.392 UTC [51] LOG:  shutting down
postgres_master   | 2024-10-10 19:12:07.419 UTC [49] LOG:  database system is shut down
postgres_master   |  done
postgres_master   | server stopped
postgres_master   | 
postgres_master   | PostgreSQL init process complete; ready for start up.
postgres_master   | 
postgres_master   | 2024-10-10 19:12:07.527 UTC [1] LOG:  starting PostgreSQL 14.9 (Debian 14.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres_master   | 2024-10-10 19:12:07.527 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_master   | 2024-10-10 19:12:07.527 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_master   | 2024-10-10 19:12:07.533 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_master   | 2024-10-10 19:12:07.540 UTC [69] LOG:  database system was shut down at 2024-10-10 19:12:07 UTC
postgres_master   | 2024-10-10 19:12:07.552 UTC [1] LOG:  database system is ready to accept connections
postgres_replica  | Master is ready. Attempting to take base backup...
postgres_replica  | pg_ctl: cannot be run as root
postgres_replica  | Please log in (using, e.g., "su") as the (unprivileged) user that will
postgres_replica  | own the server process.
postgres_replica  | pg_basebackup: initiating base backup, waiting for checkpoint to complete
postgres_replica  | pg_basebackup: checkpoint completed
postgres_replica  | pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
postgres_replica  | pg_basebackup: starting background WAL receiver
postgres_replica  | pg_basebackup: created temporary replication slot "pg_basebackup_78"
postgres_replica  |     0/34859 kB (0%), 0/1 tablespace (...lib/postgresql/data/backup_label)
postgres_replica  | 27884/34859 kB (79%), 0/1 tablespace (.../postgresql/data/base/16384/1247)
postgres_replica  | 34868/34868 kB (100%), 0/1 tablespace (...ostgresql/data/global/pg_control)
postgres_replica  | 34868/34868 kB (100%), 1/1 tablespace                                         
postgres_replica  | pg_basebackup: write-ahead log end point: 0/2000100
postgres_replica  | pg_basebackup: waiting for background process to finish streaming ...
postgres_replica  | pg_basebackup: syncing data to disk ...
postgres_replica  | pg_basebackup: renaming backup_manifest.tmp to backup_manifest
postgres_replica  | pg_basebackup: base backup completed
postgres_replica  | Base backup completed. Configuring replica...
postgres_replica  | Replica configuration complete.
postgres_replica  | 
postgres_replica  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres_replica  | 
postgres_replica  | 2024-10-10 19:12:08.283 UTC [1] LOG:  starting PostgreSQL 14.9 (Debian 14.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres_replica  | 2024-10-10 19:12:08.284 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_replica  | 2024-10-10 19:12:08.284 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_replica  | 2024-10-10 19:12:08.290 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_replica  | 2024-10-10 19:12:08.299 UTC [39] LOG:  database system was interrupted; last known up at 2024-10-10 19:12:07 UTC
postgres_replica  | 2024-10-10 19:12:08.394 UTC [39] LOG:  entering standby mode
postgres_replica  | 2024-10-10 19:12:08.400 UTC [39] LOG:  redo starts at 0/2000028
postgres_replica  | 2024-10-10 19:12:08.403 UTC [39] LOG:  consistent recovery state reached at 0/2000100
postgres_replica  | 2024-10-10 19:12:08.404 UTC [1] LOG:  database system is ready to accept read-only connections
postgres_replica  | 2024-10-10 19:12:08.426 UTC [43] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

Now let's verify the setup...

Connect to the master:

docker exec -it postgres_master psql -U myuser -d mydb

and check replication status

SELECT pid, usename, application_name, client_addr FROM pg_stat_replication;

The output will be:

pid |  usename   | application_name | client_addr 
-----+------------+------------------+-------------
  79 | replicator | replica1         | 172.20.0.3
(1 row)

Now lets check the replica

docker exec -it postgres_replica psql -U myuser -d mydb

The query and the output:

mydb=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

And with these changes, every modification made on the primary server will be reflected on the replica server in real-time. While this article is a bit lengthy, it’s important to showcase the entire configuration process and outputs. This helps to provide a comprehensive view of the many moving parts involved and gives you a better understanding of what’s happening "under the hood."

Understanding how PostgreSQL streaming replication works and configuring it correctly can greatly enhance your system’s performance and resilience. Whether you’re preparing for failover scenarios or distributing read loads across replicas, these configurations ensure your database infrastructure is ready to scale and perform reliably. Hopefully, this article has demystified the process and given you clearer insight into how replication operates behind the scenes.