Wow, the drop table silently removes entry from publication without any logs.
I could not find any monitoring view to help me figure out if the publication is broken due to ddl change.
pg_stat_replication on publisher, and pg_stat_subscription on subscriber only help with lsn based lag.
unless this is not an issue but a feature ?
i'll check more on the better part of monitoring logical replication stuff.
but for your case,
you can set up an event trigger that would avoid dropping the table.
basically any drop of a table or any ddl that would break publication.
you can have a custom query filter that would prevent dropping of objects part of publication accidentally.
and then you want to exclusively drop the table, once not part of publication, you have to first remove the table from publication and then drop.
I have not run this in production, so I believe others may chime in, but logical replication issues from logs are not the best.
I am happy to be corrected.
I'll update on more scenarios.
more logical_replication_example.sh
initdb -D 12/rep1 >/dev/null 2>&1
initdb -D 12/rep2 >/dev/null 2>&1
printf "port = 6111 \nwal_level = logical\n log_statement='all'\n" >> 12/rep1/postgresql.auto.conf
printf "port = 7111 \nwal_level = logical\n log_statement='all'\n" >> 12/rep2/postgresql.auto.conf
pg_ctl -l rep1.log -D 12/rep1 start
pg_ctl -l rep2.log -D 12/rep2 start
psql -p 6111 -c "create database source_rep;"
cat > source_rep.sql <<EOF
--- source_rep.sql content
create table test_rep_other(id int primary key, name varchar);
create table test_rep(id int primary key, name varchar);
insert into test_rep values(generate_series(1,100),'data'||generate_series(1,100));
insert into test_rep_other values(generate_series(1,100),'data'||generate_series(1,100));
select count(1) from test_rep;
select count(1) from test_rep_other;
CREATE PUBLICATION mypub FOR TABLE test_rep, test_rep_other;
EOF
psql -p 6111 source_rep -f source_rep.sql
psql -p 7111 -c "create database target_rep;"
cat > target_rep.sql <<EOF
--- target_rep.sql
create table test_rep(id int primary key, name varchar);
create table test_rep_other(id int primary key, name varchar);
CREATE SUBSCRIPTION mysub CONNECTION 'dbname=source_rep host=localhost user=postgres port=6111' PUBLICATION mypub
with (copy_data = false);
select pg_sleep(5);
select count(1) from test_rep;
select count(1) from test_rep_other ;
EOF
psql -p 7111 target_rep -f target_rep.sql
# all good till here
# now drop a table at source
psql -p 6111 source_rep -c 'drop table test_rep_other;'
psql -p 6111 source_rep -c 'select prrelid::regclass from pg_publication_rel;'
psql -p 7111 target_rep -c 'SELECT srrelid::regclass from pg_subscription_rel;'
psql -P pager -p 6111 source_rep -c 'select * from pg_stat_replication;'
psql -P pager -p 7111 target_rep -c 'select * from pg_stat_subscription;'
pg_ctl -l rep1.log -D 12/rep1 stop
pg_ctl -l rep2.log -D 12/rep2 stop
rm -rf rep* source_rep.sql target_rep.sql 12
####################################################### OUTPUT
waiting for server to start.... done
server started
waiting for server to start.... done
server started
CREATE DATABASE
CREATE TABLE
CREATE TABLE
INSERT 0 100
INSERT 0 100
count
-------
100
(1 row)
count
-------
100
(1 row)
CREATE PUBLICATION
CREATE DATABASE
CREATE TABLE
CREATE TABLE
psql:target_rep.sql:5: NOTICE: created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
pg_sleep
----------
(1 row)
count
-------
0
(1 row)
count
-------
0
(1 row)
DROP TABLE
prrelid
----------
test_rep
(1 row)
srrelid
----------------
test_rep
test_rep_other
(2 rows)
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+----------+------------------+-------------+-----------------+-------------+----------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------------+-----------------+-----------------+---------------+------------+----------------------------------
6542 | 10 | postgres | mysub | 127.0.0.1 | | 59974 | 2021-06-11 00:02:38.155799+05:30 | | streaming | 0/15861C8 | 0/15861C8 | 0/15861C8 | 0/15861C8 | 00:00:00.000093 | 00:00:00.000093 | 00:00:00.000093 | 0 | async | 2021-06-11 00:02:43.173393+05:30
(1 row)
subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
-------+---------+------+-------+--------------+----------------------------------+----------------------------------+----------------+----------------------------------
16401 | mysub | 6541 | | 0/15861C8 | 2021-06-11 00:02:43.173354+05:30 | 2021-06-11 00:02:43.173403+05:30 | 0/15861C8 | 2021-06-11 00:02:43.173354+05:30
(1 row)
publisher logs
2021-06-11 00:02:37.724 IST [6511] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-06-11 00:02:37.724 IST [6511] LOG: listening on IPv4 address "127.0.0.1", port 6111
2021-06-11 00:02:37.728 IST [6511] LOG: listening on Unix socket "/tmp/.s.PGSQL.6111"
2021-06-11 00:02:37.733 IST [6512] LOG: database system was shut down at 2021-06-11 00:02:37 IST
2021-06-11 00:02:37.738 IST [6511] LOG: database system is ready to accept connections
2021-06-11 00:02:37.931 IST [6531] LOG: statement: create database source_rep;
2021-06-11 00:02:38.024 IST [6534] LOG: statement: create table test_rep_other(id int primary key, name varchar);
2021-06-11 00:02:38.030 IST [6534] LOG: statement: create table test_rep(id int primary key, name varchar);
2021-06-11 00:02:38.035 IST [6534] LOG: statement: insert into test_rep values(generate_series(1,100),'data'||generate_series(1,100));
2021-06-11 00:02:38.036 IST [6534] LOG: statement: insert into test_rep_other values(generate_series(1,100),'data'||generate_series(1,100));
2021-06-11 00:02:38.037 IST [6534] LOG: statement: select count(1) from test_rep;
2021-06-11 00:02:38.038 IST [6534] LOG: statement: select count(1) from test_rep_other;
2021-06-11 00:02:38.038 IST [6534] LOG: statement: CREATE PUBLICATION mypub FOR TABLE test_rep, test_rep_other;
2021-06-11 00:02:38.136 IST [6540] LOG: statement: SELECT pg_catalog.set_config('search_path', '', false);
2021-06-11 00:02:38.136 IST [6540] LOG: statement: SELECT DISTINCT t.schemaname, t.tablename
FROM pg_catalog.pg_publication_tables t
WHERE t.pubname IN ('mypub')
2021-06-11 00:02:38.148 IST [6540] LOG: logical decoding found consistent point at 0/15847E8
2021-06-11 00:02:38.148 IST [6540] DETAIL: There are no running transactions.
2021-06-11 00:02:38.156 IST [6542] LOG: statement: SELECT pg_catalog.set_config('search_path', '', false);
2021-06-11 00:02:38.156 IST [6542] LOG: starting logical decoding for slot "mysub"
2021-06-11 00:02:38.156 IST [6542] DETAIL: Streaming transactions committing after 0/1584820, reading WAL from 0/15847E8.
2021-06-11 00:02:38.157 IST [6542] LOG: logical decoding found consistent point at 0/15847E8
2021-06-11 00:02:38.157 IST [6542] DETAIL: There are no running transactions.
2021-06-11 00:02:43.170 IST [6544] LOG: statement: drop table test_rep_other;
2021-06-11 00:02:43.176 IST [6546] LOG: statement: select prrelid::regclass from pg_publication_rel;
2021-06-11 00:02:43.182 IST [6550] LOG: statement: select * from pg_stat_replication;
subscriber logs
2021-06-11 00:02:37.828 IST [6522] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-06-11 00:02:37.829 IST [6522] LOG: listening on IPv4 address "127.0.0.1", port 7111
2021-06-11 00:02:37.833 IST [6522] LOG: listening on Unix socket "/tmp/.s.PGSQL.7111"
2021-06-11 00:02:37.837 IST [6523] LOG: database system was shut down at 2021-06-11 00:02:37 IST
2021-06-11 00:02:37.842 IST [6522] LOG: database system is ready to accept connections
2021-06-11 00:02:38.043 IST [6536] LOG: statement: create database target_rep;
2021-06-11 00:02:38.122 IST [6539] LOG: statement: create table test_rep(id int primary key, name varchar);
2021-06-11 00:02:38.129 IST [6539] LOG: statement: create table test_rep_other(id int primary key, name varchar);
2021-06-11 00:02:38.134 IST [6539] LOG: statement: CREATE SUBSCRIPTION mysub CONNECTION 'dbname=source_rep host=localhost user=postgres port=6111' PUBLICATION mypub
with (copy_data = false);
2021-06-11 00:02:38.154 IST [6539] LOG: statement: select pg_sleep(5);
2021-06-11 00:02:38.155 IST [6541] LOG: logical replication apply worker for subscription "mysub" has started
2021-06-11 00:02:43.156 IST [6539] LOG: statement: select count(1) from test_rep;
2021-06-11 00:02:43.162 IST [6539] LOG: statement: select count(1) from test_rep_other ;
2021-06-11 00:02:43.180 IST [6548] LOG: statement: SELECT srrelid::regclass from pg_subscription_rel;
2021-06-11 00:02:43.185 IST [6552] LOG: statement: select * from pg_stat_subscription;
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
