TutorialAll levels

PostgreSQL on a VPS: Secure Remote Access and Automated Backups

Reach PostgreSQL from another machine without exposing it to the internet, and run backups you have actually restored. Tested on Ubuntu 24.04.

Stephen NdegwaPublished Jul 17, 2026Last updated Jul 17, 202636 min read

Your database is running on a VPS. Now you need two things nobody hands you: a way to reach it from another machine, and backups that happen without you.

Search for the first one and you will be told to set listen_addresses = '*', add host all all 0.0.0.0/0 md5 to pg_hba.conf, run ufw allow 5432/tcp, and restart. That works. It also puts your database on the public internet, guarded by one password, using an authentication method PostgreSQL deprecated years ago. The advice to restrict it usually arrives further down the page, in a “best practices” section the reader has already scrolled past on their way to a working connection.

This guide does it the other way round. The procedure is safe by default: the connection you end up with does not require an open port at all. If you genuinely need one open, you will open exactly one, to exactly one address, with TLS that actually authenticates the server — and you will have tested each lock by trying the door from a second machine.

Then backups: a script, a timer that fires without you, and the part most guides skip entirely — a restore drill that destroys the database and proves the data came back.

Every command and output block below was captured from a lab run on two Ubuntu 24.04 VPS instances. Where something failed, you get the failure and the fix that actually worked — several of the sharpest lessons here came from things that broke.

What this guide is not

It is not an installation guide. It assumes PostgreSQL is already running on your VPS and picks up from there.

It also stops short of point-in-time recovery. pg_dump gives you last night’s database, not the database as it was 20 minutes ago. That is a real limitation, and the section near the end says plainly when it will not be enough for you — but WAL archiving, pg_basebackup and replication are separate topics and this guide does not half-teach them.

Conventions

Two machines. Everything hinges on knowing which one you are typing into, so every command block is labelled # on the db host or # on the client, and the two boxes have deliberately different usernames and hostnames so the prompts never look alike.

Thing	This guide uses	You substitute
Database server	user `dbadmin`, hostname `pg-db`, private IP `172.31.26.131`	your db VPS’s sudo username and IP — `dbadmin` appears in a sudoers file and a systemd unit, so change it everywhere
Client machine	user `appdev`, hostname `app-client`, private IP `172.31.26.149`	the machine that talks to the db
DNS name of the db host	`db.example.com`	a name you control (we set one up below)
PostgreSQL major version	`16` (paths like `/etc/postgresql/16/main/`)	your version — check with `psql --version`
Database / app role	`appdb` / `appuser`	your own
Backup role	`backupuser`	keep as-is
App role password	`ReplaceMe_Str0ng_Pw`	a generated password (below)
Backup role password	`B4ckup_Str0ng_Pw`	a different generated password
Backup directory	`/var/backups/postgresql`	keep as-is

One note on the client psql commands: each will prompt Password: for appuser. The lab passed the password in the PGPASSWORD environment variable, so the prompt does not appear in the captured output — when you run them, type the password you set.

Before you start

You need PostgreSQL running on the db host and a sudo-capable non-root user on both machines. If your VPS is fresh, Initial Ubuntu 24.04 Server Setup: Users, SSH, Firewall covers the user, SSH and firewall groundwork this guide builds on.

Nothing here runs as root. Every command is what a normal sudo user types, sudo included — that matters, because a procedure written from a root shell quietly omits the sudo your box will demand.

Check what you have:

# on the db host
psql --version
systemctl is-active postgresql

psql (PostgreSQL) 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1)
active

If psql --version reports 13 or older, the auth advice below still applies, but check your version’s docs before copying config verbatim. Adjust the 16 in every path to match.

Install the client tools on the client machine. Everything labelled # on the client needs psql, and a fresh box does not have it:

# on the client
sudo apt update && sudo apt install -y postgresql-client
psql --version

psql (PostgreSQL) 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1)

Give the db host a name the client can resolve. You need one for two reasons: TLS certificates are issued to names, not IPs, and sslmode=verify-full checks the name you connected to against the certificate. The production answer is a DNS A record pointing at your db host. If you do not have one, an /etc/hosts entry on the client works exactly the same way for everything in this guide, because libpq compares the certificate against the name you typed:

# on the client — substitute your db host's IP
echo "172.31.26.131  db.example.com" | sudo tee -a /etc/hosts
getent hosts db.example.com

172.31.26.131  db.example.com
172.31.26.131   db.example.com

Set up SSH from the client to the db host. Part 2’s tunnel needs it, and so does copying the CA certificate later. On the client, create a key if you do not have one:

# on the client
ssh-keygen -t ed25519 -C "appdev@app-client"
cat ~/.ssh/id_ed25519.pub

Add that public key to dbadmin‘s authorized keys on the db host:

# on the db host — paste the client's public key in place of ssh-ed25519 AAAA...
mkdir -p ~/.ssh && chmod 700 ~/.ssh
echo "ssh-ed25519 AAAA... appdev@app-client" >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys

Then verify from the client — it should print the db host’s hostname and return you to your own shell:

# on the client
ssh [email protected] hostname

pg-db

If that prompts about authenticity the first time, that is the host key — type yes.

Set up the database, the role, and some data

So the rest of the guide has something real to connect to, back up, and destroy. If you already have a database and an application role, read this section for the two grants that matter and skip the seed data.

The role, and why scram-sha-256. Both top-ranking guides for this keyword still tell you to use md5. It has been superseded by scram-sha-256, the default since PostgreSQL 14. Modern installs already do the right thing, and you can confirm rather than trust:

# on the db host
sudo -u postgres psql -c "SHOW password_encryption;"

 password_encryption 
---------------------
 scram-sha-256
(1 row)

If that says md5, set password_encryption = 'scram-sha-256', reload, and then reset every password — existing hashes are not converted, so a password set under md5 stays md5 until it is set again.

Generate two real passwords rather than inventing them, one for each role:

# on the db host
openssl rand -base64 24

xHBvfxHdqK73uDCZnb3pjNC2lBmQVZaI

Run that twice. Use the first value wherever this guide shows ReplaceMe_Str0ng_Pw and the second wherever it shows B4ckup_Str0ng_Pw.

