install postgres and set DR and also configure Bi direction Replication

-


To install postgres software 
--------------------------------------------

 sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm


PS C:\Users\user\Desktop\vagrant> cd E:\vagrant


PS E:\vagrant> vagrant status

Current machine states:


pgnode1                   running (virtualbox)

pgnode2                   running (virtualbox)

pgnode3                   running (virtualbox)

pgnode4                   running (virtualbox)


This environment represents multiple VMs. The VMs are all listed

above with their current state. For more information about a specific

VM, run `vagrant status NAME`.

PS E:\vagrant>


PS E:\vagrant> vagrant up


S E:\vagrant> vagrant status


To login to machine 

---------------------------


login as: vagrant

vagrant@192.168.56.131's password:

Last login: Sat Feb 28 02:48:12 2026 from 10.0.2.2

[vagrant@pgnode2 ~]$ sudo su - root

Last login: Sat Feb 28 02:49:44 UTC 2026 on pts/0

[root@pgnode2 ~]#

[root@pgnode2 ~]#



192.168.56.130 pgnode1  site A primary

192.168.56.131 pgnode2  site A standby


192.168.56.132 pgnode3 site B primary

192.168.56.133 pgnode4 site B standby 


pgnode1 and pgnode3 will have a Bidircetional replication.



------------------------------------------------



create user replicator with replication encrypted password 'Siva@12345';


SELECT current_user;

postgres=# \du


postgres=# \q



vi postgresql.conf


listen_addresses = '*' 

max_wal_senders = 10        

max_replication_slots = 10  

wal_keep_size = 512MB

 port = 5432  -------should be enable

hot_standby = on

 

max_wal_size = 1GB

min_wal_size = 80MB

wal_level = replica 



we need to restart the server to refelct the parameters 

/usr/pgsql-17/bin/pg_ctl restart -D /var/lib/pgsql/17/data/



in remote server pg_hba.conf we need to add slave ip address 

host    replication     replicator      192.168.56.0/24         scram-sha-256



--------Now on pgnode2  


/usr/pgsql-17/bin/pg_ctl stop -D /var/lib/pgsql/17/data/


[postgres@pgnode2 ~]$ /usr/pgsql-17/bin/pg_ctl stop -D /var/lib/pgsql/17/data/

waiting for server to shut down.... done

server stopped

[postgres@pgnode2 ~]$ cd /var/lib/pgsql/17/data/

[postgres@pgnode2 data]$ ll

total 64

drwx------. 5 postgres postgres    33 Mar  1 17:59 base

-rw-------. 1 postgres postgres    30 Mar  1 18:00 current_logfiles

drwx------. 2 postgres postgres  4096 Mar  1 18:03 global

drwx------. 2 postgres postgres    32 Mar  1 18:00 log

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_commit_ts

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_dynshmem

-rw-------. 1 postgres postgres  5499 Mar  1 17:59 pg_hba.conf

-rw-------. 1 postgres postgres  2640 Mar  1 17:59 pg_ident.conf

drwx------. 4 postgres postgres    68 Mar  1 18:30 pg_logical

drwx------. 4 postgres postgres    36 Mar  1 17:59 pg_multixact

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_notify

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_replslot

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_serial

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_snapshots

drwx------. 2 postgres postgres    25 Mar  1 18:30 pg_stat

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_stat_tmp

drwx------. 2 postgres postgres    18 Mar  1 17:59 pg_subtrans

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_tblspc

drwx------. 2 postgres postgres     6 Mar  1 17:59 pg_twophase

-rw-------. 1 postgres postgres     3 Mar  1 17:59 PG_VERSION

drwx------. 4 postgres postgres    77 Mar  1 17:59 pg_wal

drwx------. 2 postgres postgres    18 Mar  1 17:59 pg_xact

-rw-------. 1 postgres postgres    88 Mar  1 17:59 postgresql.auto.conf

-rw-------. 1 postgres postgres 30864 Mar  1 17:59 postgresql.conf

-rw-------. 1 postgres postgres    58 Mar  1 18:00 postmaster.opts

[postgres@pgnode2 data]$ rm -rf *

[postgres@pgnode2 data]$



pg_basebackup -h 192.168.56.130 -U replicator -D /var/lib/pgsql/17/data/ -P -v -R -X stream -C -S slaveslots1 


host    postgres        replicator      192.168.56.0/24         scram-sha-256


/usr/pgsql-17/bin/pg_ctl start -D /var/lib/pgsql/17/data

/usr/pgsql-17/bin/pg_ctl reload -D /var/lib/pgsql/17/data


--------------------------------------------------Now we can create a bidirection replication -----------------


192.168.56.130 pgnode1

192.168.56.132 pgnode3


on primary side pgnode1

CREATE ROLE logical_rep with REPLICATION LOGIN PASSWORD 'Siva@12345';


we need to change the wal_level=logical in both and primary and slave 

vi postgresql.conf

wal_level = logical 


Add the below entries in both the database servers 

--------------------------------------------------------------

vi pg_hba.conf     must reload the server.  

host    mydb            logical_rep     192.168.32.0/24         scram-sha-256


/usr/pgsql-17/bin/pg_ctl reload -D /var/lib/pgsql/17/data


esc shift  G O come to last line 



pgnode3

---------------  

we need to create in postgres database only ( as master database)

CREATE ROLE logical_rep with REPLICATION LOGIN PASSWORD 'Siva@12345';


