Level 9 — PostgreSQL

Your source of truth. Everything else on the server can be rebuilt from Git in ten minutes; the database cannot. Treat this chapter accordingly.

What PostgreSQL is

PostgreSQL is a relational database management system: a long-running server process that stores data on disk, enforces structure and constraints, answers SQL queries, and guarantees ACID properties.

Property	Meaning	Why it matters
Atomicity	A transaction happens entirely or not at all	A failed payment does not leave a half-created order
Consistency	Constraints are always satisfied	No orders referencing a deleted user
Isolation	Concurrent transactions do not corrupt each other	Two simultaneous purchases cannot both take the last item
Durability	Committed data survives a crash	A power cut does not lose the last minute of writes

Durability is implemented via the WAL (Write-Ahead Log): changes are written to a sequential log and fsynced before the data files are updated. After a crash, PostgreSQL replays the WAL. The WAL is also the basis of replication and point-in-time recovery.

The object hierarchy

Level	What it is
Cluster	One PostgreSQL instance: one port, one data directory, one set of roles. Confusingly named — nothing to do with high availability.
Database	An isolated namespace. A single connection talks to exactly one database; you cannot join across databases.
Schema	A namespace inside a database. Default is `public`. Useful for multi-tenancy or separating concerns.
Table	Rows and typed columns
Role	A user or a group — PostgreSQL uses one concept for both. `CREATE USER` is `CREATE ROLE ... WITH LOGIN`.

DATABASES ARE ISOLATED; SCHEMAS ARE NOT

You cannot JOIN across two databases in one query. If two parts of your system need to query each other's data, put them in schemas of the same database, not separate databases. Separate databases are for genuinely separate applications (or separate environments).

The connection string

postgresql://myapp:S0me-Str0ng-P4ss@127.0.0.1:5432/myapp_production?schema=public&sslmode=disable
└────┬───┘  └─┬─┘ └──────┬───────┘ └───┬────┘ └┬─┘ └───────┬──────┘ └──────────┬─────────────┘
  scheme     user     password        host    port     database              parameters

Part	Value	Notes
Scheme	`postgresql://`	`postgres://` also works
User	`myapp`	The PostgreSQL role, not a Linux user
Password	`S0me-Str0ng-P4ss`	Must be URL-encoded if it contains `@ : / ? # &`
Host	`127.0.0.1`	See the warning below about `localhost`
Port	`5432`	Default
Database	`myapp_production`	Must already exist
Parameters	`?schema=public&connection_limit=10`	Driver-specific

USE 127.0.0.1, NOT localhost

localhost resolves via /etc/hosts and may produce ::1 (IPv6) first. If PostgreSQL is only listening on IPv4, you get:

Error: connect ECONNREFUSED ::1:5432

Node's DNS resolution order changed in Node 17 (it no longer reorders results), which made this a very common failure. 127.0.0.1 is unambiguous. Use it everywhere.

URL-ENCODE SPECIAL CHARACTERS IN PASSWORDS

postgresql://user:p@ss:word@host/db fails to parse. Encode: @ → %40, : → %3A, / → %2F, # → %23, ? → %3F.

Simpler: generate passwords without those characters (Level 8).

Useful Prisma parameters:

?connection_limit=10&pool_timeout=20&connect_timeout=10&schema=public

connection_limit matters: Prisma's default is num_cpus * 2 + 1 per process. With PM2 cluster mode running 4 instances, that is 4 × 9 = 36 connections against PostgreSQL's default max_connections = 100. Add a second app and you exhaust the pool. Set it explicitly.

Option A — native PostgreSQL

bash

# SERVER
sudo apt update
sudo apt install -y postgresql postgresql-contrib

Ubuntu 24.04 ships PostgreSQL 16. For a specific version, use the official PGDG repository:

bash

# SERVER
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-16

The installation:

Creates the Linux user postgres and the superuser role postgres
Initialises a cluster at /var/lib/postgresql/16/main
Puts configuration in /etc/postgresql/16/main/
Creates and starts the postgresql systemd service
Listens on 127.0.0.1:5432 by default — already secure

