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