Quick guide for easy postgres database replication

Replication is one of the most important aspects of achieving high availability. Any unexpected failures on a database server could cause downtime for an application or a business. Configuring replication is thus the right practice to ensure that there is an option to perform fail-over in the event of disasters.

There are two types of replication methods that are built into the community PostgreSQL source, namely streaming replication and logical replication. in this article we will focus on the streaming replication because it is by far the method that is aimed at solving high availability concerns in production.

Setting up streaming replication in PostgreSQL

We can set up streaming replication within two instances running on different ports on the same server. But that doesn't solve the purpose of high availability. So, it is recommended to build replication between servers that are geographically distributed.

In order to get started with the steps being discussed, we need two servers (a master and a standby)

We will set up the replication using the following steps:

Step 1. Create a replication user on the master:

$ psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED
PASSWORD 'secret'

Step 2. Add necessary entries to the pg_hba.conf file of the master:

$ echo "host replication replicator <slave_ip_address>/32 md5" >>
$PGDATA/pg_hba.conf
$ psql -c "select pg_reload_conf()"

Step 3. On the master, validate the parameters required to set up replication. The best way is as follows:

$ psql -c "select name, setting from pg_settings where name IN
('listen_addresses','archive_mode','archive_command','wal_keep_segm
ents','restore_command')"

Step 4. Modify parameters that require modification on the master:

$ psql -c "ALTER SYSTEM SET listen_addresses TO '*'";
$ psql -c "ALTER SYSTEM SET archive_mode TO 'ON'";
$ psql -c "ALTER SYSTEM SET archive_command TO 'cp %p
/archives/%f'";
$ psql -c "ALTER SYSTEM SET restore_command TO 'cp /archives/%f
%p'";
$ psql -c "ALTER SYSTEM SET wal_keep_segments TO '100'";

Step 5. Restart the master if required or reload it, depending on the parameters modified:

$ pg_ctl -D $PGDATA restart -mf

$ pg_ctl -d $PGDATA reload

Step 6. Run pg_basebackup from the standby to take a backup of the master:

$ pg_basebackup -h <master_ip> -U replicator -p 5432 -D $PGDATA -Fp
-Xs -P -R

Step 7. Validate the mandatory parameters needed for replication on the standby:

$ cat $PGDATA/postgresql.auto.conf

Step 8. Add the primary_conninfo setting and standby.signal if they do not exist:

$ echo "primary_conninfo = 'user=replicator password=secret
host=<master_IP> port=5432 sslmode=prefer sslcompression=0
gssencmode=prefer krbsrvname=postgres target_session_attrs=any'" >>
$PGDATA/postgresql.auto.conf

$ echo "standby_mode = 'ON'" >> $PGDATA/postgresql.auto.conf

$ touch $PGDATA/standby.signal

Step 9. Start the standby server or reload the configuration, depending on the parameter changes made:

$ pg_ctl -D $PGDATA start

$ sudo systemctl start postgresql-14