# on the db host
sudo -u postgres createdb appdb
sudo -u postgres psql <<'SQL'
CREATE ROLE appuser LOGIN PASSWORD 'ReplaceMe_Str0ng_Pw';
CREATE ROLE backupuser LOGIN PASSWORD 'B4ckup_Str0ng_Pw';
GRANT pg_read_all_data TO backupuser;
SQL

appuser is your application. backupuser reads everything and writes nothing — pg_dump needs to read every table but never needs to change one, so it does not get your app’s role and certainly not a superuser.

Now the grants, on the database itself:

# on the db host
sudo -u postgres psql -d appdb <<'SQL'
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO appuser;
GRANT CONNECT ON DATABASE appdb TO backupuser;
GRANT USAGE ON SCHEMA public TO appuser;
SQL

REVOKE ALL ON DATABASE appdb FROM PUBLIC is the line that matters most: without it, every role on the server can connect to your database, including ones you create later for unrelated reasons. Remember it — Part 5 shows how a restore silently throws it away.

The two GRANT CONNECT lines are the necessary consequence: once PUBLIC is revoked, every role needs CONNECT granted explicitly. pg_read_all_data grants reads on every table but it does not grant CONNECT, so a backup role without that line fails with an error that says nothing about backups:

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  permission denied for database "appdb"
DETAIL:  User does not have CONNECT privilege.

That is the real error from the lab’s first attempt, and it costs ten confusing minutes if you have not seen it.

Some data to protect. Create a small schema so the backup and restore sections have something to compare:

# on the db host
cat > /tmp/seed.sql <<'SQL'
CREATE TABLE customers (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  full_name  text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  amount_kes  numeric(12,2) NOT NULL,
  placed_at   timestamptz NOT NULL DEFAULT now()
);
INSERT INTO customers (email, full_name)
SELECT 'user' || g || '@example.com', 'Customer ' || g FROM generate_series(1,5000) g;
INSERT INTO orders (customer_id, amount_kes, placed_at)
SELECT (random()*4999)::int + 1, round((random()*50000)::numeric, 2),
       now() - (random()*365) * interval '1 day'
FROM generate_series(1,20000);
SQL
sudo -u postgres psql -q -d appdb -f /tmp/seed.sql
sudo -u postgres psql -d appdb -c "SELECT (SELECT count(*) FROM customers) AS customers, (SELECT count(*) FROM orders) AS orders, pg_size_pretty(pg_database_size('appdb')) AS size;"

 customers | orders | size  
-----------+--------+-------
      5000 |  20000 | 10 MB
(1 row)

Give appuser the table privileges an application needs:

# on the db host
sudo -u postgres psql -d appdb <<'SQL'
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;
SQL

Two checks worth doing now. appuser should have no role attributes at all, and its password should be stored as a SCRAM verifier:

# on the db host
sudo -u postgres psql -c "\du appuser"
sudo -u postgres psql -c "SELECT rolname, substring(rolpassword from 1 for 14) AS pw_prefix FROM pg_authid WHERE rolname='appuser';"

     List of roles
 Role name | Attributes 
-----------+------------
 appuser   | 

 rolname |   pw_prefix    
---------+----------------
 appuser | SCRAM-SHA-256$
(1 row)

An empty Attributes column is the goal: no Superuser, no Create role, no Create DB. Compare that with the CREATE ROLE ... LOGIN SUPERUSER you will find in popular snippets for this task, which hands a remote client the entire server.

Part 1: The three gates

This is the part the top search results skip, and it is why “postgresql remote access” has such a long tail of confused follow-ups. Three independent things stand between a remote client and your database. Opening all three at once gets you a connection and teaches you nothing; you then cannot tighten any one of them without fear.

listen_addresses — which network interfaces the server binds a socket to. If Postgres is not listening on an interface, nothing can connect on it, no matter what else you configure.
pg_hba.conf — host-based authentication. Given a connection that arrived, which client addresses, users and databases are allowed, and how they must authenticate.
The firewall — whether the packet reaches Postgres at all.

They fail differently, and that is the most useful diagnostic you will get:

Gate that is closed	What the client sees
`listen_addresses`	`Connection refused` — instant
`pg_hba.conf`	`FATAL: no pg_hba.conf entry for host ...` — instant
Firewall	`timeout expired` — hangs, then gives up

If you remember one thing from this guide, remember that table: refused means nothing is listening, no pg_hba.conf entry means you reached Postgres and it declined you, and a timeout means your packet died in a firewall. Each was produced by an actual blocked connection from the second machine, below.

Gate 1: listen_addresses

A default install listens only on localhost:

# on the db host
sudo -u postgres psql -c "SHOW listen_addresses;"
ss -tlnp | grep 5432

 listen_addresses 
------------------
 localhost
(1 row)

LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*

127.0.0.1:5432 is the whole story: the socket exists only on the loopback interface. From the other machine:

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full connect_timeout=10" -c "SELECT 1;"

psql: error: connection to server at "db.example.com" (172.31.26.131), port 5432 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?

That is gate 1 doing its job. Note what it is not: it is not authentication. It is a socket that does not exist.

listen_addresses is also the one setting here that a reload will not apply:

# on the db host
sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = 'localhost,172.31.26.131';"
sudo systemctl reload postgresql
sudo -u postgres psql -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name='listen_addresses';"

       name       |  setting  | pending_restart 
------------------+-----------+-----------------
 listen_addresses | localhost | t
(1 row)

pending_restart = t is PostgreSQL telling you the new value is on disk and being ignored until a restart. The reload changed nothing.

# on the db host
sudo systemctl restart postgresql
ss -tlnp | grep 5432

LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*          
LISTEN 0      200    172.31.26.131:5432      0.0.0.0:*

Two sockets, exactly as asked: loopback plus one private address. Bind to the address the client actually reaches, and keep localhost in the list — local tooling and the backup script in Part 4 connect over loopback, and dropping it breaks them. Never use '*' unless you have a real reason: it binds every interface, including any public one.

Gate 2: pg_hba.conf

Try the client again. The socket now exists on an address it can reach, and nothing else has changed:

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full connect_timeout=10" -c "SELECT 1;"

