Usefull Commands
- sudo -u postgres psql postgres
- \du ...display users
- ALTER USER replica_user WITH PASSWORD '*******'; - change password of replica_user
- CREATE ROLE replica_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD '*****'; - create replica user
- dropuser
-e; - To check what are the existing slots are available # select * from pg_replication_slots;
- And if you want to drop a slot, you can go to the master db, run this sql... # select pg_drop_replication_slot('replica-slot-name');
- Promote a failover instance to read-write On the replica sudo su - postgres /usr/lib/postgresql/16/bin/pg_ctl promote -D /var/lib/postgresql/16/main/ after running above - you should see "waiting for server to promote.... done server promoted"... exit ( exit as pg user ) sudo pg_ctlcluster 16 main restart
If you have unused replication slots on the primary the wal files accumulate up and cause your hard disk to run out of space, be sure to delete unused slots.
Connectivity
On the primary, it is critical that the postgres port and IP address only be available from the replica IP address.
ufw allow from [replica IP] to [primary IP] port 5432
Primary
Tune database for your server as per https://pgtune.leopard.in.ua/
After basic tuning, replication related changes required to postgresql.conf
sudo nano /etc/postgresql/16/main/postgresql.conflisten_addresses = '*' - or private IP if supported. wal_level = replica full_page_writes = on wal_log_hints = on max_wal_senders = 10 |
sudo pg_ctlcluster 16 main restart
Create a replica user on the master:
sudo -u postgres psql postgres
CREATE ROLE replica_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD '*****';
\q
Changes required in pg_hba.conf
Set up authentication on the primary server to allow credentialed replication connections from trusted IP.
sudo nano /etc/postgresql/16/main/pg_hba.conf - bottom of file
host replication replica_user IPv4/32(replica vm IP) md5 host replication replica_user IPv6/64(replica vm IP) md5 |
Standby
sudo pg_ctlcluster 16 main stop rm -rf /var/lib/postgresql/16/main sudo pg_basebackup -h [primary IP] -p 5432 -U [replica_username] -R -P -v -C --slot=[descriptive-slot-name] -D /var/lib/postgresql/16/main/ |
ls -la /var/lib/postgresql/16/main/ chmod -R 700 /var/lib/postgresql/16/main/ chgrp -R postgres /var/lib/postgresql/16/main/ chown -R postgres /var/lib/postgresql/16/main/ |
standby_mode = 'on' |
listen_addresses = '*' (ideally private IP only) max_connections = 300 primary_conninfo = 'host=[primary IP or host name] user=replica_username password=********' primary_slot_name = 'descriptive-slot-name' hot_standby = on max_standby_archive_delay = 30s max_standby_streaming_delay = 30s wal_receiver_create_temp_slot = on wal_receiver_status_interval = 10s hot_standby_feedback = on wal_receiver_timeout = 60s wal_retrieve_retry_interval = 5s recovery_min_apply_delay = 0 |
sudo pg_ctlcluster 16 main restart |