PKS Standby failover setup¶

# 1. Enter the container
docker exec -it pks-db bash

# 2. Connect to PostgreSQL
psql -U postgres

# 3. Create user rules
CREATE ROLE repuser WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456';
# Username repuser; Maximum number of links: 5; Password: 123456

# 4. View rules
\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repuser   | Replication                                               +| {}
           | 5 connections                                              |

# 5. Exit
\q
exit

# 1. Enter the folder of the primary server
cd /srv/pks/db

# 2. Add rules at the end
echo "host replication repuser 10.0.3.11/24 md5" >> pg_hba.conf

archive_mode = on               # Enable Archive Mode
archive_command = '/bin/date'   # Set archiving behavior
# The sum of the number of concurrent connections from the slave to the host
max_wal_senders = 10            
# Specifies that if the backup server needs to obtain log segment files for stream replication, pg_ The minimum size of past log file segments that can be retained in the wal directory    
wal_keep_size = 16      
# Specify a list of backup servers that support synchronous replication
synchronous_standby_names = '*'

#Using pg_ctl stop stops the database safely
docker exec -it -u postgres pks-db pg_ctl stop
docker start pks-db

# Create repl directory
mkdir /srv/pks/repl
chmod 777 /srv/pks/repl

# Stop pks
pks stop

# line 92 add :
- pks-db-repl:/var/lib/postgresql/repl

# line 180 add :
pks-db-repl:
    driver: local
    driver_opts:
    type: 'none'
    o: 'bind'
    device: '/srv/pks/repl'

# And start pks
pks start

# 1. Enter the container
docker exec -it -u postgres pks-db bash

# 2. Back up the host data to the repl folder. Here, enter the password set above: 123456
pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 10.0.3.11 -p 5432 -U repuser

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: created temporary replication slot "pg_basebackup_154"
24264/24264 kB (100%), 1/1 tablespace
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

# 3. Exit the container after the backup is completed
exit

# 1. Delete container
docker rm -f pks-db

# 2. Delete the original folder and rename repl to db
cd /srv/pks/
rm -rf db
mv repl db
cd /srv/pks/db

# 3. View configuration information
# postgresql.auto.conf will contain the information required for replication
cat postgresql.auto.conf

primary_conninfo = 'user=repuser password=123456 channel_binding=prefer host=10.0.3.11 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

# Remove the settings in docker compose file

# Restart the DB container
pks start

ps -aux | grep postgres

Main library process:
postgres: walsender repuser 172.18.0.1(52678) streaming 0/3000148
 Process from library:
postgres: walreceiver streaming 0/3000148

# Enter the primary container and switch to the postgres user
docker exec -it pks-db bash
psql -U postgres

-- Query replication information
select * from pg_stat_replication;

pid | usesysid | usename | application_name | client_addr | client_hostname...
170 16384   repuser walreceiver 172.18.0.1      52678   2021-09-29 05:57:30.471391+00...