How to Set Up PostgreSQL Logical Replication with Publication and Subscription on Ubuntu Server

ByAkensai

Learning how to set up PostgreSQL logical replication with publication and subscription on Ubuntu Server gives you a powerful way to sync data between databases in real time. Unlike physical replication, logical replication lets you replicate specific tables. You can even replicate between different PostgreSQL versions. This makes it ideal for zero-downtime migrations, load distribution, and data warehousing.

In this tutorial, you’ll configure two Ubuntu servers , a publisher and a subscriber. You’ll create a publication on the source database and a subscription on the target. By the end, your databases will stay in sync automatically. This guide covers installation, configuration, firewall rules, and testing. It’s written for system administrators and developers who are comfortable with the Linux command line.

Prerequisites for PostgreSQL Logical Replication on Ubuntu Server

Before you start, make sure you have the following in place.

Two Ubuntu servers: Both should run Ubuntu 22.04 LTS or 20.04 LTS. One acts as the publisher. The other acts as the subscriber.

PostgreSQL installed on both: This guide uses PostgreSQL 15. You can check the official PostgreSQL logical replication documentation for version-specific details.

Root or sudo access: You’ll need elevated privileges on both servers.

Open network access: Port 5432 must be open between the two servers.

A matching database and table: The table schema must exist on the subscriber before replication starts.

Estimated time: 30–45 minutes.

Assumed knowledge: Basic Linux command line usage, familiarity with PostgreSQL, and understanding of database concepts like tables and users.

How to Set Up PostgreSQL Logical Replication: Publisher Configuration

You might also find this useful: Setup Pivpn Server on Ubuntu and Connect on Windows

These steps configure the publisher server , the source of your data.

Step 1: Install PostgreSQL on the publisher

Run these commands on your publisher server:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Confirm the service is running:

sudo systemctl status postgresql

Step 2: Set the WAL level to logical

PostgreSQL needs its Write-Ahead Logging level set to logical to support this replication method. Open the config file:

sudo nano /etc/postgresql/15/main/postgresql.conf

Find and update these lines:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Save the file and restart PostgreSQL:

sudo systemctl restart postgresql

Step 3: Create a replication user

Switch to the postgres system user:

sudo -i -u postgres

Open the PostgreSQL prompt and create a dedicated user:

psql
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'StrongPassword123';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

Using a dedicated user keeps your setup secure. Don’t use the default postgres superuser for replication.

Step 4: Allow the subscriber to connect

Edit the host-based authentication file:

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add this line at the bottom. Replace SUBSCRIBER_IP with your subscriber’s actual IP address:

host    replication     replicator      SUBSCRIBER_IP/32        md5

Reload PostgreSQL to apply the change:

sudo systemctl reload postgresql

Step 5: Create a publication

Log back into the PostgreSQL prompt as the postgres user. Switch to your target database first. This example uses a database called appdb:

psql -d appdb
CREATE PUBLICATION my_publication FOR TABLE users, orders;

This publishes only the users and orders tables. You can also publish all tables with FOR ALL TABLES.

Step 6: Open the firewall port

Allow inbound connections on port 5432 from your subscriber:

sudo ufw allow from SUBSCRIBER_IP to any port 5432
sudo ufw reload

How to Configure the Subscription on the Subscriber Server

Now switch to your subscriber server. These steps pull data from the publisher.

Step 7: Install PostgreSQL on the subscriber

Run the same install commands as on the publisher:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Step 8: Create the matching database and tables

The subscriber needs the same schema before the subscription starts. Log into psql:

sudo -i -u postgres
psql

Create the database and tables:

CREATE DATABASE appdb;
c appdb
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT, total NUMERIC);

The column names and types must match the publisher exactly. Replication won’t start if the schemas don’t align.

Step 9: Create the subscription

Still inside the appdb database on the subscriber, run this command. Replace PUBLISHER_IP with your publisher’s actual IP:

CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=PUBLISHER_IP port=5432 dbname=appdb user=replicator password=StrongPassword123'
PUBLICATION my_publication;

PostgreSQL will connect to the publisher and begin syncing data immediately. You can check the CREATE SUBSCRIPTION reference page for all available options.

Step 10: Verify replication is working

On the publisher, insert a test row:

psql -d appdb
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

On the subscriber, check if it arrived:

psql -d appdb
SELECT  FROM users;

You should see Alice’s row. If you do, replication is working correctly.

Troubleshooting Common PostgreSQL Replication Problems

Even with careful setup, things can go wrong. Here are the most common issues.

Subscription stuck in “connecting” state

Check that port 5432 is open on the publisher’s firewall. Verify the pg_hba.conf entry includes the correct subscriber IP. Test connectivity directly:

psql -h PUBLISHER_IP -U replicator -d appdb

Schema mismatch errors

If you see errors like ERROR: logical replication target relation does not exist, the table doesn’t exist on the subscriber. Re-create the schema manually and then refresh the subscription:

ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

Replication slot not found

If the publisher was restarted without the subscriber connected, the slot may have been dropped. Re-create the subscription from scratch on the subscriber side.

Data not syncing after schema changes

Logical replication doesn’t replicate DDL changes like ALTER TABLE. You must apply schema changes manually on both servers. Then refresh the subscription.

Check replication status

On the publisher, you can monitor active replication slots:

SELECT  FROM pg_replication_slots;

On the subscriber, check subscription status:

SELECT  FROM pg_stat_subscription;

These views give you real-time insight into lag, connection state, and errors.

You’ve now completed the full process of how to set up PostgreSQL logical replication with publication and subscription on Ubuntu Server. Your publisher streams changes to the subscriber in real time. This setup works well for read scaling, live backups, and staged migrations. As a next step, consider setting up monitoring with pgBadger to track query performance and replication health. You can also explore cascading subscriptions or row filters to control exactly which data gets replicated. With this foundation in place, your PostgreSQL infrastructure is ready to handle more demanding workloads.

Similar Posts