Deploy Postgresql 16 Primary Standby

1
2
3
4
5
6
7
8

sudo apt install -y curl ca-certificates rsync
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt -y install postgresql-16
firewall-cmd --permanent --add-service=postgresql && firewall-cmd --reload
systemctl status postgresql

1
2
3
4

listen_addresses = '*'
wal_level = replica
max_replication_slots = 10
max_wal_senders = 10

1
2

# host    replication     replicator      192.168.100.102/32    scram-sha-256
host    replication  all   samenet  scram-sha-256

1
2
3
4

systemctl restart postgresql
sudo -u postgres psql
CREATE ROLE repuser WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'somestrongpassword';
SELECT * FROM pg_create_physical_replication_slot('replica_1_slot');

1
2
3
4
5

systemctl stop postgresql
su - postgres
rm -rf /var/lib/postgresql/16/main/*
pg_basebackup -h 192.168.100.101 -D /var/lib/postgresql/16/main -U repuser --password --progress --verbose
touch /var/lib/postgresql/16/main/standby.signal

1
2
3
4
5
6

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
primary_conninfo = 'host=192.168.100.101 port=5432 user=repuser password=somestrongpassword application_name=r1'
primary_slot_name = 'replica_1_slot'

1
2

systemctl restart postgresql
tail -f /var/log/postgresql/postgresql-16-main.log

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

root@develop-pg-node-02:~# tail -f /var/log/postgresql/postgresql-16-main.log
2024-10-17 00:13:17.047 CST [3850] LOG:  listening on IPv6 address "::", port 5432
2024-10-17 00:13:17.056 CST [3850] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-10-17 00:13:17.071 CST [3853] LOG:  database system was interrupted; last known up at 2024-10-17 00:08:43 CST
2024-10-17 00:13:17.149 CST [3853] LOG:  entering standby mode
2024-10-17 00:13:17.149 CST [3853] LOG:  starting backup recovery with redo LSN 0/2000110, checkpoint LSN 0/2000148, on timeline ID 1
2024-10-17 00:13:17.162 CST [3853] LOG:  redo starts at 0/2000110
2024-10-17 00:13:17.167 CST [3853] LOG:  completed backup recovery with redo LSN 0/2000110 and end LSN 0/20001E8
2024-10-17 00:13:17.167 CST [3853] LOG:  consistent recovery state reached at 0/20001E8
2024-10-17 00:13:17.167 CST [3850] LOG:  database system is ready to accept read-only connections
2024-10-17 00:13:17.196 CST [3854] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

1
2
3

-- sudo -u postgres psql
\x
SELECT * FROM pg_stat_wal_receiver;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 3840
usesysid         | 16392
usename          | repuser
application_name | r1
client_addr      | 192.168.100.102
client_hostname  | 
client_port      | 59266
backend_start    | 2024-10-17 00:13:17.177344+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/3000148
write_lsn        | 0/3000148
flush_lsn        | 0/3000148
replay_lsn       | 0/3000148
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2024-10-17 00:15:02.668249+08

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 3854
status                | streaming
receive_start_lsn     | 0/3000000
receive_start_tli     | 1
written_lsn           | 0/3000148
flushed_lsn           | 0/3000148
received_tli          | 1
last_msg_send_time    | 2024-10-17 00:14:22.659823+08
last_msg_receipt_time | 2024-10-17 00:14:22.659285+08
latest_end_lsn        | 0/3000148
latest_end_time       | 2024-10-17 00:13:52.647283+08
slot_name             | replica_1_slot
sender_host           | 192.168.100.101
sender_port           | 5432
conninfo              | user=repuser password=******** channel_binding=prefer dbname=replication host=192.168.100.101 port=5432 application_name=r1 fallback_application_name=16/main sslmode=prefer sslnegotiation=postgres 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

1
2
3
4
5
6

CREATE DATABASE testdb;
\c testdb
CREATE TABLE TEST1 (COL1 int);
SELECT * FROM TEST1;
INSERT INTO TEST1(COL1) VALUES(1);
SELECT * FROM TEST1;