Skip to content

postgresql

Field Value
ID postgresql
Class host
Role database-server
Status active
Primary IP 192.168.6.252
On host prox (LXC 102)
Tags prod, proxmox-lxc, database, community-script
Ansible yes (proxmox-guests → ansible_user: root)
Inventory inventory/hosts/postgresql.yaml

Overview

Community-scripts PostgreSQL LXC on Prox (Debian 13 / trixie). Created after Wave A destroyed the prior aiproject guest that used VMID 102 — same VMID, different workload.

Attribute Recorded configuration
Hostname postgresql
IP 192.168.6.252 (Servers VLAN)
MAC bc:24:11:10:55:d9
Cores / RAM 6 / 4 GiB (read-only Proxmox check, 2026-10-24)
rootfs 20G local-lvm (read-only Proxmox check, 2026-10-24)
Postgres 18, listening :5432 (service active)
LXC nameserver 192.168.6.17 (AdGuard)

Ansible groups: proxmox-guests, patching_targets, patching_wave1_guests, debian. Patch-controller can SSH as root@192.168.6.252.

Inventory still records 4 vCPU and 8 GiB rootfs. Reconcile it with the observed 6 vCPU / 20 GiB allocation before using it for a rebuild.

Access

Method Target
From infra-services sudo ssh -i /etc/homelab/patch-controller/id_ed25519 root@192.168.6.252
Via prox pct enter 102
Postgres 192.168.6.252:5432 (confirm pg_hba before opening clients)

Ansible / operator shell

ansible-pull + common role apply (patch metrics, node_exporter). Golden operator-shell is disabled for this host (host_vars/postgresql.yml) — the original 8G rootfs and no someone user; installing mise/rustup filled the disk and broke pull.

The postgresql-companion role installs the matching postgresql-18-pgvector package and enables vector idempotently in the companion database only. It verifies the PostgreSQL major version and requires 512 MiB rootfs headroom before package installation. The existing node_exporter target covers / capacity; treat its filesystem alert as a deployment blocker. See the PostgreSQL Companion runbook.

Reddit Explorer also uses this LXC but not the Companion pgvector role. Its operator-created reddit_explorer database and login accept SCRAM connections only from infra-services (192.168.6.17/32); the production DSN remains in the service's host-only SOPS input.

Backup

In-guest restic to Whrrr NFS is not supported on unprivileged Prox LXCs (same constraint as phoenix/sonarqube — see site.yml backup-client play).

The implemented tier-1 job is external-postgresql-companion. On infra-services, fetch-postgresql-companion.sh uses the patch-controller SSH key to run PostgreSQL tools as the in-guest postgres user. It captures:

  • custom-format dumps of companion, tdler, and reddit_explorer;
  • cluster globals;
  • row counts for the protected Companion, TDLer, and all Reddit Explorer public tables; and
  • SHA-256 checksums for every artifact.

The fetch is assembled and checksum-verified in temporary directories before the restic staging directory is replaced. Restic stores the encrypted primary repository at /mnt/infra-backups/homelab-restic/external-postgresql-companion on the Whrrr NAS and copies tier-1 snapshots to Backblaze B2. Local staging is cleared after a successful backup. The first Ansible deployment starts this job immediately and records a durable bootstrap marker; later runs follow the normal tier-1 timer.

The weekly restore rotation includes this external manifest. When selected, it starts a network-isolated disposable postgres:18 container, restores globals and all three dumps, checks every recorded table count, and removes the container on success or failure. It does not connect to or modify live PostgreSQL at 192.168.6.252. See the restore runbook.

Secondary — Prox vzdump of LXC 102: whole-guest DR only. Crash-consistent at best unless dump uses guest freeze; not a substitute for pg_dump. Prox currently has no cluster vzdump schedule (jobs.cfg empty) — add a job to infra-backups if you want guest-level recovery.

Avoid relying on vzdump alone or mounting Whrrr restic from inside the CT.

Runbooks