Skip to content

External PostgreSQL (install, harden, HA, backup)

Repod's DATABASE_URL is a plain PostgreSQL connection string with no code path that assumes the database lives in a Docker container — see Deployment guide — using an external PostgreSQL database for how to point docker-compose.yaml at one. This page is the companion guide for the other half of that decision: how to actually stand up, secure, make highly available, and back up the PostgreSQL instance itself, once you've decided to run it outside the bundled db: container.

This is general PostgreSQL server administration, written for a Repod operator who wants the same "own your database" depth that dedicated artifact-repository products document for this exact scenario. Nothing here is Repod-specific magic — a PostgreSQL DBA who already runs production Postgres elsewhere will recognize every step. PostgreSQL only; no other database engine is in scope for Repod.

1. Compatibility & requirements

Reference version: PostgreSQL 16. It's what postgres:16-alpine ships in Repod's own docker-compose.yaml/docker-compose.rpm.yml, and what the test suite runs against — treat it as the version Repod is validated against, and prefer it (or a newer major version, once you've confirmed compatibility yourself) for a new deployment.

Minimum supported version. Nothing in Repod's own schema or query layer requires a specific recent PostgreSQL feature:

  • JSONB columns (backend/db/tables.py) have been available since PostgreSQL 9.4.
  • Advisory locks (pg_try_advisory_lock()/pg_advisory_unlock(), used for leader election in backend/services/leader_election.py) are a long-standing core feature with no version-specific behavior Repod depends on.
  • Schema-based multi-tenancy (CREATE SCHEMA IF NOT EXISTS + SET LOCAL search_path, used in SaaS mode) is standard SQL supported by every PostgreSQL version in any realistic support window.
  • Alembic (backend/alembic/versions/) generates plain DDL — CREATE TABLE, ALTER TABLE ADD COLUMN, indexes — nothing exotic.

In practice this means Repod itself does not force a minimum version newer than what any actively-supported PostgreSQL release already provides. Don't read that as license to run an old version anyway — PostgreSQL's own versioning policy supports each major version for 5 years after its initial release, after which it stops receiving security fixes. Run whatever major version is still inside that support window at deployment time, and check that page yourself rather than trusting a hardcoded date in this guide (support windows move forward every year; a specific EOL date written here today would go stale). As a floor, don't provision anything older than PostgreSQL 13 — beyond "still supported," it has no bearing on Repod compatibility specifically.

Client tooling. None to install on Repod's side. pg_dump/pg_restore already ship inside the backend-api image (used by Repod's own built-in backup feature, see Back up and restore Repod) — you only need psql/pg_dump/pg_restore client binaries on whatever host you administer the PostgreSQL server from, matched to the server's major version as usual PostgreSQL practice.

Locale & encoding. Create the database with UTF8 encoding — nothing in Repod's code is encoding-aware or locale-sensitive (it stores JSON and plain text via SQLAlchemy/psycopg2, both UTF-8 throughout), so there's no Repod-specific locale requirement. en_US.UTF-8 or the locale-independent C.UTF-8 are both safe, standard choices; avoid SQL_ASCII regardless of what your OS defaults to, since it silently allows encoding-inconsistent data into a database Repod assumes is UTF-8 end to end.