psql: error: connection to server at "db.example.com" (172.31.26.131), port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.31.26.149", user "appuser", database "appdb", SSL encryption

A completely different failure. The TCP connection succeeded, Postgres read the request, and Postgres itself said no — because pg_hba.conf has no rule matching that combination of client IP, user and database. Opening listen_addresses granted access to nothing.

Two things worth knowing before you edit that file. Rules are matched top to bottom, first match wins — a permissive line above a restrictive one silently defeats it. And unlike listen_addresses, changes take effect on a reload, which does not drop existing connections. You never need to restart Postgres to change who may connect.

Gate 3: the firewall

The firewall sits in front of both, and its symptom is silence: the packet is dropped, so the client waits for a reply that never comes.

Enable UFW on the db host. Allow SSH before you enable it — a firewall that defaults to deny incoming will otherwise lock you out of your own VPS:

# on the db host
sudo ufw allow OpenSSH
sudo ufw enable

Rules updated
Rules updated (v6)
Firewall is active and enabled on system startup

Now a rule that allows 5432 from some other address — standing in for the case where you got the address wrong:

# on the db host
sudo ufw allow from 172.31.99.99 to any port 5432 proto tcp
sudo ufw status | grep 5432

5432/tcp                   ALLOW       172.31.99.99

# on the client
time psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full connect_timeout=8" -c "SELECT 1;"

psql: error: connection to server at "db.example.com" (172.31.26.131), port 5432 failed: timeout expired

real    0m8.044s

Eight seconds of nothing, then a timeout — the packet was dropped, so there was nobody to send back a refusal. We tested this rather than assuming it: UFW does not always filter what people expect it to (published container ports are a well-known exception), so the rule was set to one address and the connection from a different address confirmed dead before we trusted it. How to Set Up a Firewall with UFW on Ubuntu 24.04 covers UFW itself in depth.

Check yours properly. ufw status verbose shows the default policy and whether it is even active — do not settle for grepping for a rule, because a rule on an inactive firewall filters nothing:

# on the db host
sudo ufw status verbose

Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
22/tcp (OpenSSH)           ALLOW IN    Anywhere                  
5432/tcp                   ALLOW IN    172.31.99.99              
22/tcp (OpenSSH (v6))      ALLOW IN    Anywhere (v6)

Status: active and Default: deny (incoming) are the two lines that matter.

Part 2: The best remote access is no open port

Now that you know what the gates do, here is the recommendation: for most people, on most VPSes, do not open 5432 at all. Leave listen_addresses on localhost, leave the firewall shut, and reach the database through an SSH tunnel using the key you set up earlier.

You get a database that is not reachable from the internet even if you misconfigure pg_hba.conf, even if a password leaks, even if a future Postgres CVE lands. There is nothing listening for an attacker to talk to. The encryption is SSH’s, already authenticated by the host key you accepted, so there are no certificates to manage.

The trade is real: the tunnel is a process that must be running, one extra hop of latency, and it is a poor fit when many hosts need the database — that is when you graduate to a private network or the exposed-port setup in Part 3.

Close everything first:

# on the db host
sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = 'localhost';"
sudo systemctl restart postgresql
sudo ufw delete allow from 172.31.99.99 to any port 5432 proto tcp
sudo ufw status verbose

Confirm from the client that the database is genuinely unreachable, then tunnel to it anyway:

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full connect_timeout=8" -tAc "SELECT 1"

psql: error: connection to server at "db.example.com" (172.31.26.131), port 5432 failed: timeout expired

# on the client — -f -N = background, no remote shell; forward local 5433 to the db's localhost:5432
ssh -f -N -L 5433:localhost:5432 [email protected]
ss -tlnH | grep 5433
psql "host=localhost port=5433 user=appuser dbname=appdb sslmode=disable" \
  -tAc "SELECT 'tunnelled: ' || count(*) || ' customers' FROM customers;"

LISTEN 0      128        127.0.0.1:5433 0.0.0.0:*
LISTEN 0      128            [::1]:5433    [::]:*
tunnelled: 5000 customers

The port is closed, the direct connection times out, and the database answers anyway. Your application connects to localhost:5433 and never knows the difference. sslmode=disable is correct here and not a compromise: SSH is already encrypting the whole path, and Postgres sees a genuine loopback connection.

If ss prints nothing, the tunnel did not open — run the same ssh command without -f -N to see the error. Close the tunnel with pkill -f "5433:localhost:5432".

For anything permanent, run the tunnel as a systemd unit or use autossh so it survives reboots and network blips, and give the tunnel account its own restricted SSH key — How to Secure SSH: Keys, Ports, Fail2ban, and 2FA on Ubuntu 24.04 covers key hardening.

If that fits your setup, you are done with the access half. Skip to Part 4 for backups.

Part 3: If you must open the port

Some setups genuinely need it — a managed BI tool, an app server fleet, a colleague’s laptop. Then every gate gets closed to the narrowest thing that works, and TLS gets configured properly, because now there is something on the internet listening for a password.

Re-open listen_addresses to loopback plus the one address the client reaches, as in Gate 1:

# on the db host
sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = 'localhost,172.31.26.131';"
sudo systemctl restart postgresql

If your provider gives you no private network and the client is genuinely remote, you will have to bind a public address — which makes the next two sections load-bearing rather than optional.

pg_hba.conf: one database, one user, one address, over TLS

Append one rule. Not 0.0.0.0/0, not all all:

# on the db host
echo "hostssl appdb          appuser         172.31.26.149/32        scram-sha-256" \
  | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
sudo systemctl reload postgresql

Every field is doing work. hostssl (not host) refuses the connection unless it is TLS-encrypted. appdb and appuser scope the rule to one database and one role. /32 is a single host, not a network. scram-sha-256 sets the authentication method.

Confirm the server actually took the rule. pg_hba_file_rules shows what is live, which beats trusting your own file edit:

# on the db host
sudo -u postgres psql -c "SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules WHERE address IS NOT NULL;"

  type   |   database    | user_name |    address    |  auth_method  
