LLM Best Practices

How to set up Postgres locally

3 min read

View markdown source

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 postgres-prod; the query tuning workflow lives in 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

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

macOS (Homebrew)

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:

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.

sudo -u postgres psql

Inside psql:

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:

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:

sudo systemctl reload postgresql

4. Connect as the application role

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 prisma read a single connection string:

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

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

Verify it worked

# 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 postgresqlto see the error; a missingdata/directory often meansinitdb` 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.

Graph View

Backlinks