lr_with_pg_dump

gistfile1.txt

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