bash

# SERVER
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"
sudo ss -tulpn | grep 5432

The last command must show 127.0.0.1:5432, not 0.0.0.0:5432.

Service management

bash

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql        # drops all connections
sudo systemctl reload postgresql         # re-reads config without dropping connections
sudo systemctl status postgresql
sudo systemctl enable postgresql         # start on boot
sudo -u postgres pg_isready              # is it accepting connections?

reload FOR MOST CONFIG CHANGES

Changes to pg_hba.conf and most postgresql.conf settings take effect on reload. Only a few (shared_buffers, max_connections, listen_addresses) need a full restart. Check with:

sql

SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;

Creating a database and user

bash

# SERVER
sudo -u postgres psql

sudo -u postgres switches to the postgres Linux user, which authenticates via peer authentication — the OS vouches for the identity, so no password is needed. This is why the default install is safe.

sql

-- Create a role for the application
CREATE USER myapp WITH PASSWORD 'S0me-Str0ng-P4ss';

-- Create the database, owned by that role
CREATE DATABASE myapp_production OWNER myapp;

-- Connect to it
\c myapp_production

-- Lock down the public schema (PostgreSQL 15+ already restricts this, but be explicit)
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO myapp;

\q

Or from the shell:

bash

# SERVER
sudo -u postgres createuser --pwprompt myapp
sudo -u postgres createdb --owner=myapp myapp_production

DO NOT USE THE postgres SUPERUSER FOR YOUR APPLICATION

It is tempting — everything just works. But a SQL injection vulnerability with a superuser connection means the attacker can read every database on the cluster, write files to disk via COPY TO, and in some configurations execute shell commands. With a scoped role, the damage is limited to that one database.

Your app role should own its database and nothing else. It should not be SUPERUSER, CREATEDB, or CREATEROLE.

Grants for an existing database

If the app role does not own the database:

sql

\c myapp_production

GRANT CONNECT ON DATABASE myapp_production TO myapp;
GRANT USAGE ON SCHEMA public TO myapp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myapp;

-- Apply automatically to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO myapp;

ALTER DEFAULT PRIVILEGES IS THE ONE PEOPLE FORGET

Without it, a migration that creates a new table leaves the app unable to read it — "permission denied for table X" appearing only for the new feature. Note that default privileges apply to objects created by the role that ran ALTER DEFAULT PRIVILEGES, so run it as the role your migrations use.

Verify the connection

bash

# SERVER
psql "postgresql://myapp:S0me-Str0ng-P4ss@127.0.0.1:5432/myapp_production" -c "SELECT current_user, current_database();"

Option B — PostgreSQL in Docker

yaml

# /home/deploy/apps/myapp/docker-compose.yml
services:
  postgres:
    image: postgres:16-alpine
    container_name: myapp-postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: myapp_production
      # Required for the healthcheck and psql to work without flags
      PGUSER: myapp
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./backups:/backups
    ports:
      - "127.0.0.1:5432:5432"     # ⚠️ localhost only — see Level 5
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myapp -d myapp_production"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
    shm_size: 256mb
    command:
      - "postgres"
      - "-c" 
      - "max_connections=100"
      - "-c"
      - "shared_buffers=512MB"

volumes:
  postgres_data:
    driver: local

bash

# SERVER
docker compose up -d postgres
docker compose ps
docker compose logs -f postgres
docker compose exec postgres psql -U myapp -d myapp_production

THE ports: LINE IS THE MOST IMPORTANT LINE IN THIS FILE

"5432:5432" binds to 0.0.0.0 and bypasses UFW entirely (Level 5). Your database is then on the public internet regardless of what ufw status says.

"127.0.0.1:5432:5432" is correct for a host process (PM2-managed Node) connecting to it.

If everything runs in Docker, omit ports: altogether and let containers reach it as postgres:5432 over the Docker network. A port that is not published cannot be exposed.

