--- title: "05 — Installing and Connecting to PostgreSQL" uid: postgresql-dba-installation-setup tags: ["roadmap:postgresql-dba", "psql", "postgresql", "docker", "cloud", "installation", "setup"] excerpt: "Every install method runs the same Postgres — one server process managing one data directory. The only real choice: who owns that process and that directory." date: 2026-08-13T03:27:52+0000 source: https://www.aveshina.my.id/en/blog/postgresql-dba-installation-setup --- 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]. ```figure ``` 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/](https://www.postgresql.org/download/) [2] PostgreSQL Global Development Group, "Install with APT," 2024. [Online]. Available: [https://www.postgresql.org/download/linux/ubuntu/](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/](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](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](https://www.postgresql.org/docs/current/app-psql.html) ```quiz Q: What are the three things every PostgreSQL installation creates? - A data directory, a server process, and a postgres superuser role - A GUI client, a web dashboard, and a backup folder correct: 0 explain: Every install produces a data directory (PGDATA), the postgres server process on port 5432, and a postgres superuser role, plus client tools like psql. Q: Why reach for Docker when running Postgres locally? - It is the only way to get the latest version - It gives an isolated, reproducible instance with a known version that can be torn down when done correct: 1 explain: Docker provides isolation and reproducibility — one command spins up a specific Postgres version, and removing the container cleans everything up. Q: What does the psql command \d do? - Deletes the current database - Describes the structure of a table (or \dt to list tables) correct: 1 explain: \d describes a table's columns and types; \dt lists tables, \l lists databases, \dx lists extensions. These are psql shortcuts, not SQL. Q: A managed cloud Postgres (RDS, Supabase) typically handles which chores for you? - Writing your application queries - Backups, point-in-time recovery, read replicas, and version upgrades correct: 1 explain: Managed services automate operational chores — backups, PITR, replication, upgrades, patching. The tradeoff is cost and less control over extensions and tuning. Q: The common-denominator client that works against any Postgres install is… - psql - a specific GUI tool correct: 0 explain: psql ships with Postgres and is guaranteed present on the server. GUIs are convenient but not always available, especially in incident response. ```