PostgreSQL Replication on a Hostperl VPS in 2026

When PostgreSQL replication makes sense

PostgreSQL replication gives you a second copy of your database that stays close to the primary server. For a Hostperl customer, that usually means better read availability, faster recovery after a failure, and a cleaner migration path when you outgrow a single VPS.

This guide walks you through a practical primary-and-replica setup on a fresh server. You’ll connect as root, check the operating system, create a non-root admin account, install PostgreSQL, enable streaming replication, and verify that the standby follows the primary. If you’re still choosing infrastructure, a Hostperl VPS gives you the control and budget fit most teams need for this kind of workload.

We’ll stay focused on PostgreSQL 16, which remains current and well supported in 2026. The commands below assume two servers: one primary and one standby. Use the reserved example IPs exactly as shown in the shell blocks, then replace them with your own public addresses and hostnames.

Connect to the servers and check the OS

On your local computer, start with the primary server. The first connection command must be copy-pasteable, and 203.0.113.10 is only a documentation example.

ssh root@203.0.113.10

Replace 203.0.113.10 with the real public IP assigned to your Hostperl server. Keep the root session open while you finish the non-root setup.

If your provider uses a default non-root account, the equivalent first login would be:

ssh deploy@203.0.113.10

That same example IP must still be replaced with your real server IP.

On the VPS as root, detect the operating system before you install anything:

cat /etc/os-release

Look for Ubuntu, Debian, AlmaLinux, or Rocky Linux. The package and service commands differ by family, so do not skip this check.

Create a non-root admin for PostgreSQL work

You should not do routine administration as root. Create a sudo-enabled account named deploy, then test it in a second terminal before locking anything down.

On Ubuntu or Debian as root, run:

adduser deploy
usermod -aG sudo deploy

This creates the account and adds it to the sudo group. Set a strong password when prompted.

On AlmaLinux or Rocky Linux as root, run:

useradd -m -s /bin/bash deploy
passwd deploy
usermod -aG wheel deploy

That creates the account, assigns a password, and grants wheel sudo access.

Next, copy your SSH key into the new account. If you already use SSH keys, append your public key safely:

install -d -m 700 /home/deploy/.ssh
cat >> /home/deploy/.ssh/authorized_keys
chown -R deploy:deploy /home/deploy/.ssh
chmod 600 /home/deploy/.ssh/authorized_keys

Paste the public key into the terminal, then press Ctrl+D. This keeps permissions correct and avoids a common login failure.

Open a second terminal on your local computer and test the new login before changing root access:

ssh deploy@203.0.113.10
sudo -v

Replace the example IP with your real one. If sudo -v asks for a password and returns without error, the account is ready.

If you want a refresher on the admin account step, Hostperl has a separate guide on creating a non-root sudo user on Linux VPS.

Install PostgreSQL 16 and verify the service

On the VPS as the non-root sudo user, install PostgreSQL and the client tools. The package names below match current 2026 repository layouts.

Ubuntu 24.04 or Debian 12+:

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

AlmaLinux 9 or Rocky Linux 9:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server postgresql16 postgresql16-contrib

On RHEL-compatible systems, the PGDG repository provides the supported PostgreSQL 16 packages. Disabling the platform module avoids package conflicts.

Initialize the database cluster where required, then start and enable the service.

Ubuntu and Debian:

sudo systemctl status postgresql --no-pager
sudo -u postgres psql --version

AlmaLinux and Rocky Linux:

sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16
sudo systemctl status postgresql-16 --no-pager

Successful output should show the service as active. If it does not, check the journal before moving on.

Prepare the primary server for streaming replication

The primary server needs a replication role, a database listen address, and a pg_hba rule that trusts only the standby’s IP for replication traffic.

On the primary VPS as the non-root sudo user, edit PostgreSQL’s main configuration file. The location differs by distro.

Ubuntu and Debian:

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

AlmaLinux and Rocky Linux:

sudo nano /var/lib/pgsql/16/data/postgresql.conf

Add or adjust these lines:

listen_addresses = 'localhost,203.0.113.10'
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
wal_keep_size = 512MB

Replace 203.0.113.10 with the primary server’s real IP. Save the file, then test the syntax by restarting PostgreSQL after a quick config review.

Now add a replication rule to pg_hba.conf.

Ubuntu and Debian:

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

AlmaLinux and Rocky Linux:

sudo nano /var/lib/pgsql/16/data/pg_hba.conf

Append a line like this, replacing the standby address with your real standby IP:

host    replication     replicator     203.0.113.11/32     scram-sha-256

Create the replication user on the primary:

sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'ChangeThisLongPasswordNow';"

Use a long password and store it securely. Then restart PostgreSQL.

Ubuntu and Debian:

sudo systemctl restart postgresql
sudo systemctl status postgresql --no-pager

AlmaLinux and Rocky Linux:

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

If the restart fails, inspect the journal before anything else:

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

On RHEL-compatible systems, replace postgresql with postgresql-16.

Open the firewall for PostgreSQL replication

Allow PostgreSQL only from the standby server, not from the whole internet.

Ubuntu and Debian with UFW:

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

AlmaLinux and Rocky Linux with firewalld:

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="203.0.113.11/32" port port="5432" protocol="tcp" accept'
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

