Install PostgreSQL — macOS, Windows, Linux & Docker

Prerequisites: none. This is the very first stop. You don't need to know any SQL yet — you just need a terminal and about fifteen minutes. Every error message in this post is real; I show you the failure first, then the fix, so when you hit it yourself you'll recognize it instead of panicking.

1What PostgreSQL Is (and Why Real Teams Pick It)

PostgreSQL (or just "Postgres") is an open-source relational database server. It runs as a background service on a machine, listens on port 5432, and stores your data in tables you query with SQL. It's been in development since the 90s, it's free, and it powers production systems at companies of every size — from two-person startups to Instagram-scale platforms.

Why do experienced teams reach for it? Because it's strict where it matters. Postgres enforces your data types and constraints instead of silently "helping" you, it has first-class support for JSON, full-text search, and window functions, and its transactional behavior (MVCC) is rock solid. When your data is the business — orders, payments, user accounts — strict is exactly what you want.

The honest comparison: SQLite is a brilliant single-file library — perfect for mobile apps, prototypes, and small tools, but it's not a server, so concurrent writers and remote connections are not its game. MySQL is a fine server and you'll meet it everywhere, but historically it has been more permissive about silently truncating or coercing bad data, and its SQL feature set trails Postgres (though modern versions have closed much of the gap). For a backend career in 2026, PostgreSQL is the safest default: everything you learn here transfers to the others, but not always the reverse.

2Pick Your Install Method

There are three sane ways to get Postgres on your machine. Wala itong "one true way" — they all give you the same database. Pick based on your OS and how you like to work:

My recommendation as a default: if you already have Docker Desktop running, use Docker — it matches how you'll run Postgres in CI and production later. If not, use your OS package manager (Homebrew / apt), or the EDB installer on Windows. Whatever you pick, jump to that section — you don't need to read the other OS sections.

3macOS: Install with Homebrew

On macOS, Homebrew is the cleanest route. If you don't have Homebrew yet, install it first from brew.sh, then come back.

brew install postgresql@16

==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/16/manifests/16.9
==> Pouring [email protected]_sequoia.bottle.tar.gz
==> Caveats
postgresql@16 is keg-only, which means it was not symlinked into /opt/homebrew,
because this is an alternate version of another formula.

If you need to have postgresql@16 first in your PATH, run:
  echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc

To start postgresql@16 now and restart at login:
  brew services start postgresql@16
==> Summary
🍺  /opt/homebrew/Cellar/postgresql@16/16.9: 3,770 files, 68.4MB

Read that caveat carefully — postgresql@16 is keg-only, meaning Homebrew does not put psql on your PATH automatically. Run the echo line it suggests, then reload your shell:

echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc
psql --version

psql (PostgreSQL) 16.9

brew services start postgresql@16

==> Successfully started `postgresql@16` (label: homebrew.mxcl.postgresql@16)

brew services registers Postgres with launchd, so it also restarts automatically when you reboot. To check it later: brew services list. To stop it: brew services stop postgresql@16.

psql postgres

psql (16.9)
Type "help" for help.

postgres=#

4Windows: The EDB Installer, Step by Step

On Windows the standard route is the EnterpriseDB (EDB) installer — it's the one linked from the official postgresql.org downloads page. It installs the server, psql, and pgAdmin in one go.

Go to the downloads page, click Download the installer, and grab PostgreSQL 16 for Windows x86-64. You'll get a file like postgresql-16.9-1-windows-x64.exe.

Launch the installer (accept the UAC prompt). On the Select Components screen, keep all four checked: PostgreSQL Server, pgAdmin 4, Stack Builder (optional extras — safe to uncheck), and Command Line Tools (this is psql — do not uncheck it).

The installer asks for a password for the postgres superuser. This is the single most important screen. Pick something you will remember (for a learning machine, even devpassword123 is fine) and write it down — every future connection as postgres needs it.

