postgresql-nix-macos-init

name	postgresql-nix-macos-init
description	Initialize and run a local PostgreSQL instance on macOS using Nix-managed binaries. Handles incomplete system installations, port conflicts, and user-owned data directories. Trigger keywords: initialize PostgreSQL, start a local Postgres database, fix PostgreSQL installation issues on macOS with Nix.
license	MIT
metadata	{"version":"1.0.1","hermes":{"tags":["PostgreSQL","Nix","macOS","database-init","local-dev"],"related_skills":["macos-launchctl-port-conflict-resolution"]}}

Initialize PostgreSQL on macOS with Nix

Initialize and run a local PostgreSQL instance on macOS using Nix-managed binaries.

Problem Context

System PostgreSQL installations on macOS (via Nix or other means) are often incomplete:

• initdb fails with "postgres.bki does not exist"
• pg_ctl fails with "could not open directory lib"
• Port 5432 may already be in use by another PostgreSQL instance

Prerequisites

• macOS with Nix installed
• User has permission to create directories in home folder

Execution Steps

1. Check Current PostgreSQL State

# Check if PostgreSQL is already running
lsof -i:5432

# Check existing PostgreSQL binaries
which psql && psql --version

2. Get Proper PostgreSQL Binaries

System PostgreSQL may be incomplete. Use Nix store directly:

# Find available PostgreSQL installations in nix store
ls -la /nix/store/*postgresql*/bin/initdb 2>/dev/null | head -5

# Get the latest one
INITDB=$(ls -t /nix/store/*postgresql*/bin/initdb 2>/dev/null | head -1)
PG_CTL=$(dirname $INITDB)/pg_ctl
PSQL=$(dirname $INITDB)/psql

3. Create Data Directory

# Create user-owned data directory (not system location)
mkdir -p ~/postgres-data

# Initialize with trust auth (local dev only)
$INITDB -D ~/postgres-data --auth=trust --username=$(whoami)

Output should show:

• Success message
• Command to start the server
• Default settings (max_connections, shared_buffers, etc.)

4. Handle Port Conflicts

If PostgreSQL is already running on port 5432:

# Find the process
lsof -ti:5432

# Option A: Use the existing instance (if available)
psql -U $(whoami) -d postgres -c "SELECT version();"

# Option B: Run parallel instance on different port (preserve existing).
# Use a SEPARATE data directory so the two instances don't collide.
postgres -D ~/postgres-data-alt -p 5433 &

# Option C: Kill existing and start fresh (destructive)
kill -9 $(lsof -ti:5432) 2>/dev/null
sleep 2

5. Start PostgreSQL

# Start the server
$PG_CTL -D ~/postgres-data -l ~/postgres-data/logfile start

# Check status
$PG_CTL -D ~/postgres-data status

# View logs if needed
cat ~/postgres-data/logfile

6. Create Application Database

# Create database for your app
createdb -U $(whoami) your_app_name

# Verify connection
psql -U $(whoami) -d your_app_name -c "SELECT version();"

Common Errors and Fixes

Error: "postgres.bki does not exist"

Cause: System PostgreSQL installation is incomplete.

Fix: Use Nix store binaries directly instead of system PATH:

/nix/store/XXXX-postgresql-16.X/bin/initdb -D ~/postgres-data --auth=trust --username=$(whoami)

Error: "could not bind IPv6/IPv4 address: Address already in use"

Cause: Another PostgreSQL instance is running on port 5432.

Fix:

# Check if it's usable
psql -U $(whoami) -d postgres -c "\l"

# If usable, just create your database

# If not usable, kill and restart
kill -9 $(lsof -ti:5432)
sleep 2
pg_ctl -D ~/postgres-data -l ~/postgres-data/logfile start

Error: "could not open directory lib"

Cause: Using mixed PostgreSQL binaries (initdb from one version, pg_ctl from another).

Fix: Use the same Nix store path for all commands:

PG_BIN="/nix/store/XXXX-postgresql-16.X/bin"
$PG_BIN/pg_ctl -D ~/postgres-data start
$PG_BIN/createdb -U $(whoami) mydb

Management Commands

# Start
pg_ctl -D ~/postgres-data -l ~/postgres-data/logfile start

# Stop
pg_ctl -D ~/postgres-data stop

# Restart
pg_ctl -D ~/postgres-data restart

# Status
pg_ctl -D ~/postgres-data status

# View logs
tail -f ~/postgres-data/logfile

Verification

# Connect and check version
psql -U $(whoami) -d postgres -c "SELECT version();"

# List databases
psql -U $(whoami) -d postgres -c "\l"

# Create test table
psql -U $(whoami) -d your_app_name -c "CREATE TABLE test (id serial PRIMARY KEY);"

Cleanup (if needed)

# Stop server
pg_ctl -D ~/postgres-data stop

# Remove data (DESTRUCTIVE)
rm -rf ~/postgres-data

Notes

• Data directory is in user's home folder (~/postgres-data) for isolation
• Uses "trust" auth for local development simplicity
• Port 5432 is standard but can be changed in postgresql.conf if needed
• Multiple PostgreSQL versions can coexist via different Nix store paths