--- title: "Self-host PostgreSQL" description: "Run PostgreSQL 18 with pgvector on an exe.dev VM. Nightly tested backups, a private web admin, SSH tunnels, and imports from Heroku or Railway." url: https://exe-staging.dev/deploy/postgres category: "Databases" updated: 2026-10-17 --- # Self-host PostgreSQL Your own Postgres on a real disk, with backups you've actually restored. - **Software:** PostgreSQL (https://www.postgresql.org) - **Source:** https://github.com/postgres/postgres - **License:** PostgreSQL License - **Time:** 10 minutes - **Example VM name:** `my-postgres` (yours will differ; on the VM, `hostname` prints it) - **Machine-readable:** https://exe-staging.dev/deploy/postgres/manifest.json ## What you get - PostgreSQL 18 with pgvector, started at boot and sized for the VM's memory. - An `app` database and role, with its DATABASE_URL in `~/postgres/app.env`. - Adminer at the VM's private URL, open only to the owner's exe.dev email. - Nightly backups of every database and role, one restored and checked. Running on the VM: - **PostgreSQL 18**: From the PostgreSQL project's apt repository, tuned for the VM, port 5432, data in `/var/lib/postgresql/18/main` - **pgvector 0.8**: Vector similarity search, enabled in your app's database - **Adminer 6**: Web admin, behind exe.dev login and an email allowlist, port 8000 - **Nightly backups**: Every database and role, 14 nights kept, data in `~/postgres/backups` How it is reached: - https://my-postgres.exe.xyz/ (private): Adminer, signed in as the database superuser ## Deploy with Shelley Open https://exe-staging.dev/new?prompt=Set%20up%20PostgreSQL%20on%20this%20VM%20by%20following%20the%20exe.dev%20recipe%20at%20https%3A%2F%2Fexe-staging.dev%2Fdeploy%2Fpostgres.md%20from%20top%20to%20bottom.%0A%0AThe%20recipe%27s%20example%20VM%20name%20is%20my-postgres.%20This%20VM%27s%20name%20is%20the%20output%20of%20%60hostname%60%3A%20use%20it%20wherever%20the%20recipe%20writes%20my-postgres.%20Skip%20the%20step%20that%20creates%20the%20VM%3B%20you%20are%20on%20it.%0A%0AYou%20are%20done%20when%20all%20of%20these%20are%20true.%20Check%20each%20one%20yourself%3A%0A-%20PostgreSQL%2018%20with%20pgvector%2C%20started%20at%20boot%20and%20sized%20for%20the%20VM%27s%20memory.%0A-%20An%20%60app%60%20database%20and%20role%2C%20with%20its%20DATABASE_URL%20in%20%60~%2Fpostgres%2Fapp.env%60.%0A-%20Adminer%20at%20the%20VM%27s%20private%20URL%2C%20open%20only%20to%20the%20owner%27s%20exe.dev%20email.%0A-%20Nightly%20backups%20of%20every%20database%20and%20role%2C%20one%20restored%20and%20checked.%0A%0ARead%20the%20whole%20recipe%2C%20including%20the%20closed%20details.%20For%20initial%20setup%2C%0Afollow%20%22Install%20by%20hand%22%20from%20top%20to%20bottom%3A%20PostgreSQL%20and%20pgvector%2C%0Atuning%2C%20the%20app%20database%2C%20Adminer%20on%20port%208000%2C%20the%20backup%20timer%2C%20and%0Athe%20scratch-cluster%20restore%20test.%20The%20walkthrough%20sections%20are%20for%20the%0Aowner%20afterwards%3A%20don%27t%20import%20data%2C%20set%20up%20Tailscale%20or%20Docker%20access%2C%0Aor%20run%20the%20Restore%20or%20Update%20instructions%20during%20initial%20setup.%0ANever%20overwrite%20an%20existing%20~%2Fpostgres%2F%2A.env%20file.%20Drop%20only%20the%0Ascratch%20cluster%20the%20restore%20test%20creates.%0AAllow%20only%20the%20owner%27s%20email%20in%20Adminer%2C%20read%20from%20the%20reflection%0Aintegration%3B%20if%20that%20fails%2C%20ask%20the%20owner%20for%20it.%20Check%20Adminer%20on%0Alocalhost%20with%20no%20email%2C%20a%20wrong%20email%2C%20and%20the%20owner%27s%20email.%0ALeave%20the%20VM%20private%20and%20do%20not%20reboot.%20Don%27t%20paste%20passwords%20into%20the%0Achat%3B%20say%20they%20are%20in%20~%2Fpostgres%2Fapp.env.%0A%0AFinish%20with%20the%20URLs%20I%20should%20open%20and%20these%20commands%2C%20which%20only%20I%20can%20run%2C%20from%20my%20own%20computer%20%28with%20this%20VM%27s%20name%20in%20them%29%3A%0A%20%20ssh%20my-postgres.exe.xyz%20cat%20postgres%2Fapp.env%0A%20%20ssh%20-N%20-L%205433%3Alocalhost%3A5432%20my-postgres.exe.xyz%0A to create a VM whose agent, Shelley, follows this recipe. From a terminal: ```sh curl -fsSL https://exe-staging.dev/deploy/postgres/prompt.txt | ssh exe.dev new --prompt=/dev/stdin ``` Then, on your own computer: ```sh ssh my-postgres.exe.xyz cat postgres/app.env ssh -N -L 5433:localhost:5432 my-postgres.exe.xyz ``` Or follow the steps below by hand. PostgreSQL is the database most apps reach for, and pgvector turns it into a vector store for AI features as well. Here it gets a VM of its own (or moves in next to your app), with memory settings to match, nightly backups you've seen restored, and a web admin only you can open. ## Open Adminer When Shelley finishes, open the URL it gives you. It'll look like `https://my-postgres.exe.xyz/`. Sign in to exe.dev and you land in [Adminer](https://www.adminer.org/), already connected as the database superuser. Browse tables, run SQL, export a table as CSV. Only the exe.dev emails listed in `~/postgres/adminer/allowed-emails` get in, even if you share the VM. Add a teammate's email, one per line, and they're in; no restart needed. Anyone on that list can change or drop anything, so keep it short. Adminer trusts exe.dev's login header, so keep untrusted software off this VM. ## Connect an app on this VM Your app's database is called `app`, owned by a role of the same name. Its connection string is in `~/postgres/app.env`, readable only by you: ```text DATABASE_URL=postgresql://app:@localhost:5432/app ``` Most frameworks read `DATABASE_URL` as is: Rails, Django with `dj-database-url`, Prisma, Drizzle, and Node's `pg`. SQLAlchemy with psycopg 3 wants it to start with `postgresql+psycopg://`. If yours wants separate settings, they're host `localhost`, port `5432`, database `app`, user `app`, and the password from the file. Want one database per app? On the VM: ```bash ~/postgres/create-db.sh blog cat ~/postgres/blog.env ``` Each database gets its own role, password, and pgvector, and the nightly backup picks it up automatically. **Localhost or the socket?** Use `localhost`, as above. PostgreSQL's Unix socket (`/var/run/postgresql`) is set to *peer* authentication: your Linux user logs in as the PostgreSQL role with the same name, no password. That's how `psql`, the backups, and Adminer connect as `exedev`, the superuser. Apps should log in as their own, less powerful role, over `localhost`. ### Run it next to your app Already have an app on a VM, say from a [framework page](/deploy)? Run [Install by hand](#create-the-vm) on that VM, skipping only "Create a VM". Put Adminer on port 8001 instead, as that step explains, because port 8000 is your app's. Then create the app's database and add its URL to the app's environment file (remove any older `DATABASE_URL` line from it first): ```bash ~/postgres/create-db.sh myapp cat ~/postgres/myapp.env >> ~/app.env ``` If the app runs under systemd, add `After=postgresql.service` to its `[Unit]` section, so that it starts after the database at boot. ### From a Docker container Inside a container, `localhost` is the container itself. Let PostgreSQL listen for Docker's networks too, on the VM: ```bash echo "listen_addresses = '*'" | sudo tee /etc/postgresql/18/main/conf.d/20-listen.conf echo "host all all 172.16.0.0/12 scram-sha-256" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf sudo systemctl restart postgresql@18-main ``` Then start containers with `--add-host=host.docker.internal:host-gateway` (in Compose: `extra_hosts: ["host.docker.internal:host-gateway"]`) and use `host.docker.internal` in place of `localhost` in the URL. `pg_hba.conf` lets in only Docker's addresses, and nothing outside the VM can reach port 5432 anyway. ## Connect from your computer Port 5432 isn't on the internet, so you bring it to your computer through SSH. exe.dev's SSH forwards ports like any SSH server. On your computer, print the connection string, then open a tunnel and leave it running: ```bash ssh my-postgres.exe.xyz cat postgres/app.env ssh -N -L 5433:localhost:5432 my-postgres.exe.xyz ``` In another terminal, use the URL with port **5433**, so a PostgreSQL on your computer doesn't get in the way: ```bash psql "postgresql://app:@localhost:5433/app" ``` Desktop clients such as TablePlus, DBeaver, DataGrip, and pgAdmin can open the tunnel themselves. Choose their SSH option, with SSH host `my-postgres.exe.xyz`, user `exedev` and your exe.dev SSH key, then database host `localhost`, port `5432`, and the app's user and password. For superuser work, skip the tunnel: `ssh -t my-postgres.exe.xyz psql`. ## Connect from another VM exe.dev VMs don't share a private network, and exe.dev's [VM-to-VM integration](/docs/integrations-vm-to-vm) carries HTTPS only. The dependable way to reach PostgreSQL from another VM is to put both VMs on your [Tailscale](https://tailscale.com/) network. On **both** VMs, start Tailscale (it's preinstalled) and open the login URL it prints. To join without a login link, use exe.dev's [Tailscale integration](/docs/integrations-tailscale-wif) instead. ```bash sudo systemctl enable --now tailscaled sudo tailscale up --hostname=$(hostname) ``` Then, on the **database** VM, accept password logins from Tailscale's IPv4 and IPv6 addresses: ```bash echo "listen_addresses = '*'" | sudo tee /etc/postgresql/18/main/conf.d/20-listen.conf echo "host all all 100.64.0.0/10 scram-sha-256" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf echo "host all all fd7a:115c:a1e0::/48 scram-sha-256" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf sudo systemctl restart postgresql@18-main ``` On the other VM, swap `localhost` in the URL for the database VM's name: `postgresql://app:@my-postgres:5432/app`. Check it with `pg_isready -h my-postgres` (from the `postgresql-client` package). Use [Tailscale's access rules](https://tailscale.com/kb/1018/acls) if not every device on your network should reach port 5432. You can also tunnel over SSH from VM to VM, as from your computer. The other VM then needs an SSH key added to your exe.dev account (`ssh exe.dev ssh-key add`), and that key opens **all** your VMs. What doesn't work: - A public `my-postgres.exe.xyz:5432`. There's no raw TCP port to open. - exe.dev share links and the VM-to-VM proxy. They speak HTTPS, and PostgreSQL's protocol isn't HTTP. - Hosted tools that only take a host and port. Some can use an SSH tunnel or Tailscale instead; check their docs. ## Import a database Moving from Heroku, Railway, Render, Supabase, Neon, or RDS? Get a `pg_dump` custom-format file (`-Fc`), then restore it into a fresh database here. Run the dump **on the VM** where you can: its `pg_dump` 18 reads older servers, and the data goes straight from server to server. ### Get a dump On **Heroku**, capture a backup, then print a download link that works for an hour. On your computer: ```bash heroku pg:backups:capture --app your-app heroku pg:backups:url --app your-app ``` Then on the VM, with the link in quotes: ```bash curl -fsSL -o ~/import.dump 'https://...the link...' ``` From **anywhere else**, dump with the provider's external connection string: `DATABASE_PUBLIC_URL` on Railway, the External Database URL on Render, the direct (not pooled) URL on Neon and Supabase. On the VM: ```bash read -rsp 'Old DATABASE_URL: ' OLD_URL; echo pg_dump --format=custom --no-owner --no-privileges --dbname="$OLD_URL" --file=$HOME/import.dump pg_restore --list ~/import.dump | head ``` On **Supabase**, add `--schema=public` (and any other schemas of your own): the `auth` and `storage` schemas belong to Supabase. ### Restore it Create a database for it, then restore as its own role, so the tables belong to the app. The role is a superuser just for the restore, because dumps often create extensions. pgvector comes out of the new database first: if the dump has it, it may live in another schema, such as Heroku's `heroku_ext`, and the tables expect it there. ```bash db=myapp ~/postgres/create-db.sh $db psql -X -d $db -c 'DROP EXTENSION IF EXISTS vector' psql -X -d postgres -c "ALTER ROLE $db SUPERUSER" pg_restore --dbname=$db --role=$db --no-owner --no-privileges --jobs=2 ~/import.dump psql -X -d postgres -c "ALTER ROLE $db NOSUPERUSER" psql -X -d $db -c 'CREATE EXTENSION IF NOT EXISTS vector' vacuumdb --analyze-only --dbname=$db psql -X -d $db -c '\dt+' ``` The last command lists your tables and their sizes; compare row counts of a few tables with the old database. pg_restore keeps going past errors and counts them at the end. Usually they're harmless, but read each one: - **`extension "..." is not available`:** install its package, such as `postgresql-18-postgis-3`, then repeat in a fresh database. - **`role "..." does not exist`:** a row-level security policy names a role from the old host, such as Supabase's `authenticated`. Create it with `psql -d postgres -c 'CREATE ROLE authenticated NOLOGIN'` if you need the policy, then repeat in a fresh database. - **Coming from Heroku?** Its extensions live in a `heroku_ext` schema, which Heroku keeps on the search path. Do the same here, or SQL such as `uuid_generate_v4()` or pgvector's `<=>` won't be found: `psql -d myapp -c 'ALTER DATABASE myapp SET search_path = "$user", public, heroku_ext'`. To repeat a restore, empty the database first with `dropdb myapp && createdb --owner=myapp myapp`, then run the block again. Finally, point your app at `~/postgres/myapp.env` and delete `~/import.dump`. Switching over for good? Stop writes on the old database first (on Heroku: `heroku maintenance:on`), take a final dump, restore it, then move the app. ## Search by meaning with pgvector pgvector stores *embeddings*, lists of numbers that capture what a piece of text means, and finds the closest ones fast. Here's a tiny semantic search, using exe.dev's [LLM integration](/docs/integrations-llm) for the embeddings, so you don't need an API key. On the VM: ```bash . ~/postgres/app.env embed() { # prints each argument, a tab, and its embedding jq -n '{model: "text-embedding-3-small", input: $ARGS.positional}' --args "$@" | curl -fsS https://llm.int.exe.xyz/v1/embeddings -H 'Content-Type: application/json' -d @- | jq -r '.data[] | [$ARGS.positional[.index], (.embedding | tostring)] | @tsv' --args "$@" } psql "$DATABASE_URL" -X <<'SQL' CREATE TABLE demo_notes ( id bigserial PRIMARY KEY, body text NOT NULL, embedding vector(1536) NOT NULL ); CREATE INDEX ON demo_notes USING hnsw (embedding vector_cosine_ops); SQL embed "Backups run every night and keep two weeks of history." \ "pg_restore loads a dump file into a database." \ "Cats sleep twelve to sixteen hours a day." \ "The Eiffel Tower is repainted every seven years." | psql "$DATABASE_URL" -X -c '\copy demo_notes (body, embedding) FROM stdin' # Ask a question that shares no keywords with the answer. q=$(embed "How do I get my data back after a mistake?" | cut -f2) psql "$DATABASE_URL" -X -v q="$q" <<'SQL' SELECT round((1 - (embedding <=> :'q'))::numeric, 2) AS similarity, body FROM demo_notes ORDER BY embedding <=> :'q' LIMIT 2; SQL ``` The two notes about backups and restores come out on top. `<=>` is cosine distance, and the HNSW index keeps it fast with millions of rows. In your app, store an embedding next to each row and run the same `ORDER BY`. When you're done: `psql "$DATABASE_URL" -c 'DROP TABLE demo_notes'`. ## Keep your data Every night, a backup of each database, plus the roles and connection files, lands in `~/postgres/backups`; the newest 14 stay. A backup on the same disk won't survive losing the VM, so keep a copy **off the VM**. On your computer: ```bash scp -r my-postgres.exe.xyz:postgres/backups postgres-backups ``` These files hold all your data and the apps' passwords; keep them private. To undo a mistake or move to a new VM, see [Back up and restore](#back-up-and-restore). Before any upgrade, run `~/postgres/backup.sh` on the VM. Something odd? On the VM, `sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log` usually explains it, or ask Shelley to look. ## The nuts and bolts Happy clicking Deploy? You can stop here. These are the same setup steps Shelley follows, plus the maintenance commands for later.
Install by hand ### Create a VM On your computer (pick another name if `my-postgres` is taken): ```bash 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](#major-upgrades).) ```bash 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: ```bash 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: ```bash 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. ```bash 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 <.env`. It never touches an existing one: ```bash 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: ```bash . ~/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](https://www.adminer.org/) 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`. ```bash 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: ```bash cat > ~/postgres/adminer/index.php <<'EOF' '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://.exe.xyz:8001/`. ```bash port=8000 sudo tee /etc/systemd/system/adminer.service >/dev/null <[^<]*' ``` Expect `302`, `403`, and `Select database - Adminer`. ### 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: ```bash cat > ~/postgres/backup.sh <<'EOF' #!/usr/bin/env bash # Back up every database, the roles, and ~/postgres/*.env into # ~/postgres/backups//. 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: ```bash sudo tee /etc/systemd/system/postgres-backup.service >/dev/null </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: ```bash 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: ```bash sudo pg_dropcluster 18 scratch --stop pg_lsclusters ``` `pg_lsclusters` should list only `18 main`. Installation done; continue with [Open Adminer](#open-adminer).
Back up and restore 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: ```bash 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: ```bash 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](#create-the-vm) 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: ```bash scp -r postgres-backups/20261017-033012 my-postgres.exe.xyz:restore ``` On the VM, load the roles, then each database with its original owner: ```bash 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: ```bash 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](/docs/what-is-exe) 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](https://pgbackrest.org/) does it well, and Shelley can set it up with your S3-compatible bucket.
Update PostgreSQL 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: ```bash ~/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--en.php` from [its releases](https://github.com/vrana/adminer/releases) over `~/postgres/adminer/adminer.php`, then repeat the three-case check from [Add a web admin](#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: ```bash 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: ```bash 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: ```bash 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.
Something not working? On the VM, PostgreSQL's log usually has the answer: ```bash 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: ```bash 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`: ```sql 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`.
## Frequently asked questions ### Is this a managed database? No, it's yours. You get root on the VM, the real `postgresql.conf`, and any extension in the PostgreSQL apt repository. Updates are yours to apply, too; [Update PostgreSQL](#update) shows how, and Shelley on the VM can do them for you. ### Can I connect to port 5432 from the internet? Not directly. exe.dev only lets HTTPS and SSH in, so you connect through an [SSH tunnel](#connect-from-your-computer), or from other machines on your [Tailscale network](#connect-from-another-vm). The database never faces the open internet. ### Do I get point-in-time recovery? Not with this setup. You get nightly dumps, so you could lose up to a day of changes; you can [back up hourly](#more-often-or-point-in-time) instead. True point-in-time recovery needs WAL archiving to storage off the VM, for example with pgBackRest. ### Do I need PgBouncer? Probably not. An app on the same VM with a normal connection pool fits easily in 100 connections. Consider PgBouncer when many app servers or serverless functions open short-lived connections. ### Can I add PostGIS or other extensions? Yes. Install the package for your version, such as `sudo apt-get install postgresql-18-postgis-3`, then run `CREATE EXTENSION postgis;` as `exedev` in your database. ### Is PostgreSQL free? Yes, under the permissive PostgreSQL License, and so are pgvector and Adminer. You pay for the VM; see [exe.dev plans](https://exe.dev/pricing). ## Related - [Deploy a Django app](https://exe-staging.dev/deploy/django.md): Your Django app on a computer of its own. git push to deploy. - [Deploy a Rails app](https://exe-staging.dev/deploy/rails.md): Your Rails app on a server of its own. git push, and it's live. - [Migrate a Heroku app to exe.dev](https://exe-staging.dev/deploy/migrate-heroku.md): Your Procfile, config vars, and Postgres on a VM of your own. - [Self-host n8n](https://exe-staging.dev/deploy/n8n.md): Connect your apps. Let the boring bits run themselves. - [Run Claude Code on a remote server](https://exe-staging.dev/deploy/claude-code.md): A computer for Claude. A breather for your laptop. - [Host OpenClaw](https://exe-staging.dev/deploy/openclaw.md): An AI sidekick with its own computer. Your laptop can take a nap.