PostgreSQL 16: Streaming Replication

Assumptions:

− Same version of PostgreSQL is installed on the Primary and Standby DB servers.

− PostgreSQL 16 DB cluster is already up and running on both servers.

Primary database:

Prepare the PostgreSQL 16 primary database to ship the WAL files to the standby database using the below steps:

− Updating the postgresql.conf file.

− Updating the pg_hba.conf.

− Creating replication role and reload configuration.

postgresql.conf:

1. Take a safe backup of postgresql.conf file.

[root@prd-prime data]# hostname

prd-prime

[root@prd-prime data]# pwd

/u02/pgsql/16/data

cp postgresql.conf postgresql.conf_2_1_2025

2. Add the below parameters into it:

archive_mode = on

synchronous_commit = on

#hot_standby = on

wal_level = replica

wal_log_hints = on

wal_keep_segments = 300

wal_keep_size = 1024

max_wal_senders = 10

PG_HBA.conf:

1. Take a safe backup of pg_hba.conf file.

cp pg_hba.conf pg_hba.conf_2_1_2025

2. Add the below parameters into it:

host replication rep_user xxx.xx.xx.xx/32 md5

host replication rep_user yyy.yy.yy.yy/32 md5

Note 1: rep_user will be created shortly.

Note 2: IP Address of Primary and Standby, xxx.xx.xx.xx and yyy.yy.yy.yy

CREATE REPLICATION ROLE & Reload configuration file:

1. Create replication role:

postgres=# CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'Welcome2025';

CREATE ROLE

2. Run reload configuration to avoid DB restart.

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

Standby database:

Prepare the PostgreSQL 16 standby database to receive WAL files from the primary database using the below steps:

− Creating a new data directory and grant ownership & privileges.

− Run pg_basebackup with required parameters.

− Update postgresql.service file.

− View the auto-created postgresql.auto.conf. (Read only).

− Update the standby.signal file.

− Update the postgresql.conf.

− Start the cluster.

Create data directory and grant access to postgres user:

1. Create a new data directory and grant required privileges and ownership:

[root@prd-stand ]# hostname

prd-stand

[root@prd-stand ]# cd /u02/pgsql/16/

[root@prd-stand ]# mkdir datarep

[root@prd-stand ]# sudo chown -R postgres:postgres /u02/pgsql/16/datarep

[root@prd-stand ]# sudo chmod 750 /u02/pgsql/16/datarep

run pg_basebackup:

2. Switch to Postgres user and run base backup with correct path and parameters:

[postgres@prd-stand] tmux new -s mysession

[postgres@prd-stand bin] cd /usr/bin

[postgres@prd-stand bin]$ pg_basebackup -h xxx.xx.xx.xx -p 5432 -U rep_user -D /u02/pgsql/16/datarep -Fp -Xs -P -R --wal-method=stream

Password:

1025169417/1800888245 kB (56%), 0/1 tablespace

3. Once the base backup is completed, ensure all the files are copied under datarep directory:

[root@prd-stand datarep]# ls -ltr

total 64444

-rw-------. 1 postgres postgres 231 Feb 1 12:54 backup_label

drwx------. 6 postgres postgres 66 Feb 1 12:54 base

drwx------. 3 postgres postgres 131072 Feb 2 03:57 pg_wal

drwx------. 2 postgres postgres 4096 Feb 2 03:57 log

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_commit_ts

drwx------. 4 postgres postgres 84 Feb 2 03:57 pg_logical

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_dynshmem

drwx------. 4 postgres postgres 48 Feb 2 03:57 pg_multixact

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_twophase

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_tblspc

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_subtrans

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_stat_tmp

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_stat

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_snapshots

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_serial

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_replslot

drwx------. 2 postgres postgres 10 Feb 2 03:57 pg_notify

-rw-------. 1 postgres postgres 29693 Feb 2 03:57 postgresql.conf.bak

-rw-------. 1 postgres postgres 425 Feb 2 03:57 postgresql.auto.conf

drwx------. 2 postgres postgres 4096 Feb 2 03:57 pg_xact

-rw-------. 1 postgres postgres 3 Feb 2 03:57 PG_VERSION

-rw-------. 1 postgres postgres 2640 Feb 2 03:57 pg_ident.conf

-rwxrwxrwx. 1 postgres postgres 5499 Feb 2 03:57 pg_hba.conf_original

