Replication

Logical

docker run --name primary_postgres \
 -e POSTGRES_PASSWORD=mysecretpassword \
 -d -p 5435:5432 postgres:18.0-alpine3.22 \
 -c wal_level=logical \
 -c max_wal_senders=4 \
 -c max_replication_slots=4

docker run --name secondary_postgres \
 -e POSTGRES_PASSWORD=mysecretpassword \
 -d -p 5434:5432 postgres:18.0-alpine3.22

primary node

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_login TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT TRUE
);

INSERT INTO users (username, email)
SELECT 
    'user_' || g AS username,
    'user_' || g || '@example.com' AS email
FROM generate_series(1, 100000) AS g;

CREATE PUBLICATION users_publication FOR TABLE users;

secondary node

There were some problems with network (all docker containers were running locally). So that I had to specify internal docker container ip (172.17.0.5) and internal port (5432). I found them using docker network inspect bridge

CREATE SUBSCRIPTION users_subscription
    CONNECTION 'host=172.17.0.5 port=5432 dbname=postgres user=postgres password=mysecretpassword'
    PUBLICATION users_publication;   

then I need to create a table on secondary node which I am going to replicate (otherwise I get an error)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_login TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT TRUE
);   

select count(*) from users u ;

And after I created a user table logical replication automatically immediately happened.