PostgreSQL Streaming Replication on openSUSE Leap

Before you begin

Run the first command from On your local computer. Then replace 203.0.113.10 with the real public IP address of your Hostperl server.

ssh root@203.0.113.10

If your server uses a default non-root login, connect with that account instead:

ssh deploy@203.0.113.10

After you log in, keep this root session open until the new administrator account and database replication are fully verified. This tutorial uses openSUSE Leap because it is the requested least-covered OS track, and PostgreSQL supports it well for production replication setups.

This guide shows you how to build PostgreSQL streaming replication on openSUSE Leap, with a primary database, a standby replica, firewall rules, service checks, and a tested failover path. Hostperl customers often use this pattern to protect order data, application content, and reporting databases on Hostperl VPS or on a dedicated server hosting plan when write volume is higher.

Confirm the operating system and prepare the server

On the VPS as root, confirm the OS release before you install anything. openSUSE Leap uses zypper, systemd, and firewalld, so the commands in this guide are specific to that family.

cat /etc/os-release

You should see openSUSE Leap details in the output. If you do not, stop and use the matching guide for your distribution.

Update the package index, install PostgreSQL, and add the tools you need for replication and firewall management:

zypper refresh
zypper update -y
zypper install -y postgresql17-server postgresql17 firewalld rsync vim

The package name may vary slightly by Leap version. If your repository offers PostgreSQL 16 instead of 17, use the available postgresql16-server and postgresql16 packages. You can confirm the installed version later with psql --version.

Enable the database service only after the configuration is ready. For now, initialize the primary database cluster:

systemctl status postgresql || true
postgresql-setup --initdb

The initialization command creates the data directory under the PostgreSQL service path. If it reports that the cluster already exists, you can continue.

Create a non-root administrator for routine work

Do not manage the database host as root every day. Create a sudo-capable administrator called deploy, then keep root only for controlled maintenance tasks.

On the VPS as root, create the account, set the password, and grant sudo access:

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

Now create the SSH directory and copy your key safely. Replace the key text with your own public key.

install -d -m 700 -o deploy -g deploy /home/deploy/.ssh
cat > /home/deploy/.ssh/authorized_keys <<'EOF'
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIExampleKeyForDeployOnly replace-with-your-real-public-key
EOF
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys

Open a second terminal on your local computer and test the new login:

ssh deploy@203.0.113.10

When you are logged in as deploy, verify sudo works before making any root-login changes:

sudo -v
whoami
sudo whoami

You should see deploy for the first whoami and root for sudo whoami. Leave the original root session open until replication is finished and tested.

Decide the replication layout

This tutorial uses a simple, support-friendly layout:

• Primary: writes happen here, and PostgreSQL sends WAL changes to the standby.
• Standby: read-only replica that stays close to the primary in data freshness.
• Replication user: a dedicated PostgreSQL role used only for streaming.
• Firewall: only the database port is opened between the two hosts.

That layout works well for small businesses, agencies, and ecommerce sites that need a fast recovery path without jumping straight to a full cluster manager.

If you are planning a higher-write platform or a regional failover design, Hostperl can place the database on a larger dedicated server hosting build or a regional VPS arrangement, but keep the initial rollout simple until the standby is healthy.

Configure the primary PostgreSQL server

On the primary server as root, locate the PostgreSQL configuration directory and enable the service:

systemctl enable --now postgresql
sudo -iu postgres psql --version

Open the main PostgreSQL settings file. On openSUSE Leap, the path usually sits under /var/lib/pgsql/data/.

sudo -iu postgres vim /var/lib/pgsql/data/postgresql.conf

Add or adjust these settings. Replace the existing values if they are already present.

listen_addresses = '*'
port = 5432
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 256MB
hot_standby = on

Save and exit vim with :wq.

Now allow the replication user and the standby host in pg_hba.conf. Open the file:

sudo -iu postgres vim /var/lib/pgsql/data/pg_hba.conf

Add a line for the standby host using its real private or public address. This example uses 203.0.113.10 only as documentation; replace it with the actual standby IP.

host    replication     replicator     203.0.113.10/32     scram-sha-256

Save and exit, then create the replication role with a strong password:

sudo -iu postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'ChangeThisReplicationPasswordNow';"

Restart PostgreSQL and confirm it comes back cleanly:

systemctl restart postgresql
systemctl status postgresql --no-pager

Check that PostgreSQL is listening on port 5432:

ss -ltnp | grep 5432 || true

You should see postgres bound to the network interface you enabled.

Open the firewall safely

On the primary server as root, enable firewalld, then allow PostgreSQL only if you need remote access from the standby. Keep the rule tight.

systemctl enable --now firewalld
firewall-cmd --permanent --add-service=postgresql
firewall-cmd --reload
firewall-cmd --list-services

If your standby connects from a known address only, a rich rule is better than opening PostgreSQL broadly. For example:

firewall-cmd --permanent --remove-service=postgresql
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="203.0.113.10/32" port port="5432" protocol="tcp" accept'
firewall-cmd --reload

Again, replace 203.0.113.10 with the actual standby address. This sequencing removes the broad rule only after the narrow rule is in place.

Build the standby replica

On the standby server as root, install the same packages and initialize the database directory if needed:

cat /etc/os-release
zypper refresh
zypper install -y postgresql17-server postgresql17 firewalld rsync vim

Stop PostgreSQL if it started automatically, then clear the data directory so you can clone the primary cleanly.

