Run a PostgreSQL database server

What you will set up

A managed database service gives you an address, a user name and a password, and looks after the rest. This guide builds the same on your own server: PostgreSQL 18 with one database and one login per app, settings sized for the server, nightly backups, and access only from the places your apps run.

PostgreSQL is developed by the PostgreSQL Global Development Group, a community project. The names PostgreSQL and Postgres and the elephant logo are registered trademarks of the PostgreSQL Community Association of Canada. It is open source under the PostgreSQL Licence, a short licence similar to BSD and MIT. PostgreSQL contacts no online service: on our test server it made no outgoing connections. The packages here come from apt.postgresql.org, the project's own apt repository, which the server contacts at every apt update.

Every step below was run on a fresh Melonslab VC-P Alloy (2 vCPU, 8 GB) with Debian 13:

• PostgreSQL 18.6 installed from the PostgreSQL apt repository,
• an app login that can create tables in its own database, but cannot open another app's database, create databases or read files on the server,
• connections over WireGuard and through an SSH tunnel, with port 5432 closed on the public address,
• in the public variant: a Let's Encrypt certificate checked by the client, plaintext connections and wrong passwords refused, an address not on the list timing out, and a forced certificate renewal picked up without a restart,
• a pgbench run before and after tuning,
• a nightly dump restored into a new database,
• a major upgrade from PostgreSQL 17 to 18 with pg_upgradecluster,
• everything working again after a reboot.

After a reboot, with the settings from step 3, the whole server used 437 MB of memory. PostgreSQL's own cache takes more as it fills with your data, up to the 2 GB set in step 3.

Before you start

You need:

• a server with Debian 13, set up as in Secure a new Debian server, with ufw on. When you order, leave swap off and use zram if you want swap,
• for private access: WireGuard on the server with the tunnel address 10.8.0.1, or only SSH,
• for public access: a name such as pg.example.com with an A record and an AAAA record pointing at the server, and the fixed addresses of the machines that will connect.

If your app runs on another Melonslab server, read Connect two servers first: two servers in the same network cannot reach each other directly.

The examples use 203.0.113.10 for the database server, 198.51.100.7 for the app server or computer that connects to it, and appdb and appuser for the app's database and login. Replace them with your own throughout.

1. Install PostgreSQL 18

Debian 13 includes PostgreSQL 17 and keeps it for the life of the release. The PostgreSQL project's own repository has 18 today, and each new major version when it comes out, so you decide when to upgrade. This guide uses it.

As root, add the repository with the script that Debian's postgresql-common package includes, then install PostgreSQL 18:

apt update
apt install -y postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
apt install -y postgresql-18
pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

The script writes /etc/apt/sources.list.d/pgdg.sources and the repository's signing key. PostgreSQL starts straight away and listens only on 127.0.0.1 and ::1. Passwords are stored as SCRAM-SHA-256 hashes, and data checksums are on, both by default in PostgreSQL 18.

Install its updates automatically

The automatic updates from the security guide only install packages from Debian. To include PostgreSQL's bug and security fixes, create /etc/apt/apt.conf.d/51unattended-upgrades-postgresql:

Unattended-Upgrade::Origins-Pattern {
    "origin=apt.postgresql.org";
};

unattended-upgrade --dry-run --debug 2>&1 | grep 'Allowed origins'

The list now ends with origin=apt.postgresql.org. An update restarts PostgreSQL, so apps lose their connections for a moment in the morning when updates run. Each major version is its own package, so this never moves you from 18 to 19.

2. Create a database and a login for each app

Give every app its own login and its own database, so a leak in one app does not open the others. Make up a long password first:

openssl rand -base64 24

Create the login, and give it a database it owns. createuser asks for the password twice:

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdb
sudo -u postgres psql -c "REVOKE CONNECT, TEMPORARY ON DATABASE appdb FROM PUBLIC"

The login is not a superuser and cannot create databases or other logins. As the owner of appdb, it can create tables there, which is what an app's migrations need.

