Postgres and pgvector on a VPS with Docker: Setup, Backup and Restore

The scary moment with a self-hosted vector database isn't the install. It's the day you restore a backup onto a fresh server and the restore dies on the first line, because the new Postgres has never heard of the vector type. I ran exactly that failure below so you can see it before it happens to you.

This post is a working setup for Postgres with pgvector on a single VPS using Docker Compose, plus a backup script and a restore rehearsal. Everything marked "ran" was run in a Docker sandbox on 2026-10-09 with Docker Engine 29.8.2, Compose v5.6.0, image pgvector/pgvector:0.8.7-pg18-trixie (Postgres 18.6, pgvector 0.8.7). It isn't a benchmark. My test table has 5,001 rows of 3-dimensional vectors, so it proves the mechanics and not the performance.

If you'd rather use a dedicated vector engine, the Qdrant, n8n and Ollama RAG stack covers that route. For hardening the host first, run through the VPS hardening checklist. Backups of the whole server (not only the database) are in the restic backup guide.

Should you run pgvector on your VPS or use a vector database?

Use pgvector if your app already needs Postgres, your corpus is modest, and you want one thing to back up. The extension adds a vector column type and distance operators, and it can index with HNSW or IVFFlat. Per the pgvector README (read 2026-10-09), the vector type supports up to 2,000 dimensions for indexing, which covers common embedding sizes but not every model. Check your embedding model's output size before you commit.

Pick a dedicated engine when vector search is the product, when you need features pgvector doesn't have, or when one machine's RAM becomes the limit. If you're still deciding, the vector store comparison and the HNSW vs IVF explainer help.

One more honest point in favour of Postgres: your chunks, users, sessions and embeddings can live in the same transaction. That makes deletes (think "remove this customer's documents") a single DELETE, not a sync job.

What does the Docker Compose file look like?

This is the file I ran. Save it as compose.yaml in an empty folder:

services:
  db:
    image: pgvector/pgvector:0.8.7-pg18-trixie
    restart: unless-stopped
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
      POSTGRES_DB: appdb
    secrets:
      - pg_password
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql
      - ./init:/docker-entrypoint-initdb.d:ro
      - ./backups:/backups
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5
    shm_size: 256mb

secrets:
  pg_password:
    file: ./pg_password.txt

volumes:
  pgdata:

Why each choice:

• Pinned tag. The pgvector README (2026-10-09) lists tags such as 0.8.7-pg18-trixie and pg18-trixie. The first pins both the extension and the Postgres major version. A rolling tag can change your extension version under a restart.
• POSTGRES_PASSWORD_FILE with a Compose secret. The password sits in pg_password.txt (create it, chmod 600, keep it out of git), not in the compose file. The container read it and started healthy in my run. Docker's guidance on secrets is covered in the hardening post.
• 127.0.0.1:5432:5432. Docker-published ports bypass ufw, so bind to localhost and reach the database through an SSH tunnel or from other containers by service name. If your app runs in the same compose project, you don't need ports: at all.
• /var/lib/postgresql as the volume. See the next section, because this one bites.
• Healthcheck. docker compose up -d --wait blocks until Postgres is ready, which makes scripts reliable.

The init folder holds one file, init/01-extension.sql:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS chunks (
    id         bigserial PRIMARY KEY,
    source     text NOT NULL,
    body       text NOT NULL,
    embedding  vector(3) NOT NULL
);

CREATE INDEX IF NOT EXISTS chunks_embedding_hnsw
    ON chunks USING hnsw (embedding vector_cosine_ops);

Use vector(1536) or whatever your embedding model returns; I used 3 so the output is readable. Scripts in that folder run only when the data directory is empty. I restarted the container with an existing volume and the log said PostgreSQL Database directory appears to contain a database; Skipping initialization. So changing the SQL later won't alter a live database. Use migrations for that.

docker compose config -q validated the file with no output, and docker compose up -d --wait brought the service to healthy.

Why does Postgres 18 refuse to start with the old volume path?

Because the official image changed its layout in version 18. When I mounted a volume at /var/lib/postgresql/data on the 18 image (a throwaway compose file, no other config), the container exited and printed:

Error: in 18+, these Docker images are configured to store database data in a
       format which is compatible with "pg_ctlcluster" (specifically, using
       major-version-specific directory names).
...
       Counter to that, there appears to be PostgreSQL data in:
         /var/lib/postgresql/data (unused mount/volume)
...
       The suggested container configuration for 18+ is to place a single mount
       at /var/lib/postgresql which will then place PostgreSQL data in a
       subdirectory, allowing usage of "pg_upgrade --link" without mount point
       boundary issues.

