How to setup postgres streaming replication in AWS EC2 instance
In this article we are going to explain, how to setup postgres streaming replication in aws with free tier. Once we configure are streaming replication, we will use EFM for managing and monitoring streaming replication.
So for that we will create 3 EC2 instances with RHEL 8 Operating System.
- Primary node
- Standby node
- Witness node
Create 3 EC2 instances
Create a free aws account and go to EC2 page.
Create primary node:
Select Redhat 8
Click on create new key pair
Make sure to keep the generated .pem file safely.
just click on launch instance. After that proceed with another ec2 instance creation.
Create standby instance:
Create the witness node:
Follow the similar steps used for creating primary and standby.
Below are the details of the instances created:
Each instance has public ip and private ip. When you want to connect to any server from outside using putty, then you need to use public ip. But within postgres setup , we will use only private ip.
| EC2 INSTANCE_NAME | PUBLIC_IP | PRIVATE_IP |
| PRIMARY_PG | 54.167.136.127 | 172.31.86.197 |
| STANDBY_PG | 54.89.226.98 | 172.31.89.172 |
| WITNESS_PG | 3.87.32.202 | 172.31.93.81 |
Open required ports :
By default only 22,80,443 ports are enabled. So other required ports need to be opened on all instances.
- 5444 ( EDB POSTGRES server port)
- 7800 ( Required for EFM)
Each EC2 instance have their own security group, where inbound and outbound rules are defined.
Click on security groups mapped to our instances and then edit inbound rules . and add custom ports.
Do the same for all the 3 security groups.
Genereate .ppk file from the keypairs:(For windows only)
NOTE – For macos or linux, no need to generate .ppk file. We can use the keypair files directly and login to the servers. Even on windows also if you have gitbash, then you can use the same command.
syntax – > ssh -i PRIMAY-PAIR.pem ec2-user@<<public_ip>>
In windows if you want to login to these server from putty, then we need to generate private .ppk files from the key pairs.
Use puttygen to generate .ppk file from these keypair .
Use the same steps for other 2 files(STANDBY-PAIR and WITNESS-PAIR).
Now connect to server using putty(public_ip)
ec2-user is the default user for all AWS EC2 instance. It don’t need any password.
Downloading EDB software packages:
For downloading edb packages , You need to create an free account with EDB.
MY ACCOUNT – > ACCOUNT SETTINGS
REPO ACCESS
In the left drop down ,select platform and select the components you wish to install
Below is the link for setting up repo and downloading packages.
Install EDB binaries on Primary node
[ec2-user@ip-172-31-86-197 ~]$ sudo su - root
Last login: Thu Sep 8 06:52:39 UTC 2022 on pts/2
[root@ip-172-31-86-197 ~]# curl -1sLf 'https://downloads.enterprisedb.com/VCzzWWw6MuBBy5u8AdSk4yN/enterprise/setup.rpm.sh' | sudo -E bash
Executing the setup script for the 'enterprisedb/enterprise' repository ...
OK: Checking for required executable 'curl' ...
OK: Checking for required executable 'rpm' ...
OK: Detecting your OS distribution and release using system methods ...
^^^^: ... Detected/provided for your OS/distribution, version and architecture:
>>>>:
>>>>: ... distro=rhel version=8.6 codename=Ootpa arch=x86_64
>>>>:
OK: Importing 'enterprisedb/enterprise' repository GPG keys into rpm ...
OK: Checking for available package manager (DNF/Microdnf/YUM/Zypper) ...
^^^^: ... Detected package manager as 'dnf'
OK: Checking for dnf dependency 'yum-utils' ...
NOPE: Checking for dnf dependency 'dnf-plugin-config-manager' ...
OK: Attempting to install 'dnf-plugin-config-manager' ...
OK: Checking if upstream install config is OK ...
OK: Fetching 'enterprisedb/enterprise' repository configuration ...
OK: Installing 'enterprisedb/enterprise' repository via dnf ...
RUN: Updating the dnf cache to fetch the new repository metadata ...Importing GPG key 0x9F1EF813:
Userid : "Cloudsmith Package (enterprisedb/enterprise) <support@cloudsmith.io>"
Fingerprint: 31A4 CF09 0B3A E265 F131 58DE E71E B082 9F1E F813
From : https://downloads.enterprisedb.com/VCzzWWTMuBBy5u8AdSk4yN/enterprise/gpg.E71EB0829F1EF813.key
Importing GPG key 0x7E30651C:
Userid : "EnterpriseDB Inc. (EnterpriseDB Yum Repositories) <packages@enterprisedb.com>"
Fingerprint: CA40 9F7C 635F 2AE5 6C9E 8B34 E5ED E919 7E30 651C
From : https://downloads.enterprisedb.com/VCzzWWw6MHcuBBy5u8AdSk4yN/enterprise/upstream.gpg.E5EDE9197E30651C.key
Importing GPG key 0x7E30651C:
Userid : "EnterpriseDB Inc. (EnterpriseDB Yum Repositories) <packages@enterprisedb.com>"
Fingerprint: CA40 9F7C 635F 2AE5 6C9E 8B34 E5ED E919 7E30 651C
From : https://downloads.enterprisedb.com/VCzzWWw6MHcu8AdSk4yN/enterprise/upstream.gpg.E5EDE9197E30651C.key
.
.
.
.
.
.
OK: Updating the dnf cache to fetch the new repository metadata ...
OK: The repository has been installed successfully - You're ready to rock!
[root@ip-172-31-86-197 ~]#
[root@ip-172-31-86-197 ~]# sudo dnf -y install edb-pem edb-efm44 edb-as14-server edb-xdb barman edb-as14-edbplus
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
Last metadata expiration check: 0:03:34 ago on Thu 08 Sep 2022 06:53:45 AM UTC.
Dependencies resolved.
=============================================================================================================================================================
Package Architecture Version Repository Size
=============================================================================================================================================================
Installing:
barman noarch 3.0.1-1.el8 enterprisedb-enterprise-noarch 54 k
edb-as14-edbplus x86_64 40.0.1-1.rhel8 enterprisedb-enterprise 1.2 M
edb-as14-server x86_64 14.5.0-1.rhel8 enterprisedb-enterprise 9.8 k
edb-efm44 x86_64 4.4-1.rhel8 enterprisedb-enterprise 5.8 M
edb-pem x86_64 8.5.0-1.rhel8 enterprisedb-enterprise 12 k
edb-xdb x86_64 7.2.1-1.rhel8 enterprisedb-enterprise 14 k
Installing dependencies:
alsa-lib x86_64 1.2.6.1-3.el8 rhel-8-appstream-rhui-rpms 491 k
apr x86_64 1.6.3-12.el8 rhel-8-appstream-rhui-rpms 130 k
apr-util x86_64 1.6.1-6.el8 rhel-8-appstream-rhui-rpms 105 k
perl-Data-Dumper-2.167-399.el8.x86_64 perl-Digest-1.17-395.el8.noarch
perl-Digest-MD5-2.55-396.el8.x86_64 perl-Encode-4:2.97-3.el8.x86_64
perl-Errno-1.28-421.el8.x86_64 perl-Exporter-5.72-396.el8.noarch
perl-File-Path-2.15-2.el8.noarch perl-File-Temp-0.230.600-1.el8.noarch
perl-Getopt-Long-1:2.50-4.el8.noarch perl-HTTP-Tiny-0.074-1.el8.noarch
perl-IO-1.38-421.el8.x86_64 perl-IO-Socket-IP-0.39-5.el8.noarch
perl-IO-Socket-SSL-2.066-4.module+el8.3.0+6446+594cad75.noarch perl-MIME-Base64-3.15-396.el8.x86_64
perl-Mozilla-CA-20160104-7.module+el8.3.0+6498+9eecfe51.noarch perl-Net-SSLeay-1.88-2.module+el8.6.0+13392+f0897f98.x86_64
perl-PathTools-3.74-1.el8.x86_64 perl-Pod-Escapes-1:1.07-395.el8.noarch
perl-Pod-Perldoc-3.28-396.el8.noarch perl-Pod-Simple-1:3.35-395.el8.noarch
perl-Pod-Usage-4:1.69-395.el8.noarch perl-Scalar-List-Utils-3:1.49-2.el8.x86_64
perl-Socket-4:2.027-3.el8.x86_64 perl-Storable-1:3.11-3.el8.x86_64
perl-Term-ANSIColor-4.06-396.el8.noarch perl-Term-Cap-1.17-395.el8.noarch
perl-Text-ParseWords-3.30-395.el8.noarch perl-Text-Tabs+Wrap-2013.0523-395.el8.noarch
perl-Time-Local-1:1.280-1.el8.noarch perl-URI-1.73-3.el8.noarch
perl-Unicode-Normalize-1.25-396.el8.x86_64 perl-constant-1.33-396.el8.noarch
perl-interpreter-4:5.26.3-421.el8.x86_64 perl-libnet-3.11-3.el8.noarch
perl-libs-4:5.26.3-421.el8.x86_64 perl-macros-4:5.26.3-421.el8.x86_64
perl-parent-1:0.237-1.el8.noarch perl-podlators-4.11-1.el8.noarch
perl-threads-1:2.21-2.el8.x86_64 perl-threads-shared-1.58-2.el8.x86_64
pixman-0.38.4-2.el8.x86_64 python3-argcomplete-1.9.3-6.el8.noarch
python3-barman-3.0.1-1.el8.noarch python3-mod_wsgi-4.6.4-4.el8.x86_64
python3-pip-9.0.3-22.el8.noarch python3-psycopg2-2.8.6-1.rhel8.x86_64
python3-setuptools-39.2.0-6.el8.noarch python36-3.6.8-38.module+el8.5.0+12207+5c5719bc.x86_64
redhat-logos-httpd-84.5-1.el8.noarch snmp++-3.4.2-1.rhel8.x86_64
sscg-2.3.3-14.el8.x86_64 tcl-1:8.6.8-2.el8.x86_64
ttmkfdir-3.0.9-54.el8.x86_64 tzdata-java-2022c-1.el8.noarch
xorg-x11-font-utils-1:7.5-41.el8.x86_64 xorg-x11-fonts-Type1-7.5-19.el8.noarch
Complete!
Initialise postgres cluster on primary:
[root@ip-172-31-86-197 ~]# id enterprisedb
uid=994(enterprisedb) gid=991(enterprisedb) groups=991(enterprisedb)
[root@ip-172-31-86-197 ~]# PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as14/bin/edb-as-14-setup initdb
Initializing database ... OK
[root@ip-172-31-86-197 ~]#
[root@ip-172-31-86-197 ~]# systemctl start edb-as-14
[root@ip-172-31-86-197 ~]# systemctl status edb-as-14
● edb-as-14.service - EDB Postgres Advanced Server 14
Loaded: loaded (/usr/lib/systemd/system/edb-as-14.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2022-09-08 07:01:55 UTC; 4s ago
Process: 19550 ExecStartPre=/usr/edb/as14/bin/edb-as-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 19555 (edb-postmaster)
Tasks: 9 (limit: 4700)
Memory: 36.4M
CGroup: /system.slice/edb-as-14.service
├─19555 /usr/edb/as14/bin/edb-postmaster -D /var/lib/edb/as14/data
├─19557 postgres: logger
├─19559 postgres: checkpointer
├─19560 postgres: background writer
├─19561 postgres: walwriter
├─19562 postgres: autovacuum launcher
├─19563 postgres: stats collector
├─19564 postgres: dbms_aq launcher
└─19565 postgres: logical replication launcher
Sep 08 07:01:54 ip-172-31-86-197.ec2.internal systemd[1]: Starting EDB Postgres Advanced Server 14...
Sep 08 07:01:55 ip-172-31-86-197.ec2.internal edb-postmaster[19555]: 2022-09-08 07:01:55 UTC LOG: redirecting log output to logging collector process
Sep 08 07:01:55 ip-172-31-86-197.ec2.internal edb-postmaster[19555]: 2022-09-08 07:01:55 UTC HINT: Future log output will appear in directory "log".
Sep 08 07:01:55 ip-172-31-86-197.ec2.internal systemd[1]: Started EDB Postgres Advanced Server 14.
[root@ip-172-31-86-197 lib]# su - enterprisedb
Last login: Thu Sep 8 07:04:08 UTC 2022 on pts/2
[enterprisedb@ip-172-31-86-197 ~]$ psql -d postgres
psql (14.5.0, server 14.5.0)
Type "help" for help.
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
(4 rows)
Do some data changes on primary db
postgres=#
postgres=# create database SRCDB;
CREATE DATABASE
postgres=# \c srcdb
psql (14.5.0, server 14.5.0)
You are now connected to database "srcdb" as user "enterprisedb".
srcdb=#
srcdb=# create table TEST1 as select * from pg_settings;
SELECT 408
srcdb=# create table TEST2 as select * from pg_settings;
SELECT 408
srcdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------------
public | test1 | table | enterprisedb
public | test2 | table | enterprisedb
(2 rows)
srcdb=# \db
List of tablespaces
Name | Owner | Location
------------+--------------+----------
pg_default | enterprisedb |
pg_global | enterprisedb |
(2 rows)
srcdb=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
srcdb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
(5 rows)
set enterprisedb password on primary node:
[root@ip-172-31-86-197 ~]# passwd enterprisedb
Changing password for user enterprisedb.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.Add clientaliveinteral parameter in sshd_config file
EC2 instances has idle_timeout set to 60 seconds. So this parameter will help in keep the session active.
[root@ip-172-31-86-197 ~]# echo 'ClientAliveInterval 60' | sudo tee --append /etc/ssh/sshd_config
ClientAliveInterval 60
Enable PasswordAuthentication to yes in sshd_config file:
If this parameter is enabled we will be able to login to EC2 instance without the use of .ppk files.
We can login to the instance with password directly.
[root@ip-172-31-86-197 ssh]# cat sshd_config | grep PasswordAuthentication
PasswordAuthentication yes
#PasswordAuthentication no
# PasswordAuthentication. Depending on your PAM configuration,
# PAM authentication, then enable this but set PasswordAuthentication
[root@ip-172-31-86-197 ssh]#
Restart the sshd service:
[root@ip-172-31-86-197 ~]# systemctl restart sshd
[root@ip-172-31-86-197 ~]#
Provide sudo privilege to enterprisedb:
cat /etc/sudoers
## Same thing without a password
# %wheel ALL=(ALL) NOPASSWD: ALL
enterprisedb ALL=(ALL) NOPASSWD: ALL
Update postgres.conf file:
[enterprisedb@ip-172-31-86-197 ~]#mkdir -p /var/lib/edb/as14/archive
Add parameters required for streaming replication
cat /var/lib/edb/as14/data/postgres.conf
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /var/lib/edb/as14/archive/%f'
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
wal_level = replica # minimal, replica, or logical
wal_log_hints = on # also do full page writes of non-critical updates
Update pg_hba.conf file:
Add the standby server and witness server ips.
cat /var/lib/edb/as14/data/pg_hba.conf
# configuration parameter, or via the -i or -h command line switches.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 172.31.86.197/32 md5
host all all 172.31.89.172/32 md5
host all all 172.31.93.81/32 md5
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
host replication all 172.31.89.172/32 md5
host replication all 172.31.86.197/32 md5
Restart edb services:
[root@ip-172-31-86-197 ~]# systemctl restart edb-as-14
[root@ip-172-31-86-197 ~]# systemctl status edb-as-14
● edb-as-14.service - EDB Postgres Advanced Server 14
Loaded: loaded (/usr/lib/systemd/system/edb-as-14.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2022-09-08 08:33:10 UTC; 2s ago
Process: 23015 ExecStartPre=/usr/edb/as14/bin/edb-as-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 23021 (edb-postmaster)
Tasks: 10 (limit: 4700)
Memory: 28.7M
CGroup: /system.slice/edb-as-14.service
├─23021 /usr/edb/as14/bin/edb-postmaster -D /var/lib/edb/as14/data
├─23024 postgres: logger
├─23026 postgres: checkpointer
├─23027 postgres: background writer
├─23028 postgres: walwriter
├─23029 postgres: autovacuum launcher
├─23030 postgres: archiver
├─23031 postgres: stats collector
├─23032 postgres: dbms_aq launcher
└─23033 postgres: logical replication launcher
Sep 08 08:33:10 ip-172-31-86-197.ec2.internal systemd[1]: edb-as-14.service: Succeeded.
Sep 08 08:33:10 ip-172-31-86-197.ec2.internal systemd[1]: Stopped EDB Postgres Advanced Server 14.
Sep 08 08:33:10 ip-172-31-86-197.ec2.internal systemd[1]: Starting EDB Postgres Advanced Server 14...
Sep 08 08:33:10 ip-172-31-86-197.ec2.internal edb-postmaster[23021]: 2022-09-08 08:33:10 UTC LOG: redirecting log output to logging collector process
Sep 08 08:33:10 ip-172-31-86-197.ec2.internal edb-postmaster[23021]: 2022-09-08 08:33:10 UTC HINT: Future log output will appear in directory "log".
Sep 08 08:33:10 ip-172-31-86-197.ec2.internal systemd[1]: Started EDB Postgres Advanced Server 14.
Create user for streaming replication:
postgres=# create user stream_replica with password 'test123';
CREATE ROLE
postgres=# alter user stream_replica with superuser;
ALTER ROLE
PREPARE STANDBY NODE:
Install edb postgres binaries
[root@ip-172-31-89-172 ~]# curl -1sLf 'https://downloads.enterprisedb.com/VCzzWWw6MHcAP8Sk4yN/enterprise/setup.rpm.sh' | sudo -E bash
[root@ip-172-31-89-172 ~]# sudo dnf -y install edb-pem edb-efm44 edb-as14-server edb-xdb barman edb-as14-edbplus
set enterprisedb password on primary node:
[root@ip-172-31-89-172 ~]# passwd enterprisedb
Changing password for user enterprisedb.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.Add clientaliveinteral parameter in sshd_config file
EC2 instances has idle_timeout set to 60 seconds. So this parameter will help in keep the session active.
[root@ip-172-31-89-172 ~]# echo 'ClientAliveInterval 60' | sudo tee --append /etc/ssh/sshd_config
ClientAliveInterval 60
Enable PasswordAuthentication to yes in sshd_config file:
If this parameter is enabled we will be able to login to EC2 instance without the use of .ppk files.
We can login to the instance with password directly.
[root@ip-172-31-89-172 ssh]# cat sshd_config | grep PasswordAuthentication
PasswordAuthentication yes
#PasswordAuthentication no
# PasswordAuthentication. Depending on your PAM configuration,
# PAM authentication, then enable this but set PasswordAuthentication
[root@ip-172-31-89-172 ssh]#
Restart the sshd service:
[root@ip-172-31-89-172 ~]# systemctl restart sshd
[root@ip-172-31-89-172 ~]#
Provide sudo to root privilege to enterprisedb:
cat /etc/sudoers
## Same thing without a password
# %wheel ALL=(ALL) NOPASSWD: ALL
enterprisedb ALL=(ALL) NOPASSWD: ALL
Create archive directory on standby:
mkdir -p /var/lib/edb/as14/archive
Sync standby from primary with streaming:
Run below pg_basebackup command on standby
[enterprisedb@ip-172-31-89-172 bin]$ /usr/edb/as14/bin/pg_basebackup -D /var/lib/edb/as14/data -X fetch -p 5444 -U stream_replica -h 172.31.86.197 -R
Password:
Check the content of postgres.auto.conf file and the presence of standby.signal file
[enterprisedb@ip-172-31-89-172 data]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=stream_replica password=test123 channel_binding=prefer host=54.167.136.127 port=5444 sslmode=prefer sslcompression=0 sslsni=1 ssl_m
in_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
[enterprisedb@ip-172-31-89-172 data]$ ls -ltr standby.signal
-rw-------. 1 enterprisedb enterprisedb 0 Sep 8 08:36 standby.signal
Start edb services on standby
[root@ip-172-31-89-172 ~]# systemctl start edb-as-14
[root@ip-172-31-89-172 ~]# systemctl status edb-as-14
● edb-as-14.service - EDB Postgres Advanced Server 14
Loaded: loaded (/usr/lib/systemd/system/edb-as-14.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2022-09-08 08:38:29 UTC; 4s ago
Process: 21716 ExecStartPre=/usr/edb/as14/bin/edb-as-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 21722 (edb-postmaster)
Tasks: 7 (limit: 4700)
Memory: 52.4M
CGroup: /system.slice/edb-as-14.service
├─21722 /usr/edb/as14/bin/edb-postmaster -D /var/lib/edb/as14/data
├─21723 postgres: logger
├─21724 postgres: startup recovering 000000010000000000000004
├─21725 postgres: checkpointer
├─21726 postgres: background writer
├─21727 postgres: stats collector
└─21728 postgres: walreceiver streaming 0/4000060
Sep 08 08:38:29 ip-172-31-89-172.ec2.internal systemd[1]: Starting EDB Postgres Advanced Server 14...
Sep 08 08:38:29 ip-172-31-89-172.ec2.internal edb-postmaster[21722]: 2022-09-08 08:38:29 UTC LOG: redirecting log output to logging collector process
Sep 08 08:38:29 ip-172-31-89-172.ec2.internal edb-postmaster[21722]: 2022-09-08 08:38:29 UTC HINT: Future log output will appear in directory "log".
Sep 08 08:38:29 ip-172-31-89-172.ec2.internal systemd[1]: Started EDB Postgres Advanced Server 14.
[root@ip-172-31-89-172 ~]# su - enterprisedb
Last login: Thu Sep 8 08:31:14 UTC 2022 on pts/2
[enterprisedb@ip-172-31-89-172 ~]$ psql -d postgres
psql (14.5.0, server 14.5.0)
Type "help" for help.
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
srcdb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | |
template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +
| | | | | | enterprisedb=CTc/enterprisedb
(5 rows)
Do changes on primary and check whether reflecting on standby:
--- PRIMARY DB
postgres=# \c srcdb
psql (14.5.0, server 14.5.0)
You are now connected to database "srcdb" as user "enterprisedb".
srcdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------------
public | test1 | table | enterprisedb
public | test2 | table | enterprisedb
(2 rows)
srcdb=# select count(*) from test1;
count
-------
408
(1 row)
srcdb=# insert into test1 select * from test1;
INSERT 0 408
srcdb=# select count(*) from test1;
count
-------
816
(1 row)
----on standby;
srcdb=# select count(*) from test1;
count
-------
816
(1 row)
Check replication stats on both primary and standby
-- On primary
srcdb=# \x
Expanded display is on.
srcdb=# select * from pg_stat_replication;
-[ RECORD 1 ]----+---------------------------------
pid | 23293
usesysid | 16401
usename | stream_replica
application_name | walreceiver
client_addr | 54.89.226.98
client_hostname |
client_port | 49262
backend_start | 08-SEP-22 08:38:29.288804 +00:00
backend_xmin |
state | streaming
sent_lsn | 0/4035EE8
write_lsn | 0/4035EE8
flush_lsn | 0/4035EE8
replay_lsn | 0/4035EE8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 08-SEP-22 08:41:11.57209 +00:00
-- On standby db
Expanded display is on.
srcdb=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 21728
status | streaming
receive_start_lsn | 0/4000000
receive_start_tli | 1
written_lsn | 0/4035FD0
flushed_lsn | 0/4035FD0
received_tli | 1
last_msg_send_time | 08-SEP-22 08:41:35.752662 +00:00
last_msg_receipt_time | 08-SEP-22 08:41:35.753026 +00:00
latest_end_lsn | 0/4035FD0
latest_end_time | 08-SEP-22 08:41:35.752662 +00:00
slot_name |
sender_host | 54.167.136.127
sender_port | 5444
conninfo | user=stream_replica password=******** channel_binding=prefer dbname=replication host=54.167.136.127 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
srcdb=#
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------------------+------------------------------------------------------------+-----------
aq_administrator_role | No inheritance, Cannot login +| {}
| Profile default |
enterprisedb | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
| Profile default |
stream_replica | Superuser +| {}
| Profile default |
With this our streaming replication setup completed.
EFM CONFIGURATION
EFM changes on PRIMARY:
postgres=# alter user enterprisedb identified by edb#123;
ALTER ROLE
postgres=#
Set proper permission for efm files
[root@ip-172-31-86-197 ]# cd /etc/edb/efm-4.4
[root@ip-172-31-86-197 ]# cp efm.properties.in efm.properties
[root@ip-172-31-86-197 ]# cp efm.nodes.in efm.nodes
[root@ip-172-31-86-197 ]# chmod 666 efm.properties
[root@ip-172-31-86-197 ]# chmod 666 efm.nodes
Genereate encrypted password for user enterprisedb
[root@ip-172-31-86-197 efm-4.4]# /usr/edb/efm-4.4/bin/efm encrypt efm
This utility will generate an encrypted password for you to place in your
EFM cluster property file: /etc/edb/efm-4.4/efm.properties
Please enter the password and hit enter:
Please enter the password again to confirm:
The encrypted password is: 5056a00b81775cb6390ad8db8d340513
Please paste this into your efm.properties file
db.password.encrypted=5056a00b81775cb6390ad8db8d340513
Update efm.properties files as below
db.user=enterprisedb
db.password.encrypted=5056a00b81775cb6390ad8db8d340513
db.port=5444
db.database=edb
db.service.owner=enterprisedb
db.service.name=edb-as-14
db.bin=/usr/edb/as14/bin
db.data.dir=/var/lib/edb/as14/data
db.config.dir=/var/lib/edb/as14/data
user.email=xxxxxxxx@gmail.com
bind.address=172.31.86.197:7800
admin.port=7809
is.witness=false
Start efm service:
[root@ip-172-31-86-197 efm-4.4]# systemctl start edb-efm-4.4
add standby and witness server ips in allow node list
[root@ip-172-31-86-197 efm-4.4]# /usr/edb/efm-4.4/bin/efm allow-node efm 172.31.89.172
allow-node signal sent to local agent.
[root@ip-172-31-86-197 efm-4.4]# /usr/edb/efm-4.4/bin/efm allow-node efm 172.31.93.81
allow-node signal sent to local agent.
[root@ip-172-31-86-197 efm-4.4]# /usr/edb/efm-4.4/bin/efm cluster-status efm
Cluster Status: efm
Agent Type Address DB VIP
----------------------------------------------------------------
Primary 172.31.86.197 UP
Allowed node host list:
172.31.86.197 172.31.89.172 172.31.93.81
Membership coordinator: 172.31.86.197
Standby priority host list:
(List is empty.)
Promote Status:
DB Type Address WAL Received LSN WAL Replayed LSN Info
---------------------------------------------------------------------------
Primary 172.31.86.197 0/80000D8
No standby databases were found.
EFM CONFIG ON STANDBY NODE:
Set proper permission for efm files
[root@ip-172.31.89.172 ]# cd /etc/edb/efm-4.4
[root@ip-172.31.89.172 ]# cp efm.properties.in efm.properties
[root@ip-172.31.89.172 ]# cp efm.nodes.in efm.nodes
[root@ip-172.31.89.172 ]# chmod 666 efm.properties
[root@ip-172.31.89.172 ]# chmod 666 efm.nodescat efm.properties
db.user=enterprisedb
db.password.encrypted=5056a00b81775cb6390ad8db8d340513
db.port=5444
db.database=edb
db.service.owner=enterprisedb
db.service.name=edb-as-14
db.bin=/usr/edb/as14/bin
db.data.dir=/var/lib/edb/as14/data
db.config.dir=/var/lib/edb/as14/data
user.email=xxxxxxxxxx@gmail.com
bind.address=172.31.89.172:7800
admin.port=7809
is.witness=false
Update efm.nodes file with primary and witness node ip
[enterprisedb@ip-172-31-89-172 archive]$ cat /etc/edb/efm-4.4/efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
172.31.86.197:7800 172.31.93.81:7800
Start efm service
[root@ip-172-31-89-172 efm-4.4]# systemctl start edb-efm-4.4
[root@ip-172-31-89-172 efm-4.4]# /usr/edb/efm-4.4/bin/efm cluster-status efm
Cluster Status: efm
Agent Type Address DB VIP
----------------------------------------------------------------
Primary 172.31.86.197 UP
Standby 172.31.89.172 UP
Allowed node host list:
172.31.86.197 172.31.89.172 172.31.93.81
Membership coordinator: 172.31.86.197
Standby priority host list:
172.31.89.172
Promote Status:
DB Type Address WAL Received LSN WAL Replayed LSN Info
---------------------------------------------------------------------------
Primary 172.31.86.197 0/80001C0
Standby 172.31.89.172 0/80001C0 0/80001C0
Standby database(s) in sync with primary. It is safe to promote.
EFM CONFIGURATION ON WITNESS NODE
[root@ip-172-31-93-81 ] sudo dnf -y install edb-efm44
[root@ip-172-31-93-81 ] yum install java
[root@ip-172-31-93-81 ] yum install telnet
set required permisson:
[root@ip-172-31-93-81 ]# cd /etc/edb/efm-4.4
[root@ip-172-31-93-81 ]# cp efm.properties.in efm.properties
[root@ip-172-31-93-81 ]# cp efm.nodes.in efm.nodes
[root@ip-172-31-93-81 ]# chmod 666 efm.properties
[root@ip-172-31-93-81 ]# chmod 666 efm.nodes
update the efm.properites with below values:
db.user=enterprisedb
db.password.encrypted=5056a00b81775cb6390ad8db8d340513
db.port=5444
db.database=edb
db.service.owner=enterprisedb
db.service.name=edb-as-14
db.bin=/usr/edb/as14/bin
db.data.dir=/var/lib/edb/as14/data
db.config.dir=/var/lib/edb/as14/data
user.email=rpbhagato@gmail.com
bind.address=172.31.93.81:7800
admin.port=7809
is.witness=true
Update efm.nodes file:
[root@ip-172-31-93-81 efm-4.4]# cat /etc/edb/efm-4.4/efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
172.31.86.197:7800 172.31.89.172:7800
[root@ip-172-31-93-81 efm-4.4]# systemctl start edb-efm-4.4
[root@ip-172-31-86-197 efm-4.4]# /usr/edb/efm-4.4/bin/efm cluster-status efm
Cluster Status: efm
Agent Type Address DB VIP
----------------------------------------------------------------
Primary 172.31.86.197 UP
Standby 172.31.89.172 UP
Witness 172.31.93.81 N/A
Allowed node host list:
172.31.86.197 172.31.89.172 172.31.93.81
Membership coordinator: 172.31.86.197
Standby priority host list:
172.31.89.172
Promote Status:
DB Type Address WAL Received LSN WAL Replayed LSN Info
---------------------------------------------------------------------------
Primary 172.31.86.197 0/80001C0
Standby 172.31.89.172 0/80001C0 0/80001C0
Standby database(s) in sync with primary. It is safe to promote.
Performing switchover
[root@ip-172-31-89-172 efm-4.4]# /usr/edb/efm-4.4/bin/efm promote efm -switchover
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original primary. Run the 'cluster-status' command for information about the new cluster state.
[root@ip-172-31-89-172 efm-4.4]# /usr/edb/efm-4.4/bin/efm cluster-status efm
Cluster Status: efm
Agent Type Address DB VIP
----------------------------------------------------------------
Standby 172.31.86.197 UP
Primary 172.31.89.172 UP
Witness 172.31.93.81 N/A
Allowed node host list:
172.31.86.197 172.31.89.172 172.31.93.81
Membership coordinator: 172.31.86.197
Standby priority host list:
172.31.86.197
Promote Status:
DB Type Address WAL Received LSN WAL Replayed LSN Info
---------------------------------------------------------------------------
Primary 172.31.89.172 0/90001B8
Standby 172.31.86.197 0/90001B8 0/90001B8
Standby database(s) in sync with primary. It is safe to promote.