Introduction to Replication in PostgreSQL

July 19, 2021

Introduction to Replication in PostgreSQL

Introduction to Replication:-

Replication refers to the process of copying modifications in data from the Primary database to the Standby database. Both these databases are usually located on different physical servers and help in distributing various types of database queries.

Replication is implemented in PostgreSQL using a master-slave configuration. The master acts as the primary instance and is responsible for handling the primary database and its operations. The standby acts as the secondary instance and implements all modifications made to the primary database on itself, thus making itself an identical copy. The master is the read/write server whereas the standby is a read-only server.

Why Need Replication:-

Replication is required for 1. High Availability (HA) 2. Load Balancing

1. High Availability (HA):- HA is required to avoid Single Point of Failure in database. Consider your application is connected to Primary Database. If your Primary Database is goes down due to any reason like Hardware or Software failure. You can promote your Standby as Primary Database and re-direct all your client request to newly promoted Primary Database without replication this is not possible.

2. Load Balancing:- You can use your Standby database to serve READ request from client thus by this approach you can reduce load on primary server.

Replication Modes:-

PostgreSql support 1. Asynchronous Replication Mode 2. Synchronous Replication Mode

1. Asynchronous Replication:-
Transaction complete – Consider only the primary Server.
Never consider Standby.
Can be out of sync.
Advantage:- No additional overhead – Commit only on one server and write performance will be better.
Disadvantage:- Chances of Data loss.

2. Synchronous Replication
Transaction complete – When changes have been replicated to All standby
Standby should be available all the time
Standby will always be in sync
Advantage – No data loss
Disadvantage – Additional overhead

Replication Type:-

PostgreSQL support 1. Physical Replication 2. Logical Replication

1. Physical Replication:-
Physical Replication send changes to Standby in form of Binary Format.
Disk block level changes made by PostgreSQL are sent to Standby end.
Standby is an exact copy of Primary.

Limitation of Physical Replication:-
Same postgresql version
Same architecture
Entire Dataset
No writes are possible

Physical Replication can be achieved by A. File Based Log Shipping B. Streaming Replication

A. Physical Replication:– File Based Log Shipping
Available from PostgreSql 8.2.
Replication based on Archival.
archive_command on the primary.
restore_command on secondary.
File based – Primary will send complete WAL File to Secondary.
Advantage:- No direct connection, No replication user.
Disadvantage:- Replication always in lag, No support for Synchronous mode.

B. Physical Replication:– Streaming Replication
Available from PostgreSql 9.0.
Stream WALs files send to Standby.
primary_conninfo in recovery.conf of Standby.
Block based – Each and every WAL block is send immediately to Standby Server.
WAL Receiver process on Standby responsible to receive WAL Files.
Startup process on Standby responsible to apply all WAL files received by WAL receiver.
WAL Sender process on Primary responsible to collect all WAL records send it to secondary Servers.
Advantage:- Replication lag can be reduce, Support synchronous replication, Used for Failover.
Disadvantage:- Require direct connection , Requires DB users.

2. Logical Replication:-
Available from PostgreSql 10.
Data is replicated in form of SQL Statement.
Example – Primary insert into test1 value (now());
Secondary insert into test1 value (‘2021-01-31’);
Advantage:- Granularity in replicating data, Can write on Destination, Replication across different Version.
Disadvantage:- DDL not supported, Not for Failover.

Logical Replication can be achieved by only A. Publisher/Subscriber Model

A. Logical Replication:- Publisher/Subscriber Model
Publisher – Source Server.
Publication – Table t1 and t2 on database test.
Subscription – Table t1 and t2 on database test.
Connection details.
Subscriber – Destination Server.
WAL Sender Process – Publisher database side.
Logical Replication Launcher – Subscriber database side.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

PGADMIN, POSTGRESQL, REPLICATION

Introduction to Replication in PostgreSQL

Introduction to Replication:-

Why Need Replication:-

Replication Modes:-

Replication Type:-

Related articles