Accept port 5432 (the Postgres standard) and the default locale, click through, and let it install. When it offers to launch Stack Builder at the end, you can skip it.

Open a fresh Command Prompt or PowerShell and type:

psql --version

'psql' is not recognized as an internal or external command,
operable program or batch file.

Press Win + S, search "environment variables", open Edit the system environment variables → Environment Variables…. Under System variables, select Path → Edit → New, and add:

C:\Program Files\PostgreSQL\16\bin

Click OK on every dialog, then open a new terminal (existing windows keep the old PATH) and verify:

psql --version
psql -U postgres

psql (PostgreSQL) 16.9

Password for user postgres:
psql (16.9)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

That code-page warning is cosmetic — psql works fine. The server itself was registered as a Windows service named postgresql-x64-16, so it starts automatically with Windows. You can manage it from services.msc if you ever need to stop or restart it.

5Linux: Ubuntu/Debian with the Official PGDG Repo

Ubuntu's default repositories carry Postgres, but often an older major version depending on your release. The habit worth building: install from PGDG — the PostgreSQL project's own apt repository — so you choose the exact major version and get updates straight from the source.

sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

This script will enable the PostgreSQL APT repository on apt.postgresql.org on
your system. The distribution codename used will be noble-pgdg.

Press Enter to continue, or Ctrl-C to abort.

Using keyring /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
Writing /etc/apt/sources.list.d/pgdg.sources ...
Running apt-get update ...
You can now start installing packages from apt.postgresql.org.

sudo apt install -y postgresql-16

Setting up postgresql-16 (16.9-1.pgdg24.04+1) ...
Creating new PostgreSQL cluster 16/main ...
/usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main --auth-local peer --auth-host scram-sha-256 --no-instructions
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Note the last line: apt already created a cluster on port 5432, started it, and created a Linux system user called postgres that owns it. Confirm the service is up:

sudo systemctl status postgresql

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Fri 2026-07-17 10:42:11 PST; 1min 4s ago
   Main PID: 4821 (code=exited, status=0/SUCCESS)

Naturally you now type psql. And Postgres greets you with this:

psql

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "juan" does not exist

Here juan is my Linux username — yours will show your own. The database is running fine; it just has no database user matching your Linux account.

sudo -u postgres psql

psql (16.9 (Ubuntu 16.9-1.pgdg24.04+1))
Type "help" for help.

postgres=#

You're in. In section 8 we'll create a proper database user for daily work so you don't live inside sudo -u postgres forever. If you also want password (TCP) logins as postgres — e.g., for a GUI client — set one now:

ALTER USER postgres WITH PASSWORD 'devpassword123';

ALTER ROLE

6Docker: The Same Postgres on Every Machine

If you have Docker, you can skip everything above and run the official image. This is my favorite for teaching because the command is identical on macOS, Windows, and Linux. Here's the naive version first:

docker run --name shop-postgres \
  -e POSTGRES_PASSWORD=devpassword123 \
  -p 5432:5432 \
  -d postgres:16

Unable to find image 'postgres:16' locally
16: Pulling from library/postgres
7ce705000c39: Pull complete
c690f0ee83f9: Pull complete
Digest: sha256:4ec37d2a07a0067f176fdcc9d4bb633a5724d2cc4f892c7a2046d054bb6939e5
Status: Downloaded newer image for postgres:16
8f3b2a91c4d75e02a6b1f9d8c3e47a10b52c96d40f8ea7315d90cb28a4e61f7c

Three flags doing all the work: -e POSTGRES_PASSWORD sets the password for the postgres superuser (the image refuses to start without it), -p 5432:5432 publishes the container's port to your machine so local tools can connect, and -d runs it in the background. It works — but there's a trap in it.

docker rm -f shop-postgres   # remove the throwaway one first

docker run --name shop-postgres \
  -e POSTGRES_PASSWORD=devpassword123 \
  -p 5432:5432 \
  -v shop_pgdata:/var/lib/postgresql/data \
  -d postgres:16

docker ps

CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS          PORTS                    NAMES
9c1e5b2f8a3d   postgres:16   "docker-entrypoint.s…"   12 seconds ago   Up 11 seconds   0.0.0.0:5432->5432/tcp   shop-postgres

Now docker rm -f shop-postgres followed by the same docker run brings your data back intact, because the volume shop_pgdata outlives the container. Verify it exists with docker volume ls.

Flags in your shell history are easy to lose. A compose file is the same setup as reviewable, commit-able code — this is how teams share their local database setup:

shop-db/
└── docker-compose.yml   # the whole database setup, in one file

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

volumes:
  shop_pgdata:

docker compose up -d

[+] Running 3/3
 ✔ Network shop-db_default   Created
 ✔ Volume "shop-db_shop_pgdata"  Created
 ✔ Container shop-postgres   Started

Bonus: POSTGRES_DB: shop pre-creates a database named shop on first start, and the healthcheck lets other services (your future FastAPI container) wait until Postgres is actually ready. To connect with psql inside the container — no local install needed:

docker exec -it shop-postgres psql -U postgres

psql (16.9 (Debian 16.9-1.pgdg120+1))
Type "help" for help.

postgres=#

7Your First Connection: psql, \conninfo, and version()

Whichever route you took, you now have a server. Let's make a deliberate, understood connection. psql is the official terminal client — it ships with every install and it's the tool every Postgres tutorial (including this whole series) assumes. Connect as the superuser:

# macOS (Homebrew):    psql postgres
# Windows / password:  psql -U postgres
# Linux (peer auth):   sudo -u postgres psql
# Docker:              docker exec -it shop-postgres psql -U postgres

psql -U postgres

Password for user postgres:
psql (16.9)
Type "help" for help.

postgres=#

Read the prompt: postgres=#. The word is the database you're connected to (a fresh install has a default database called postgres), and the # means you're a superuser. Regular users get => instead — you'll see that in the next section. First meta-command to learn: \conninfo, which tells you exactly where you're connected — invaluable when you have multiple Postgres installs running:

\conninfo

You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".

And your first actual SQL query — ask the server what it is:

