Logical replication basics in PostgreSQL - Step-by-Step Execution

Concept Flow - Logical replication basics

Setup Publisher

↓

Create Publication

↓

Setup Subscriber

↓

Create Subscription

↓

Data Changes on Publisher

↓

Changes Sent as WAL Logs

↓

Subscriber Applies Changes

↓

Data Synced on Subscriber

Logical replication starts by setting up a publisher and subscriber. Changes on the publisher are sent as logs and applied on the subscriber to keep data in sync.

Execution Sample

PostgreSQL

CREATE PUBLICATION mypub FOR TABLE users;
CREATE SUBSCRIPTION mysub CONNECTION 'host=pubhost dbname=mydb user=replicator password=secret' PUBLICATION mypub;

This code creates a publication on the publisher for the 'users' table and a subscription on the subscriber to receive changes.

Execution Table

Step	Action	Details	Result
1	Create Publication	CREATE PUBLICATION mypub FOR TABLE users;	Publication 'mypub' created on publisher
2	Create Subscription	CREATE SUBSCRIPTION mysub CONNECTION 'host=pubhost dbname=mydb user=replicator password=secret' PUBLICATION mypub;	Subscription 'mysub' created on subscriber
3	Insert Data on Publisher	INSERT INTO users VALUES (1, 'Alice');	Change logged in WAL
4	Send WAL Logs	Logical replication sends changes from publisher to subscriber	Change sent to subscriber
5	Apply Changes on Subscriber	Subscriber applies insert to 'users' table	Data inserted on subscriber
6	Verify Data	SELECT * FROM users;	Row (1, 'Alice') present on subscriber
7	Exit	Replication running continuously	Process ongoing, no exit

💡 Logical replication runs continuously to keep data synced; no termination unless stopped

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
users table on publisher	empty	1 row inserted	1 row inserted	1 row inserted
users table on subscriber	empty	empty	1 row inserted	1 row inserted
WAL logs	empty	insert logged	sent and applied	up to date

Key Moments - 3 Insights

Why does the subscriber not have data before creating the subscription?

Because data is only sent after the subscription is created and connected, as shown in execution_table step 4 and 5.

Does logical replication copy the entire database automatically?

No, it replicates only the tables included in the publication, as seen in step 1 where only 'users' table is published.

What happens if data changes on the publisher after subscription creation?

Changes are continuously sent and applied on the subscriber, keeping data in sync as shown in steps 4 and 5.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of the 'users' table on the subscriber after step 3?

A Empty

B 1 row inserted

C Subscription created

D Data deleted

💡 Hint

Check variable_tracker column 'After Step 3' for 'users table on subscriber'

At which step does the subscriber apply the data change?

A Step 2

B Step 3

C Step 5

D Step 6

💡 Hint

Look at execution_table row describing 'Apply Changes on Subscriber'

If the publication included two tables, how would the replication change?

A Only one table would replicate

B Both tables' changes would replicate

C No tables would replicate

D Subscriber would reject subscription

💡 Hint

Refer to key_moments about publication scope and step 1 in execution_table

Concept Snapshot

Logical replication basics in PostgreSQL:
- Publisher creates a publication for specific tables
- Subscriber creates a subscription to that publication
- Data changes on publisher are sent as WAL logs
- Subscriber applies changes to keep data synced
- Replication runs continuously until stopped

Full Transcript

Logical replication in PostgreSQL involves setting up a publisher and subscriber. The publisher creates a publication specifying which tables to replicate. The subscriber creates a subscription connecting to the publisher and subscribing to that publication. When data changes occur on the publisher, these changes are sent as write-ahead logs (WAL) to the subscriber. The subscriber applies these changes to its tables, keeping data synchronized. This process runs continuously, ensuring ongoing replication of data changes. The example shows creating a publication for the 'users' table, creating a subscription, inserting data on the publisher, and the data appearing on the subscriber after replication.