Logical replication basics in PostgreSQL - Mini Project: Build & Apply

Logical Replication Basics in PostgreSQL

📖 Scenario: You are setting up a simple logical replication system between two PostgreSQL databases to keep data synchronized for a small business. This will help the business maintain a live copy of important data on a secondary server for reporting and backup.

🎯 Goal: Build a basic logical replication setup by creating a publication on the primary database and a subscription on the secondary database to replicate a specific table.

📋 What You'll Learn

Create a table named products with columns id (integer primary key) and name (text).

Create a publication named product_pub that publishes the products table.

Create a subscription named product_sub on the subscriber database that connects to the publisher and subscribes to product_pub.

Ensure the subscription replicates data from the products table.

💡 Why This Matters

🌍 Real World

Logical replication is used to keep data synchronized between databases for reporting, backup, or scaling read workloads.

💼 Career

Database administrators and backend engineers often set up and maintain logical replication to ensure data availability and consistency across systems.

1

Create the products table on the primary database

Write a SQL statement to create a table called products with two columns: id as an integer primary key and name as text.

PostgreSQL

-- Your code here to create the products table

Need a hint?

Use CREATE TABLE with the column definitions exactly as specified.

2

Create a publication named product_pub for the products table

Write a SQL statement to create a publication called product_pub that publishes the products table.

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT
);
-- Your code here to create the publication

Need a hint?

Use CREATE PUBLICATION with the exact publication name and specify the table.

3

Create a subscription named product_sub on the subscriber database

Write a SQL statement to create a subscription called product_sub that connects to the primary database at host=primary_host port=5432 dbname=primary_db user=replicator password=secret and subscribes to the publication product_pub.

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE PUBLICATION product_pub FOR TABLE products;
-- Your code here to create the subscription

Need a hint?

Use CREATE SUBSCRIPTION with the exact subscription name, connection string, and publication name.

4

Verify the subscription replicates the products table

Add a SQL query to check the status of the subscription product_sub in the pg_stat_subscription view to confirm it is active and replicating.

PostgreSQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE PUBLICATION product_pub FOR TABLE products;

CREATE SUBSCRIPTION product_sub CONNECTION 'host=primary_host port=5432 dbname=primary_db user=replicator password=secret' PUBLICATION product_pub;
-- Your code here to query subscription status

Need a hint?

Use a SELECT query on pg_stat_subscription filtering by the subscription name.