PostgreSQL Logical Replication on Ubuntu Server 24.04

What you are building

This tutorial sets up PostgreSQL logical replication on Ubuntu Server 24.04 so you can stream selected tables from a primary database to a standby-like subscriber server. It suits reporting replicas, staged cutovers, and low-risk migrations on a Hostperl VPS, where you want predictable recovery options without copying the entire data directory.

You will create a non-root admin user, install PostgreSQL 16 from Ubuntu packages, adjust the primary for replication, create a publication and subscription, and verify that changes flow end to end. Logical replication is not the same as physical standby replication. It moves row changes for chosen tables, not the whole cluster, so it fits application data that can work within table-level scope.

If you are migrating a customer database or preparing a read-only reporting node, this approach gives you a clean test path. It also pairs well with the operational guidance in Linux VPS setup for production and with the storage and restore discipline covered in 2026 hosting buyer checklist for VPS and cloud.

Prerequisites and topology

• Two Ubuntu Server 24.04 hosts: one primary and one subscriber.
• PostgreSQL 16 on both servers.
• A reachable private or public route between the servers on port 5432.
• Root SSH access for the first login, then a sudo user named deploy.

In the commands below, 203.0.113.10 is a documentation IP. Replace it with your real server address. Use one terminal for the primary and one for the subscriber so you do not mix the configuration steps.

Connect to the server and confirm the operating system

On your local computer:

ssh root@203.0.113.10

This is the first login command. Replace 203.0.113.10 with the public IP assigned by your hosting provider. If your provider gave you a default non-root account, connect with that account instead, then elevate with sudo after verification.

Once logged in, confirm that this is the Ubuntu host you expect.

On the VPS as root:

cat /etc/os-release

You should see Ubuntu 24.04 details. If you are not on Ubuntu 24.04, stop here and adjust the package commands to your platform.

Create a sudo user and keep root open for safety

Do not close the original root SSH session yet. Create a non-root administrator, give it sudo access, and test a second login before you change SSH policy.

On the VPS as root:

adduser deploy

Set a strong password when prompted. Then grant sudo access:

usermod -aG sudo deploy

Create the SSH directory and copy your key. Replace the key file path with your actual public key if needed.

install -d -m 700 -o deploy -g deploy /home/deploy/.ssh
cp /root/.ssh/authorized_keys /home/deploy/.ssh/authorized_keys
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys

Open a second terminal and test the new account before making any SSH hardening changes.

On your local computer:

ssh deploy@203.0.113.10

Then confirm sudo works:

On the VPS as the non-root sudo user:

sudo -v
whoami

You should be able to authenticate sudo and see deploy from whoami. Keep the original root session open until this works.

Update the host, time sync, and PostgreSQL packages

Patch the server first. Logical replication only makes sense on current hosts with synchronized clocks.

On the VPS as the non-root sudo user:

sudo apt update
sudo apt -y full-upgrade
sudo apt -y install postgresql-16 postgresql-client-16

Check the version and service state:

psql --version
systemctl status postgresql --no-pager

Ubuntu 24.04 uses systemd, and PostgreSQL cluster management is handled by the package scripts. You should see PostgreSQL 16 running or ready to start after install.

If you are standardizing server setup across multiple hosts, Hostperl VPS is a good fit for this pattern because you can build a primary, a subscriber, and a backup node without changing the deployment model. That also makes migration rehearsals easier than on a cramped shared platform.

Prepare the primary server for logical replication

Logical replication needs a few PostgreSQL settings on the primary. On Ubuntu, the cleanest route is to edit the active cluster configuration files directly.

On the primary VPS as the non-root sudo user:

sudo -iu postgres psql -c "SHOW config_file;"
sudo -iu postgres psql -c "SHOW hba_file;"

Open the main PostgreSQL configuration file. On Ubuntu 24.04 this is typically under /etc/postgresql/16/main/postgresql.conf.

On the primary VPS as the non-root sudo user:

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

Add or adjust these lines:

listen_addresses = '*'
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
max_worker_processes = 10
max_logical_replication_workers = 10
max_sync_workers_per_subscription = 2

Save and exit with Ctrl+O, Enter, and Ctrl+X. Then open the host-based authentication file.

On the primary VPS as the non-root sudo user:

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

Add a line that allows the subscriber to connect. Replace the sample subscriber network with your actual subscriber IP or subnet.

host    replication     repl_user     203.0.113.20/32     scram-sha-256
host    all              repl_user     203.0.113.20/32     scram-sha-256

If the subscriber lives on a private network, use that private address instead of the public sample. Save the file, then restart PostgreSQL after a syntax-safe check.

On the primary VPS as the non-root sudo user:

sudo pg_ctlcluster 16 main restart
sudo systemctl status postgresql --no-pager

Restart is required here because wal_level takes effect only after a restart. If PostgreSQL fails to come up, inspect the logs immediately:

sudo journalctl -u postgresql -n 50 --no-pager

Create the replication role and a sample database

Now create a dedicated role for replication and a database for the test table. Use a long password and do not reuse application credentials.

On the primary VPS as the non-root sudo user:

sudo -iu postgres psql

At the psql prompt, run:

CREATE ROLE repl_user WITH LOGIN REPLICATION PASSWORD 'Use-A-Long-Random-Password-Here';
CREATE DATABASE appdb;
\c appdb
CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    order_ref text NOT NULL,
    status text NOT NULL DEFAULT 'new',
    created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO orders (order_ref, status) VALUES ('ORD-1001', 'new');

Then exit:

\q

Logical replication works through publications. Create one for the table you want to replicate.