Verify with the docker ps --format command shown in the security section below.

shm_size MATTERS

The default 64 MB of shared memory causes could not resize shared memory segment errors during parallel queries and large sorts. 256 MB is a sensible minimum.

Native vs Docker — which to choose

	Native	Docker
Install effort	`apt install`	Compose file
Version control	Whatever the repo has (or PGDG)	Exact tag pinned in Git
Upgrade path	`pg_upgradecluster` — fiddly	Change the tag; still needs a dump/restore across majors
Data location	`/var/lib/postgresql/16/main`	A named volume in `/var/lib/docker/volumes/`
Performance	Baseline	Effectively identical with a native volume
Backups	`pg_dump` directly	`docker compose exec` wrapper
Memory overhead	None	Small (~30 MB)
Risk of accidental deletion	Low	Higher — `docker compose down -v` destroys the volume
Matches dev environment	Depends	Yes, if dev also uses Docker
systemd integration	Native	Via Docker's restart policy

RECOMMENDATION

Native PostgreSQL for a single production server. Reasons: it starts on boot via systemd with no extra thought, pg_dump and psql are on PATH, the data directory is a normal path your backup tooling already understands, and there is no way to destroy the data with a stray docker compose down -v.

Docker PostgreSQL when: you run multiple projects with different PostgreSQL major versions on one box, your dev environment is already Compose-based and you value parity, or you are heading toward a fully containerised deployment.

Do not mix: pick one and stick to it. Two PostgreSQL instances fighting over port 5432 is a confusing afternoon.

docker compose down -v DELETES YOUR DATABASE

The -v flag removes named volumes. There is no confirmation and no undo. docker compose down (without -v) is safe — it stops and removes containers but keeps volumes.

Make it muscle memory: never type -v with down on a machine that has production data.

`psql` — the client

bash

# SERVER
sudo -u postgres psql                                   # as superuser
psql -U myapp -d myapp_production -h 127.0.0.1          # as the app role
psql "postgresql://myapp:pass@127.0.0.1:5432/myapp_production"   # by URL
docker compose exec postgres psql -U myapp -d myapp_production   # in Docker

Meta-commands (all start with \):

Command	Shows
`\l`	All databases
`\c dbname`	Connect to a database
`\dt`	Tables in the current schema
`\dt+`	Tables with sizes
`\d tablename`	Table structure, indexes, constraints
`\du`	Roles and their attributes
`\dn`	Schemas
`\di`	Indexes
`\dp`	Table access privileges
`\x`	Toggle expanded output — essential for wide rows
`\timing`	Show query execution time
`\e`	Edit the current query in `$EDITOR`
`\i file.sql`	Run a SQL file
`\o file.txt`	Send output to a file
`\q`	Quit
`\?`	Help on meta-commands
`\h CREATE TABLE`	Help on SQL syntax

\x auto IS THE BEST QUALITY-OF-LIFE SETTING

Add to ~/.psqlrc:

\set QUIET 1
\timing on
\x auto
\pset null '[NULL]'
\set HISTFILE ~/.psql_history-:DBNAME
\set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%]%R%# '
\unset QUIET

\x auto switches to expanded output only when a row is too wide for the terminal — no more unreadable wrapped tables.

Queries you will actually run

sql

-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- Table sizes, biggest first
SELECT relname AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS data,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

-- Currently running queries
SELECT pid, now() - query_start AS duration, state, left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Connection count by state — are you leaking connections?
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Kill a runaway query (polite, then forceful)
SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

-- Unused indexes — candidates for removal
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE idx_scan < 50 ORDER BY pg_relation_size(indexrelid) DESC;

-- Cache hit ratio — should be > 0.99
SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

-- Tables needing VACUUM
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;

Configuration

File	Location (native)	Purpose
`postgresql.conf`	`/etc/postgresql/16/main/postgresql.conf`	Server settings: memory, connections, logging
`pg_hba.conf`	`/etc/postgresql/16/main/pg_hba.conf`	Host-Based Authentication — who may connect, from where, how
`pg_ident.conf`	same directory	OS-user → DB-role mapping

