Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
cascading_replication
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment