05 — Installing and Connecting to PostgreSQL

The boring prelude — getting Postgres running — turned out to be the perfect introduction to its architecture. The framing that made it click: every installation method is the same Postgres under the hood — one server process managing one data directory — and the only real choice is who owns that process and that directory. [1] Docker, a system package manager, and a managed cloud service are all the same architecture; they differ in who handles setup, upgrades, backups, and the operating-system plumbing. Once that landed, picking an install method became a question about my workload, not a research project.

What an installation actually creates

A Postgres install produces three things [1]:

• A data directory (the PGDATA environment variable points at it) holding all databases, the WAL, config, and logs.
• A server process (postgres) that owns that directory and listens on a port (default 5432).
• A superuser role named postgres, plus the client tools (psql, pg_dump, pg_restore) for talking to it.

Everything else — databases, schemas, tables — is created from inside, via SQL. The unit of administration is the cluster (one server, one data directory).

The three install paths

The roadmap points at three families of install, and they map cleanly onto "who manages it" [2][3][4]:

• Package manager — apt on Debian/Ubuntu, yum/dnf on RHEL, brew on macOS. The OS owns the binaries and the service. Best when I want Postgres running natively on a machine I control, with the OS handling start/stop and upgrades [2].
• Docker — the official postgres image. One command gives me an isolated, reproducible instance with a known version, and I can throw it away when I'm done. Best for local development, CI, and any case where isolation and reproducibility matter [3].
• Managed cloud — RDS, Cloud SQL, Supabase, Neon, and others run Postgres for me, handling backups, replication, and upgrades as part of the service. Best for production when I'd rather pay than staff a DBA on call [4].

The Docker path I reach for most often for local work is a one-liner:

docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16

That gives me a throwaway Postgres 16 on port 5432, ready in seconds, gone when I tear it down.

psql: the client I should not avoid

The roadmap is right to give psql its own node, because it's the client I keep underestimating [5]. psql is a terminal front-end to Postgres: I connect with psql -h host -U user -d dbname, and I get an interactive prompt where SQL is first-class. Two habits made it stick:

• Backslash commands — \d describes a table, \dt lists tables, \l lists databases, \dx lists extensions, \? lists all backslash commands. These are not SQL; they're psql shortcuts for inspecting the catalog.
• Reading from files — \i schema.sql runs a file. For migrations and seed data, this beats pasting into the prompt.

The temptation is to jump straight to a GUI client. GUIs are fine, but psql is the one tool guaranteed to be on the server when something breaks at 2am, so being fluent in it is operational insurance.

Cloud deployment: the tradeoff

The managed-cloud option is the one I weigh most carefully for production [4]. The pitch is that the provider handles the chores that are easy to get wrong: automated backups, point-in-time recovery, read replicas, major-version upgrades, and security patching. The cost is money (managed Postgres is markedly more expensive than self-hosting on equivalent hardware) and a loss of control — I cannot run arbitrary extensions, and I'm bound by the provider's supported versions and feature flags. For a portfolio site or a side project, a managed instance is a clear win. For a workload needing a specific extension or a tuned configuration, self-hosting (often on Kubernetes with an operator) earns its complexity.

How I use this

The decision is now a quick matrix. For local development and tests, Docker — reproducible and disposable. For production where I want to ship features rather than operate a database, a managed cloud service. For production where I need specific extensions, kernel-level tuning, or cost control at scale, self-hosted with a package manager or a Kubernetes operator. In every case, I keep psql in muscle memory, because it's the common denominator that works against any of these. The install method is a packaging choice; the database underneath is the same.

References

[1] PostgreSQL Global Development Group, "Installation and Setup," 2024. [Online]. Available: https://www.postgresql.org/download/

[2] PostgreSQL Global Development Group, "Install with APT," 2024. [Online]. Available: https://www.postgresql.org/download/linux/ubuntu/

[3] Docker, "How to use the Postgres Docker official image," 2024. [Online]. Available: https://www.docker.com/blog/how-to-use-the-postgres-docker-official-image/

[4] Prisma, "5 ways to host PostgreSQL databases," 2024. [Online]. Available: https://www.prisma.io/dataguide/postgresql/5-ways-to-host-postgresql

[5] PostgreSQL Global Development Group, "psql," 2024. [Online]. Available: https://www.postgresql.org/docs/current/app-psql.html