SELECT version();

                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.9 (Debian 16.9-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

8Create Your First Database and App User

Right now you have one database (postgres) and one all-powerful user. Production instinct says: apps should get their own database and a non-superuser account. Let's set up the shop database this series uses everywhere, plus a shop_app user. Connected as the superuser, run:

CREATE DATABASE shop;

CREATE USER shop_app WITH PASSWORD 'app_password_123';

GRANT CONNECT ON DATABASE shop TO shop_app;

CREATE DATABASE
CREATE ROLE
GRANT

(Postgres echoes CREATE ROLE for CREATE USER — a user is a role, just one with login permission.) Now switch into the new database and let shop_app actually build things in it:

\c shop

You are now connected to database "shop" as user "postgres".

GRANT USAGE, CREATE ON SCHEMA public TO shop_app;

GRANT

Exit with \q and reconnect as the new user — over TCP this time (-h localhost), so password auth applies on every platform:

psql -h localhost -U shop_app -d shop

Password for user shop_app:
psql (16.9)
Type "help" for help.

shop=>

Notice the prompt: shop=> — connected to shop, as a regular (non-super) user. Prove the privileges work end to end with a tiny table:

CREATE TABLE connection_test (
  test_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  note    TEXT NOT NULL
);

INSERT INTO connection_test (note) VALUES ('kaya na natin ito!');

SELECT test_id, note FROM connection_test;

CREATE TABLE
INSERT 0 1
 test_id |        note
---------+--------------------
       1 | kaya na natin ito!
(1 row)

Clean up the scratch table — the real schema comes in the next tutorial:

DROP TABLE connection_test;

DROP TABLE

9GUI Clients: pgAdmin, TablePlus, DBeaver

You don't need a GUI — this series teaches psql on purpose, because psql is on every server you'll ever SSH into. But a GUI genuinely helps when you're exploring an unfamiliar schema, eyeballing rows in a wide table, or editing data by hand. The three worth knowing:

Whichever you choose, the connection settings are the same ones you've been using: host localhost, port 5432, database shop, user shop_app, and your password. If the GUI can't connect but psql can, compare against \conninfo — nine times out of ten it's a wrong port or a wrong database name.

10Troubleshooting: The Four Errors Everyone Hits

Ito ang section na babalikan mo. Four real errors, exactly as Postgres prints them, and what each one actually means.

docker: Error response from daemon: driver failed programming external connectivity on endpoint shop-postgres: Bind for 0.0.0.0:5432 failed: port is already allocated.

Something on your machine already owns port 5432 — usually a second Postgres you forgot about (Homebrew service + Docker container is the classic combo). Find the culprit:

# macOS / Linux
lsof -i :5432

# Windows (PowerShell)
netstat -ano | findstr :5432

COMMAND   PID   USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
postgres  731  thirdy   7u  IPv6 0x9a2f...      0t0  TCP localhost:postgresql (LISTEN)

Then either stop the other server (brew services stop postgresql@16 / sudo systemctl stop postgresql), or run the new one on a different host port, e.g. -p 5433:5432 in Docker and connect with psql -h localhost -p 5433 -U postgres.

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "postgres"

The server is up; your credentials are wrong. Check in this order: (1) typo in the password, (2) wrong user — on Homebrew the superuser is your macOS username, not postgres, (3) wrong server — see the two-servers trap above, (4) Docker: you changed POSTGRES_PASSWORD after the volume was initialized, so the old password still applies. If you're truly locked out on a dev machine, connect via a channel that skips passwords (sudo -u postgres psql on Linux, docker exec -it shop-postgres psql -U postgres on Docker) and reset it:

ALTER USER postgres WITH PASSWORD 'devpassword123';

ALTER ROLE

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

psql found no server at all. Nothing is listening — the service is stopped. Start it with your platform's command and retry:

# macOS
brew services start postgresql@16

# Linux
sudo systemctl start postgresql

# Docker
docker start shop-postgres

# Windows: services.msc -> postgresql-x64-16 -> Start

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

Similar smell, different meaning: you reached the machine over TCP, but nothing is listening on that port. Usual suspects: the Docker container is running but you forgot -p 5432:5432 (check docker ps — the PORTS column must show 0.0.0.0:5432->5432/tcp), you're connecting to the wrong port after remapping to 5433, or the service crashed on startup — check the logs (docker logs shop-postgres, or /var/log/postgresql/ on Linux).

Quick reference for when you meet these again:

11Best Practices From Day One

None of these take extra effort today, and every one of them saves you a bad afternoon later. Sanayin mo na habang maaga:

12Practice Exercise + What's Next

Cement the muscle memory with one full round trip — no copy-paste from the sections above if kaya mo. All the commands appeared in this post:

1. Connect to your server as the superuser and run \conninfo to confirm host and port.
2. Create a database called practice_db and a user practice_user with a password.
3. Grant it CONNECT on the database, plus USAGE, CREATE on schema public (remember to \c practice_db first!).
4. Reconnect as practice_user with psql -h localhost -U practice_user -d practice_db.
5. Create any small table, insert one row, select it back — then clean up by dropping the table.

Expected checkpoints along the way:

CREATE DATABASE
CREATE ROLE
GRANT
You are now connected to database "practice_db" as user "postgres".
GRANT
-- and after reconnecting, the non-superuser prompt:
practice_db=>
-- and your select returns your row with:
(1 row)

If you can do that loop without looking, you understand more about Postgres setup than many people who've used it for years — most devs only ever see a connection string someone else wrote.

Recap: you picked an install method with open eyes, put PostgreSQL 16 on your machine (or in a container with a volume), made a verified first connection, and created a database plus a non-superuser app account — with the real error messages already demystified. Your server is running quietly in the background now. Next stop: actually talking to it fluently.