create database  on both servers 

-----------------------------------------

create database mydb;

cd /var/lib/pgsql/17/data

vi pg_hba.conf     ESC SIFT G O

host    mydb            logical_rep     192.168.32.0/24         scram-sha-256

:wq

/usr/pgsql-17/bin/pg_ctl reload -D /var/lib/pgsql/17/data


create the table and structure in both the databases

-----------------------------------------------------------------------------------


mydb=# create sequence test_table_id_seq START WITH 1 INCREMENT BY 2;

CREATE SEQUENCE

mydb=# create table test_table(id INT PRIMARY KEY DEFAULT nextval('test_table_id_seq'),name VARCHAR(225) NOT NULL);

CREATE TABLE

mydb=# grant select,insert,update,delete on test_table to logical_rep;

GRANT

mydb=# grant usage,select ON all sequences in schema public to logical_rep;

GRANT



create the publication pgnode01

----------------------------------------


mydb=# create publication pub_primary1 for table test_table;

WARNING:  "wal_level" is insufficient to publish logical changes

HINT:  Set "wal_level" to "logical" before creating subscriptions.

CREATE PUBLICATION



on other primary means pgnode03

---------------------------------------------------

create publication2 pub_primary2 for table test_table;


now create subscriptions on pgnode01

----------------------------------------------------


CREATE SUBSCRIPTION sub_to_primary2

CONNECTION 'host=192.168.56.132 port=5432 dbname=mydb user=logical_rep password=Siva@12345'

PUBLICATION pub_primary2

WITH (copy_data = false, origin = none);

 

 

 pgnode03

 ------------------------------------------------------------------------

CREATE SUBSCRIPTION sub_to_primary1

CONNECTION 'host=192.168.56.130 port=5432 dbname=mydb user=logical_rep password=Siva@12345'

PUBLICATION pub_primary1

WITH (copy_data = false, origin = none);


 verify both sides

 --------------------------

mydb=# select * from pg_stat_subscription;

 subid |     subname     | worker_type | pid  | leader_pid | relid | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time

-------+-----------------+-------------+------+------------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------

 16404 | sub_to_primary2 | apply       | 4759 |            |       | 0/3C9E900    | 2026-03-01 19:28:09.982944+00 | 2026-03-01 19:28:09.988202+00 | 0/3C9E900      | 2026-03-01 19:28:09.982944+00

(1 row)











 

Comments

Post a Comment

Popular posts from this blog

MY NOTEPAD

-
set lines 200 pages 200; col END_TIME for a20; col ELAPSED_TIME for a25; col START_TIME for a20 SELECT T.SESSION_KEY, T.INPUT_TYPE, T.STATUS, T.SUM_BACKUP_PIECES_IN_GB, T.START_TIME, T.END_TIME, T.SECONDS ELAPSED_TIME FROM ( SELECT SESSION_KEY, INPUT_TYPE, STATUS, OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB, TO_CHAR (START_TIME, 'dd/mm/yyyy hh24:mi') START_TIME, TO_CHAR (END_TIME, 'dd/mm/yyyy hh24:mi') END_TIME, TIME_TAKEN_DISPLAY SECONDS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE INPUT_TYPE='ARCHIVELOG' ORDER BY SESSION_KEY DESC) T WHERE ROWNUM <= 1; ================ ASM disk group free space ============== set sqlblanklines on set lines 180 set pages 999le col "Group Name" form a25 col "Disk Name" form a30 col "State" form a15 ...
Read more

Oracle OEM Holistic Patch

-
  Oracle OEM Holistic Patch  The Concept Starting with Oracle Enterprise Manager (OEM) 13.5 RU24 (October 2024), Oracle introduced the Holistic Patch for OEM — a unified patching model that consolidates several separate updates into a single coordinated package. Previously, administrators had to patch: * The OMS and Agents with the quarterly Release Update (RU). * OEM Application via the System Patch Bundle (SPB). * WebLogic CPU and JDK manually. There was an element of complication because of the need to keep the solution homogeneous. With the Holistic Patch, the last two steps are merged into a single patch bundle. Now we have a new Holistic patch for OEM that is released with each new OMS Release Update. Prerequisites Apply RU 24 Patch  PATCH 36761595  /later on all OMSes before applying Oct Holistic Patch   PATCH 37305292             (OR) Apply RU 25 Patch  PATCH 37057635  /later on all OMSes before applying ...
Read more

Upgrade Oracle 19c DB to 21c using Auto Upgrade utility inprogress

-
Image
  Upgrade Oracle 11g to 21c using AutoUpgrade utility: How do I upgrade Oracle database to 21c?  install the 21c oracle binaries into the server . Oracle Virtual Bix  Oracle Linux 7 Oracle Database 19c currently running Upgrading 19c database to 21c directly Oracle Database 19c on premises is available – and one of the key features is the new Auto Upgrade utility. In the following days I will publish several blog posts explaining and showcasing the new Auto Upgrade. What is the Auto Upgrade? The Oracle Database Auto Upgrade utility is a new tiny little command line tool which allows you to upgrade your databases in an unattended way. I call it the Hands-Free Upgrade. The new Auto Upgrade utility in Oracle 19che idea of the tool is to run the prechecks against multiple databases, fix 99% of the potential issues, set a restore point in case something goes wrong – and then upgrade your databases. And of course, do the post upgrade, recompilation and time zone adjustment. The...
Read more