Skip to content

Instantly share code, notes, and snippets.

@drzero42
Last active December 2, 2022 07:23
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save drzero42/02b4082ce002c1d90ddd64f5fe03aee0 to your computer and use it in GitHub Desktop.
Save drzero42/02b4082ce002c1d90ddd64f5fe03aee0 to your computer and use it in GitHub Desktop.
Postgres logical replication breakage
version: "3"
services:
master:
image: postgres:11
environment:
POSTGRES_PASSWORD: abc123
command: -c config_file=/etc/postgresql/postgresql.conf
volumes:
- ./postgresql.conf:/etc/postgresql/postgresql.conf
ports:
- 5432:5432
replica:
image: postgres:11
environment:
POSTGRES_PASSWORD: abc123
command: -c config_file=/etc/postgresql/postgresql.conf
volumes:
- ./postgresql.conf:/etc/postgresql/postgresql.conf
ports:
- 15432:5432
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');
CREATE ROLE repuser WITH LOGIN REPLICATION PASSWORD 'abc123';
CREATE TABLE widgets (
id SERIAL,
name TEXT,
price DECIMAL,
CONSTRAINT widgets_pkey PRIMARY KEY (id)
);
GRANT ALL PRIVILEGES ON TABLE widgets TO repuser;
INSERT INTO widgets (name, price) VALUES ('Hammer', 4.50), ('Coffee Mug', 6.20), ('Cupholder', 3.80);
CREATE PUBLICATION widgets_pub;
ALTER PUBLICATION widgets_pub ADD TABLE ONLY widgets;
CREATE ROLE repuser WITH LOGIN REPLICATION PASSWORD 'abc123';
CREATE TABLE widgets (
id SERIAL,
name TEXT,
price DECIMAL,
CONSTRAINT widgets_pkey PRIMARY KEY (id)
);
GRANT ALL PRIVILEGES ON TABLE widgets TO repuser;
INSERT INTO widgets (name, price) VALUES ('Hammer', 4.50), ('Coffee Mug', 6.20), ('Cupholder', 3.80);
CREATE PUBLICATION widgets_pub;
ALTER PUBLICATION widgets_pub ADD TABLE ONLY widgets;
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');
wal_level = logical
listen_addresses = '*'
log_min_messages = debug1
log_min_error_statement = debug1
CREATE TABLE widgets (
id SERIAL,
name TEXT,
price DECIMAL,
CONSTRAINT widgets_pkey PRIMARY KEY (id)
);
CREATE SUBSCRIPTION widgets_sub
CONNECTION 'dbname=testdb host=master port=5432 user=repuser password=abc123'
PUBLICATION widgets_pub
WITH (slot_name='my_slot', create_slot=false);
#!/usr/bin/env bash
docker-compose up -d
export PGHOST=127.0.0.1
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=abc123
echo -n "Waiting for database to be available"
until psql postgres -c 'SELECT 1;' &>/dev/null; do
echo -n "."
sleep 1
done; echo
echo "Load sql on master"
createdb testdb
psql testdb < $1
sleep 2
export PGPORT=15432
echo "Creating database on replica"
createdb testdb
echo "Load sql on replica"
psql testdb < replica.sql
docker-compose logs -f replica
docker-compose down
@drzero42
Copy link
Author

./run.sh master-broken.sql shows that logical replication breaks if replication slot is created first.
./run.sh master-works.sql shows that logical replication works if replication slot is created last (or at least after data is inserted).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment