logical_replication_to_diff_dbs_via_schemas

gistfile1.txt

setup:
original server has schema db1,db2

goal: create new servers db1 and db2 and move over schema db1 to server db1 and schema db2 to server db2
we make use of logical replication to publish INS,UPD,DEL changes from original server to respective schema servers.
https://www.postgresql.org/docs/current/logical-replication-restrictions.html
https://www.postgresql.org/docs/current/logical-replication.html

server mainserver  db1server  db2server
schema db1 ->      db1         
       db2 ->                 db2
       
postgres@db:~/playground/demo$ initdb -D maindb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory maindb ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D maindb -l logfile start

postgres@db:~/playground/demo$ printf "port=8000\n wal_level=logical\n" >> maindb/postgresql.conf
postgres@db:~/playground/demo$ tail -2 maindb/postgresql.conf
port=8000
 wal_level=logical
postgres@db:~/playground/demo$  pg_ctl -D maindb -l maindb.log start
waiting for server to start.... done
server started
postgres@db:~/playground/demo$ psql -p 8000
psql (14beta1)
Type "help" for help.

postgres=# create schema db1;
CREATE SCHEMA
postgres=# create schema db2;
CREATE SCHEMA
postgres=# create table db1.t(id int primary key);
CREATE TABLE
postgres=# create table db2.t(id int primary key);
CREATE TABLE
postgres=# create publication db1_pub
FOR ALL TABLES  FOR TABLE       WITH (
postgres=# create publication db1_pub for table db1.t with (publish='insert,update,delete');
CREATE PUBLICATION
postgres=# create publication db2_pub for table db2.t with (publish='insert,update,delete');
CREATE PUBLICATION
postgres=# \q
postgres@db:~/playground/demo$ initdb -D db1
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory db1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D db1 -l logfile start

postgres@db:~/playground/demo$ printf "port=8001\n wal_level=logical\n" >> db1/postgresql.conf
postgres@db:~/playground/demo$ tail -2 db1/postgresql.conf
port=8001
 wal_level=logical
postgres@db:~/playground/demo$ pg_ctl -D db1 -l db1.log start
waiting for server to start.... done
server started
postgres@db:~/playground/demo$ psql -p 8001
psql (14beta1)
Type "help" for help.

postgres=# create schema db1;
CREATE SCHEMA
postgres=# create table db1.t(id int primary key);
CREATE TABLE
postgres=# create subscription db1_sub CONNECTION 'port=8000 dbname=postgres' PUBLICATION db1_pub;
NOTICE:  created replication slot "db1_sub" on publisher
CREATE SUBSCRIPTION
postgres=# \q
postgres@db:~/playground/demo$ initdb -D db2
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory db2 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D db2 -l logfile start

postgres@db:~/playground/demo$ printf "port=8002\n wal_level=logical\n" >> db2/postgresql.conf
postgres@db:~/playground/demo$ tail -2 db2/postgresql.conf
port=8002
 wal_level=logical
postgres@db:~/playground/demo$ pg_ctl -D db2 -l db2.log start
waiting for server to start.... done
server started
postgres@db:~/playground/demo$ psql -p 8002
psql (14beta1)
Type "help" for help.

postgres=# create schema db2;
CREATE SCHEMA
postgres=# create table db2.t(id int primary key);
CREATE TABLE
postgres=# create subscription db2_sub CONNECTION 'port=8000 dbname=postgres' PUBLICATION db2_pub;
NOTICE:  created replication slot "db2_sub" on publisher
CREATE SUBSCRIPTION
postgres=# \q
postgres@db:~/playground/demo$ psql -p 8000
psql (14beta1)
Type "help" for help.

postgres=# insert into db1.t select x from generate_series(1, 100) x;
INSERT 0 100
postgres=# insert into db2.t select x from generate_series(1, 1000) x;
INSERT 0 1000
postgres=# \q
postgres@db:~/playground/demo$ psql -p 8001 -c 'select count(1) from db1.t;'
 count
-------
   100
(1 row)

postgres@db:~/playground/demo$ psql -p 8002 -c 'select count(1) from db1.t;'
ERROR:  relation "db1.t" does not exist
LINE 1: select count(1) from db1.t;
                             ^
postgres@db:~/playground/demo$ psql -p 8002 -c 'select count(1) from db2.t;'
 count
-------
  1000
(1 row)