---------+---------------+-----------+---------------+---------------
 host    | {all}         | {all}     | 127.0.0.1     | scram-sha-256
 host    | {all}         | {all}     | ::1           | scram-sha-256
 host    | {replication} | {all}     | 127.0.0.1     | scram-sha-256
 host    | {replication} | {all}     | ::1           | scram-sha-256
 hostssl | {appdb}       | {appuser} | 172.31.26.149 | scram-sha-256
(5 rows)

The rule is live after a reload alone — nothing restarted, no connection dropped.

TLS: the part that decides whether any of this matters

Ubuntu’s PostgreSQL package turns SSL on out of the box — and points it at a self-signed “snakeoil” certificate:

# on the db host
sudo -u postgres psql -c "SHOW ssl;" -c "SHOW ssl_cert_file;"

 ssl 
-----
 on
(1 row)

            ssl_cert_file             
--------------------------------------
 /etc/ssl/certs/ssl-cert-snakeoil.pem
(1 row)

This is the trap. Connections are encrypted, sslmode=require succeeds, and everything looks secure — but that certificate proves nothing about who the server is. Encryption without identity stops someone passively sniffing your traffic. It does not stop someone who can answer to your database’s name.

Give the server a certificate from your own small CA. (A public CA certificate works too if your db host has a public DNS name — but a private CA is usually the better fit, since database hosts often have no public name at all.)

# on the db host
mkdir -p ~/pg-tls && cd ~/pg-tls

# 1. your CA — this key signs your server certs; keep it, and keep it private
openssl req -new -x509 -days 3650 -nodes -newkey rsa:2048 \
  -keyout ca.key -out ca.crt -subj "/CN=Example Postgres CA"

# 2. the server's key and request. CN must be the name clients will connect to.
openssl req -new -nodes -newkey rsa:2048 -keyout server.key -out server.csr \
  -subj "/CN=db.example.com"

# 3. sign it, with a SAN — modern clients check SAN, not CN
cat > san.cnf <<'CNF'
subjectAltName = DNS:db.example.com
extendedKeyUsage = serverAuth
CNF
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -out server.crt -days 825 -sha256 -extfile san.cnf

Check the result before installing it:

# on the db host
openssl x509 -in server.crt -noout -subject -issuer -ext subjectAltName

subject=CN = db.example.com
issuer=CN = Example Postgres CA
X509v3 Subject Alternative Name: 
    DNS:db.example.com

The SAN must match the hostname clients type. A certificate for db.example.com will be rejected when the client connects to an IP address — by design, and we test exactly that below.

Install the files where Postgres can read them and point the server at them. ca.crt goes there too — it is not secret, and keeping it beside the cert is where you will look for it when setting up the next client:

# on the db host
cd ~/pg-tls
sudo install -d -o postgres -g postgres -m 700 /etc/postgresql/16/main/tls
sudo install -o postgres -g postgres -m 644 server.crt /etc/postgresql/16/main/tls/server.crt
sudo install -o postgres -g postgres -m 600 server.key /etc/postgresql/16/main/tls/server.key
sudo install -o postgres -g postgres -m 644 ca.crt     /etc/postgresql/16/main/tls/ca.crt
sudo -u postgres psql -c "ALTER SYSTEM SET ssl_cert_file = '/etc/postgresql/16/main/tls/server.crt';"
sudo -u postgres psql -c "ALTER SYSTEM SET ssl_key_file  = '/etc/postgresql/16/main/tls/server.key';"
sudo systemctl restart postgresql

That -m 600 on the key is not decoration. The lab installed the key 644 first, and PostgreSQL refused to start at all. This is real output from that broken run:

2026-07-17 13:42:19.529 UTC [4000] DETAIL:  File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root.
2026-07-17 13:42:19.529 UTC [4000] LOG:  database system is shut down
pg_ctl: could not start server

If your server will not come back after a TLS change, that log line (in /var/log/postgresql/postgresql-16-main.log) is why: sudo chmod 600 the key and start it again. Postgres reads the key as a daemon and will not touch one that other users can read.

Confirm the server came back and is serving your certificate:

# on the db host
systemctl is-active postgresql
sudo -u postgres psql -c "SHOW ssl_cert_file;"
sudo ls -l /etc/postgresql/16/main/tls/

active
             ssl_cert_file              
----------------------------------------
 /etc/postgresql/16/main/tls/server.crt
(1 row)

total 12
-rw-r--r-- 1 postgres postgres 1139 Jul 17 14:26 ca.crt
-rw-r--r-- 1 postgres postgres 1176 Jul 17 14:26 server.crt
-rw------- 1 postgres postgres 1704 Jul 17 14:26 server.key

sslmode: require encrypts, verify-full authenticates

Copy the CA certificate (ca.crt — never the CA key, never the server key) to the client, where libpq looks for it by default:

# on the client
mkdir -p ~/.postgresql
scp [email protected]:~/pg-tls/ca.crt ~/.postgresql/root.crt
chmod 600 ~/.postgresql/root.crt
ls -l ~/.postgresql/root.crt
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full" \
  -c "SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid=pg_backend_pid();"

-rw------- 1 appdev appdev 1139 Jul 17 14:28 /home/appdev/.postgresql/root.crt
 ssl | version |         cipher         
-----+---------+------------------------
 t   | TLSv1.3 | TLS_AES_256_GCM_SHA384
(1 row)

pg_stat_ssl is the honest check — it reports what the server sees for your session, not what you hoped you configured.

Now the demonstration that makes the point. In the lab we gave an attacker the one capability that matters: the ability to answer to the database’s name (DNS poisoning, ARP spoofing, a hostile network — the mechanism does not matter). A rogue PostgreSQL with its own self-signed certificate claiming to be db.example.com was stood up, and the name was pointed at it.

With sslmode=require, on a client with no CA installed:

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=require" \
  -c "SELECT ssl, version FROM pg_stat_ssl WHERE pid=pg_backend_pid();" -c "SELECT * FROM loot;"

 ssl | version 
-----+---------
 t   | TLSv1.3
(1 row)

                       whoami                       
----------------------------------------------------
 you are talking to the ATTACKER, not your database
(1 row)

The client connected happily, over TLS 1.3, to the wrong server, and never noticed. Here is the attacker’s own log of that connection:

