cascading_replication

cascading_replication.sh

i just did a quick

primary -> replica -> cascading_replica setup and then failed over
from primary to replica.
cascading replica was getting all changes from replica(new primary) just fine.

i have pg14beta installed btw.

initdb -D primary
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

postgres@db:~/playground$ pg_ctl -D primary -l logfile start
waiting for server to start.... done
server started
postgres@db:~/playground$ psql
psql (14beta1)
Type "help" for help.

postgres=# select pg_create_physical_replication_slot('replica');
 pg_create_physical_replication_slot
-------------------------------------
 (replica,)
(1 row)

postgres=# create table t(id int primary key);
CREATE TABLE
postgres=# insert into t select x from generate_series(1, 100) x; checkpoint;
INSERT 0 100
postgres=# \q

-- create a replica
postgres@db:~/playground$ pg_basebackup -D replica -R -X stream -S
replica -v -d "dbname=postgres port=5432" -U postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
postgres@db:~/playground$ vim replica/postgresql.conf

--start the replica (port 5433)
postgres@db:~/playground$  pg_ctl -D replica -l replicalog start
waiting for server to start.... done
server started
postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
-------
   100
(1 row)

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


-- create a replica slot for cascading streaming replication
postgres=# select pg_create_physical_replication_slot('cascading_replica');
 pg_create_physical_replication_slot
-------------------------------------
 (cascading_replica,)
(1 row)

postgres=# \q

-- create a cascading replica off replica

postgres@db:~/playground$ pg_basebackup -D cascading_replica -R -X
stream -S cascading_replica -v -d "dbname=postgres port=5433" -U
postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/30000D8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
postgres@db:~/playground$ vim cascading_replica/postgresql.conf
postgres@db:~/playground$ pg_ctl -D cascading_replica -l creplica start
waiting for server to start.... done
server started

-- validate receiving data fine.
postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
-------
   100
(1 row)


-- stop primary
postgres@db:~/playground$ pg_ctl -D primary -l logfile stop
waiting for server to shut down.... done
server stopped


-- promote replica to new primary
postgres@db:~/playground$ psql -p 5433
psql (14beta1)
Type "help" for help.

postgres=# select pg_promote();
 pg_promote
------------
 t
(1 row)

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

postgres=# \q

--do some dml, validate changes replayed to new replica.
postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
-------
   100
(1 row)

postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
-------
   100
(1 row)

postgres@db:~/playground$ psql -p 5433 -c 'delete from t where id < 50;'
DELETE 49
postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
-------
    51
(1 row)

postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
-------
    51
(1 row)