Copy-pasted compose files from older tutorials still have the /data path. If you pin Postgres 17 or earlier, the old path is right for those images (I only tested 18 and, in an earlier pass, 16). The official image's README (the docker-library docs on GitHub, read 2026-10-09) says the same: the volume moved to /var/lib/postgresql in 18, and for 17 and below you mount /var/lib/postgresql/data because a mount at the parent path will not persist data there. The rule: match the mount to the major version you pin, and write it down next to the tag.

How do you add data and run a similarity query?

Seed some rows (this is the exact SQL I ran, with setseed so you get the same numbers):

INSERT INTO chunks (source, body, embedding)
SELECT 'doc-' || (g % 7), 'chunk number ' || g, ARRAY[random(), random(), random()]::real[]::vector
FROM (SELECT setseed(0.42)) s, generate_series(1, 5000) g;
INSERT INTO chunks (source, body, embedding) VALUES ('probe', 'known probe row', '[1,0,0]');

Then ask for the three nearest chunks to the vector [1,0,0] by cosine distance:

SELECT id, body, embedding <=> '[1,0,0]' AS cosine_distance
FROM chunks ORDER BY embedding <=> '[1,0,0]' LIMIT 3;

Real output:

  id  |       body        |    cosine_distance    
------+-------------------+-----------------------
 5001 | known probe row   |                     0
 1923 | chunk number 1923 | 0.0003674828901744087
 4637 | chunk number 4637 |  0.001428602275362234
(3 rows)

The probe row comes first with distance 0, which is the sanity check you want in any vector setup: plant a row you know should win, and confirm it does. The SELECT extname, extversion FROM pg_extension query returned vector | 0.8.7.

Is the HNSW index actually used?

After the restore, EXPLAIN showed it:

 Limit  (cost=83.65..84.32 rows=3 width=16)
   ->  Index Scan using chunks_embedding_hnsw on chunks  (cost=83.65..1204.02 rows=5001 width=16)
         Order By: (embedding <=> '[1,0,0]'::vector)

Always check this. If your ORDER BY uses a different operator than the index's operator class (vector_cosine_ops goes with <=>), Postgres falls back to a sequential scan and your queries slow down quietly.

Three README facts worth knowing before production (2026-10-09):

1. Recall is tunable. hnsw.ef_search defaults to 40 and caps how many candidates a query can return. Raise it with SET hnsw.ef_search = 100; for better recall at some speed cost.
2. Filters apply after the index scan. The README's example: if a WHERE clause matches 10% of rows and ef_search is 40, you get about 4 rows on average. The README points to iterative index scans (hnsw.iterative_scan) for this. If your RAG app filters by tenant or document, test it.
3. Build the index after bulk loading, with enough memory. Builds are much faster when the graph fits in maintenance_work_mem, and Postgres prints a notice when it no longer fits. Don't set it so high that the VPS runs out of RAM.

Storage is easy to estimate. pg_column_size(embedding) returned 17 bytes for a 3-dimensional vector in my table, which fits roughly 4 bytes per dimension plus a small header. By that arithmetic (not measured), a 1,536-dimension vector is a little over 6 KB, so a million chunks is on the order of 6 GB for the vectors alone, before the text, the HNSW index and any other tables. Measure with your own data.

How do you back up Postgres with pgvector?

Use pg_dump in custom format (-Fc). It's a single compressed file that pg_restore can read selectively. Run it inside the container so the client always matches the server.

Here's what happens if you don't. My host had pg_dump 16.15 and the container ran Postgres 18.6:

pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 18.6 (Debian 18.6-1.pgdg13+2); pg_dump version: 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

So docker compose exec it is. This is the script I ran, backup-pg.sh:

#!/usr/bin/env bash
# Nightly Postgres dump for the pgvector stack. Run from the compose directory.
set -euo pipefail

OUT_DIR="${OUT_DIR:-./backups}"
KEEP_DAYS="${KEEP_DAYS:-14}"
STAMP="$(date -u +%Y%m%dT%H%M%SZ)"
FINAL="$OUT_DIR/appdb-$STAMP.dump"

mkdir -p "$OUT_DIR"

# -T: no TTY, so the binary dump is not mangled. Dump to a temp name first.
docker compose exec -T db pg_dump -U app -d appdb -Fc > "$FINAL.tmp"

# A dump that cannot be listed is not a backup.
docker compose exec -T db pg_restore --list < "$FINAL.tmp" > /dev/null

