15 – AWS CLOUD DBA

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

ARCHITECTURE

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

SETUP

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

REFERENCES:

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

Tag: 15

Enable pg_stat_statements extension on Postgres 14

PostgreSQL 14 Streaming Replication on Oracle Cloud Infrastructure (OCI) VM

Introduction

Step 1: Installing PostgreSQL 14 on Master and Slave Nodes

Step 2: Enabling Postgres User and Streaming Replication

Step 3: Configuring `pg_hba.conf` and Firewall Settings

#Whitelist Ports on Instance

Step 4: Configuring Master Node for Streaming Replication

Step 5: Configuring Slave Nodes for Streaming Replication

Step 6: Checking Replication Status from Slave Nodes

Step 7: Checking Replication Status from Master Node

Step 8: Additional Notes and References

Introduction

Step 1: Installing PostgreSQL 14 on Master and Slave Nodes

Step 2: Enabling Postgres User and Streaming Replication

Step 3: Configuring pg_hba.conf and Firewall Settings

#Whitelist Ports on Instance

Step 4: Configuring Master Node for Streaming Replication

Step 5: Configuring Slave Nodes for Streaming Replication

Step 6: Checking Replication Status from Slave Nodes

Step 7: Checking Replication Status from Master Node

Step 8: Additional Notes and References

Step 3: Configuring `pg_hba.conf` and Firewall Settings