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.