A database replica is able to provide another layer of protection in case of the main database server going down.

Postgres has this capability out of the box.

In this case the requirement was to create a replica on a separate server. (It’s also possible to create it on the same server where Postgres is running).

The replica maintains an exact copy of the main database (called Primary) on a real-time basis.

For this, the Primary database publishes the changes as they happen asynchronously.

The Replica database then subscribes to the changes being published and updates itself.

Primary DB configuration

Connect to the primary Postgres server and get to the psql prompt.

$> sudo su - postgres -c psql

Connect to database

psql> \c testdb

Setup publication

CREATE PUBLICATION <publication name> FOR ALL TABLES;

psql> CREATE PUBLICATION clone FOR ALL TABLES;

Confirm that the publication has been added

psql> SELECT * FROM pg_catalog.pg_publication;

In case the publication needs to be removed.

DROP PUBLICATION <publication name> CASCADE;

psql> DROP PUBLICATION clone CASCADE;

Modify Postgres config files.

In postgresql.conf file

/etc/postgresql/10/main/postgresql.conf

listen_addresses = '*'

In pg_hba.conf file, add the IP address of the Replica server so that connections from that server are allowed in

# IPv4 external connections:
host    all             all             123.456.789.123/32               md5

Replica DB configuration

The Replica DB should be set up with the complete schema but with no data.

  1. Database should be created
  2. All tables should be created
  3. All tables should be blank, i.e. have 0 records
  4. All tables should have a primary_key column. In case there are some tables which don’t have a primary_key, an ALTER TABLE command has to be run on this table.
ALTER TABLE <table_name> REPLICA IDENTITY FULL;

Related post: Postgres DB migration error due to replication

IMPORTANT: If any of the tables have data due to database migrations, these records should be removed

Create a new subscription from the psql prompt.

CREATE SUBSCRIPTION <subscription name> CONNECTION 'host=<IP of PRIMARY server> port=<Port of Primary Server> password=<db password of Primary> user=<db user of Primary> dbname=<Primary DB name>' PUBLICATION <publication name>;

psql> CREATE SUBSCRIPTION replicant CONNECTION 'host=12.345.678.90 port=5432 password=dbpassword user=dbuser dbname=testdb' PUBLICATION clone;

The subscription details are stored in the pg_catalog.pg_subscription and can be viewed using

psql> select * from pg_catalog.pg_subscription;

As soon as the subscription is added, the replication will begin.

Check the subscription status

psql> select * from pg_catalog.pg_stat_subscription;

Verify that replication is happening by checking the Postgres log

$> tail -f /var/log/postgresql/postgresql-10-main.log

Notes

As mentioned in the Postgres documentation on subscription –

Unlike most system catalogs, pg_subscription is shared across all databases of a cluster: There is only one copy of pg_subscription per cluster, not one per database.

References

This was the first time I was setting up a replica of a primary database, so I had to refer to many sites

How To Set Up Logical Replication with PostgreSQL 10 on Ubuntu 18.04

Managing PostgreSQL Logical Replication

Using PostgreSQL logical replication, how do you know that the subscriber is caught up?

Additional reading

PostgreSQL Documentation

Continuous Archiving and Point-in-Time Recovery

Chapter 31. Logical Replication

CitrusData
Three Approaches to PostgreSQL Replication and Backup