On the primary VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb -c "CREATE PUBLICATION app_pub FOR TABLE orders;"

Check that the publication exists:

sudo -iu postgres psql -d appdb -c "\dRp+"

Open the firewall without exposing more than needed

On Ubuntu, use UFW and allow only the subscriber source if possible. Add the rule before you rely on it.

On the primary VPS as the non-root sudo user:

sudo ufw allow from 203.0.113.20 to any port 5432 proto tcp
sudo ufw status verbose

If UFW is not enabled yet, enable it after you confirm SSH is allowed.

sudo ufw allow OpenSSH
sudo ufw enable

Check that port 5432 is listening only on the intended interface set by PostgreSQL.

ss -ltnp | grep 5432

Prepare the subscriber server

Log in to the second Ubuntu host and repeat the OS check. The subscriber needs PostgreSQL installed, but it does not need your application schema preloaded.

On the subscriber VPS as root:

ssh root@203.0.113.10
cat /etc/os-release

Replace the sample IP with the subscriber's real address. Then install PostgreSQL and create the same role only if your chosen authentication method needs it locally.

On the subscriber VPS as the non-root sudo user:

sudo apt update
sudo apt -y install postgresql-16 postgresql-client-16

Create the target database and table, but do not insert source data manually if you want replication to remain the source of truth.

On the subscriber VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb

At the prompt, create the same table definition only if you are using an existing-schema workflow. For a fresh logical subscription that copies data, PostgreSQL will replicate the table data after subscription creation if the structure matches.

If you are building a multi-host data path for a customer migration, this is the point where a regional managed hosting style workflow becomes useful: you can keep the source live while validating the destination before cutover.

Create the subscription and test the data flow

Run the subscription on the subscriber. Replace the sample host, database, user, and password values with your own.

On the subscriber VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb -c "CREATE SUBSCRIPTION app_sub CONNECTION 'host=203.0.113.10 port=5432 dbname=appdb user=repl_user password=Use-A-Long-Random-Password-Here sslmode=prefer' PUBLICATION app_pub WITH (copy_data = true);"

Confirm the subscription status:

sudo -iu postgres psql -d appdb -c "SELECT subname, subenabled, subsynccommit FROM pg_subscription;"

Now test with a write on the primary and a read on the subscriber.

On the primary VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb -c "INSERT INTO orders (order_ref, status) VALUES ('ORD-1002', 'packed');"

On the subscriber VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb -c "SELECT id, order_ref, status, created_at FROM orders ORDER BY id;"

You should see both rows. If the second row appears on the subscriber, logical replication is working.

Validate logs, slots, and restart behavior

Replication failures usually show up first in the logs or slot status. Check both sides before you call the setup complete.

On the primary VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"
sudo journalctl -u postgresql -n 50 --no-pager

On the subscriber VPS as the non-root sudo user:

sudo journalctl -u postgresql -n 50 --no-pager
sudo systemctl is-enabled postgresql
sudo systemctl is-active postgresql

Reboot the hosts one at a time if you want persistence proof. After each reboot, confirm PostgreSQL comes back and the subscription resumes.

On each VPS as the non-root sudo user:

sudo reboot

After reconnecting, repeat:

systemctl status postgresql --no-pager
sudo -iu postgres psql -d appdb -c "SELECT subname, subenabled FROM pg_subscription;"

Common failures and how to fix them

Replication connection refused. Check whether PostgreSQL is listening on the primary and whether the firewall allows the subscriber source.

ss -ltnp | grep 5432
sudo ufw status verbose

If the service is not listening on the right address, confirm listen_addresses = '*' in postgresql.conf and restart PostgreSQL.

Authentication failed for user repl_user. Check pg_hba.conf and the password.

sudo tail -n 30 /etc/postgresql/16/main/pg_hba.conf
sudo -iu postgres psql -c "ALTER ROLE repl_user PASSWORD 'Use-A-Long-Random-Password-Here';"

Subscription is created but rows do not appear. Confirm the publication includes the table and that the table has a primary key or replica identity suitable for updates and deletes.

sudo -iu postgres psql -d appdb -c "\dRp+"
sudo -iu postgres psql -d appdb -c "\d+ orders"

PostgreSQL fails after config edits. Check the journal for a syntax or permission error, then correct the file and restart again.

sudo journalctl -u postgresql -n 80 --no-pager
sudo nano /etc/postgresql/16/main/postgresql.conf

Rollback and cleanup

If you need to back out the test, remove the subscription first, then the publication, and finally the replication role if nothing else uses it. That keeps the source database clean.

On the subscriber VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb -c "DROP SUBSCRIPTION IF EXISTS app_sub;"

On the primary VPS as the non-root sudo user:

sudo -iu postgres psql -d appdb -c "DROP PUBLICATION IF EXISTS app_pub;"
sudo -iu postgres psql -c "DROP ROLE IF EXISTS repl_user;"

If you no longer need the test firewall rule, remove it after you confirm nothing else depends on it.

sudo ufw delete allow from 203.0.113.20 to any port 5432 proto tcp

FAQ

Is logical replication the same as backup?

No. It is a live data stream. You still need backups and restore tests for point-in-time recovery.

Can I replicate every table automatically?

Yes, but you must add the tables to the publication. For production, start with the tables you actually need.

Does this work for a full failover clone?

Not by itself. Logical replication is useful for selective data movement and migration, but physical standby replication is better for exact server failover.

What should I test after a reboot?

Check the PostgreSQL service, the subscription status, and one write on the primary followed by one read on the subscriber.