-rw-------. 1 postgres postgres 343 Feb 2 03:57 nohup.out

-rw-------. 1 postgres postgres 30718 Feb 2 03:57 postgresql.conf_1_28_2025

-rw-------. 1 postgres postgres 5829 Feb 2 03:57 pg_hba.conf_works_exceptpostgresuser

-rw-------. 1 postgres postgres 5824 Feb 2 03:57 pg_hba.conf_gui_cl_gui_works

-rw-------. 1 postgres postgres 30 Feb 2 03:57 current_logfiles

-rw-------. 1 postgres postgres 30721 Feb 2 03:57 postgresql.conf_2_1_2025

-rw-------. 1 postgres postgres 5935 Feb 2 03:57 pg_hba.conf_2_1_2025

-rw-------. 1 postgres postgres 6052 Feb 2 03:57 pg_hba.conf

drwx------. 2 postgres postgres 4096 Feb 2 03:57 global

-rw-------. 1 postgres postgres 65586211 Feb 2 03:57 backup_manifest

-rw-------. 1 postgres postgres 302 Feb 2 10:53 standby.signal

-rw-------. 1 postgres postgres 31003 Feb 2 10:57 postgresql.conf

postgresql.service:

4. Update the postgresql.service file with new data directory for Environment (PGDATA):

[root@prd-stand datarep]# vim /lib/systemd/system/postgresql.service

Environment=PGDATA=/u02/pgsql/16/datarep

postgresql.auto.conf (Read only):

5. Check the auto-created postgresql.auto.conf file for primany conninfo value:

bash-4.4$ cat postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

max_locks_per_transaction = '1024'

primary_conninfo = 'user=rep_user password=Welcome2025 channel_binding=prefer host= xxx.xx.xx.xx port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

bash-4.4$ ls -ltr postgresql.auto.conf

standby.signal:

6. Check the auto-created EMPTY standby.signal file and add primary_conninfo from step 5:

[root@prd-stand datarep]# su postgres

bash-4.4$ cat /u02/pgsql/16/datarep/standby.signal

bash-4.4$

postgresql.config:

7. Update the postgresql.conf:

[root@prd-stand datarep]# su postgres

bash-4.4$ vi /u02/pgsql/16/datarep/postgresql.conf

data_directory = '/u02/pgsql/16/datarep'

hot_standby = on

#shared_preload_libraries = 'timescaledb,pg_cron'

Start the cluster:

8. Start the cluster using new data directory:

[root@prd-stand datarep]# systemctl start postgresql.service

[root@prd-stand datarep]# systemctl status postgresql.service

● postgresql.service - PostgreSQL database server

Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)

Active: active (running) since Sun 2025-02-02 11:19:37 EST; 19s ago

Process: 780094 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)

Main PID: 780097 (postgres)

Tasks: 6 (limit: 1234086)

Memory: 13.0G

CGroup: /system.slice/postgresql.service

├─780097 /usr/bin/postgres -D /u02/pgsql/16/datarep

├─780099 postgres: logger

├─780100 postgres: checkpointer

├─780101 postgres: background writer

├─780102 postgres: startup recovering 0000000100000DC6000000EE

└─781295 postgres: walreceiver streaming DC6/EEEC0000

Check the log file:

[root@prd-stand log]# su postgres

bash-4.4$ pwd

/u02/pgsql/16/datarep/log

bash-4.4$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

bash-4.4$ ls -ltr

total 35588

-rw-------. 1 postgres postgres 6002122 Feb 2 03:57 postgresql-Tue.log

-rw-------. 1 postgres postgres 6251455 Feb 2 03:57 postgresql-Wed.log

-rw-------. 1 postgres postgres 6074026 Feb 2 03:57 postgresql-Thu.log

-rw-------. 1 postgres postgres 5720180 Feb 2 03:57 postgresql-Fri.log

-rw-------. 1 postgres postgres 5694620 Feb 2 03:57 postgresql-Sat.log

-rw-------. 1 postgres postgres 5702075 Feb 2 03:57 postgresql-Mon.log

-rw-------. 1 postgres postgres 938135 Feb 2 15:37 postgresql-Sun.log

bash-4.4$ tail -20f postgresql-Sun.log

2025-02-02 15:17:25.074 EST [780100] LOG: restartpoint complete: wrote 42916 buffers (2.7%); 0 WAL file(s) added, 0 removed, 11 recycled; write=269.716 s, sync=0.033 s, total=269.768 s; sync files=253, longest=0.005 s, average=0.001 s; distance=465555 kB, estimate=492192 kB; lsn=DD0/CC59B128, redo lsn=DD0/C0170158

