Physical Replication:
Vm1 : primary site
Vm2: clone: secondary site
Note: If the same machines which were used for backup are used for physical
backup then do the following changes before doing the replication:
Connect to centos 7 and start the server
su – root
password: mysql
su - postgres
export PATH=$PATH:/usr/pgsql-14/bin
pg_ctl -D /var/lib/pgsql/14/data start
In [Link] file:
Change the following
su – root
password:mysql
su – postgres
cd /var/lib/pgsql/14/data
vi [Link]
archive_mode = off
archive_commane = ‘’
restore_command=’’
save and exist
In [Link] file:
Change the following
Vi [Link]
Remove the lines:
restore_command = ‘cp ….’
recovery_target_time=’……’
Disable firewall on both VMS:
a. Disable firewall and selinux at both the VMs
i. systemctl stop firewalld
ii. systemctl disable firewalld
b. cd /etc/selinux/
i. ls
ii. vi config
1. edit selinux=disabled
At VM1:Primary
su – root
password:mysql
su – postgres
cd /var/lib/pgsql/14/data
Edit [Link]:
vi [Link]
Listen_addressess=”*”
Wal_level=replica
Max_wal_sender=10
Wal_keep_size=32MB
Hot_standby=on
Edit pg_hba.conf:
Vi pg_hba.conf
Add at replication
#### Host replication all [Link]/32 trust
Host replication all ip addr of vm2/32 trust
Restart the server
export PATH=$PATH:/usr/pgsql-14/bin
pg_ctl -D /var/lib/pgsql/14/data restart
To check:
Psql
Show hot_standby;
Show wal_keep_size;
\q
At VM2: standby/replica
su – root
ping ip_of_vm1 // check if connected
su - postgres
cd /var/lib/pgsql/14/data
rm -rf *
cd
pg_basebackup -P -R -X stream -c fast -h ip_address_vm1/primary -U postgres
-D /var/lib/pgsql/14/data
stop server
start server:
pg_ctl -D /var/lib/pgsql/14/data start
psql
check for databases and table (whatever in VM1 will be replicated here)
\l
\dt
at VM1:
add more rows to any table
at vm2:
check the newly added rows
at vm1:
select * from pg_stat_replication;
note: if primary fails then secondary can be promoted as primary as:
Switch over:
At Primary:
select * from pg_stat_replication;
SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) from pg_stat_replication;
Select pg_is_in_recovery();
At Secondary:
select * from pg_stat_wal_receiver;
select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
pg_last_xact_replay_timestamp();
Select pg_is_in_recovery();
Stop Primary(vm-1):
pg_ctl -D /var/lib/pgsql/14/data stop
Vm-2
Promote secondary :
pg_ctl -D /var/lib/pgsql/14/data promote
Start the server :
pg_ctl -D /var/lib/pgsql/14/data start
On the primary(vm1):
In data directory, create standby signal:
Cd /var/lib/pgsql/14/data
touch [Link]
edit vi [Link]
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer
host=ip_address_of_vm2 port=5432 sslmode=prefer sslcompression=0 sslsni=1
ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres
target_session_attrs=any'
wal_log_hints = 'on'
At secondary vm2:
Su – postgres
Cd /var/lib/pgsql/14/data
Edit vi pg_hba.conf file:
At IPV4 add:
Host all all ip addr of vm1/32 trust
At replication add:
Host replication all ip addr of vm1/32 trust
Save and exit
Start the new standby I.e earlier primary(vm1):
pg_ctl -D /var/lib/pgsql/14/data start
psql
select * from pg_stat_wal_receiver;
Select pg_is_in_recovery();
\q
At new primary (vm2):
psql
select * from pg_stat_replication;
Select pg_is_in_recovery();
at VM2(new primary):
add tables
at vm1(new standby):
check if tables appear
manually done
repmgr: software can be installed to automate switching b/w primary to
secondary in case of failure
Logical Replication:
Before starting logical replication if same primary was used for physical
replication undo the following :
On the primary(vm1):
su – root
password:mysql
su – postgres
cd /var/lib/pgsql/14/data
In data directory, remove standby signal
rm [Link]
edit vi [Link]
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
# remove the following lines
primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer
host=[Link] port=5432 sslmode=prefer sslcompression=0 sslsni=1
ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres
target_session_attrs=any'
wal_log_hints = 'on'
Vm1: primary
Vm3: clone (secondary/replica)
At VM1:
su – root
password:mysql
su – postgres
cd /var/lib/pgsql/14/data
vi [Link]
Listen_addresses= “*”
Wal_level = ‘logical’
Save and exit
Edit vi pg_hba.conf:
Add in IPV4:
Host all all (ip_addr of replica) trust
Add in replication:
Host replication all (ip_addr of replica) trust
Save and exit
Restart the server
pg_ctl -D /var/lib/pgsql/14/data start
psql
create database source_rep;
\c source_rep;
Create table test_rep(num int);
Insert into test_rep select generate_series(1,10);
At VM3:
su – root
password:mysql
su – postgres
pg_ctl -D /var/lib/pgsql/14/data start
psql
Create database target_rep;
\c target_rep;
Create table test_rep(num int); (same table as in VM1)
At VM1:
Create publication mypub for table test_rep;
select * from pg_publication; /list of publications
select * from pg_publication_tables;
At VM3:
create subscription mysub connection 'dbname=source_rep host=
ip_address_vm1/primary user=postgres port=5432' publication mypub;
select * from pg_subscription;
Select * from test_rep;(all data inserted in vm1 appears here)
At vm1:
Add more rows
At vm3:
Added rows will be visible
Refresh publication:
At VM1:
Create table test(id int);
Insert into test values(1);
Select * from test;
At VM3:
Create table test(id int);
Select * from test; //no rows
At VM1:
Alter publication mypub add table test;
At VM3:
Alter subscription mysub refresh publication;
Select * from test;