---
title: "How to set up Postgres locally"
slug: "set-up-postgres-locally"
category: "howto"
tags: ["howto", "postgres", "backend", "database", "tutorial", "devenv"]
status: "stable"
last_updated: 2026-05-14
summary: "Install Postgres, create a role and database, configure pg_hba auth, connect via psql, and fix peer-auth errors in under 10 minutes."
related: ["[[backend/postgres]]", "[[backend/postgres-indexes]]", "[[backend/postgres-vacuum]]", "[[backend/migrations]]", "[[backend/prisma]]", "[[ops/postgres-prod]]", "[[howto/debug-postgres-slow-query]]"]
---

## Overview

A local Postgres instance is the foundation for every backend tutorial on this site. This guide installs Postgres, creates a dedicated role and database, sets up password authentication, and verifies the connection. The production hardening checklist lives in [[ops/postgres-prod]]; the query tuning workflow lives in [[howto/debug-postgres-slow-query]].

## Prerequisites

- Ubuntu 22.04 / 24.04, macOS 14+, or Windows 11 with WSL2. The commands below target Debian-family Linux; macOS variants are noted inline.
- `sudo` access. The Postgres service install requires it.
- A terminal with `curl` available.

## Steps

### 1. Install Postgres

**Ubuntu / Debian**

```bash
sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql
```

**macOS (Homebrew)**

```bash
brew install postgresql@16
brew services start postgresql@16
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc
```

Verify the process is running:

```bash
pg_isready
# /var/run/postgresql:5432 - accepting connections
```

### 2. Create a role and database

Connect as the superuser, then create a non-superuser role for application use. Avoid running application code as `postgres`.

```bash
sudo -u postgres psql
```

Inside psql:

```sql
CREATE ROLE myapp WITH LOGIN PASSWORD 'changeme' CREATEDB;
CREATE DATABASE myapp_dev OWNER myapp;
\q
```

Keep the password out of shell history by using `\password myapp` instead of the inline form when security matters.

### 3. Configure `pg_hba.conf` for password auth

By default Ubuntu configures `peer` auth for local connections, which means the OS user must match the Postgres role. Switch to `scram-sha-256` so any user can authenticate with a password.

Find the config file:

```bash
sudo -u postgres psql -c "SHOW hba_file;"
# /etc/postgresql/16/main/pg_hba.conf
```

Edit the file. Change the line for `local` from `peer` to `scram-sha-256`:

```
# TYPE  DATABASE  USER  ADDRESS  METHOD
local   all       all            scram-sha-256
host    all       all  127.0.0.1/32  scram-sha-256
host    all       all  ::1/128       scram-sha-256
```

Reload the service for the change to take effect:

```bash
sudo systemctl reload postgresql
```

### 4. Connect as the application role

```bash
psql -U myapp -d myapp_dev -h localhost
# Password for user myapp: changeme
```

The `-h localhost` flag forces a TCP connection, which uses `host` rules in pg_hba.conf. Without it you get a UNIX socket and hit the `local` rules.

### 5. Set a `DATABASE_URL` environment variable

Most frameworks and [[backend/prisma]] read a single connection string:

```bash
export DATABASE_URL="postgresql://myapp:changeme@localhost:5432/myapp_dev"
```

Add it to a `.env` file and gitignore it. Never commit credentials. See [[backend/migrations]] for how Prisma and raw SQL migrations consume this variable.

## Verify it worked

```bash
# 1. psql connects and shows the correct database.
psql "$DATABASE_URL" -c "SELECT current_user, current_database();"
#  current_user | current_database
# --------------+-----------------
#  myapp        | myapp_dev

# 2. The role exists with the right privileges.
psql -U postgres -c "\du myapp"

# 3. The service restarts cleanly.
sudo systemctl restart postgresql && pg_isready
```

## Common errors

- **`FATAL: Peer authentication failed for user "myapp"`**. You connected via UNIX socket. Add `-h localhost` to force TCP, or change the `local` rule in `pg_hba.conf` to `scram-sha-256` and reload.
- **`pg_isready` shows "no response"`**. The service is not running. Run `sudo systemctl status postgresql` to see the error; a missing `data/` directory often means `initdb` was not run.
- **`createdb: error: could not connect to database template1`**. The install did not run `initdb`. On Ubuntu run `sudo pg_createcluster 16 main --start`.
- **Password prompt appears but correct password is rejected**. The `pg_hba.conf` line uses `md5` while the role was created with `scram-sha-256`. Align the method or recreate the role.
- **Port 5432 already in use**. Another Postgres version is running. `sudo lsof -i :5432` to find it; stop it or change the port in `postgresql.conf`.

## Related

- [[backend/postgres]]
- [[backend/postgres-indexes]]
- [[backend/postgres-vacuum]]
- [[backend/migrations]]
- [[backend/prisma]]
- [[ops/postgres-prod]]
- [[howto/debug-postgres-slow-query]]