The default choice, and the settings that decide whether it stays boring.
PostgreSQL is the default choice for relational data in almost every stack CodeNx covers, and it is well-behaved enough that most of the operational work is configuration done once.
Reach for it for anything transactional, anything relational, and most things people reach for a document store for — the JSONB type covers a lot of ground without giving up constraints or joins. Reach for something else when the access pattern is genuinely key-value at high volume, or when you need full-text search good enough to be a product feature rather than a filter.
Four settings decide whether a self-hosted instance is safe.
listen_addresses, which should stay on loopback unless a remote host genuinely
needs access. pg_hba.conf, which is evaluated top to bottom with first match
winning — a permissive line above a strict one makes the strict one dead code.
TLS with hostssl rules for anything not on loopback. And role design, where the
application login must not be the object owner and must never be a superuser.
The two operational failures worth pre-empting are timeouts and backups. Without
statement_timeout and idle_in_transaction_session_timeout, one stuck client
holds locks and blocks vacuum indefinitely. And a dump that has never been
restored is a hypothesis — restoring into a scratch database on a schedule is
the only thing that turns it into a backup.
Everything published here states the major version it was verified against. PostgreSQL is stable across minors, but defaults have changed meaningfully between majors, and instructions written for 12 are not always right for 17.
Start here
A nightly backup for a self-hosted Docker host: database dumps taken with the right tools, file volumes snapshotted with restic, a systemd timer to run it, and a restore test that proves it works.
A reading order that builds PostgreSQL knowledge in the sequence it is actually needed.
Failure modes that come up repeatedly with PostgreSQL, and where each one is solved.
Keep the listener off the public internet, get pg_hba.conf right, require TLS for remote connections, and give the application a role that cannot drop your tables — on PostgreSQL 17 and Ubuntu 24.04.
A nightly backup for a self-hosted Docker host: database dumps taken with the right tools, file volumes snapshotted with restic, a systemd timer to run it, and a restore test that proves it works.
A nightly backup for a self-hosted Docker host: database dumps taken with the right tools, file volumes snapshotted with restic, a systemd timer to run it, and a restore test that proves it works.
No. Separate them — an owner role that holds the schema and cannot log in, and a login role with only the table privileges the application needs. Run migrations as the owner from your deployment pipeline.
`pg_dump` for portability and selective restore; a filesystem or base backup with WAL archiving for point-in-time recovery on a large database. Most self-hosted setups need the first and think they need the second.
The defaults are conservative — loopback only, peer authentication for local sockets. The risk comes from changes made afterwards, usually opening `listen_addresses` to get a remote client working.