Skip to content

PostgreSQL Companion pgvector

The dedicated PostgreSQL 18 LXC (postgresql, 192.168.6.252) hosts both the companion and tdler databases. Ansible manages pgvector for companion only. It does not create, migrate, or enable extensions in tdler.

Managed state

The postgresql-companion role:

  1. verifies the installed PostgreSQL client is major version 18;
  2. requires at least 512 MiB free on the 8 GiB root filesystem;
  3. installs the matching Debian package, postgresql-18-pgvector;
  4. verifies that the pre-existing companion database exists; and
  5. runs CREATE EXTENSION IF NOT EXISTS vector in companion as the local postgres operating-system user.

The package must be available from the PostgreSQL Apt repository already configured by the community-scripts appliance. Ansible deliberately fails instead of adding or changing Apt repositories.

Deployment

Run only after a successful external-postgresql-companion backup and before deploying a Companion application migration that creates vector columns:

cd /opt/homelab
ansible-playbook infra/ansible/playbooks/site.yml \
  --limit postgresql --tags postgresql,companion,pgvector

The common role normally runs first and keeps node_exporter active. Prometheus already scrapes postgresql:9100; root filesystem warning and critical alerts therefore cover package installation and later vector-index growth. If the role's free-space preflight or a filesystem alert fails, increase LXC 102 rootfs capacity and resize the guest filesystem before retrying.

Verify

sudo -u postgres psql --dbname=companion --no-psqlrc \
  --command="SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';"
sudo -u postgres psql --dbname=tdler --no-psqlrc \
  --command="SELECT extname FROM pg_extension WHERE extname = 'vector';"

The first query must return one row. The second is observational only and should remain empty; do not change tdler as part of the Companion rollout. Rerunning Ansible should report no extension change.

Recovery and rollback

Removing the package or extension can break vector columns and indexes, so there is no automatic rollback. Restore into the isolated PostgreSQL 18 drill container first using the restore runbook. For a live rollback, use a separately approved database migration and validated logical backup rather than DROP EXTENSION ... CASCADE.