Logical_replication

Chapter 42: Logical Replication

Selective Data Replication

---

42.1 Understanding Logical Replication

Logical replication allows selective table replication with row/column filtering.

-- On Publisher:
-- 1. Enable logical replication
ALTER SYSTEM SET wal_level = logical;

-- 2. Create publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- 3. Create publication for specific rows
CREATE PUBLICATION active_users FOR TABLE users
    WHERE (status = 'active');

-- 4. Create publication for all tables
CREATE PUBLICATION all_tables FOR ALL TABLES;

-- List publications
SELECT * FROM pg_publication;

-- On Subscriber:
-- 1. Create subscription
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=primary_host port=5432 dbname=mydb user=replicator password=secret'
    PUBLICATION my_publication;

-- Check subscription status
SELECT * FROM pg_subscription;

-- Check subscription tables
SELECT * FROM pg_subscription_rel;

---

42.2 Conflict Resolution

-- Check for replication conflicts
SELECT * FROM pg_stat_replication_conflicts;

-- Handle conflicts with notification
CREATE OR REPLACE FUNCTION handle_repl_conflict()
RETURNS TRIGGER AS $$
BEGIN
    -- Log conflict
    INSERT INTO replication_conflicts (table_name, key, conflict_type, resolution_time)
    VALUES (TG_TABLE_NAME, OLD.id, 'update', NOW());

    -- Apply subscriber's version
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create event trigger for conflicts
CREATE EVENT TRIGGER conflict_handler ON replication_conflict
    EXECUTE FUNCTION handle_repl_conflict();

---

Summary

Concept	Description
Publication	Set of tables to replicate
Subscription	Connection to publisher
Slot	Tracks subscription progress

---

Next: Chapter 43: High Availability with Patroni