By default, every login may connect to every database. The REVOKE line takes that away from everyone but the owner. Since PostgreSQL 15, other logins can no longer create tables in a database's public schema either. We checked both with a second app's login, shopuser:

$ psql -h 127.0.0.1 -U shopuser -d appdb
FATAL:  permission denied for database "appdb"
DETAIL:  User does not have CONNECT privilege.

$ psql -h 127.0.0.1 -U shopuser -d postgres -c 'create table t(i int)'
ERROR:  permission denied for schema public

And appuser itself is stopped at create database (permission denied to create database) and at reading server files with pg_read_file (permission denied for function pg_read_file).

Repeat the three commands for each app, with its own names.

3. Size it for the server

PostgreSQL's defaults are made to start on any machine, with 128 MB for its own cache. Create /etc/postgresql/18/main/conf.d/tuning.conf for 2 vCPU and 8 GB:

# Sized for 2 vCPU and 8 GB of memory
max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
random_page_cost = 1.1

• shared_buffers is PostgreSQL's own cache. A quarter of the memory is the usual starting point, because the rest is used by the system's file cache, which PostgreSQL reads through as well.
• effective_cache_size reserves nothing. It tells the planner how much of the data is likely in memory, counting the file cache.
• work_mem is what one sort or join may use before it spills to disk. A complex query can use it several times over, in every connection, so keep it modest.
• maintenance_work_mem speeds up VACUUM and index builds.
• max_connections stays at the default 100. Each connection is a process of its own; if your apps need more, a connection pooler such as PgBouncer is the usual answer (not covered here).
• random_page_cost = 1.1 tells the planner that random reads are cheap on SSD storage.

Apply it with a restart, and check one value:

systemctl restart postgresql@18-main
sudo -u postgres psql -c "SHOW shared_buffers"

We measured with pgbench, which comes with PostgreSQL, on a 1.5 GB test database: 16 clients for 60 seconds, read-write and read-only.

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 100 bench
sudo -u postgres pgbench -c 16 -j 2 -T 60 bench
sudo -u postgres pgbench -S -c 16 -j 2 -T 60 bench
sudo -u postgres dropdb bench

That is 5 % and 7 % more. The test database fits in memory either way, so the difference is small here. Run each test twice: our first read-write run straight after loading the data gave only 646 tps with the default settings, because the server was still writing the load to disk.

4. Choose how apps connect

The safest database is one nobody else can reach. Pick one of two ways:

• Private (tunnel), recommended: port 5432 stays closed. App servers connect over WireGuard, and your own computer over WireGuard or an SSH tunnel. Nothing about the database is visible from the internet.
• Public with TLS: port 5432 is open, but only to the addresses you list, only with TLS and a Let's Encrypt certificate the client checks, and only with a password. Use it when the client cannot run WireGuard, such as a hosted app platform with fixed outgoing addresses.

5. Let your apps in

listen_addresses = 'localhost,10.8.0.1'

[Unit]
After=wg-quick@wg0.service
Wants=wg-quick@wg0.service

host    appdb           appuser         10.8.0.2/32             scram-sha-256

ufw allow in on wg0 to any port 5432 proto tcp
systemctl daemon-reload
systemctl restart postgresql@18-main
ss -tlnp | grep 5432

LISTEN 0      200         10.8.0.1:5432      0.0.0.0:*    users:(("postgres",pid=15963,fd=8))
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=15963,fd=7))
LISTEN 0      200            [::1]:5432         [::]:*    users:(("postgres",pid=15963,fd=6))

psql "host=10.8.0.1 dbname=appdb user=appuser" -c "select current_user, inet_client_addr()"

 current_user | inet_client_addr
--------------+------------------
 appuser      | 10.8.0.2

ssh -N -L 5433:localhost:5432 root@203.0.113.10

psql "host=localhost port=5433 dbname=appdb user=appuser"

apt install -y certbot
ufw allow 80/tcp