2026-07-17 14:39:06.049 UTC [5147] appuser@appdb LOG:  connection authenticated: identity="appuser" method=password (/etc/postgresql/16/rogue/pg_hba.conf:2)
2026-07-17 14:39:06.050 UTC [5147] appuser@appdb LOG:  connection authorized: user=appuser database=appdb application_name=psql SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)

method=password — the rogue asked for the password in cleartext, and psql sent it, because a server the client never authenticated is free to choose the authentication method. The attacker now has the real password. scram-sha-256 on your server did not help; the attacker’s server simply did not ask for it. TLS 1.3 did not help either; the attacker held the other end.

Same attacker, same hijacked name, sslmode=verify-full:

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full connect_timeout=8" -c "SELECT * FROM loot;"

psql: error: connection to server at "db.example.com" (127.0.0.1), port 5432 failed: SSL error: certificate verify failed

That is the entire difference between the two modes, and it is not a detail.

One subtlety, because it is easy to draw the wrong conclusion from a quick test: require is not always blind. If ~/.postgresql/root.crt exists, libpq verifies the certificate against it even under sslmode=require. The same require connection that was hijacked above refuses the attacker once a CA is installed:

# on the client — CA now present
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=require connect_timeout=8" -tAc "SELECT 1"

psql: error: connection to server at "db.example.com" (127.0.0.1), port 5432 failed: SSL error: certificate verify failed

So require‘s safety silently depends on a file being present. Do not rely on it — ask for what you actually want:

sslmode	Encrypted?	Server identity checked?	Verdict
`disable`	No	No	Refused outright by a `hostssl` rule
`allow` / `prefer`	Maybe	No	`prefer` is the default — it silently accepts plaintext
`require`	Yes	Only if `root.crt` happens to exist	Do not depend on it
`verify-ca`	Yes	Signed by your CA, but any hostname	Better
`verify-full`	Yes	Signed by your CA and hostname matches	Use this

verify-full checks the hostname, which is why you connect by name and not by IP:

psql: error: connection to server at "172.31.26.131", port 5432 failed: server certificate for "db.example.com" (and 1 other name) does not match host name "172.31.26.131"

If you see that, the fix is to connect by the name in the certificate — not to downgrade the sslmode. And hostssl means a plaintext client is refused outright, which you can confirm:

psql: error: connection to server at "db.example.com" (172.31.26.131), port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.31.26.149", user "appuser", database "appdb", no encryption

Put sslmode=verify-full in your application’s connection string today; a DATABASE_URL ending in ?sslmode=require is one hostile network away from leaking its password. Building a Production-Ready REST API with Node.js, Express, and PostgreSQL shows where that string lives in a typical app.

The firewall, closed to one address

# on the db host
sudo ufw allow from 172.31.26.149 to any port 5432 proto tcp
sudo ufw status numbered

Status: active

     To                         Action      From
     --                         ------      ----
[ 1] OpenSSH                    ALLOW IN    Anywhere                  
[ 2] 5432/tcp                   ALLOW IN    172.31.26.149             
[ 3] OpenSSH (v6)               ALLOW IN    Anywhere (v6)

Never ufw allow 5432/tcp. That means “from anywhere”, and it is what the popular guides tell you to run. Now the good path works:

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full" -tAc "SELECT 'connected as ' || current_user;"

connected as appuser

Confirm it all survives a reboot

Security settings that only hold until the next reboot are not security settings:

# on the db host
sudo reboot
# ... wait, reconnect, then:
uptime -s
systemctl is-active postgresql
sudo -u postgres psql -tAc "SHOW listen_addresses;" -c "SHOW ssl;"
sudo ufw status | grep -E "Status|5432"

2026-07-17 14:00:11
active
*
on
Status: active
5432/tcp                   ALLOW       172.31.26.149

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full connect_timeout=10" \
  -tAc "SELECT 'reconnected after reboot: ' || count(*) || ' customers' FROM customers;"

reconnected after reboot: 5000 customers

Postgres came back enabled, the config held, the firewall rule held, and the client reconnected under verify-full. (That capture is from the lab’s reboot check, taken while listen_addresses was still '*' from the Gate 1 demonstration — yours will show the narrowed value.) The rule also survives sudo ufw reload, which we checked separately.

For the wider hardening picture around this box, 8 Essential Security Steps for Every Production VPS is the companion piece.

Part 4: Backups that run without you

A backup strategy has three parts and most people build one: the dump. The other two are the thing that runs it while you are asleep, and the drill that proves it restores.

backupuser already exists from the setup section, with pg_read_all_data and CONNECT. Confirm it does what the name promises, in both directions:

# on the db host
psql -h localhost -U backupuser -d appdb -tAc "SELECT count(*) FROM customers;"
psql -h localhost -U backupuser -d appdb -c "DELETE FROM customers WHERE id=1;"

5000
ERROR:  permission denied for table customers

Reads everything, cannot destroy anything. If that role’s password leaks, the blast radius is a data leak rather than a data loss. (Both commands will prompt for backupuser‘s password until you create the .pgpass file below.)

.pgpass, and the trap that silently breaks scheduled jobs

A scheduled job cannot type a password. ~/.pgpass supplies it, in the format host:port:database:user:password:

# on the db host
echo "localhost:5432:*:backupuser:B4ckup_Str0ng_Pw" > ~/.pgpass
chmod 600 ~/.pgpass
ls -l ~/.pgpass

-rw------- 1 dbadmin dbadmin 45 Jul 17 14:29 /home/dbadmin/.pgpass

The chmod 600 is mandatory and its failure mode is nasty. With the file at 644, libpq ignores it entirely and falls back to prompting — real output from the broken run:

WARNING: password file "/home/dbadmin/.pgpass" has group or world access; permissions should be u=rw (0600) or less
Password: 
pg_dump: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "backupuser"

Interactively you see the warning. From a timer at 02:30 there is no terminal to prompt, so the job just fails — every night, silently, until the day you need a dump and find none.

The backup script

# on the db host
sudo nano /usr/local/bin/pg-backup.sh

#!/usr/bin/env bash
# Nightly logical backup of one PostgreSQL database, plus the server's globals
# (roles and their passwords), which a single-database dump does NOT contain.
set -euo pipefail

