Postgres – Install and Connect

You do not need Postgres installed on your machine. You need a Postgres you can throw away and rebuild, which is a container, and a copy of psql to talk to it.

This post gets you from nothing to a database you can connect to, then to a docker-compose.yml worth keeping in the repository.

One command

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

docker exec -it pg psql -U postgres

That is a working database. Three things about it are worth knowing before you rely on it:

• POSTGRES_PASSWORD is required. The image refuses to start without it, on purpose.
• The data lives inside the container. docker rm pg and it is gone. Fine for an experiment, not for the database you are developing against for a month.
• The image only runs initialisation on an empty data directory. Change POSTGRES_DB or an init script later and nothing happens, because the directory is no longer empty. This confuses everyone exactly once.

Getting psql itself

docker exec gets you a psql inside the container, which is fine until you want to run a file from your machine or copy a result out. Install the client tools natively — just the client, not the server:

# macOS
brew install libpq && brew link --force libpq

# Debian/Ubuntu
sudo apt install postgresql-client-16

psql --version
# psql (PostgreSQL) 16.15

Keep the client's major version at or above the server's. An older psql against a newer server mostly works and then fails on one meta-command with a message about a missing system column, which is not a fun half hour.

The version worth pinning

Use the same major version you will run in production, and pin it. postgres:16-alpine is a smaller image than postgres:16 and behaves identically for everything in this track. Plain postgres means "whatever is newest today", which is how a team ends up with three different databases.

docker exec pg psql -U postgres -c 'SHOW server_version;'
# 16.15

The compose file to keep

This is the Postgres service from the booking application every example in this track runs against, unedited:

services:
  postgres:
    image: postgres:16-alpine
    container_name: stayhub-postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: stayhub
      POSTGRES_PASSWORD: stayhub
      POSTGRES_DB: stayhub
    ports:
      - "5433:5432"
    volumes:
      - stayhub-pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U stayhub -d stayhub"]
      interval: 5s
      timeout: 5s
      retries: 10

volumes:
  stayhub-pgdata:

Four decisions in there are the whole point of using compose rather than docker run:

The healthcheck is the one people leave out. depends_on alone waits for the container to start, not for Postgres to be ready — and Postgres starts, initialises, and restarts itself once during first boot.

  api:
    depends_on:
      postgres:
        condition: service_healthy

The password rule that looks like a bug

This works with no password at all:

docker exec -it stayhub-postgres psql -U stayhub -d stayhub

and this asks for one:

psql "postgresql://stayhub:stayhub@localhost:5433/stayhub"

Not a misconfiguration. The image's default pg_hba.conf trusts connections that originate inside the container and demands a password over the published port — which is how your application and every tool on your machine connect. A wrong password there is refused.

Connecting

One connection string format, understood by psql, JDBC drivers, SQLAlchemy, and every Postgres client worth using:

postgresql://user:password@host:port/database

# psql
psql "postgresql://stayhub:stayhub@localhost:5433/stayhub"

# non-interactive, one query
psql "postgresql://stayhub:stayhub@localhost:5433/stayhub" -c 'SELECT count(*) FROM properties;'

Language drivers take the same string with their own scheme prefix:

# SQLAlchemy with psycopg 3
DATABASE_URL = "postgresql+psycopg://stayhub:stayhub@localhost:5433/stayhub"

Never put the password in the URL in anything committed. psql reads PGPASSWORD from the environment, and a ~/.pgpass file (chmod 600) holds credentials per host so no command line carries them.

Seeding a fresh database

Files mounted into /docker-entrypoint-initdb.d run once, in filename order, when the data directory is empty:

    volumes:
      - stayhub-pgdata:/var/lib/postgresql/data
      - ./initdb:/docker-entrypoint-initdb.d:ro

One setting can only be chosen here and never afterwards: the cluster's locale and encoding are fixed when the data directory is created, and changing them later means dumping and reloading the whole database. The defaults in the official image are UTF-8 already, so this matters mainly if someone hands you a cluster built with something else.

    environment:
      POSTGRES_INITDB_ARGS: "--locale=C.UTF-8 --encoding=UTF8"

Use init scripts for the things that must exist before your application's own migrations run — an extension, a role, a schema. Do not use it for your schema itself. Schema belongs in a migration tool that also knows how to move an existing database forward, which is a post of its own later in this track.

-- initdb/01-extensions.sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS btree_gist;

Proving it works

Three checks, in the order worth running when something is wrong. Is the container healthy, is Postgres accepting connections, and can you authenticate?

docker compose ps postgres
# STATUS: Up 2 minutes (healthy)

docker exec stayhub-postgres pg_isready -U stayhub -d stayhub
# /var/run/postgresql:5432 - accepting connections

psql "postgresql://stayhub:stayhub@localhost:5433/stayhub" -c 'SELECT 1;'

If the first two pass and the third does not, the problem is the password, the port, or the host — not the database. If the container is unhealthy, the logs say why and they are the first place to look, not the last:

docker compose logs --tail 50 postgres

The two messages you will actually meet there are database files are incompatible with server, which means a volume created by a different major version — the fix is a new volume, not a flag — and role "postgres" does not exist, which means you set POSTGRES_USER and are still connecting as postgres.

One container, several databases

A cluster serves many databases, so you do not need a second container to get a second database. This is worth doing early: keep the one your application migrates against separate from the one you are free to break.

docker exec stayhub-postgres createdb -U stayhub scratch

# same schema and data as an existing database, copied
docker exec stayhub-postgres createdb -U stayhub -T stayhub scratch2

docker exec stayhub-postgres psql -U stayhub -l

-T copies an existing database as a template, and it requires that nobody is connected to the template at the time. Later posts in this track run against a database built exactly this way — the same schema as the application, filled with 400,000 bookings, so that a query plan has something real to plan against.

Starting over

docker compose down          # stop, keep the data
docker compose down -v       # stop AND delete the volume
docker compose up -d         # back up; init scripts run again on the now-empty volume

Being able to do that in ten seconds is what makes it safe to experiment for the rest of this track.