logical_rep_demo_on_same_server

gistfile1.txt

option 2, create pub sub on the same server

from the docs,
Creating a subscription that connects to the same database cluster 
(for example, to replicate between databases in the same cluster or to replicate within the same database)
will only succeed if the replication slot is not created as part of the same command. 
Otherwise, the CREATE SUBSCRIPTION call will hang. To make this work, create the 
replication slot separately (using the function pg_create_logical_replication_slot with the plugin name pgoutput) 
and create the subscription using the parameter create_slot = false. 
This is an implementation restriction that might be lifted in a future release

DEMO--------------------------------------on the same server 

 initdb -D example

postgres@db:~/playground/logical_replication$ vim example/postgresql.conf  # wal_level = 'logical'

postgres@db:~/playground/logical_replication$ pg_ctl -D /home/postgres/playground/logical_replication/example start

postgres@db:~/playground/logical_replication$ createdb pub
postgres@db:~/playground/logical_replication$ createdb sub

postgres@db:~/playground/logical_replication$ psql pub
psql (14beta1)
Type "help" for help.

pub=# create table t(id int primary key);
CREATE TABLE
pub=# insert into t select x from generate_series(1, 100) x;
INSERT 0 100
pub=# create publication mypub for all tables with ( publish = 'insert,update,delete' );
CREATE PUBLICATION
pub=# select pg_create_logical_replication_slot('mysub', 'pgoutput');
pg_create_logical_replication_slot
------------------------------------
 (mysub,0/16A9A18)
(1 row)

pub=# \c sub
You are now connected to database "sub" as user "postgres".
sub=# create table t(id int primary key);
CREATE TABLE
sub=#  create subscription mysub CONNECTION 'dbname=pub' publication mypub with ( create_slot = false );
CREATE SUBSCRIPTION

lr.sh

#!/bin/bash


dbs=${1:-2}
startport=5000

startdb() {
        local dbs=$dbs
        local startport=$startport
        for i in `seq 1 ${dbs}`
        do
                startport=$((startport + 1))
                port=$startport
                dbpath=/home/postgres/playground/logical_replication/db${i}
                initdb -D $dbpath
config_content=$(cat<<EOF
port=$port
wal_level=logical
archive_mode = on
archive_command = '/bin/true'
max_wal_size = 48MB
min_wal_size = 32MB
shared_buffers = 32MB
EOF
)
                echo "${config_content}" >> ${dbpath}/postgresql.conf
                pg_ctl -D $dbpath -l ${dbpath}.log start
                psql -p $startport -c "select 'a';"
                psql -p $startport -c 'create table t(id int primary key, name text);'
                if [[ $startport -eq 5001 ]]; then
                        echo $startport
                        psql -p $startport -c 'insert into t select x, x::text from generate_series(1,1000) x;'
                        psql -p $startport -c "create publication mypub for all tables with (publish = 'insert,update,delete');"
                elif [[ $startport -eq 5002 ]]; then
                        echo $startport
                        psql -p $startport -c "create subscription mysub connection 'port=5001' publication mypub with (synchronous_comm
it = 'remote_apply');"
                fi
        done
}

startdb
psql -p 5001 -c 'select count(*) from t;'
psql -p 5002 -c 'select count(*) from t;'


######################## output

bash run.sh
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 /home/postgres/playground/logical_replication/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 /home/postgres/playground/logical_replication/db1 -l logfile start

waiting for server to start.... done
server started
 ?column?
----------
 a
(1 row)

CREATE TABLE
5001
INSERT 0 1000
CREATE PUBLICATION
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 /home/postgres/playground/logical_replication/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 /home/postgres/playground/logical_replication/db2 -l logfile start

waiting for server to start.... done
server started
 ?column?
----------
 a
(1 row)

CREATE TABLE
5002
NOTICE:  created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
 count
-------
  1000
(1 row)

 count
-------
     0
(1 row)

postgres@db:~/playground/logical_replication$ psql -p 5002
psql (14beta1)
Type "help" for help.

postgres=# select count(1) from t;
 count
-------
  1000
(1 row)