DB="appdb"
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=14
STAMP="$(date -u +%Y%m%d-%H%M%S)"
DUMP="${DB}-${STAMP}.dump"
GLOBALS="globals-${STAMP}.sql"

mkdir -p "$BACKUP_DIR"
cd "$BACKUP_DIR"        # so the checksum file records plain filenames, not absolute
                        # paths — it must still verify after you copy it elsewhere

# 1. the database: -Fc = custom format (compressed, restorable with pg_restore)
pg_dump -h localhost -U backupuser -d "$DB" -Fc -f "${DUMP}.tmp"
mv "${DUMP}.tmp" "$DUMP"      # atomic: a half-written dump never looks complete
chmod 640 "$DUMP"

# 2. the globals: roles + passwords. Needs a superuser to read pg_authid, so this
#    runs over the local peer-authenticated socket as the postgres OS user.
sudo -u postgres pg_dumpall --globals-only > "${GLOBALS}.tmp"
mv "${GLOBALS}.tmp" "$GLOBALS"
chmod 640 "$GLOBALS"          # contains password hashes — never world-readable

sha256sum "$DUMP" "$GLOBALS" > "${DUMP}.sha256"

echo "$(date -u '+%Y-%m-%d %H:%M:%S') OK  ${BACKUP_DIR}/${DUMP} ($(du -h "$DUMP" | cut -f1)) + globals"

# retention: delete dumps older than RETENTION_DAYS
find "$BACKUP_DIR" \( -name "${DB}-*.dump*" -o -name "globals-*.sql" \) -type f \
  -mtime +"$RETENTION_DAYS" -print -delete \
  | sed "s/^/$(date -u '+%Y-%m-%d %H:%M:%S') PRUNED /"

Five decisions in there are worth stating, because each came from something that bit us:

-Fc, not plain SQL. Custom format is compressed and lets pg_restore pick out single tables. Our 10 MB test database dumped to 428 KB.
cd "$BACKUP_DIR" before hashing.sha256sum records whatever path you give it. Absolute paths make the checksum file useless the moment you copy it to another machine, because sha256sum -c then looks for /var/backups/... there.
Write to .tmp, then mv. A rename within one filesystem is atomic, so an interrupted dump can never be mistaken for a complete one by your restore script or your retention rule.
pg_dumpall --globals-only. A single-database dump contains no roles. Restore one onto a fresh server and your users do not exist. The dump’s own table of contents says so:

--- grep the restored SQL for CREATE ROLE: 0 — roles are NOT in a single-database dump – sha256sum. Cheap, and it turns “the file is there” into “the file is intact”.

Now the directory. The dumps must be readable by the postgres user at restore time, and by nobody else:

# on the db host
sudo chmod 755 /usr/local/bin/pg-backup.sh
sudo install -d -o dbadmin -g postgres -m 2750 /var/backups/postgresql
echo 'dbadmin ALL=(postgres) NOPASSWD: /usr/bin/pg_dumpall' | sudo tee /etc/sudoers.d/91-pg-backup
sudo chmod 440 /etc/sudoers.d/91-pg-backup
/usr/local/bin/pg-backup.sh

2026-07-17 14:29:13 OK  /var/backups/postgresql/appdb-20260717-142913.dump (428K) + globals

The 2750 is the setgid bit, and it is load-bearing: without it, new dumps get dbadmin‘s group, postgres cannot read them, and your restore dies at the worst possible moment. The lab hit exactly that. Check it rather than trusting it:

