https://mindhub365.com/sql/postgresql-streaming-replication-wal-what-it-is-and-how-to-configure-one/

[MindHub365]
Software Engineering
Python Web Development Mobile Development Productivity Tools
GIS
Mapping Spatial Analysis Remote Sensing
Databases
RDBMS NoSQL Database Design
DevOps
CI/CD Containerization Container orchestration Infrastructure as Code
SysAdmin
Linux Administration Network Management Security
search icon
[                    ] Find!
Categories

Software Engineering
Python Web Development Mobile Development Productivity Tools
GIS
Mapping Spatial Analysis Remote Sensing
Databases
RDBMS NoSQL Database Design
DevOps
CI/CD Containerization Container orchestration Infrastructure as Code
SysAdmin
Linux Administration Network Management Security
Back

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

[pg_replica]

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.

Continue reading:

 
10 Important PostgreSQL Tools That Database Administrators and
Developers Should Know About

10 Important PostgreSQL Tools That Database Administrators and
Developers Should Know About

Discover 10 powerful PostgreSQL tools that database administrators
and developers need for efficient performance, backups, migrations,
and optimization. Learn about ...

 
The Role of Indexing in Database Performance: How to Speed Up Your
Queries

The Role of Indexing in Database Performance: How to Speed Up Your
Queries

Discover how to generate one million random user records in
PostgreSQL and understand the significant performance improvements
gained through indexing. ...

Stay in the Loop!

Join our weekly byte-sized updates. We promise not to overflow your
inbox!

[                    ] Join Us!
[MindHub365]

Your IT Knowledge Hub

Software Engineering

  * Python
  * Web Development
  * Mobile Development
  * Productivity Tools

GIS

  * Mapping
  * Spatial Analysis
  * Remote Sensing

Databases

  * RDBMS
  * NoSQL
  * Database Design

DevOps

  * CI/CD
  * Containerization
  * Container orchestration
  * Infrastructure as Code

SysAdmin

  * Linux Administration
  * Network Management
  * Security

(c) 2024 MindHub365. All rights reserved.

Contribute Privacy Policy Terms of Service Contact Us