Extensions. None are required. There is no CREATE EXTENSION anywhere in Repod's backend — the codebase deliberately doesn't lean on Postgres extensions for its core functionality. (One extension, pg_stat_statements, is a genuinely worthwhile operational addition for query monitoring — see Additional operational parameters below. That's an operator's own monitoring choice, not something Repod requires to function, and it doesn't contradict the "no extensions required" statement above.)

2. Installation

Use the official PostgreSQL Global Development Group (PGDG) repositories rather than your distribution's own bundled postgresql package — distro repos typically lag several major versions behind, and PGDG is how the PostgreSQL project itself recommends installing a current, actively supported release on Linux. The steps below match the two OS families Repod's own client setup guide already documents: Debian/Ubuntu (APT) and RHEL-family/openSUSE (RPM).

Debian / Ubuntu (PGDG APT repository)

# Import PGDG's signing key
sudo apt install -y curl ca-certificates gnupg
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Add the PGDG repository (matches your Debian/Ubuntu codename automatically)
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16

apt-get install postgresql (no version suffix) would pull whatever version your distro's own repos carry — always install the versioned package (postgresql-16) from the PGDG repo instead, so the version is a deliberate choice, not whatever happened to be current when your distro release was cut.

RHEL family (AlmaLinux / Rocky / RHEL) — PGDG yum/dnf repository

# Install the PGDG repository RPM (adjust the URL for your major OS version, here EL9)
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# RHEL 8/9 ship their own built-in "postgresql" module stream, which
# conflicts with PGDG's own postgresql16-server package — disable it first
sudo dnf -qy module disable postgresql

sudo dnf install -y postgresql16-server postgresql16-contrib

# Initialize the data directory and enable the service
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

openSUSE Leap

sudo zypper addrepo https://download.postgresql.org/pub/repos/zypp/repos/opensuse/15.6/postgresql16-16/postgresql16-16.repo
sudo zypper refresh
sudo zypper install postgresql16-server postgresql16
sudo systemctl enable --now postgresql

Exact PGDG repository URLs may shift over time

The PGDG key/repo URLs above reflect the current, standard PGDG setup procedure at the time of writing. If any of them 404, check postgresql.org/download for the current repository-setup instructions for your distribution — the underlying mechanism (a dedicated PGDG apt/yum repo, disable the distro's bundled module stream on RHEL 8/9) is stable; exact filenames occasionally change across PostgreSQL project infrastructure updates.

Create the repod database and role

Once the server is installed and running, create a dedicated database and a role scoped to only that database — never point Repod at a shared, pre-existing database, and never use the postgres superuser role for the application connection.

sudo -u postgres psql <<'SQL'
CREATE ROLE repod WITH LOGIN PASSWORD 'CHANGE_ME';
CREATE DATABASE repod OWNER repod ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE repod TO repod;
SQL

Generate the password the same way Repod's own deployment guide already generates POSTGRES_PASSWORD for the bundled container:

openssl rand -hex 24

Use that value both as the role's password above and in DATABASE_URL:

DATABASE_URL=postgresql://repod:<same password>@<external-host>:5432/repod

Verify it worked

psql "postgresql://repod:<password>@<external-host>:5432/repod" -c '\conninfo'

You should see You are connected to database "repod" as user "repod". If this connects, entrypoint.sh will succeed at running Alembic migrations against it the same way it would against the bundled container — see Deployment guide, Step 4 for wiring DATABASE_URL into docker-compose.yaml.

3. Security hardening

Network binding

Set listen_addresses in postgresql.conf to the specific interface(s) that need to accept connections — never leave it at the default (localhost-only, which would make it unreachable from backend-api entirely) without immediately pairing a wider binding with a restrictive pg_hba.conf:

postgresql.conf
listen_addresses = '10.0.1.5'   # the private-network interface backend-api reaches, not '*'
port = 5432

Binding to * is sometimes unavoidable (e.g. the interface address isn't static), but if you do, pg_hba.conf — not the listen address — must be what actually restricts who can connect. Never rely on listen_addresses alone as an access control.

pg_hba.conf — least privilege

Only allow the repod role to connect, only from the specific host(s) or subnet running backend-api, and only using scram-sha-256 password authentication (PostgreSQL's current recommended password method — never trust, and never the older md5 method for a new deployment):

pg_hba.conf
# TYPE  DATABASE  USER   ADDRESS           METHOD
host    repod     repod  10.0.1.10/32      scram-sha-256   # single backend-api host
host    repod     repod  10.0.1.0/24       scram-sha-256   # or a subnet, for multiple replicas (HA)

Remove or comment out any broader default rule your installer added (some distro packages ship a permissive 0.0.0.0/0/::/0 line by default — check for it explicitly rather than assuming it isn't there). Set password_encryption = scram-sha-256 in postgresql.conf too, so newly created roles hash with SCRAM rather than the legacy MD5 format:

postgresql.conf
password_encryption = scram-sha-256

Reload after any pg_hba.conf change: sudo systemctl reload postgresql (or SELECT pg_reload_conf(); from psql) — no restart, no connection drop, required.

TLS for connections in transit

Enable TLS on the server:

postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Use a certificate issued by your internal CA (or a public CA if the endpoint is externally reachable) — a self-signed certificate works for testing but means Repod can't verify the server's identity against a real trust chain in verify-full mode.

Express the TLS requirement in DATABASE_URL via the standard libpq/psycopg2 sslmode query parameter — psycopg2 (and SQLAlchemy's postgresql+psycopg2:// dialect, what Repod uses) both parse standard libpq connection parameters appended to the URL:

# Require TLS, but don't verify the server certificate against a CA
DATABASE_URL=postgresql://repod:<password>@pg-host:5432/repod?sslmode=require

# Require TLS AND verify the server certificate against a trusted CA
# (the strongest mode — also verifies the hostname matches the certificate)
DATABASE_URL=postgresql://repod:<password>@pg-host:5432/repod?sslmode=verify-full&sslrootcert=/path/to/ca.crt

sslrootcert must point to a path inside the backend-api container — mount your CA certificate into the container image/volume if you use verify-full. sslmode=require alone (no sslrootcert) encrypts the connection but doesn't authenticate the server, which is still a real improvement over an unencrypted connection and a reasonable minimum on a trusted private network; prefer verify-full whenever the connection crosses a network boundary you don't fully control.

Firewall

Only the host(s) running backend-api should ever be able to reach port 5432 — the same "never expose directly to the internet" principle Repod's own docs already state for its other network-facing services (nginx-fronted APT/RPM repos, the SaaS internal-token endpoints). Restrict with your platform's firewall (ufw, firewalld, a cloud security group) in addition to pg_hba.conf — defense in depth, not a substitute for it.

# ufw example — only allow the backend-api subnet
sudo ufw allow from 10.0.1.0/24 to any port 5432 proto tcp

Password rotation & least privilege

  • Rotate the repod role's password periodically (ALTER ROLE repod WITH PASSWORD '<new value>';), update DATABASE_URL in backend.env, and restart backend-api — there is no live credential-reload path, the connection pool is built once at process startup (db/engine.py:get_engine()).
  • The repod role should own its database and nothing else — it needs no SUPERUSER, CREATEDB, or CREATEROLE attribute. The CREATE ROLE ... WITH LOGIN PASSWORD ... statement above deliberately grants none of those.
  • Never reuse the postgres superuser account as the application role — Repod never needs superuser privileges for anything it does (schema creation via Alembic, CREATE SCHEMA for SaaS tenants, and advisory locks are all ordinary-role-privileged operations).

4. High availability

This section is about PostgreSQL server-side HA — running a resilient database cluster that survives losing a node. It's a different concern from Deploy multi-replica high availability, which covers application-level HA for backend-api itself and simply assumes "an external HA PostgreSQL endpoint" as a prerequisite without explaining how to build one. This page is what that guide's prerequisites section links to.

Streaming replication fundamentals

PostgreSQL's built-in replication mechanism is a primary node that ships its write-ahead log (WAL) to one or more standby nodes, which continuously replay it to stay in sync:

  • Asynchronous replication (the default) — the primary commits a transaction and returns to the client without waiting for any standby to confirm receipt. Lowest write latency; a standby can lag behind by a small, usually sub-second window. If the primary fails before a standby catches up, the most recent transactions can be lost — an availability/durability tradeoff, not a bug.
  • Synchronous replication (synchronous_standby_names) — the primary waits for acknowledgment from at least one designated standby before committing. Guarantees zero data loss on failover to that standby, at the cost of added write latency (every commit now waits on a network round trip) and, if the synchronous standby becomes unreachable, either stalled writes or a fallback behavior you must explicitly configure (synchronous_commit mode).

Choose based on what you actually need: asynchronous is the common default for most deployments; synchronous is worth the latency cost specifically when a few seconds of lost transactions on failover would be unacceptable (financial/compliance-sensitive data, for instance — evaluate this against your own requirements, not a blanket recommendation either way).

Automated failover tooling

PostgreSQL's own replication is the data-shipping mechanism — it does not by itself promote a standby to primary automatically when the primary dies. That's what a dedicated failover-orchestration tool adds. Three widely used, realistic options, in rough order of maturity/adoption:

Tool Approach Notes
Patroni A Python agent running alongside each PostgreSQL node, using a distributed consensus store (etcd, Consul, or ZooKeeper) to elect and maintain the primary The most widely adopted modern option for automated PostgreSQL HA; most capable, but requires operating a separate DCS (distributed configuration store) cluster alongside PostgreSQL itself — real added operational surface
repmgr A PostgreSQL-native replication manager with its own metadata schema; can automate failover via repmgrd, or be used purely for manual/assisted promotion Simpler to reason about and no external DCS dependency, at the cost of less sophisticated automatic split-brain protection than Patroni
pg_auto_failover A simpler two/three-node model (originally from Citus Data, now Microsoft) with its own lightweight monitor process, no external DCS Easier to stand up than Patroni for a small cluster; less battle-tested at very large scale

Repod does not integrate with any of these tools, and doesn't need to. Repod's own code has no awareness of PostgreSQL replication topology at all — it just needs one stable connection string that always resolves to whichever node is currently the primary. Whichever tool you choose is entirely your own operational decision, made independently of Repod.

Presenting a single stable endpoint to Repod

Whatever failover mechanism you pick, backend-api must connect through a single endpoint that transparently routes to the current primary — never directly to a specific standby's hostname, which would silently break the moment that node stops being the primary. Standard options:

  • A connection pooler/proxy in front of the cluster — PgBouncer or HAProxy configured with a health check that only routes to whichever node currently reports as primary (Patroni ships a REST health-check endpoint designed exactly for this; HAProxy can poll it directly). This is the most common production pattern and the one most HA tutorials for PostgreSQL converge on.
  • A floating/virtual IP — moved to whichever node is primary by the failover tool itself (Patroni supports this via a callback script; keepalived is a common companion for the VIP mechanics). Simple, but requires the nodes to share an L2 network segment.
  • DNS-based failover — a low-TTL DNS record updated to point at the current primary. Works, but is the least immediate of the three — DNS caching (including PostgreSQL client-side caching, and any resolver in between) can delay convergence beyond what a proxy or VIP achieves.

DATABASE_URL then simply points at that stable endpoint's host and port — the same variable, same format, as pointing at a single non-HA instance:

DATABASE_URL=postgresql://repod:<password>@pg-ha-endpoint:5432/repod?sslmode=require

What Repod does (and doesn't do) during a failover

Repod does not participate in PostgreSQL failover in any way — there is no Repod code that detects a primary/standby topology, triggers a promotion, or reacts specially to one. All it does is reconnect. SQLAlchemy's connection pool (pool_pre_ping=True, pool_recycle=1800 — backend/db/engine.py) already validates a connection before reusing it and transparently opens a new one if the old connection is stale or broken, so once your chosen HA mechanism finishes promoting a new primary and the stable endpoint routes to it again, backend-api picks that up on its next query with no restart and no special configuration — exactly the same reconnect behavior the application-level HA guide already documents relying on.

5. Backup & restore (PostgreSQL-server-operator level)

This section covers additional protection at the PostgreSQL-server operator level — complementary to, not a replacement for, Repod's own admin-triggered pg_dump-based backup already documented in Back up and restore Repod and the operations runbook. Repod's built-in backup already works unmodified against an external PostgreSQL instance — it runs pg_dump <DATABASE_URL> -F c from inside the backend-api container (backend/services/backup.py), so it needs nothing more than network reachability and works identically whether the database is a sibling container or a remote managed instance. What's covered here is what a PostgreSQL server operator would add on top for server-level durability, independent of whether Repod's own backup runs at all: physical backups, continuous WAL archiving, and point-in-time recovery (PITR).

Physical backups with pg_basebackup

pg_basebackup takes a consistent binary copy of the entire data directory directly from a running server — the foundation both for standing up a new standby and for a physical (as opposed to logical/pg_dump) base backup:

pg_basebackup -h pg-host -U repod_backup -D /backups/base/$(date +%Y%m%d) \
  -F tar -z -P -X stream

-X stream includes the WAL generated during the backup itself, so the result is immediately restorable on its own without needing a separately archived WAL segment for that exact point. Use a dedicated role with the REPLICATION privilege for this (not the repod application role) — pg_basebackup needs replication-level access, a materially different privilege than the application ever needs.

Continuous WAL archiving (for point-in-time recovery)

A single base backup only restores you to the moment it was taken. To recover to any arbitrary point between base backups — "restore to just before the bad migration ran at 14:32" — PostgreSQL needs every WAL segment generated since the base backup, continuously archived:

postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'   # or push to object storage, see below
wal_level = replica

A local cp is illustrative only — in a real deployment, archive_command typically ships each WAL segment to durable storage (object storage, a separate host) rather than a local directory that shares the same failure domain as the primary.

Dedicated backup/PITR tools

Hand-rolled pg_basebackup + archive_command scripting works, but at production scale, two dedicated tools are the standard choice for automating this properly (parallel backups, retention policies, backup verification, streamlined PITR) — reach for one of these rather than maintaining custom scripting once you're relying on this for real recovery guarantees:

  • pgBackRest — widely adopted, supports parallel backup/restore, backup verification, and multiple repository targets (local, S3-compatible object storage, Azure, GCS).
  • Barman ("Backup and Recovery Manager") — originally from 2ndQuadrant/EnterpriseDB, similarly mature, with a slightly different operational model (a dedicated backup host that pulls from the PostgreSQL server(s) it manages).

Both are genuine, standard choices in production PostgreSQL deployments — pick based on your existing operational tooling and team familiarity rather than either being a strictly "better" default.

PITR restore procedure (outline)

The general shape of a point-in-time restore, regardless of which tool orchestrates it:

  1. Restore the most recent base backup taken before your target recovery time, into a fresh data directory.
  2. Configure the recovery target — in postgresql.conf (or recovery.signal + postgresql.auto.conf depending on your PostgreSQL version's exact mechanism), set: 
    restore_command = 'cp /archive/wal/%f %p'   # or the tool's own restore command
recovery_target_time = '2026-08-21 14:30:00+00'
  3. Start the server. PostgreSQL replays WAL from the base backup forward until it reaches recovery_target_time, then stops recovery and comes up as a normal, writable primary at that exact point in time.
  4. Verify the restored state before pointing backend-api at it — check that the data you expect (e.g. the last known-good package upload, the last CVE decision before the incident) is present and that nothing past the target time leaked in.

pgBackRest and Barman both wrap this exact sequence into a single restore command (pgbackrest restore --target=... --type=time, barman recover --target-time=...) — the manual steps above are what either tool does under the hood, useful to understand even if you use the tool's own command in practice.

Operational discipline

Apply the same discipline Repod's own backup documentation already asks for at the application layer, at the PostgreSQL-server layer too:

  • 3-2-1: at least 3 copies of your data, on 2 different media, with 1 copy off-site — a WAL archive and base backups sitting in the same datacenter as the primary aren't a real disaster-recovery plan, only a faster local-restore convenience.
  • RPO/RTO: decide, explicitly, how much data loss is acceptable (Recovery Point Objective — governed by how frequently you take base backups and how continuously WAL is archived/shipped) and how long a restore is allowed to take (Recovery Time Objective — governed by base backup size, network speed to the archive, and how practiced the procedure is). Write both numbers down; don't discover them for the first time during a real incident.
  • Test restores regularly. A WAL archive or base backup you've never actually restored from is an unverified assumption, not a backup. Run the PITR procedure above against a scratch environment periodically, exactly the same "restore testing" discipline the operations backup-restore runbook already asks for with Repod's own pg_dump archives.

6. Additional operational parameters worth setting

Connection pool sizing

Repod's own SQLAlchemy engine (backend/db/engine.py:get_engine()) is configured with:

pool_size=10
max_overflow=20
pool_pre_ping=True
pool_recycle=1800   # 30 minutes

That's per backend-api process — up to 30 connections (10 base + 20 overflow) from a single replica under load. These values are hardcoded, not exposed via an environment variable — there is currently no DATABASE_POOL_SIZE-style knob to tune this without editing backend/db/engine.py directly. Size your server's max_connections (postgresql.conf) with that in mind, multiplied by however many backend-api replicas you run (see the HA guide for multi-replica deployments), plus headroom for your own administrative connections, monitoring agents, and any pooler in front (if you place PgBouncer between backend-api and PostgreSQL for the HA endpoint above, size max_connections against PgBouncer's own backend pool, not directly against backend-api's 30).

postgresql.conf's own default (max_connections = 100) is a reasonable starting point for a single-replica deployment; raise it explicitly (and budget the corresponding memory — each connection carries real overhead) for multi-replica HA.

pg_stat_statements for query monitoring

Not required by Repod, but a genuinely useful, standard addition for visibility into what's actually slow — the extension in this codebase's "no extensions required" statement was strictly about what Repod needs to function, not a recommendation against operator-chosen observability tooling:

postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Requires a server restart (it's a shared_preload_libraries entry, not a reloadable setting), then:

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC
  LIMIT 20;

Timezone configuration

PostgreSQL's server-level timezone setting does not need to match whatever timezone backend-api or its host run in. Every timestamp column in Repod's schema is TIMESTAMP WITH TIME ZONE (TimestampTZ = TIMESTAMP(timezone=True), backend/db/tables.py) — PostgreSQL stores TIMESTAMPTZ values internally normalized to UTC regardless of the session's timezone setting, and converts to/from that session's timezone only for display. This means a mismatch between the server's configured timezone and Repod's own (UTC-oriented, per the many datetime.now(timezone.utc) call sites throughout the backend) has no correctness impact — it's purely cosmetic for anyone running raw psql queries against the server directly. Leaving timezone at its OS-inherited default is fine; there's no Repod-specific reason to set it to UTC, though doing so is a reasonable, common convention if you'd like psql sessions to display times the same way the application reasons about them.

Monitoring & alerting basics

General production PostgreSQL guidance, not specific to Repod's schema or query patterns:

  • Replication lag (if running the HA setup above) — SELECT * FROM pg_stat_replication; on the primary, or pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn() on a standby. Alert if lag grows unexpectedly, especially before relying on a standby for a synchronous-replication durability guarantee.
  • Connection count — SELECT count(*) FROM pg_stat_activity; against max_connections. Alert well before saturation; a connection-exhausted PostgreSQL server rejects new connections outright, which for Repod means every request that needs a fresh pool connection starts failing.
  • Disk usage — both the data directory (SELECT pg_database_size('repod');) and, separately, wherever WAL archives/base backups land — a full WAL archive volume with archive_mode = on and a failing archive_command will eventually block the primary from recycling WAL segments and can fill its own data disk.
  • Autovacuum activity — SELECT * FROM pg_stat_user_tables; (columns last_autovacuum, n_dead_tup) — standard PostgreSQL housekeeping to watch on any table with heavy UPDATE/DELETE churn (Repod's manifests, inventory_cve, and audit-adjacent tables are the more write-heavy ones in this schema).

See also