# on the db host
D=$(ls -t /var/backups/postgresql/appdb-*.dump | head -1)
ls -l "$D"
sudo -u postgres test -r "$D" && echo "postgres CAN read it" || echo "postgres CANNOT read it"
cat /var/backups/postgresql/*.sha256

-rw-r----- 1 dbadmin postgres 437853 Jul 17 14:29 /var/backups/postgresql/appdb-20260717-142913.dump
postgres CAN read it
6a475853ee2dccd93d8ca936a26fbec1678590d2d9aa2efc9ae51757f60c9a97  appdb-20260717-142913.dump
e2aed0b18f074d01c3d7ff8388799d65f7b9aa3f1ed094d7b589e0f5ad417f95  globals-20260717-142913.sql

Group postgres on the file, and plain filenames in the checksum file. The sudoers line grants dbadmin exactly one command as postgres — pg_dumpall, nothing else — because the alternative is running the whole backup as a superuser. If your sudo user is not dbadmin, change it in that line and in the systemd unit below.

The timer

A systemd timer over a crontab, for one reason worth caring about: Persistent=true runs a missed job when the machine comes back, and journalctl gives you a real log of every run.

# on the db host
sudo nano /etc/systemd/system/pg-backup.service

[Unit]
Description=PostgreSQL logical backup (appdb)
After=postgresql.service

[Service]
Type=oneshot
User=dbadmin
ExecStart=/usr/local/bin/pg-backup.sh

# on the db host
sudo nano /etc/systemd/system/pg-backup.timer

[Unit]
Description=Run the PostgreSQL backup nightly

[Timer]
OnCalendar=*-*-* 02:30:00
Persistent=true

[Install]
WantedBy=timers.target

# on the db host
sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
systemctl list-timers pg-backup.timer --no-pager

NEXT                        LEFT LAST PASSED UNIT            ACTIVATES
Sat 2026-07-18 02:30:00 UTC  12h -         - pg-backup.timer pg-backup.service

“It works when I run it” is not the claim being made — the claim is that it runs when nobody is watching. To prove that without waiting twelve hours, the lab temporarily overrode the same timer to fire every 30 seconds and then left it alone. The journal:

Jul 17 13:54:22 pg-db systemd[1]: Starting pg-backup.service - PostgreSQL logical backup (appdb)...
Jul 17 13:54:22 pg-db pg-backup.sh[5408]: 2026-07-17 13:54:22 OK  /var/backups/postgresql/appdb-20260717-135422.dump (428K)
Jul 17 13:54:22 pg-db systemd[1]: pg-backup.service: Deactivated successfully.
Jul 17 13:54:43 pg-db systemd[1]: Starting pg-backup.service - PostgreSQL logical backup (appdb)...
Jul 17 13:54:43 pg-db pg-backup.sh[5426]: 2026-07-17 13:54:43 OK  /var/backups/postgresql/appdb-20260717-135443.dump (428K)
Jul 17 13:54:43 pg-db systemd[1]: pg-backup.service: Deactivated successfully.

systemd[1] started each run — no human, no shell. Your timer stays on the nightly schedule above; that override existed only to make the proof observable. Check yours the morning after:

# on the db host
journalctl -u pg-backup.service --since yesterday

Retention that actually deletes

A retention rule you have not watched delete something is a disk-full incident with a delay on it. The lab planted dumps aged 3, 10, 15 and 40 days and ran the script:

2026-07-17 14:35:10 OK  /var/backups/postgresql/appdb-20260717-143510.dump (428K) + globals
2026-07-17 14:35:10 PRUNED /var/backups/postgresql/appdb-old-15d.dump
2026-07-17 14:35:10 PRUNED /var/backups/postgresql/appdb-old-40d.dump

The 15- and 40-day dumps are gone; the 3- and 10-day dumps survived. That is RETENTION_DAYS=14 behaving. Test yours the same way — touch -d "-40 days" somefile costs nothing and tells you whether your find expression means what you think it means.

Get the dump off the box

Dumps sitting on the database’s own disk die with that disk. Copy them somewhere else — the same script can scp to another host, or rclone/aws s3 cp to object storage. Whatever you choose, verify the copy at the other end:

# on the client
mkdir -p ~/offsite && cd ~/offsite
scp [email protected]:'/var/backups/postgresql/appdb-*.dump' .
scp [email protected]:'/var/backups/postgresql/appdb-*.dump.sha256' .
scp [email protected]:'/var/backups/postgresql/globals-*.sql' .
sha256sum -c *.sha256

appdb-20260717-142913.dump: OK
globals-20260717-142913.sql: OK

That copy now lives on a different machine and still verifies — which is exactly why the script hashes plain filenames instead of absolute paths.

Lineserve Object Storage is the natural home for these: it is S3-compatible, so aws s3 cp and rclone work unchanged, transfer in and out and API requests are free, and it starts at $0.035/GB/month — a fortnight of nightly dumps of a database this size costs a rounding error.

Part 5: The restore drill

Everything above is theatre until this section passes. A backup you have not restored is not a backup; it is a file you have feelings about.

Where to run this. The drill below drops your database and your roles. That is the point — but run it on a scratch VPS, a clone, or during an announced maintenance window, never casually against live data. The lab’s first restore attempt failed after the database was already dropped (see below), which is precisely the situation you do not want to meet on production at 02:00.

First, fingerprint the live data so the comparison afterwards is arithmetic rather than eyeballing. Row counts alone are weak — hash the actual contents:

# on the db host
sudo -u postgres psql -d appdb -tAc "SELECT 'customers rows: ' || count(*) FROM customers;"
sudo -u postgres psql -d appdb -tAc "SELECT 'orders rows:    ' || count(*) FROM orders;"
sudo -u postgres psql -d appdb -tAc "SELECT 'customers md5:  ' || md5(string_agg(c::text, '|' ORDER BY c.id)) FROM customers c;"
sudo -u postgres psql -d appdb -tAc "SELECT 'orders md5:     ' || md5(string_agg(o::text, '|' ORDER BY o.id)) FROM orders o;"
sudo -u postgres psql -d appdb -tAc "SELECT 'sum of orders:  ' || sum(amount_kes) FROM orders;"

customers rows: 5000
orders rows:    20000
customers md5:  c53dbaf7fbfc7f773cbcb7e71ec12a45
orders md5:     cff91acea50df5836157eff9e5376c64
sum of orders:  496963407.04

Pick the newest dump and its matching globals file, and verify both before you destroy anything:

# on the db host
cd /var/backups/postgresql
DUMP=$(ls -t appdb-*.dump | head -1)
STAMP=${DUMP#appdb-}; STAMP=${STAMP%.dump}
GLOB="globals-${STAMP}.sql"
echo "DUMP=$DUMP"; echo "GLOB=$GLOB"
sha256sum -c "${DUMP}.sha256"

DUMP=appdb-20260717-143510.dump
GLOB=globals-20260717-143510.sql
appdb-20260717-143510.dump: OK
globals-20260717-143510.sql: OK

Deriving GLOB from the dump’s own timestamp keeps the pair from the same run — and the checksum file covers both, so one command verifies the pair. Restoring is not the moment to discover the dump was truncated.

Now destroy it. Not a scratch copy — the database and the roles, which is what losing a server actually costs you:

# on the db host
sudo -u postgres psql -c "DROP DATABASE appdb WITH (FORCE);" -c "DROP ROLE appuser;" -c "DROP ROLE backupuser;"

DROP DATABASE
DROP ROLE
DROP ROLE

Drop the database before the roles — the roles own privileges inside it, and Postgres refuses to drop a role that still has dependent objects.

Bring it back in three steps — and it is three, not one:

# on the db host
# 1. globals: the roles and their passwords
sudo -u postgres psql -f "$GLOB"

SET
SET
SET
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
psql:globals-20260717-142913.sql:20: ERROR:  role "postgres" already exists
ALTER ROLE
GRANT ROLE

That ERROR: role "postgres" already exists is expected and harmless on a drill like this: the globals file recreates every role on the server, and postgres is still there because you did not lose the whole machine. The two CREATE ROLE lines that succeeded are appuser and backupuser — the ones you dropped. On a genuinely fresh server you would see no error at all.

# on the db host
# 2. the database itself
sudo -u postgres createdb appdb
sudo -u postgres pg_restore -d appdb "$DUMP"
echo "pg_restore exit code: $?"

pg_restore exit code: 0

pg_restore says nothing on success — the exit code is the signal, which is why the echo is there.

# on the db host
# 3. database-level grants — NOT in the dump, see below
sudo -u postgres psql -d appdb \
  -c "REVOKE ALL ON DATABASE appdb FROM PUBLIC;" \
  -c "GRANT CONNECT ON DATABASE appdb TO appuser;" \
  -c "GRANT CONNECT ON DATABASE appdb TO backupuser;"

REVOKE
GRANT
GRANT

The verdict — the same five queries as before:

customers rows: 5000
orders rows:    20000
customers md5:  c53dbaf7fbfc7f773cbcb7e71ec12a45
orders md5:     cff91acea50df5836157eff9e5376c64
sum of orders:  496963407.04

Identical hashes, identical row counts, identical money. And from the other machine, with the original password — which only works because the globals dump carried the SCRAM verifier across:

# on the client
psql "host=db.example.com port=5432 user=appuser dbname=appdb sslmode=verify-full" \
  -c "SELECT count(*) AS customers FROM customers;"

 customers 
-----------
      5000
(1 row)

That is a passed drill: the server was gutted and the application reconnected, authenticated, and read its data.

Two things the drill taught us that a dry run never would

pg_restore could not read its own backup. The first attempt died after the database was already dropped:

pg_restore: error: could not open input file "/var/backups/postgresql/appdb-20260717-135551.dump": Permission denied

The dump was 0600 dbadmin:dbadmin; pg_restore runs as postgres. The hardening broke the recovery. The directory setup in Part 4 (setgid, group postgres, chmod 640) is the permanent fix. If you are staring at this error mid-incident, the one-liner that gets you out is to feed the dump on stdin, so your shell opens the file and postgres only reads a pipe:

# on the db host
sudo -u postgres pg_restore -d appdb --no-owner < "$DUMP"

A restore silently reverted the database-level hardening. Skip step 3 and everything still looks perfect — the app works, the data matches. But:

# on the db host — immediately after the restore, before re-applying grants
sudo -u postgres psql -tAc "SELECT datname, datacl FROM pg_database WHERE datname='appdb';"
sudo -u postgres psql -q -c "CREATE ROLE nobodyrole LOGIN PASSWORD 'x';"
sudo -u postgres psql -tAc "SELECT has_database_privilege('nobodyrole','appdb','CONNECT') AS nobody_can_connect;"

appdb|
t

An empty datacl means default privileges: PUBLIC may connect. Our REVOKE ALL ON DATABASE appdb FROM PUBLIC was gone, because database-level grants live in the pg_database catalog and a single-database pg_dump does not carry them. A brand-new role with no grants could connect to the restored database — and nothing appeared broken, so nobody would ever notice.

After step 3, the same check:

appdb|{postgres=CTc/postgres,appuser=c/postgres,backupuser=c/postgres}
f

That is why the restore is three commands and not one. (Table-level grants are in the dump — it is specifically the database-level ones, and the roles themselves, that are not.)

What pg_dump does not give you

Be honest with yourself about the gap you still have:

No point-in-time recovery. A nightly dump means that when the server dies at 02:29 you lose almost 24 hours of writes. If that is unacceptable, you need WAL archiving and pg_basebackup — a different toolset with real operational cost, and a separate guide.
Restore time grows with your data. Our 10 MB test database dumped in 0.34s and restored in 0.19s, which tells you nothing about yours except that the method works. pg_restore rebuilds indexes as it goes, so restore time on a large database is dominated by that, not by the file copy. Time a restore of your database and write the number down; that number is your real recovery time objective, and guessing it is how outages get long.
A dump is a snapshot, not replication. It is consistent, and it is from last night. It will not save you from a disk that dies at noon with the day’s orders on it.
Nothing here protects against a bad DELETE that you then back up. That is what retention is for — which is why those 14 days are worth more than they look.

For a small-to-medium database on a VPS, dumps plus a tested restore is a genuinely good answer, and it beats the elaborate setup nobody has ever restored from. Just know which one you have.

Troubleshooting

Symptom	Cause	Fix
`Connection refused`	Nothing is listening on that address	`SHOW listen_addresses;` — set it, then restart (reload will not do it)
`no pg_hba.conf entry for host ...`	No matching rule	Add the `hostssl` line; check `pg_hba_file_rules`; remember first match wins
`timeout expired`	Firewall dropping the packet	`sudo ufw status verbose` — is it even active? Allow the client’s IP specifically
`server certificate for "x" does not match host name "y"`	Connecting by IP under `verify-full`	Connect by the name in the cert’s SAN — do not downgrade sslmode
`root certificate file ... does not exist`	No CA on the client	Copy `ca.crt` to `~/.postgresql/root.crt`
Postgres will not start after a TLS change	Key permissions too loose	`sudo chmod 600` the key; check `/var/log/postgresql/postgresql-16-main.log`
`.pgpass` prompts anyway	File is not `0600`	`chmod 600 ~/.pgpass`
`permission denied for database "appdb"`	`pg_read_all_data` does not grant CONNECT	`GRANT CONNECT ON DATABASE appdb TO backupuser;`
`pg_restore: could not open input file ... Permission denied`	`postgres` cannot read the dump	setgid the backup dir (`2750`, group `postgres`), or restore via stdin
`role "..." cannot be dropped because some objects depend on it`	Dropping a role before its database	Drop the database first

Where to run this

Our lab ran both machines on 2 vCPU / 2 GB instances, and PostgreSQL 16 with this workload never came close to using them — for a database this size, the constraint you hit first is disk and transfer, not CPU.

On Lineserve, the equivalents are the S1 (1 vCPU, 2 GB RAM, 20 GB SSD, 1 TB transfer) at KES 1,746 / TZS 33,775 / NGN 21,616 (about $13.51) per month, or the M1 (2 vCPU, 4 GB RAM, 80 GB SSD, 3 TB transfer) at KES 4,418 / TZS 85,450 / NGN 54,688 (about $34.18) if you want room for the database to grow into cache. Both come with a dedicated IPv4, in Nairobi, Dar es Salaam or Lagos — so the app server and the database sit in the same locally-hosted region as the users they serve, and your data stays in-country for the Kenya Data Protection Act, Tanzania’s PDPA or Nigeria’s NDPA. Pair either with Object Storage for the offsite dumps at $0.035/GB/month, and pay in your own currency by M-Pesa, bank transfer or card — no forex.

Launch a Linux VPS in Nairobi, Dar es Salaam or Lagos →

Whichever you pick: run the restore drill this week, not the week you need it.

Back to tutorials Talk to our team