bash

# SERVER — find them regardless of version
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"
sudo -u postgres psql -c "SHOW data_directory;"

`postgresql.conf` — settings that matter

conf

# ---- Connections ----
listen_addresses = 'localhost'     # ⚠️ NEVER '*' unless you know exactly why
port = 5432
max_connections = 100

# ---- Memory (tuned for a 4 GB server) ----
shared_buffers = 1GB               # ~25% of RAM
effective_cache_size = 3GB         # ~75% of RAM — a planner hint, not an allocation
work_mem = 16MB                    # PER SORT OPERATION — see warning
maintenance_work_mem = 256MB       # for VACUUM, CREATE INDEX

# ---- Write-ahead log ----
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 512MB

# ---- SSD tuning ----
random_page_cost = 1.1             # default 4.0 assumes spinning disks
effective_io_concurrency = 200

# ---- Logging ----
logging_collector = on
log_directory = 'log'
log_min_duration_statement = 1000  # log queries slower than 1 second
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_line_prefix = '%m [%p] %q%u@%d '
log_temp_files = 0

# ---- Autovacuum ----
autovacuum = on
autovacuum_max_workers = 3

listen_addresses = '*' PUTS YOUR DATABASE ON THE INTERNET

Combined with an open firewall port, this is the exposure that ends companies. The default is localhost — leave it.

If you genuinely need a remote application server to connect, bind to the private network interface only (listen_addresses = '10.0.0.4'), restrict pg_hba.conf to that subnet, require SSL, and firewall 5432 to the specific source IP. Never 0.0.0.0.

work_mem IS PER-OPERATION, NOT PER-SERVER

A single query with several sorts and hash joins can use work_mem multiple times over, and each connection can be running one. Worst case is roughly max_connections × work_mem × operations_per_query. Setting work_mem = 256MB with 100 connections is a plausible route to an OOM kill.

Start at 16 MB. Raise it for specific heavy queries with SET LOCAL work_mem = '256MB' inside a transaction.

USE PGTUNE FOR A STARTING POINT

pgtune.leopard.in.ua generates a config from your RAM, CPU count, and workload type. It is a good baseline. Do not treat it as final — measure, then adjust.

Apply changes:

bash

sudo systemctl reload postgresql       # most settings
sudo systemctl restart postgresql      # shared_buffers, max_connections, listen_addresses

`pg_hba.conf` — authentication rules

This file answers: which role, connecting to which database, from which address, using which method? Rules are evaluated top to bottom, first match wins — including a match that then rejects the connection.

conf

# TYPE  DATABASE          USER      ADDRESS        METHOD

# Local Unix socket — OS user must match the role name
local   all               postgres                 peer
local   all               all                      peer

# IPv4 loopback — password required
host    myapp_production  myapp     127.0.0.1/32   scram-sha-256

# IPv6 loopback
host    myapp_production  myapp     ::1/128        scram-sha-256

# Docker bridge network (if the app runs in a container)
# host  myapp_production  myapp     172.17.0.0/16  scram-sha-256

TYPE	Meaning
`local`	Unix domain socket (no network)
`host`	TCP, with or without SSL
`hostssl`	TCP, SSL required
`hostnossl`	TCP, SSL not used

METHOD	Meaning
`trust`	No authentication at all. Never in production.
`peer`	The OS username must match the database role. Local socket only.
`scram-sha-256`	Password, SCRAM-hashed. The correct choice.
`md5`	Legacy password hashing. Weaker — upgrade to SCRAM.
`reject`	Explicitly deny

NEVER USE trust

trust means anyone who can reach the socket or port becomes any role, including postgres, with no password. It appears in tutorials as a "fix" for authentication errors. It is the equivalent of removing the lock because you lost the key.

Check for it:

bash

sudo grep -vE '^\s*#|^\s*$' /etc/postgresql/16/main/pg_hba.conf