#!/bin/sh
# Give PostgreSQL its own copy of the certificate, then reload it
set -e
dir=/etc/postgresql/ssl
install -d -m 750 -o root -g postgres "$dir"
install -m 644 -o root -g postgres "$RENEWED_LINEAGE/fullchain.pem" "$dir/server.crt"
install -m 640 -o root -g postgres "$RENEWED_LINEAGE/privkey.pem" "$dir/server.key"
systemctl reload postgresql

chmod 755 /usr/local/sbin/postgresql-cert-hook
certbot certonly --standalone -d pg.example.com --email you@example.com --agree-tos -n \
  --deploy-hook /usr/local/sbin/postgresql-cert-hook
ls -l /etc/postgresql/ssl

-rw-r--r-- 1 root postgres 4812 Oct  2 19:10 server.crt
-rw-r----- 1 root postgres  241 Oct  2 19:10 server.key

ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'

listen_addresses = '*'

hostssl appdb           appuser         198.51.100.7/32         scram-sha-256

ufw allow from 198.51.100.7 to any port 5432 proto tcp
systemctl restart postgresql@18-main

psql "host=pg.example.com dbname=appdb user=appuser sslmode=verify-full sslrootcert=system" \
  -c "select ssl, version from pg_stat_ssl where pid = pg_backend_pid()"

 ssl | version
-----+---------
 t   | TLSv1.3

certbot renew --force-renewal

6. Check what others see

psql "host=203.0.113.10 dbname=appdb user=appuser connect_timeout=10"

psql: error: connection to server at "203.0.113.10", port 5432 failed: timeout expired

FATAL:  no pg_hba.conf entry for host "10.8.0.2", user "appuser", database "shopdb", SSL encryption

psql "host=pg.example.com dbname=appdb user=appuser sslmode=disable"

FATAL:  no pg_hba.conf entry for host "198.51.100.7", user "appuser", database "appdb", no encryption

FATAL:  password authentication failed for user "appuser"

server certificate for "pg.example.com" (and 1 other name) does not match host name "203.0.113.10"

psql: error: connection to server at "203.0.113.10", port 5432 failed: timeout expired

7. Back up every night

pg_dump writes a consistent copy of a database while apps keep using it. pg_dumpall --globals-only adds the logins and their password hashes, which the database dumps leave out. Create /usr/local/sbin/pg-backup:

#!/bin/sh
# Dump the roles and every database, and keep 14 days of dumps
set -e
umask 077
dir=/var/backups/postgresql
day=$(date +%F)
pg_dumpall --globals-only > "$dir/globals-$day.sql"
for db in $(psql -Atc 'SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate'); do
    pg_dump --format=custom --file="$dir/$db-$day.dump" "$db"
done
find "$dir" -type f -mtime +13 -delete

chmod 755 /usr/local/sbin/pg-backup
install -d -m 700 -o postgres -g postgres /var/backups/postgresql

The dumps are readable only by postgres and root, because they hold all your data and the password hashes. Run it as the postgres user every night with a systemd service and timer. Create /etc/systemd/system/pg-backup.service:

[Unit]
Description=Dump PostgreSQL databases

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/sbin/pg-backup

And /etc/systemd/system/pg-backup.timer:

[Unit]
Description=Dump PostgreSQL databases every night

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

[Install]
WantedBy=timers.target

Persistent=true runs a missed backup when the server comes back, if it was off at 02:30. Switch it on, and run it once now:

systemctl daemon-reload
systemctl enable --now pg-backup.timer
systemctl start pg-backup.service
ls -l /var/backups/postgresql

-rw------- 1 postgres postgres 7887 Oct  2 19:15 appdb-2026-10-02.dump
-rw------- 1 postgres postgres 1062 Oct  2 19:15 globals-2026-10-02.sql
-rw------- 1 postgres postgres 1106 Oct  2 19:15 postgres-2026-10-02.dump
-rw------- 1 postgres postgres 1075 Oct  2 19:15 shopdb-2026-10-02.dump

Test a restore. A backup you have never restored is a hope. Restore into a new database, next to the real one:

