Connectivity
On the primary, it is critical that the postgres port and IP address only be available from the replica IP address. Ideally the replication would be over a private IP.
Primary | 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 are required to postgresql.conf
sudo nano /etc/postgresql/17/main/postgresql.conf |
listen_addresses = '*' - 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/17/main/pg_hba.conf |
host replication replica_user IPv4/32(replica vm IP) md5
host replication replica_user IPv6/64(replica vm IP) md5
|
sudo pg_ctlcluster 17 main restart |
Standby
Install and tune postgres
sudo pg_ctlcluster 17 main stop
rm -rf /var/lib/postgresql/17/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/
|
Check and grant permission to the data folder
ls -la /var/lib/postgresql/17/main/
chmod -R 700 /var/lib/postgresql/17/main/
chgrp -R postgres /var/lib/postgresql/17/main/
chown -R postgres /var/lib/postgresql/17/main/
|
sudo nano /var/lib/postgresql/17/main/standby.signal |
sudo nano /etc/postgresql/17/main/postgresql.conf |
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 17 main restart
|
Misc. Commands
- sudo -u postgres psql postgres
- \du ...display users
- Change password of replica_user | ALTER USER replica_user WITH PASSWORD '*******';
- Create replica user | CREATE ROLE replica_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD '*****';
- 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 these comands.
# select pg_drop_replication_slot('replica-slot-name');
Promote a failover ( read-only replica ) instance to read-write
sudo su - postgres
/usr/lib/postgresql/16/bin/pg_ctl promote -D /var/lib/postgresql/17/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 and may cause your hard disk to run out of space - be sure to delete unused slots.