MIGRATING FROM md5 TO scram-sha-256

Changing the method in pg_hba.conf is not enough — existing password hashes are still MD5 and will fail. You must reset each password after setting password_encryption = 'scram-sha-256':

sql

SHOW password_encryption;                     -- should be scram-sha-256
ALTER USER myapp WITH PASSWORD 'same-or-new-password';
SELECT rolname, substring(rolpassword, 1, 14) FROM pg_authid;   -- verify SCRAM-SHA-256

bash

sudo systemctl reload postgresql

Why 5432 must not be public

AN EXPOSED POSTGRESQL PORT

Automated scanners find open 5432 within hours. What follows:

Credential attacks — dictionary attacks against common roles (postgres, admin, root). PostgreSQL has no built-in rate limiting or lockout.
Version fingerprinting — the handshake reveals your version, which is then matched against known CVEs.
Total data access on success — read every row, dump every table.
Ransomware — the well-documented pattern: dump the data, drop the tables, leave a ransom note in a table called readme.
Server compromise — as superuser, COPY ... TO PROGRAM executes shell commands as the postgres OS user.

There is no legitimate reason to expose 5432 to 0.0.0.0 on a single-server deployment. Your app connects over loopback. You connect via an SSH tunnel:

bash

# LOCAL
ssh -L 5433:127.0.0.1:5432 deploy@203.0.113.10
# then point your GUI client at localhost:5433

Verify you are safe:

bash

# SERVER
sudo ss -tulpn | grep 5432            # must show 127.0.0.1, not 0.0.0.0
sudo ufw status | grep 5432           # should return nothing
docker ps --format "{{.Names}}\t{{.Ports}}" | grep 5432

bash

# LOCAL — from outside; must time out or refuse
nc -vz 203.0.113.10 5432

Prisma

bash

# SERVER / LOCAL
pnpm add -D prisma
pnpm add @prisma/client

prisma

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  orders    Order[]

  @@index([createdAt])
}

