Replication with PostgreSQL - High Avail ...

Replication with PostgreSQL - High Availability

Mar 03, 2023

PostgreSQL - probably my favourite database vendor. Listed as the world's most advanced open-source database - good enough for me as open-source is what I deal with for a living.

Immensely powerful and fun to use, PostgreSQL is an RDBMS. It has many features from B-Tree indexing for concurrency, stored functions and procedures, GSSAPI and LDAP authentication amongst many others.

In fact, I've timed its compile time on a fairly powerful VM to just over 5 minutes which is absolutely ridiculous when compared to compiling MySQL which took me just over an hour.

When considering high availability for your production environment - replication is a must. I will be going through setting this up in this article.

// Run on master

CREATE USER replicator
WITH REPLICATION
ENCRYPTED PASSWORD 'replicator';

// These to be set in the postgresql.conf on the master server

ALTER SYSTEM SET wal_level TO 'hot_standby';
ALTER SYSTEM SET archive_mode TO 'ON';
ALTER SYSTEM SET max_wal_senders TO '5';
ALTER SYSTEM SET wal_keep_segments TO '10';
ALTER SYSTEM SET listen_addresses TO '*';
ALTER SYSTEM SET hot_standby TO 'ON';
ALTER SYSTEM SET archive_command TO 'ssh IP_OF_ARCHIVE_SERVER test ! -f /home/wfg_boombox/pg_wal/%f && rsync -a %p IP_OF_ARCHIVE_SERVER:/home/whatever/pg_wal/%f' // Make sure that ssh keys have been paired

In the pg_hba.conf

host replication replicator MASTER_P/32 trust

// Restart service
$ systemctl restart postgresql.service

// Onto slave

// Backup the data dir on the slave with a mv

// Run this on the slave
$ pg_basebackup -h MASTER_IP -U replicator -p 5432 -D $PGDATA -P -Xs -R


// chown -R postgres.postgres /location_of_data_from_pg_basebackup

// Start postgres on the slave
$ systemctl start postgresql.service

//Verify the recovery.conf on the slave
$ cat $PGDATA/recovery.conf


//Verify replication from the master

select * from pg_stat_replication;

That's basically all for today. Feel free to comment if you have any questions. Thank you for reading!

Until next time.

Enjoy this post?

Buy Alexis Panopoulos a coffee

More from Alexis Panopoulos