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
$> sudo su - postgres -c psql
Connect to database
psql> \c testdb
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.
listen_addresses = '*'
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.
- Database should be created
- All tables should be created
- All tables should be blank, i.e. have 0 records
- All tables should have a
primary_keycolumn. In case there are some tables which don’t have a
ALTER TABLEcommand 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
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
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.
This was the first time I was setting up a replica of a primary database, so I had to refer to many sites