Installation and psql

Introduction

Before you run SQL, you need a running PostgreSQL server and the psql client to connect to it. This chapter covers Docker (recommended for development), native installs on Linux and macOS, Windows options, and your first psql commands—\l, \c, \dt, and SELECT 1. By the end, PostgreSQL answers on port 5432.

Prerequisites

• A terminal (Linux, macOS, or Windows with WSL2)
• What Is PostgreSQL
• Docker introduction optional but recommended

Install With Docker (Recommended)

Docker gives a consistent PostgreSQL 16 instance without touching system packages.

# Pull the official image
docker pull postgres:16
 
# Run with a named volume so data survives container restarts
docker run -d \
  --name postgres \
  -p 5432:5432 \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=learnpass \
  -e POSTGRES_DB=hello_demo \
  -v pg_data:/var/lib/postgresql/data \
  postgres:16

Verify the container:

docker ps
docker logs postgres --tail 20

Connect with psql inside the container:

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

Or install psql on your host and connect to localhost:

psql "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo"

Docker Compose (Optional)

docker-compose.yml:

docker compose up -d

Compare MySQL Docker install—Postgres uses POSTGRES_* env vars instead of MYSQL_*.

Init Scripts on First Start

Place *.sql or *.sh in /docker-entrypoint-initdb.d/ (via volume mount). They run once when the data directory is empty—useful for seed tables in dev.

Linux (Ubuntu / Debian)

Package install (version may be 15 or 16 depending on distro):

sudo apt update
sudo apt install -y postgresql postgresql-client
 
# Service status
sudo systemctl status postgresql
sudo systemctl start postgresql

Switch to the default postgres system user and open psql:

sudo -u postgres psql

One-line health check from your user (after setting a password or pg_hba.conf trust for local socket):

psql -h 127.0.0.1 -U postgres -d postgres -c "SELECT 1 AS ok;"

Expected:

 ok
----
  1
(1 row)

macOS (Homebrew)

brew install postgresql@16
brew services start postgresql@16
 
# Add psql to PATH if needed (Apple Silicon example)
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

Connect:

psql postgres

Create a dev database:

CREATE DATABASE hello_demo;
\q

psql hello_demo

Windows

Options:

1. WSL2 + Docker (recommended for parity with Linux deploys)—follow Docker steps inside Ubuntu
2. EDB installer from postgresql.org/download/windows—includes psql and pgAdmin
3. Docker Desktop on Windows with the same docker run command above

Inside WSL:

wsl --install

Then use Docker or apt install postgresql in the Ubuntu distro.

Cloud Preview: Neon / Supabase

Managed Postgres for dev without local postgres:

1. Create a free project at Neon or Supabase
2. Copy the connection string: postgresql://user:pass@host/dbname?sslmode=require
3. Connect:

psql "postgresql://USER:PASSWORD@HOST/dbname?sslmode=require"

Production backups and HA differ from local Docker—treat cloud as hosted Postgres, same SQL and psql.

psql Basics

Launch interactive shell:

psql hello_demo
# or full URI
psql "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo"

Essential meta-commands

Meta-commands start with \ (backslash). They are not SQL.

Run SQL in the same session:

SELECT version();
SELECT current_database(), current_schema();
SELECT 1 AS ping;

Code explanation:

• \dt shows tables in search_path schemas—default is public
• \c dbname switches database but keeps the same user unless you pass a new user

Run one-liners from the terminal

psql hello_demo -c "SELECT 1 AS ok;"
psql -U postgres -d hello_demo -c "\dt"

Run a script file

seed.sql:

-- seed.sql — run with: psql hello_demo -f seed.sql
CREATE TABLE IF NOT EXISTS greeting (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message TEXT NOT NULL
);
 
INSERT INTO greeting (message) VALUES ('Hello from seed.sql');

psql hello_demo -v ON_ERROR_STOP=1 -f seed.sql

-v ON_ERROR_STOP=1 stops the script on first error—recommended for migrations.

Connection URI Format

postgresql://[user[:password]@][host][:port][/dbname][?param=value]

URL-encode special characters in passwords (@, #, %).

Drivers use the same URI shape—psycopg chapter, JDBC: jdbc:postgresql://host:5432/db.

Configuration Files (Overview)

Docker images ship defaults suited for dev. Native Linux paths often look like /etc/postgresql/16/main/.

Useful dev settings (concept):

# postgresql.conf excerpts
listen_addresses = '*'
log_min_duration_statement = 500   # log queries slower than 500ms

After editing pg_hba.conf, reload:

# Native Linux
sudo systemctl reload postgresql
 
# Docker
docker exec postgres pg_ctl reload -D /var/lib/postgresql/data

Details compare with MySQL configuration—Postgres splits auth into pg_hba.conf.

GUI Clients

Use the same connection URI as psql. GUIs help browsing; this track emphasizes psql for reproducible learning.

Common Problems

Post-Chapter Checklist

• PostgreSQL runs locally (Docker or native)
• psql connects and SELECT 1 succeeds
• You can \l, \c hello_demo, and \dt
• You know default port 5432 and URI format

FAQ

Postgres vs postgresql command?

The server binary is postgres; the client is psql. Package names often say postgresql.

Do I need auth for local Docker dev?

The example uses POSTGRES_PASSWORD. Never commit real passwords—use .env locally; see Git gitignore.

Can I run PostgreSQL and MySQL together?

Yes—different ports (5432 vs 3306). Common in polyglot stacks—MySQL vs PostgreSQL.

Which version should I install?

PostgreSQL 16+ for new learning. Pin postgres:16 in Docker for consistency.

psql vs pgAdmin?

Learn psql first—scripts, CI, and docs use it. pgAdmin is fine for visual exploration.

Upgrade PostgreSQL major version?

Read release notes; logical backup with pg_dump before upgrade—Backup chapter.