Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
lr_with_pg_dump
publisher pg13
postgres@db:~/playground/pg13$ createdb pagila
postgres@db:~/playground/pg13$ psql -p 5444 pagila < pagila-0.10.1/pagila-schema.sql 2>/dev/null >/dev/null
postgres@db:~/playground/pg13$ psql -p 5444 pagila < pagila-0.10.1/pagila-data.sql 2>/dev/null >/dev/null
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c 'CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;'
ERROR: publication "pglogical_rep01" already exists
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c 'drop publication pglogical_rep01;'
DROP PUBLICATION
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c 'CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;'
CREATE PUBLICATION
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c "SELECT pg_create_logical_replication_slot('pglogical_rep01', 'pgoutput');"
pg_create_logical_replication_slot
------------------------------------
(pglogical_rep01,0/739D2D0)
(1 row)
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c "SELECT restart_lsn from pg_replication_slots;"
restart_lsn
-------------
0/739D298
(1 row)
subscriber pg14
postgres@db:~/playground$ dropdb pagila
postgres@db:~/playground$ createdb pagila
postgres@db:~/playground$ rm pagila.dump
postgres@db:~/playground$ pg_dump -p 5444 -f pagila.dump pagila
postgres@db:~/playground$ psql pagila < pagila.dump 2>/dev/null >/dev/null
postgres@db:~/playground$ psql pagila
psql (14beta1)
Type "help" for help.
pagila=# CREATE SUBSCRIPTION pglogical_sub01 CONNECTION 'port=5444 dbname=pagila' PUBLICATION pglogical_rep01
WITH (
copy_data = false,
create_slot = false,
enabled = false,
connect = true,
slot_name = 'pglogical_rep01'
);
CREATE SUBSCRIPTION
pagila=# SELECT 'pg_'||oid::text AS "external_id"
FROM pg_subscription
WHERE subname = 'pglogical_sub01';
external_id
-------------
pg_74211
(1 row)
pagila=# SELECT pg_replication_origin_advance('pg_74211', '0/739D298');
pg_replication_origin_advance
-------------------------------
(1 row)
pagila=# ALTER SUBSCRIPTION pglogical_sub01 ENABLE;
ALTER SUBSCRIPTION
pagila=# \x
Expanded display is on.
pagila=# table pg_stat_subscription;
-[ RECORD 1 ]---------+---------------------------------
subid | 74211
subname | pglogical_sub01
pid | 20883
relid |
received_lsn | 0/73A7630
last_msg_send_time | 2021-08-18 23:30:47.95082+05:30
last_msg_receipt_time | 2021-08-18 23:30:47.950874+05:30
latest_end_lsn | 0/73A7630
latest_end_time | 2021-08-18 23:30:47.95082+05:30
all verified logical rep is working with pg_dump.
now we can delete from table on pub and the same on sub, and check autov runs. if required lower threshold to trigger
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment