logical replication

deterrent than stream replication , where the master will send wall log , there we send the actual command such (insert into t1 values (1,'''value'' ))

physical replication

in physical replication , the replica is force to copy the whole database schema tables and so on from master server .
physical replication in this case can not support in replicating singe table

logical replication

as mentioned only replicated the changes , which give the advantage of replicating singe table .
here the primary database will send the DML command to be replayed on standby server
here’s the scoop:

1. Decoding WAL Records: In PostgreSQL, Write-Ahead Logging (WAL) records all changes made to the database. When using logical replication, the first step is to decode these WAL records. Think of it like decrypting a secret code; this process extracts the actual changes that were made to the data.
2. Streaming to Replica Server: Once those changes are decoded, they’re streamed over to the replica server. This is like sending a live feed of the changes happening in the source database to the replica, ensuring it stays up to date with the latest data modifications.
3. Applying Statements on Replica: On the replica server, these decoded changes are then applied as SQL statements. It’s like having a copycat follow along with the source database’s actions, executing the same SQL commands to mimic the changes.

So, in a nutshell, PostgreSQL goes through this process of decoding, streaming, and applying changes to keep the replica database in sync with the source. It’s like a well-choreographed dance of data replication!

this whole setup the primary server is called publisher server , and replica is called subscriber server .
similar to MS SQL replicationudemy

physical replication

1. must have the both server must have identical configuration
2. the data has to be placed on file system to be similar to both master and slave
3. these type of replication wont work to migrate from older version to newer version .
   sudo update-alternatives –install /usr/bin/initdb initdb /usr/lib/postgresql/12/bin/initdb 1

logical replication limitations

1- doesn’t support DDL command corresponded to creating index

logical replication setup

sequence of steps:

1- instantiate 2 PostgreSQL database cluster
2. configure the publisher with ”wal_level =logical”
3. start the instances
4. create a database and the tables