sudo -u postgres createdb --owner=appuser appdb_restore
sudo -u postgres pg_restore --dbname=appdb_restore --no-owner --role=appuser \
  /var/backups/postgresql/appdb-2026-10-02.dump
sudo -u postgres psql -d appdb_restore -c "select count(*) from notes"
sudo -u postgres dropdb appdb_restore

Use one of your own tables in the count. Our test table, notes, came back with all its 1,000 rows, owned by appuser. To rebuild a whole server, run the globals file with psql first, so the logins exist, then restore each database.

The dumps are still on the same server. Copy /var/backups/postgresql off it every night, for example with restic.

A nightly dump loses up to a day of changes. PostgreSQL can also archive its write-ahead log continuously, which lets you restore to any moment, with tools such as pgBackRest. We have not tested that here.

8. Update PostgreSQL

Minor versions, such as 18.6 to 18.7, fix bugs and security problems and change nothing in the data files. They come with apt upgrade, or by themselves if you set that up in step 1. The package restarts PostgreSQL. 18.6 was the newest version when we tested, so we have not run a minor update.

Major versions come once a year, and each is supported for five years: 18 until November 2030. A major upgrade changes the data format, so the data has to be moved to a new cluster. Debian's pg_upgradecluster does that, and keeps the old cluster until you remove it. We tested it from 17 to 18, with the same steps you will use from 18 to 19.

Take a backup first, then install the new version:

systemctl start pg-backup.service
apt install -y postgresql-19
pg_lsclusters

On our server, installing a second version did not create an empty cluster for it. If pg_lsclusters shows a 19 main anyway, remove that empty one with pg_dropcluster 19 main --stop first. Then upgrade:

pg_upgradecluster 18 main
pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5433 down   postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
19  main    5432 online postgres /var/lib/postgresql/19/main /var/log/postgresql/postgresql-19-main.log

By default it copies the data with a dump and restore, so the database is offline while it runs: 14 seconds for our small test cluster, longer for yours. The new cluster takes over port 5432, and the old one is stopped on another port.

The new cluster gets your postgresql.conf and pg_hba.conf, but not the files in conf.d: in our test the new cluster started with shared_buffers back at 128 MB. Copy them over and restart:

cp /etc/postgresql/18/main/conf.d/*.conf /etc/postgresql/19/main/conf.d/
systemctl restart postgresql@19-main
sudo -u postgres psql -c "SHOW shared_buffers"

The WireGuard drop-in, the certificate hook and the backup script work for any version as they are. When your apps work, remove the old cluster and its packages:

pg_dropcluster 18 main
apt purge -y postgresql-18 postgresql-client-18

Troubleshooting

no pg_hba.conf entry for host "...", user "appuser", database "appdb", no encryption. The client connected without TLS, and the line for it is hostssl. Add sslmode=verify-full sslrootcert=system to its connection settings.

no pg_hba.conf entry for host "...", ... SSL encryption. There is no line for that combination of address, login and database. Check all three in pg_hba.conf, and reload with systemctl reload postgresql after changing it.

permission denied for database "appdb" with User does not have CONNECT privilege. That login is not the database's owner, and step 2 took the right to connect away from everyone else. If it should have access, run GRANT CONNECT ON DATABASE appdb TO otheruser as postgres.

timeout expired, or the client waits for minutes. ufw drops the connection. Check that the client's address, IPv4 or IPv6, has its own ufw allow from rule, or that it comes through the tunnel.

server certificate for "pg.example.com" ... does not match host name. The client connects by IP address. Use the name the certificate was issued for.

Settings are back at their defaults after a major upgrade. pg_upgradecluster does not copy conf.d. Copy the files as in step 8.

WireGuard devices get Connection refused after a reboot, and the log says could not create listen socket for "10.8.0.1". PostgreSQL started before WireGuard. Add the drop-in from step 5 and run systemctl daemon-reload and systemctl restart postgresql@18-main.

sudo: unable to resolve host. The server's own name is missing from /etc/hosts. The command still runs. Add the name, as shown by hostname, to the 127.0.1.1 line in /etc/hosts to silence it.