Introduction
PostgreSQL 14 is a powerful and feature-rich open-source relational database management system.
In this guide, we’ll walk through the process of installing and configuring PostgreSQL 14 on Oracle Cloud Infrastructure (OCI) with Oracle Linux 8. The setup includes one master node and two slave nodes, forming a streaming replication setup.
Note : The word slave and replica is used interchangeably in this article when referring to anything which is not a master node
OS– Oracle Linux 8
PostgreSQL Version – 14.10
1 Master Node – IP- 10.180.2.102
2 Slave Nodes – IPs- 10.180.2.152, 10.180.2.58
3 Node PostgreSQL 14 Cluster on OCI
You can create a DR architecture using streaming replication. Put 1 replica in the same region and 2 additional replicas in another OCI region.The VCN’s in both OCI regions have to be remotely peered using a DRG and all routes should permit the traffic over the different subnets and allow communication over port 5432. You can refer to this articleo n how to configure VCN remote peering on OCI: https://docs.oracle.com/en-us/iaas/Content/Network/Tasks/scenario_e.htm
4-Node Cross-Region PostgreSQL 14 Cluster on OCI
Step 1: Installing PostgreSQL 14 on Master and Slave Nodes
Start by updating the system and installing necessary dependencies on both the master and slave nodes:
sudo dnf update -y
sudo dnf module list postgresql
sudo yum -y install gnupg2 wget vim tar zlib openssl
sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum -qy module disable postgresql
sudo yum install postgresql14-server -y
sudo yum install postgresql14-contrib -y
sudo systemctl enable postgresql-14
sudo postgresql-14-setup initdb
sudo systemctl start postgresql-14
sudo systemctl status postgresql-14
Step 2: Enabling Postgres User and Streaming Replication
Enable the Postgres user and configure streaming replication on both the master and slave nodes:
sudo -iu postgres
psql -c "ALTER USER postgres WITH PASSWORD 'RAbbithole1234#_';"
tree -L 1 /var/lib/pgsql/14/data
psql -U postgres -c 'SHOW config_file'
config_file
----------------------------------------
/var/lib/pgsql/14/data/postgresql.conf
(1 row)
Step 3: Configuring pg_hba.conf and Firewall Settings
Update the pg_hba.conf file on both the master and slave nodes to allow connections and adjust firewall settings:
sudo -iu postgres
vim /var/lib/pgsql/14/data/pg_hba.conf
# If ident is available in file then replace 'ident' with 'md5' or 'scram-sha-256'
# Change this line to allow all hosts 0.0.0.0/0
# IPv4 local connections:
host all all 0.0.0.0/0 scram-sha-256
exit
sudo systemctl restart postgresql-14
#Whitelist Ports on Instance
sudo firewall-cmd --list-ports
sudo firewall-cmd --zone=public --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports
Step 4: Configuring Master Node for Streaming Replication
On the master node (10.180.2.102), configure streaming replication:
sudo -iu postgres
mkdir -p /var/lib/pgsql/14/data/archive
vim /var/lib/pgsql/14/data/postgresql.conf
## Uncomment and set below parameters
listen_addresses = '*'
archive_mode = on # enables archiving; off, on, or always
archive_command = 'cp %p /var/lib/pgsql/14/data/archive/%f'
max_wal_senders = 10 # max number of walsender processes
max_replication_slots = 10 # max number of replication slots
wal_keep_size = 50000 # Size of WAL in megabytes; 0 disables
wal_level = replica # minimal, replica, or logical
wal_log_hints = on # also do full page writes of non-critical updates
## Only set below if you want to create synchronous replication##
synchronous_commit = remote_apply
synchronous_standby_names = '*'
exit
sudo systemctl restart postgresql-14
netstat -an | grep 5432
Update pg_hba.conf on the master node:
sudo -iu postgres
vim /var/lib/pgsql/14/data/pg_hba.conf
#Add below entry to end of file
host replication all 10.180.2.152/32 scram-sha-256
host replication all 10.180.2.58/32 scram-sha-256
exit
sudo systemctl restart postgresql-14
Step 5: Configuring Slave Nodes for Streaming Replication
On the slave nodes (10.180.2.152 and 10.180.2.58), configure streaming replication:
sudo -iu postgres
mkdir -p /var/lib/pgsql/14/data/backup
vim /var/lib/pgsql/14/data/pg_hba.conf
exit
sudo systemctl restart postgresql-14
sudo chmod 0700 /var/lib/pgsql/14/data/backup
sudo -iu postgres
#Backup and Clone Database from Slave Node using IP of Master Node
pg_basebackup -D /var/lib/pgsql/14/data/backup -X fetch -p 5432 -U postgres -h 10.180.2.102 -R
cd /var/lib/pgsql/14/data/backup
cat postgresql.auto.conf
#Stop the Instance and Restart using Data in New location
/usr/pgsql-14/bin/pg_ctl stop
/usr/pgsql-14/bin/pg_ctl start -D /var/lib/pgsql/14/data/backup
waiting for server to start....2023-11-27 03:36:48.205 GMT [169621] LOG: redirecting log output to logging collector process
2023-11-27 03:36:48.205 GMT [169621] HINT: Future log output will appear in directory "log".
done
server started
Step 6: Checking Replication Status from Slave Nodes
Check the status of streaming replication from slave nodes using psql:
psql -h localhost -p 5432 -U postgres -d postgres
postgres# select pg_is_wal_replay_paused();
pg_is_wal_replay_paused
-------------------------
f
(1 row)
Note - f means , recovery is running fine. t means it is stopped.
postgres# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 414090
status | streaming
receive_start_lsn | 0/A000000
receive_start_tli | 1
written_lsn | 0/A002240
flushed_lsn | 0/A002240
received_tli | 1
last_msg_send_time | 2023-12-04 11:40:51.853918+00
last_msg_receipt_time | 2023-12-04 11:40:51.853988+00
latest_end_lsn | 0/A002240
latest_end_time | 2023-11-30 08:16:43.217865+00
slot_name |
sender_host | 10.180.2.102
sender_port | 5432
conninfo | user=postgres password=******** channel_binding=prefer dbname=replication host=10.180.2.102 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
Step 7: Checking Replication Status from Master Node
On the master node, check the status of replication:
psql -h localhost -p 5432 -U postgres -d postgres:
postgres# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 382513
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 10.180.2.152
client_hostname |
client_port | 47312
backend_start | 2023-11-30 08:11:42.536364+00
backend_xmin |
state | streaming
sent_lsn | 0/A002240
write_lsn | 0/A002240
flush_lsn | 0/A002240
replay_lsn | 0/A002240
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-12-04 11:43:12.033364+00
-[ RECORD 2 ]----+------------------------------
pid | 382514
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 10.180.2.58
client_hostname |
client_port | 35294
backend_start | 2023-11-30 08:11:42.542539+00
backend_xmin |
state | streaming
sent_lsn | 0/A002240
write_lsn | 0/A002240
flush_lsn | 0/A002240
replay_lsn | 0/A002240
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-12-04 11:43:10.113253+00
Step 8: Additional Notes and References
To restart slave nodes, use the following commands:
/usr/pgsql-14/bin/pg_ctl stop
sudo rm -rf /var/lib/pgsql/14/data/backup/postmaster.pid
/usr/pgsql-14/bin/pg_ctl start -D /var/lib/pgsql/14/data/backup
- DBA Class
- Narasimman Tech
- PostgreSQL Continuous Archiving Documentation
- Stack Overflow
- Girders
- Kinsta
Follow this comprehensive guide to set up PostgreSQL 14 streaming replication on Oracle Cloud Infrastructure with Oracle Linux 8. Ensure high availability and robust backup capabilities for your PostgreSQL database
AWS CLOUD DBA 