This keeps the database port restricted to the standby. If you use an external monitoring host, add a separate rule for that IP later.

Clone the primary onto the standby

Now build the replica from a clean copy of the primary data directory. This step rewrites the standby’s local database directory, so make sure the target server does not contain data you still need.

On the standby VPS as the non-root sudo user, stop PostgreSQL and clear the old data directory.

Ubuntu and Debian:

sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*

AlmaLinux and Rocky Linux:

sudo systemctl stop postgresql-16
sudo -u postgres rm -rf /var/lib/pgsql/16/data/*

Next, run pg_basebackup from the standby against the primary. Replace the primary IP if yours differs.

sudo -u postgres pg_basebackup -h 203.0.113.10 -p 5432 -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R

On AlmaLinux and Rocky Linux, change the destination path to /var/lib/pgsql/16/data. The -R flag writes the standby signal and primary connection info automatically.

Because the destination path differs by distro, use the correct command for your OS family:

Ubuntu and Debian:

sudo -u postgres pg_basebackup -h 203.0.113.10 -p 5432 -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R

AlmaLinux and Rocky Linux:

sudo -u postgres pg_basebackup -h 203.0.113.10 -p 5432 -U replicator -D /var/lib/pgsql/16/data -Fp -Xs -P -R

After the copy completes, start PostgreSQL on the standby.

Ubuntu and Debian:

sudo systemctl start postgresql
sudo systemctl status postgresql --no-pager

AlmaLinux and Rocky Linux:

sudo systemctl start postgresql-16
sudo systemctl status postgresql-16 --no-pager

For a recovery-focused companion, Hostperl also has PostgreSQL point-in-time recovery on a Hostperl VPS, which pairs well with replication.

Verify PostgreSQL replication from both servers

Verification should happen on both sides. First, confirm the standby is connected and replaying WAL.

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

sudo -u postgres psql -c "SELECT client_addr, state, sync_state, sent_lsn, replay_lsn FROM pg_stat_replication;"

You should see the standby IP, a streaming state, and matching WAL positions that move forward over time.

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

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
sudo -u postgres psql -c "SELECT status, receive_start_lsn, received_lsn, latest_end_lsn FROM pg_stat_wal_receiver;"

The first command should return t. The second should show an active WAL receiver.

Run a simple test write on the primary, then confirm it appears on the standby.

On the primary:

sudo -u postgres psql -c "CREATE DATABASE hostperl_test;"
sudo -u postgres psql -d hostperl_test -c "CREATE TABLE repl_check(id serial primary key, note text); INSERT INTO repl_check(note) VALUES ('replication works'); SELECT * FROM repl_check;"

On the standby:

sudo -u postgres psql -d hostperl_test -c "SELECT * FROM repl_check;"

The standby should show the same row after a short delay. If it does, your replication pipeline is functioning.

Keep it stable after the first sync

Replication is not finished when the standby connects. You still need to make sure it survives a reboot and that the service comes back cleanly.

On both servers as the non-root sudo user, confirm the service is enabled at boot and that port 5432 is listening:

sudo systemctl is-enabled postgresql
sudo ss -ltnp | grep 5432

On AlmaLinux and Rocky Linux, replace postgresql with postgresql-16. If the port is not listening, check the service status and logs before restarting.

If you are planning a larger database footprint, a managed VPS hosting plan can give you room for storage growth, backups, and a separate standby without overbuying a dedicated server too early.

For teams comparing database choices before a migration, Hostperl’s PostgreSQL vs MySQL for VPS hosting in 2026 article helps frame the operational trade-offs.

Troubleshooting the most common failures

Replication user rejected: run this on the primary to confirm the role exists and the password matches what you typed on the standby.

sudo -u postgres psql -c "\du replicator"

If it is missing or the password is wrong, recreate the role and rerun pg_basebackup on the standby.

Standby stays in recovery but never streams: check the standby logs.

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

Look for authentication errors, connection refusals, or path problems. The fix is usually a wrong IP in pg_hba.conf, an unopened firewall rule, or an incorrect data directory.

Primary not accepting remote replication connections: confirm PostgreSQL is listening on the right address.

sudo ss -ltnp | grep 5432
sudo grep -n "listen_addresses" /etc/postgresql/16/main/postgresql.conf /var/lib/pgsql/16/data/postgresql.conf 2>/dev/null

If the server is bound only to localhost, edit postgresql.conf and restart PostgreSQL again.

Firewall blocks the standby: verify the source rule or rich rule is present.

sudo ufw status numbered
sudo firewall-cmd --list-rich-rules

If the rule is missing, add it before testing again. Do not open 5432 to the whole internet just to make it work.

FAQ

Can I use PostgreSQL replication for backups?
Not by itself. Replication helps with availability, but it also copies mistakes and deletes. Keep logical or PITR backups as well.

Should the replica be writable?
No. A standard physical standby stays read-only until you promote it during failover.

Can I replicate across different Linux distributions?
Yes, if the PostgreSQL major version matches and your network rules allow the primary and standby to talk to each other.

What should I test after a reboot?
Check service status, WAL receiver activity, and one read query on the standby. Then confirm the primary still shows the standby in pg_stat_replication.