mv "$FINAL.tmp" "$FINAL"
find "$OUT_DIR" -name 'appdb-*.dump' -mtime +"$KEEP_DAYS" -delete
echo "ok $FINAL $(stat -c %s "$FINAL") bytes"

Output: ok /tmp/H2/pg/backups/appdb-20261009T071300Z.dump 108525 bytes. That's about 106 KiB for 5,001 small rows. Don't extrapolate from it.

Three details matter here. The -T flag stops Docker allocating a terminal, which would corrupt a binary stream. Writing to a .tmp file and renaming means a failed dump never looks like a good one (with pipefail and set -e the script stops before the mv). And pg_restore --list is a cheap smoke test: it reads the archive's table of contents, which in my run included EXTENSION - vector, the chunks table, its data and INDEX public chunks_embedding_hnsw.

Schedule it with cron on the host (unrun, adjust paths and add your own alerting):

17 2 * * * cd /srv/ai/stack && /srv/ai/stack/backup-pg.sh >> /var/log/backup-pg.log 2>&1

A dump on the same disk isn't a backup of the server. Copy it off the machine. The restic guide shows an encrypted, versioned way to do that, and the Vaultwarden and Uptime Kuma post walks through a similar restore rehearsal for SQLite apps.

How do you restore it, and how do you know it worked?

Rehearse on a second, empty stack. I started another compose project with the same image on port 5433, an empty volume and my backup folder mounted read-only at /backups. Because POSTGRES_DB: appdb creates an empty database, the restore command is:

docker compose exec -T db pg_restore -U app -d appdb --no-owner /backups/appdb-20261009T071300Z.dump

It printed nothing, which for pg_restore means success. Then I ran four checks. Extension, row count and indexes:

 extname | extversion 
---------+------------
 vector  | 0.8.7

 rows 
------
 5001

       indexname       
-----------------------
 chunks_pkey
 chunks_embedding_hnsw

The nearest-neighbour query returned the same three rows and distances as before the dump, and nextval('chunks_id_seq') returned 5002, so the sequence came back at the right place. Your own list should include the one query your app depends on, run against the restored copy.

Note that the dump stores the CREATE INDEX statement, not the graph, so the HNSW index is rebuilt during restore. On a big table that's the slow part of a restore, which I didn't measure. Time it on a copy of your real data so you know your recovery time.

What happens if the target server lacks pgvector?

This is the failure from the opening. I restored the same dump into the stock postgres:18 image. The first errors:

pg_restore: error: could not execute query: ERROR:  extension "vector" is not available
HINT:  The extension must first be installed on the system where PostgreSQL is running.
Command was: CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;

pg_restore: error: could not execute query: ERROR:  type "public.vector" does not exist
...
pg_restore: warning: errors ignored on restore: 8

Eight errors in total: the extension, its comment, the table, the sequence ownership, the default, the data copy, the primary key and the HNSW index. Nothing useful was restored. Two lessons: record the exact image tag next to your backups, and keep a restore target that uses the same image. If you ever change the Postgres major version, the dump-and-restore path is how you move data, so rehearse it before you need it.

Where should the VPS and the database live?

Anywhere with enough RAM for your index to stay hot and disks you can rely on. Other server guides on this site use Hostinger as the example; its KVM 2 plan is a plausible size for a small RAG app (check the current spec and price on the plan page; I did not test this stack on it), and the provider notes are in the Hostinger VPS review. Some links on this page are affiliate links; they cost you nothing and I only link tools I'd mention anyway. Hetzner, DigitalOcean and Linode run the same compose file unchanged, and a managed Postgres is worth pricing if you don't want to own backups at all. The cost calculator post helps compare. If you are new to the retrieval side, the RAG lesson explains what these embeddings are for.

What did I not verify?

• Performance and recall. My table is tiny, so there are no latency or recall numbers here.
• Index build time and restore time at realistic sizes.
• The cron line, which is a template.
• Anything on Postgres 17 or earlier images beyond the volume path I describe from the 18 error message.
• Online (zero-downtime) backups under write load. pg_dump takes a consistent snapshot, but I didn't test it under traffic.

Checklist

• Image tag pinned, with the Postgres major version and pgvector version written down.
• Volume mounted at the path your Postgres major version expects (/var/lib/postgresql on 18).
• Port bound to 127.0.0.1 or not published at all.
• Password in a secret file, not in git or the compose file.
• EXPLAIN shows your query using the HNSW index.
• A planted probe row comes back first.
• pg_dump -Fc runs inside the container, output checked with pg_restore --list.
• Dumps copied off the server and old ones pruned.
• Restore rehearsed into the same image on an empty volume, with your real query run against it.