Self-host PostgreSQL

Create a VM#

On your computer (pick another name if my-postgres is taken):

ssh exe.dev new --name=my-postgres
ssh my-postgres.exe.xyz

Adding PostgreSQL next to an app? Skip this step and run the rest on the app's VM. All remaining commands run on the VM, as exedev.

Install PostgreSQL 18 and pgvector#

Add the PostgreSQL project's apt repository (PGDG), which carries every major version and pgvector. The second postgresql-common install upgrades it to PGDG's version before we change its settings. (To run PostgreSQL 19 once it's released in November 2026, read every 18 on this page as 19, or move an existing cluster with Major upgrades.)

sudo apt-get update
sudo apt-get install -y postgresql-common jq
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt-get install -y postgresql-common

Install PostgreSQL without its default cluster, then create the cluster with an en_US.UTF-8 locale. That's what Heroku and RDS use, so sorting matches theirs when you import:

sudo pg_conftool /etc/postgresql-common/createcluster.conf set create_main_cluster off
sudo apt-get install -y postgresql-18 postgresql-18-pgvector
locale -a | grep -qx en_US.utf8 || sudo locale-gen en_US.UTF-8
[ -e /etc/postgresql/18/main/postgresql.conf ] || sudo pg_createcluster 18 main --locale=en_US.UTF-8 --start
pg_lsclusters

Expect one line: 18 main 5432 online. Make exedev a superuser with its own database, so psql works with no options:

sudo -u postgres createuser --superuser exedev
sudo -u postgres createdb --owner=exedev exedev
psql -XAtc 'SELECT version()'
systemctl is-enabled postgresql

Expect PostgreSQL 18.x and enabled: it starts at boot. It listens on localhost only, and the cluster has data checksums on.

Size it for the VM#

PostgreSQL's defaults suit a tiny machine. This script sets memory from the VM's RAM (a quarter for PostgreSQL's cache, three quarters as the planner's estimate of the OS cache), tunes for the VM's NVMe disk, and turns on slow-query logging and pg_stat_statements. Run it again after resizing the VM or a major upgrade.

mkdir -p -m 700 ~/postgres
cat > ~/postgres/tune.sh <<'EOF'
#!/usr/bin/env bash
# Size PostgreSQL for this VM's memory and restart it. Run again after
# resizing the VM or upgrading to a new major version.
set -euo pipefail
ver=$(pg_lsclusters -h | awk '$2 == "main" && $3 == 5432 {print $1}')
[ -n "$ver" ] || { echo "no 'main' cluster on port 5432" >&2; exit 1; }
mem=$(awk '/^MemTotal:/ {print int($2 / 1024)}' /proc/meminfo)  # MB
maint=$((mem / 16 < 2048 ? mem / 16 : 2048))
work=$((mem / 512 > 4 ? mem / 512 : 4))
sudo tee /etc/postgresql/$ver/main/conf.d/10-exe-dev.conf >/dev/null <<CONF
# Written by ~/postgres/tune.sh for $mem MB of RAM. Rerunning it replaces
# this file; put your own settings in conf.d/50-local.conf.
max_connections = 100
shared_buffers = $((mem / 4))MB
effective_cache_size = $((mem * 3 / 4))MB
maintenance_work_mem = ${maint}MB
work_mem = ${work}MB
random_page_cost = 1.1
effective_io_concurrency = 200
wal_compression = zstd
max_wal_size = 2GB
checkpoint_timeout = 15min
shared_preload_libraries = 'pg_stat_statements'
log_min_duration_statement = 1s
log_lock_waits = on
CONF
sudo systemctl restart postgresql@$ver-main
psql -X -d postgres -q -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements'
psql -X -d postgres -c 'SELECT name, setting, unit FROM pg_settings WHERE name IN ($$shared_buffers$$, $$effective_cache_size$$, $$work_mem$$)'
EOF
chmod +x ~/postgres/tune.sh
~/postgres/tune.sh

The table shows the new values; shared_buffers is in 8 kB pages, so on an 8 GB VM it's about 254000 (2 GB).

Create a database for your app#

This script creates a role and a database with the same name, enables pgvector in it, and writes its connection string to ~/postgres/<name>.env. It never touches an existing one:

cat > ~/postgres/create-db.sh <<'EOF'
#!/usr/bin/env bash
# Usage: create-db.sh NAME
# Create a PostgreSQL role and database called NAME, enable pgvector,
# and write DATABASE_URL to ~/postgres/NAME.env (mode 600).
set -euo pipefail
db=${1:?usage: create-db.sh NAME}
[[ $db =~ ^[a-z_][a-z0-9_]*$ ]] || { echo "Use lowercase letters, digits, and _." >&2; exit 1; }
env="$HOME/postgres/$db.env"
if [ -e "$env" ]; then echo "$env already exists; leaving $db alone."; exit 0; fi
pass=$(openssl rand -hex 24)
psql -X -q -d postgres -v ON_ERROR_STOP=1 -v db="$db" -v pass="$pass" <<'SQL'
CREATE ROLE :"db" LOGIN PASSWORD :'pass';
CREATE DATABASE :"db" OWNER :"db";
\connect :"db"
CREATE EXTENSION vector;
SQL
(umask 077; echo "DATABASE_URL=postgresql://$db:$pass@localhost:5432/$db" > "$env")
echo "$env"
EOF
chmod +x ~/postgres/create-db.sh
~/postgres/create-db.sh app

Check that the app's role can log in and use pgvector:

. ~/postgres/app.env
psql "$DATABASE_URL" -XAtc "SELECT current_user, '[1,2,3]'::vector <-> '[3,2,1]'"
stat -c '%a %n' ~/postgres/app.env

Expect app|2.8284271247461903 and 600.

Add a web admin#

Adminer is a database admin in a single PHP file. It runs on PHP's built-in web server, as in Adminer's official Docker image, behind exe.dev's login. A small wrapper signs you in as exedev over the socket, and only for the emails in allowed-emails.

sudo apt-get install -y php-cli php-pgsql
mkdir -p ~/postgres/adminer && cd ~/postgres/adminer
curl -fsSL -o adminer.php https://github.com/vrana/adminer/releases/download/v6.1.2/adminer-6.1.2-en.php
[ -s allowed-emails ] || curl -fsS https://reflection.int.exe.xyz/email | jq -r .email > allowed-emails
cat allowed-emails

The last line should print your exe.dev email. If it doesn't, put your email in ~/postgres/adminer/allowed-emails yourself. Now the wrapper:

cat > ~/postgres/adminer/index.php <<'EOF'
<?php
// Adminer behind exe.dev login. exe.dev's proxy sends X-ExeDev-Email for
// signed-in visitors; only emails listed in allowed-emails get in, and they
// are connected as the PostgreSQL superuser exedev over the local socket.
$email = strtolower($_SERVER['HTTP_X_EXEDEV_EMAIL'] ?? '');
if ($email === '') {
    header('Location: /__exe.dev/login?redirect=' . urlencode($_SERVER['REQUEST_URI']));
    exit;
}
$allowed = array_map('strtolower', array_map('trim',
    file(__DIR__ . '/allowed-emails', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) ?: []));
if (!in_array($email, $allowed, true)) {
    http_response_code(403);
    header('Content-Type: text/plain');
    exit("$email isn't in ~/postgres/adminer/allowed-emails on this VM.\n");
}
if (!isset($_GET['username']) && empty($_POST)) {
    $_POST['auth'] = ['driver' => 'pgsql', 'server' => '', 'username' => 'exedev', 'password' => '', 'db' => ''];
}

function adminer_object() {
    class ExeDevAdminer extends Adminer\Adminer {
        function credentials() { return ['', 'exedev', '']; }
        function login($login, $password) { return true; }
        function verifyLoginToken() { return false; }
    }
    return new ExeDevAdminer;
}
require __DIR__ . '/adminer.php';
EOF

Run it as a service, as exedev. On a VM where an app uses port 8000, or will, set port=8001 in the first line; Adminer is then at https://<vm>.exe.xyz:8001/.

port=8000
sudo tee /etc/systemd/system/adminer.service >/dev/null <<EOF
[Unit]
Description=Adminer web admin for PostgreSQL, behind exe.dev login
After=postgresql.service

[Service]
User=$USER
WorkingDirectory=$HOME/postgres/adminer
Environment=PHP_CLI_SERVER_WORKERS=4
ExecStart=/usr/bin/php -S 0.0.0.0:$port index.php
Restart=always

[Install]
WantedBy=multi-user.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now adminer

Check the three cases: no email (sent to exe.dev login), someone else (refused), and you (Adminer's database list):

port=$(grep -o ':[0-9]* ' /etc/systemd/system/adminer.service | tr -d ': ')
email=$(head -n 1 ~/postgres/adminer/allowed-emails)
timeout 30 sh -c "until curl -so /dev/null http://localhost:$port/; do sleep 1; done"
curl -s -o /dev/null -w '%{http_code}\n' http://localhost:$port/
curl -s -o /dev/null -w '%{http_code}\n' -H 'X-ExeDev-Email: someone@example.com' http://localhost:$port/
curl -sL -b /tmp/adminer.cookies -c /tmp/adminer.cookies -H "X-ExeDev-Email: $email" http://localhost:$port/ | grep -o '<title>[^<]*</title>'

Expect 302, 403, and <title>Select database - Adminer</title>.

Set up nightly backups#

This script dumps every database in pg_dump's compressed custom format, checks each file reads back, and saves the roles (with their password hashes) and the .env files beside them. It keeps the 14 newest backups:

cat > ~/postgres/backup.sh <<'EOF'
#!/usr/bin/env bash
# Back up every database, the roles, and ~/postgres/*.env into
# ~/postgres/backups/<UTC time>/. Keeps the 14 newest backups.
set -euo pipefail
umask 077
dest="$HOME/postgres/backups"
keep=14
mkdir -p "$dest"
out="$dest/$(date -u +%Y%m%d-%H%M%S)"
trap 'rm -rf "$out.part"' EXIT
mkdir "$out.part"

pg_dumpall --globals-only --file="$out.part/globals.sql"
psql -X -d postgres -At -c 'SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate' |
  while IFS= read -r db; do
    pg_dump --dbname="$db" --format=custom --file="$out.part/$db.dump"
    pg_restore --list "$out.part/$db.dump" >/dev/null
  done
cp -p "$HOME"/postgres/*.env "$out.part/" 2>/dev/null || true

mv "$out.part" "$out"
ls -1d "$dest"/2*/ | head -n -"$keep" | xargs -r rm -rf --
echo "$out"
EOF
chmod +x ~/postgres/backup.sh

Schedule it, then take a first backup:

sudo tee /etc/systemd/system/postgres-backup.service >/dev/null <<EOF
[Unit]
Description=Back up PostgreSQL to ~/postgres/backups
After=postgresql.service

[Service]
Type=oneshot
User=$USER
ExecStart=$HOME/postgres/backup.sh
EOF
sudo tee /etc/systemd/system/postgres-backup.timer >/dev/null <<'EOF'
[Unit]
Description=Back up PostgreSQL every night

[Timer]
OnCalendar=*-*-* 03:30:00
RandomizedDelaySec=30m
Persistent=true

[Install]
WantedBy=timers.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now postgres-backup.timer
sudo systemctl start postgres-backup.service
systemctl list-timers postgres-backup.timer
ls -l ~/postgres/backups/*/

Expect a next run time, and globals.sql, app.env, and a .dump per database. For failures: journalctl -u postgres-backup.service.

Test a backup#

Restore the newest backup into a scratch cluster on port 5439, exactly as you would on a new VM, and compare it with the real one:

latest=$(ls -1d ~/postgres/backups/2*/ | tail -n 1)
sudo pg_createcluster 18 scratch --port=5439 --locale=en_US.UTF-8 --start
sudo -u postgres createuser --port=5439 --superuser exedev
grep -Ev '^CREATE ROLE (postgres|exedev);$' "$latest/globals.sql" | psql -X -q -p 5439 -d postgres -v ON_ERROR_STOP=1
for f in "$latest"/*.dump; do
  case $(basename "$f") in postgres.dump|exedev.dump) continue ;; esac
  pg_restore --port=5439 --create --exit-on-error --dbname=postgres "$f"
done
for p in 5432 5439; do
  psql -X -p $p -d app -At -c "SELECT $p, current_database(), pg_get_userbyid(datdba), (SELECT extversion FROM pg_extension WHERE extname = 'vector') FROM pg_database WHERE datname = current_database()"
done
psql "$(sed -n 's/^DATABASE_URL=//p' "$latest/app.env" | sed 's/:5432/:5439/')" -XAtc 'SELECT current_user'

Expect two matching lines, 5432|app|app|0.8.7 and 5439|app|app|0.8.7, then app: the restored role still logs in with the password from the backup. Remove only the scratch cluster:

sudo pg_dropcluster 18 scratch --stop
pg_lsclusters

pg_lsclusters should list only 18 main. Installation done; continue with Open Adminer.

On the VM, ~/postgres/backup.sh takes an extra backup and prints its folder. Each folder has globals.sql (the roles), a .dump per database, and the .env files. The postgres and exedev databases are for admin work; keep your data in databases of your own.

Undo a mistake#

Dropped the wrong table? Restore a backup next to the live database. On the VM, pick a backup folder from the list:

ls ~/postgres/backups/
when=20261017-033012
db=app
createdb --owner=$db ${db}_restored
pg_restore --dbname=${db}_restored --exit-on-error ~/postgres/backups/$when/$db.dump
psql -X -d ${db}_restored -c '\dt'

Copy a single table back with pg_dump -d app_restored -t mytable | psql -d app (for a table that's gone from app), or swap the whole database. Stop your app first, because renaming needs the database to have no connections:

db=app
psql -X -d postgres -c "ALTER DATABASE $db RENAME TO ${db}_broken" -c "ALTER DATABASE ${db}_restored RENAME TO $db"

Start the app again, and dropdb ${db}_broken once you're happy. Settings made with ALTER DATABASE, such as an imported search path, stay with the old database; set them again on the new one.

Restore everything on a new VM#

On a new VM, follow Install by hand up to and including "Size it for the VM", but don't create the app database. Then copy a backup folder over. On your computer:

scp -r postgres-backups/20261017-033012 my-postgres.exe.xyz:restore

On the VM, load the roles, then each database with its original owner:

grep -Ev '^CREATE ROLE (postgres|exedev);$' ~/restore/globals.sql | psql -X -q -d postgres -v ON_ERROR_STOP=1
for f in ~/restore/*.dump; do
  case $(basename "$f") in postgres.dump|exedev.dump) continue ;; esac
  pg_restore --create --exit-on-error --dbname=postgres "$f"
done
cp --update=none ~/restore/*.env ~/postgres/
chmod 600 ~/postgres/*.env
psql -l

Every database should be listed with its owner. Apps keep their old passwords, so the .env files work unchanged. Continue Install by hand from "Create a database for your app" (it leaves the existing app alone), and rm -r ~/restore when you're done.

More often, or point in time#

Nightly dumps can lose up to a day of changes. To back up hourly and keep two days, on the VM:

sudo sed -i 's/^OnCalendar=.*/OnCalendar=hourly/; s/^RandomizedDelaySec=.*/RandomizedDelaySec=5m/' /etc/systemd/system/postgres-backup.timer
sed -i 's/^keep=.*/keep=48/' ~/postgres/backup.sh
sudo systemctl daemon-reload && sudo systemctl restart postgres-backup.timer
systemctl list-timers postgres-backup.timer

Each backup is a full dump, so this suits databases up to a few GB. exe.dev already replicates the VM's disk to other machines, which guards against hardware failure, not against a bad DELETE. Point-in-time recovery needs continuous WAL archiving to storage off the VM; pgBackRest does it well, and Shelley can set it up with your S3-compatible bucket.

Updates don't install themselves on this VM, so put them on your calendar. PostgreSQL ships fixes, including security fixes, every February, May, August, and November.

Minor updates#

On the VM, back up, then upgrade just the PostgreSQL packages. The upgrade restarts PostgreSQL, so apps see a few seconds of errors:

~/postgres/backup.sh
sudo apt-get update
sudo apt-get install --only-upgrade -y -o Dpkg::Options::=--force-confold \
  postgresql-18 postgresql-client-18 postgresql-18-pgvector postgresql-common
psql -XAtc 'SELECT version()'
psql -X -d app -c '\dx vector'

If \dx shows pgvector older than the package, update it in each database that uses it: psql -d app -c 'ALTER EXTENSION vector UPDATE'.

To update Adminer, download the new release's adminer-<version>-en.php from its releases over ~/postgres/adminer/adminer.php, then repeat the three-case check from Add a web admin.

Major upgrades#

A new major version comes out each autumn; PostgreSQL 19 is due in November 2026. Each one is supported for five years, so there's no rush: upgrade once its release notes look good and your extensions support it.

pg_upgradecluster moves the data to the new version and keeps the old cluster, stopped, until you remove it. It needs free disk space for a second copy; df -h / tells you. On the VM, with new set to the new version:

new=19
~/postgres/backup.sh
sudo apt-get update
sudo apt-get install -y -o Dpkg::Options::=--force-confold postgresql-$new postgresql-$new-pgvector
sudo systemctl stop adminer
sudo pg_upgradecluster --method=upgrade 18 main
sudo cp --update=none /etc/postgresql/18/main/conf.d/*.conf /etc/postgresql/$new/main/conf.d/
~/postgres/tune.sh
sudo systemctl start adminer
pg_lsclusters

Stop your apps before pg_upgradecluster and start them afterwards; it takes seconds for small databases. pg_lsclusters shows the new version online on port 5432 and 18 down on 5433.

The new version may come with newer extensions, and some planner statistics don't carry over. Update the one and rebuild the other:

psql -XAt -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn" | while IFS= read -r db; do
  psql -XAt -d "$db" -c "SELECT format('ALTER EXTENSION %I UPDATE;', e.extname) FROM pg_extension e JOIN pg_available_extensions a ON a.name = e.extname WHERE a.default_version <> e.extversion" | psql -X -q -e -d "$db"
done
vacuumdb --all --analyze-in-stages --missing-stats-only

It prints each ALTER EXTENSION it runs, if any. Check your app, then remove the old version:

sudo pg_dropcluster 18 main
sudo apt-get purge -y postgresql-18 postgresql-18-pgvector

From then on, read 18 as the new version in this page's paths, such as /etc/postgresql/18. If disk is too tight for a second copy, use --method=link: it's faster and needs no space, but the old cluster can't be started again afterwards, so your backup is the way back.

On the VM, PostgreSQL's log usually has the answer:

pg_lsclusters
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

• No such file or directory on /var/run/postgresql/.s.PGSQL.5432: PostgreSQL isn't running. Start it with sudo systemctl start postgresql@18-main and read the log if it stops.

• It won't start after making the VM smaller: its memory settings are too big now. Run ~/postgres/tune.sh.

• Peer authentication failed for user "app": the app used the socket. Put localhost in its URL, as in ~/postgres/app.env.

• password authentication failed for user "app": the password doesn't match the .env file. Set a new one in both places, then restart the app:

  pass=$(openssl rand -hex 24)
  echo "ALTER ROLE app PASSWORD :'pass'" | psql -X -d postgres -v pass="$pass"
  sed -i "s|//app:[^@]*@|//app:$pass@|" ~/postgres/app.env
  

• sorry, too many clients already: the app's connection pool is bigger than PostgreSQL's limit of 100. Make the pool smaller, or raise max_connections in conf.d/50-local.conf and restart.

• The tunnel says Address already in use: something on your computer has port 5433. Pick another, such as -L 5434:localhost:5432.

• SSH refuses the tunnel: try the form that always works, ssh -N -L 5433:localhost:5432 vm+my-postgres@vm.exe.xyz.

• Adminer says your email isn't allowed: add it to ~/postgres/adminer/allowed-emails. Adminer not loading at all? Check systemctl status adminer and journalctl -u adminer.

• Disk full: PostgreSQL stops accepting writes. Check df -h / and du -sh ~/postgres/backups /var/lib/postgresql, then lower keep in ~/postgres/backup.sh. Copy old backups off the VM first.

• Slow queries: queries over a second land in the log. To see which ones take the most time overall, run this in psql:

  SELECT round(total_exec_time) AS total_ms, calls, left(query, 70) AS query
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
  

• could not load library "$libdir/vector" during an upgrade: install pgvector for the new version, such as postgresql-19-pgvector.