model Order {
  id        String   @id @default(uuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  total     Decimal  @db.Decimal(10, 2)
  createdAt DateTime @default(now())

  @@index([userId, createdAt])
}

The three commands, and when each is correct

Command	Use	Environment
`prisma migrate dev`	Create a new migration from schema changes	Development only
`prisma migrate deploy`	Apply pending migrations	Production
`prisma db push`	Sync schema without a migration file	Prototyping only
`prisma generate`	Generate the typed client	Both — required after install

NEVER RUN prisma migrate dev OR db push IN PRODUCTION

migrate dev compares the database to the migration history and, on any drift, offers to reset the database — dropping every table. In a non-interactive shell it may take that path without asking.

db push alters the schema directly with no migration record and will happily drop a column (and its data) to match the schema file.

Production uses exactly one command:

bash

pnpm prisma migrate deploy

It applies pending migrations in order, never resets, never prompts, and fails safely if the history does not match.

Production deployment sequence

bash

# SERVER
pnpm install --frozen-lockfile
pnpm prisma generate           # regenerate the client — required
pnpm prisma migrate deploy     # apply pending migrations
pnpm build
pm2 reload ecosystem.config.cjs --update-env

prisma generate IS EASY TO FORGET

The generated client lives in node_modules/.prisma. A fresh pnpm install may not run the postinstall hook (pnpm's strictness, or --ignore-scripts in a hardened CI). You then get:

@prisma/client did not initialize yet. Please run "prisma generate"

Always run it explicitly in your deploy script. Do not rely on the postinstall hook.

Safe migrations — the expand/contract pattern

A MIGRATION THAT DROPS A COLUMN CAN BREAK THE RUNNING APP

During a rolling deploy, old and new code run simultaneously. If the migration drops users.old_field while old instances still SELECT it, those instances 500 until they are replaced.

Use expand/contract across two deploys:

Deploy 1 (expand): add the new column as nullable, write to both old and new, backfill in the background. Deploy 2 (contract): stop reading the old column, then drop it.

For a rename, never ALTER TABLE ... RENAME COLUMN in one step. Add the new, dual-write, backfill, switch reads, drop the old.

Always review generated SQL before deploying:

bash

# LOCAL
cat prisma/migrations/20260811120000_add_orders/migration.sql

Look for DROP COLUMN, DROP TABLE, ALTER COLUMN ... SET NOT NULL (fails if any row is null), and ALTER COLUMN ... TYPE (may rewrite and lock the whole table).

MIGRATIONS TAKE LOCKS

ALTER TABLE takes an ACCESS EXCLUSIVE lock. On a large table this blocks all reads and writes for the duration. Worse, a blocked ALTER TABLE queues behind a long-running query and then blocks everything behind itself.

Set a timeout so a migration fails fast rather than freezing your site:

sql

SET lock_timeout = '5s';
SET statement_timeout = '30s';

Adding an index concurrently avoids the lock (but cannot run inside a transaction):

sql

CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);

Connection pooling with PM2 cluster

DATABASE_URL="postgresql://myapp:pass@127.0.0.1:5432/myapp_production?connection_limit=10&pool_timeout=20"

With 4 PM2 instances: 4 × 10 = 40 connections, comfortably under max_connections = 100. Formula:

connection_limit ≤ (max_connections − 10 reserved) / number_of_processes

If you need many more application instances, put PgBouncer in front (transaction pooling mode, pgbouncer=true in the Prisma URL).

Backups

Covered fully in Level 22. The essentials here:

bash

# SERVER — logical backup, custom format (compressed, allows selective restore)
pg_dump -U myapp -h 127.0.0.1 -Fc -f /var/backups/myapp-$(date +%F-%H%M).dump myapp_production

# Plain SQL (human-readable, restorable with psql)
pg_dump -U myapp -h 127.0.0.1 myapp_production | gzip > backup-$(date +%F).sql.gz

# Everything including roles (run as postgres)
sudo -u postgres pg_dumpall -f /var/backups/cluster-$(date +%F).sql

# Docker
docker compose exec -T postgres pg_dump -U myapp -Fc myapp_production > backup.dump

Restore:

bash

# SERVER — custom format
pg_restore -U myapp -h 127.0.0.1 -d myapp_production --clean --if-exists backup.dump

# Plain SQL
gunzip -c backup.sql.gz | psql -U myapp -h 127.0.0.1 -d myapp_production

# Safest: restore into a NEW database first and verify
sudo -u postgres createdb myapp_restore_test
pg_restore -U postgres -d myapp_restore_test backup.dump
psql -U postgres -d myapp_restore_test -c "SELECT count(*) FROM users;"

AN UNTESTED BACKUP IS NOT A BACKUP

The failure mode is always the same: backups have "run successfully" for eight months, and on the day you need one it turns out the cron job's pg_dump was writing a 0-byte file because the password changed, or the disk filled, or it only ever backed up the empty default database.

Restore to a scratch database monthly. Check row counts. Write down the date you last did it. This is the single highest-value operational habit in this guide.

Troubleshooting

Problem	Cause	Diagnose	Fix
`ECONNREFUSED 127.0.0.1:5432`	Not running	`systemctl status postgresql`	`sudo systemctl start postgresql`
`ECONNREFUSED ::1:5432`	IPv6/IPv4 mismatch	Check `DATABASE_URL`	Use `127.0.0.1`, not `localhost`
`password authentication failed`	Wrong password, or method mismatch	`sudo tail -f /var/log/postgresql/*.log`	Reset password; check `pg_hba.conf` method
`no pg_hba.conf entry for host`	No matching rule	`sudo -u postgres psql -c "SHOW hba_file"`	Add a `host` line; reload
`database "x" does not exist`	Typo, or never created	`\l` in psql	`createdb`
`permission denied for table`	Missing grants on a new table	`\dp tablename`	`GRANT` + `ALTER DEFAULT PRIVILEGES`
`too many connections`	Pool leak or too many processes	`SELECT count(*) FROM pg_stat_activity;`	Lower `connection_limit`; raise `max_connections`; find the leak
`could not resize shared memory`	`shm_size` too small in Docker	Container logs	Set `shm_size: 256mb`
`disk full`	WAL or data growth	`df -h`, check `pg_wal/`	Free space immediately; PostgreSQL stops writing at 100%
Migration hangs	Blocked on a lock	`SELECT * FROM pg_locks WHERE NOT granted;`	Find and cancel the blocking query
Slow queries appearing	Missing index, or stale statistics	`EXPLAIN ANALYZE`, `pg_stat_user_tables`	Add an index; `ANALYZE`
Prisma: "did not initialize yet"	`prisma generate` not run	—	Add it to the deploy script

bash

# SERVER — where are the logs?
sudo tail -f /var/log/postgresql/postgresql-16-main.log
docker compose logs -f postgres
sudo journalctl -u postgresql -f

ENABLE pg_stat_statements

The best tool for finding slow queries in production.

conf

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

sql

CREATE EXTENSION pg_stat_statements;
SELECT substring(query,1,80), calls, round(mean_exec_time::numeric,2) AS avg_ms,
       round(total_exec_time::numeric,2) AS total_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

Requires a restart. Worth it.

Production Checklist — Level 9

[ ] PostgreSQL 16 installed and systemctl enabled
[ ] sudo ss -tulpn | grep 5432 shows 127.0.0.1, not 0.0.0.0
[ ] listen_addresses = 'localhost' in postgresql.conf
[ ] Port 5432 is not in ufw status
[ ] Docker ports: for postgres is 127.0.0.1:5432:5432 or absent
[ ] Application uses a dedicated role, not postgres
[ ] Strong password, URL-safe, generated with openssl rand
[ ] pg_hba.conf uses scram-sha-256; no trust anywhere
[ ] ALTER DEFAULT PRIVILEGES set so new tables are accessible
[ ] Memory settings tuned for the server's RAM; work_mem conservative
[ ] log_min_duration_statement = 1000 so slow queries are visible
[ ] pg_stat_statements enabled
[ ] connection_limit set in DATABASE_URL and sized against max_connections
[ ] Production migrations use only prisma migrate deploy
[ ] prisma generate is an explicit step in the deploy script
[ ] Migration SQL reviewed for destructive operations before deploying
[ ] Automated daily pg_dump running (Level 22)
[ ] A restore has actually been tested, and I know the date
[ ] Disk usage monitored; alert configured below 20% free

Next: Level 10 — Redis →

Level 9 — PostgreSQL ​

What PostgreSQL is ​

The object hierarchy ​

The connection string ​

Option A — native PostgreSQL ​

Service management ​

Creating a database and user ​

Grants for an existing database ​

Verify the connection ​

Option B — PostgreSQL in Docker ​

Native vs Docker — which to choose ​

psql — the client ​

Queries you will actually run ​

Configuration ​

postgresql.conf — settings that matter ​

pg_hba.conf — authentication rules ​

Why 5432 must not be public ​

Prisma ​

The three commands, and when each is correct ​

Production deployment sequence ​

Safe migrations — the expand/contract pattern ​

Connection pooling with PM2 cluster ​

Backups ​

Troubleshooting ​

Production Checklist — Level 9 ​

Level 9 — PostgreSQL

What PostgreSQL is

The object hierarchy

The connection string

Option A — native PostgreSQL

Service management

Creating a database and user

Grants for an existing database

Verify the connection

Option B — PostgreSQL in Docker

Native vs Docker — which to choose

`psql` — the client

Queries you will actually run

Configuration

`postgresql.conf` — settings that matter

`pg_hba.conf` — authentication rules

Why 5432 must not be public

Prisma

The three commands, and when each is correct

Production deployment sequence

Safe migrations — the expand/contract pattern

Connection pooling with PM2 cluster

Backups

Troubleshooting

Production Checklist — Level 9