Postgres Logical Replication Made Easy

#postgres #unix

This doesn't seem to be well documented and I've previously made a post on fedi about it, but that's not as easy to find. Here's how I setup replication between two Postgres servers. It will replicate all the data so you do not need to do any data transfers to pre-seed the server receiving the data first.

On the source Postgres server:

db=# CREATE USER replication_user WITH REPLICATION PASSWORD 'replication_password';

Don't forget to change pg_hba.conf to allow the connection for that user to the desired database or with whatever scope you desire.

# pg_hba.conf
host    db     replication_user      10.0.0.4/32            scram-sha-256

The postgresql.conf also needs a few changes for wal replication.

# postgresql.conf
# 10 is default suggested, dunno if more ore less is better for avg use case
wal_level = 'logical'
max_replication_slots = '10'
max_wal_senders = '10'

Create a publication to publish the data. We're going to do the whole database, but you could target specific tables, though you should know what you're doing and if there are any relationships that would make this break. Ensure you're connected to the database when you execute this query, not the default/internal postgres database.

db=# CREATE PUBLICATION your_publication_name FOR ALL TABLES;

Now go to the server that will be receiving the data. Run this query to setup the subscription to the publication.

postgres=# CREATE SUBSCRIPTION your_subscription_name
CONNECTION 'dbname=target_db_name host=target_hostname port=5432 user=replication_user password=replication_password'
PUBLICATION your_publication_name;

That's it. It should begin syncing all the data. You can see the status by checking pg_subscription:

postgres=# SELECT * FROM pg_subscription;

I haven't evaluated how to monitor this but I'm sure you can figure out how to do it best for your environment. More details in the docs.