systemctl stop postgresql
rm -rf /var/lib/pgsql/data/*

That rm -rf is destructive. Run it only on the standby, and only after you confirm the directory is empty of data you need.

Use pg_basebackup from the standby to copy the primary database. Run this as the PostgreSQL system user:

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

Replace 203.0.113.10 with the primary server IP. The -R flag writes the standby connection info automatically, which reduces hand-editing mistakes.

After the clone finishes, confirm the standby configuration file exists:

sudo -iu postgres ls -l /var/lib/pgsql/data/standby.signal /var/lib/pgsql/data/postgresql.auto.conf

If standby.signal is present, PostgreSQL knows this host should follow the primary.

Start replication and test status

On the standby server as root, enable and start PostgreSQL:

systemctl enable --now postgresql
systemctl status postgresql --no-pager

On the primary, check that the standby appears as a streaming sender. Run this as the PostgreSQL user:

sudo -iu postgres psql -c "select client_addr, state, sync_state, write_lag, flush_lag, replay_lag from pg_stat_replication;"

You should see the standby address and a streaming state. If the list is empty, review pg_hba.conf, the firewall, and the replication password.

On the standby, confirm that recovery mode is active and that it is receiving WAL:

sudo -iu postgres psql -c "select pg_is_in_recovery();"
sudo -iu postgres psql -c "select now() - pg_last_xact_replay_timestamp() as replay_delay;"

The first command should return t. The second should return a small delay, often just a few seconds on a quiet server.

Run a real smoke test

Write a test record on the primary, then confirm it appears on the standby. This proves the data path works, not just the service status.

On the primary server as root, create a simple database and table:

sudo -iu postgres psql -c "CREATE DATABASE hostperl_demo;"
sudo -iu postgres psql -d hostperl_demo -c "CREATE TABLE replication_check(id serial primary key, note text, created_at timestamptz default now());"
sudo -iu postgres psql -d hostperl_demo -c "INSERT INTO replication_check(note) VALUES ('first replicated row');"
sudo -iu postgres psql -d hostperl_demo -c "SELECT * FROM replication_check;"

Now check the same data on the standby. It should be visible there without manual copying.

sudo -iu postgres psql -d hostperl_demo -c "SELECT * FROM replication_check;"

If the row appears on the standby, PostgreSQL streaming replication is working end to end.

Protect the standby from accidental writes

The standby should remain read-only. Confirm the setting and reject test writes:

sudo -iu postgres psql -c "SHOW default_transaction_read_only;"
sudo -iu postgres psql -d hostperl_demo -c "INSERT INTO replication_check(note) VALUES ('should fail on standby');"

The insert should fail on the standby. That failure is expected. It shows the replica is not being used as a writable database by mistake.

Useful diagnostics if replication fails

If the standby does not connect, start with the PostgreSQL logs and the firewall state.

On the primary server as root:

journalctl -u postgresql -n 100 --no-pager
firewall-cmd --list-all

Look for authentication errors, address mismatch, or a port blocked by firewalld. If you see no pg_hba.conf entry, add the standby IP to pg_hba.conf and reload PostgreSQL.

sudo -iu postgres vim /var/lib/pgsql/data/pg_hba.conf
systemctl reload postgresql

If the error mentions password failure, reset the replication role password:

sudo -iu postgres psql -c "ALTER ROLE replicator WITH PASSWORD 'ChangeThisReplicationPasswordNow';"

If you need to re-clone the standby, stop PostgreSQL there, clear the data directory, and rerun pg_basebackup. That is safer than trying to patch a corrupted replica by hand.

Safe failover and rollback

PostgreSQL streaming replication does not automatically manage promotion. For a planned failover, stop writes on the primary first, then promote the standby only after the primary is no longer accepting application traffic.

On the standby server as root, promote it when you are ready:

sudo -iu postgres pg_ctl -D /var/lib/pgsql/data promote

Confirm the standby is no longer in recovery:

sudo -iu postgres psql -c "select pg_is_in_recovery();"

The result should now be f. After promotion, repoint your application to the new primary, or update the virtual IP, DNS, or load balancer you use for database access.

If you need to roll back the promotion, do not write to both sides. Stop PostgreSQL on the old primary, wipe its data directory, and reclone from the new primary. That keeps the cluster consistent.

Reboot persistence and final verification

Restart both servers one at a time and verify PostgreSQL and firewalld come back automatically.

On each server as root:

systemctl is-enabled postgresql
systemctl is-enabled firewalld
systemctl status postgresql --no-pager
systemctl status firewalld --no-pager

Then confirm the port is still open where expected:

ss -ltnp | grep 5432 || true

From the primary, run a final replication check:

sudo -iu postgres psql -c "select client_addr, state, sync_state from pg_stat_replication;"

From the standby, confirm read-only mode remains active:

sudo -iu postgres psql -c "select pg_is_in_recovery();"

That gives you a production-ready baseline for PostgreSQL streaming replication on openSUSE Leap. For teams that want help choosing the right database host size or a cleaner migration path, Hostperl VPS and dedicated server hosting both fit this workload well.

FAQ

Can I use this setup for a read-heavy website?
Yes. The primary handles writes, and the standby can support read-only reporting, analytics, or delayed failover testing.

Should I expose PostgreSQL to the public internet?
No, not unless you have a specific requirement. Restrict port 5432 to the standby host or your private network.

What if my PostgreSQL package version differs?
Use the PostgreSQL server and client package available in your Leap repositories, then keep the configuration steps the same.

Do I need a replication slot?
Not for this basic setup. Add one only if you need stronger WAL retention guarantees.

How do I rebuild a broken standby?
Stop PostgreSQL, clear the standby data directory, and run pg_basebackup again from the primary.