2025-02-02 15:17:25.074 EST [780100] LOG: recovery restart point at DD0/C0170158

2025-02-02 15:17:25.074 EST [780100] DETAIL: Last completed transaction was at log time 2025-02-02 15:17:24.862653-05.

2025-02-02 15:17:55.105 EST [780100] LOG: restartpoint starting: time

2025-02-02 15:22:29.742 EST [780100] LOG: restartpoint complete: wrote 28778 buffers (1.8%); 0 WAL file(s) added, 36 removed, 0 recycled; write=269.955 s, sync=4.568 s, total=274.638 s; sync files=277, longest=4.184 s, average=0.017 s; distance=216945 kB, estimate=464667 kB; lsn=DD0/E1E315C8, redo lsn=DD0/CD54C7F8

2025-02-02 15:22:29.743 EST [780100] LOG: recovery restart point at DD0/CD54C7F8

2025-02-02 15:22:29.743 EST [780100] DETAIL: Last completed transaction was at log time 2025-02-02 15:22:29.362784-05.

2025-02-02 15:22:55.768 EST [780100] LOG: restartpoint starting: time

2025-02-02 15:27:25.303 EST [780100] LOG: restartpoint complete: wrote 90546 buffers (5.8%); 1 WAL file(s) added, 21 removed, 0 recycled; write=269.309 s, sync=0.009 s, total=269.536 s; sync files=210, longest=0.003 s, average=0.001 s; distance=347360 kB, estimate=452937 kB; lsn=DD0/EBB84098, redo lsn=DD0/E2884AE0

2025-02-02 15:27:25.304 EST [780100] LOG: recovery restart point at DD0/E2884AE0

2025-02-02 15:27:25.304 EST [780100] DETAIL: Last completed transaction was at log time 2025-02-02 15:27:24.979757-05.

2025-02-02 15:27:55.309 EST [780100] LOG: restartpoint starting: time

2025-02-02 15:32:25.149 EST [780100] LOG: restartpoint complete: wrote 34176 buffers (2.2%); 0 WAL file(s) added, 10 removed, 0 recycled; write=269.748 s, sync=0.039 s, total=269.840 s; sync files=222, longest=0.003 s, average=0.001 s; distance=169634 kB, estimate=424606 kB; lsn=DD1/1E80F358, redo lsn=DD0/ECE2D658

2025-02-02 15:32:25.149 EST [780100] LOG: recovery restart point at DD0/ECE2D658

2025-02-02 15:32:25.149 EST [780100] DETAIL: Last completed transaction was at log time 2025-02-02 15:32:24.124176-05.

2025-02-02 15:32:55.159 EST [780100] LOG: restartpoint starting: time

2025-02-02 15:37:25.119 EST [780100] LOG: restartpoint complete: wrote 37302 buffers (2.4%); 0 WAL file(s) added, 0 removed, 32 recycled; write=269.866 s, sync=0.052 s, total=269.960 s; sync files=256, longest=0.011 s, average=0.001 s; distance=838543 kB, estimate=838543 kB; lsn=DD1/2D5D28D8, redo lsn=DD1/201115E8

2025-02-02 15:37:25.119 EST [780100] LOG: recovery restart point at DD1/201115E8

2025-02-02 15:37:25.119 EST [780100] DETAIL: Last completed transaction was at log time 2025-02-02 15:37:23.864674-05.

2025-02-02 15:37:55.150 EST [780100] LOG: restartpoint starting: time

Validate the replication & check the replication lag:

@Primary:

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

(1 row)

postgres=# SELECT * FROM pg_stat_replication;

(1 row)

@Standby:

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

(1 row)

postgres=# SELECT * FROM pg_stat_wal_receiver;

@Find Replication Lag from Standby:

postgres=# SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS lag_bytes;

Thanks!

Govind.

DBA Vision

Monday, February 3, 2025

PostgreSQL 16: Streaming Replication

Assumptions:

Primary database:

CREATE REPLICATION ROLE & Reload configuration file:

Standby database:

Create data directory and grant access to postgres user:

run pg_basebackup:

postgresql.auto.conf (Read only):

Start the cluster:

Validate the replication & check the replication lag:

Pages

Search This Blog