Imported from matejformanek/postgres-claude (.claude/skills/debugging/SKILL.md). Install upstream with npx skills add matejformanek/postgres-claude --skill debugging. Copyright stays with the author.

debugging

PostgreSQL is a multi-process server, not threaded: the postmaster forks a fresh backend per connection. That single fact dictates the whole debugging workflow. [verified-by-code; see source/src/backend/postmaster/postmaster.c, and .claude/skills/build-and-run/SKILL.md line 59–63]

1. Build first

Debugging only makes sense against a debug build. See .claude/skills/build-and-run/SKILL.md — --buildtype=debug -Dcassert=true. debug (not debugoptimized) keeps locals and inline frames readable. [verified-by-code; build-and-run/SKILL.md line 33]

The upstream FAQ recommends CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer" for autoconf builds — -Og keeps reasonable perf while preserving frames. [from-wiki: Developer_FAQ]

2. The attach pattern (most common workflow)

Project shortcuts. In this repo, /pg-attach automates the held-PID grab and prints the exact lldb -p <pid> line ready to paste; /pg-tail-log follows dev/data-debug/server.log (where pprint and elog output land). Use them.

The postmaster itself never executes your query. Attach to the backend that handles your session. The key footgun: a one-shot psql -tAc "SELECT pg_backend_pid()" closes the connection immediately, so the backend you "got the PID of" is already gone by the time you read the output. You need a held backend — one whose connection stays open long enough for you to attach.

The held-PID pattern (works from a script)

Open a backend that holds its connection for N seconds via pg_sleep, tag it with a recognizable application_name, then look the PID up via pg_stat_activity:

# Hold a backend open for 60s, tagged so we can find it.
PGAPPNAME=hold psql -h /tmp -d postgres -X -c 'SELECT pg_sleep(60);' &

# Look up its backend PID (give it a moment to register).
sleep 0.5
PID=$(psql -h /tmp -d postgres -tAc \
  "SELECT pid FROM pg_stat_activity WHERE application_name='hold'")
echo "$PID"

# Attach in a separate terminal:
lldb -p "$PID"

PGAPPNAME is a libpq env var (NOT a psql \set variable — that won't propagate to the server). Inside the lldb session, the backend will be parked inside pg_sleep's WaitLatch loop, ready for breakpoints.

The interactive variant

When you actually want to drive queries through the attached session, hold psql open interactively instead:

-- in psql, on the connection you want to debug:
SELECT pg_backend_pid();
 pg_backend_pid
----------------
          54321

Keep that psql session alive while you attach in another terminal:

Then in another terminal:

# Linux / where gdb works
gdb -p 54321

# macOS — lldb is the path of least resistance
lldb -p 54321

Why lldb on macOS: gdb on Darwin needs a code-signed binary with the com.apple.security.cs.debugger entitlement; lldb ships with Xcode Command Line Tools and works out of the box. [from-wiki: Developer_FAQ implies gdb-or-lldb; macOS codesigning requirement is well-known toolchain quirk — [inferred] for the entitlement specifics]

Translating common gdb commands to lldb

First command after every attach

PostgreSQL uses SIGUSR1 heavily for latch wakeups. Silence it before you do anything else, or every continue lands back in the signal handler:

(lldb) pro hand -p true -s false SIGUSR1
(gdb)  handle SIGUSR1 noprint pass

[from-wiki: Developer_FAQ]

3. Stepping the startup path — single-user mode

Decision rule — which tool for which startup phase:

Attaching to a forked backend can't help with code that runs before the backend is ready to accept queries (auth, shmem init, startup hooks). Use postgres --single for that. No postmaster, no fork — the debugger sees the whole lifetime in one process.

gdb --args postgres --single -D "$PGDATA" postgres
# or
lldb -- postgres --single -D "$PGDATA" postgres

[verified-by-code; source/doc/src/sgml/ref/postgres-ref.sgml line 714+: "To start a single-user mode server, use a command like postgres --single -D /usr/local/pgsql/data other-options my_database"]

In single-user mode newline terminates a command (not semicolon). Use -j to switch to ;\n\n mode if you're pasting psql-style scripts. [verified-by-code; postgres-ref.sgml around line 730]

4. Catching things that happen too fast

For code that runs before you can grab the PID (e.g. early backend startup, parallel workers, autovacuum workers):

• PGOPTIONS="-W 10" psql ... — the backend sleeps 10 seconds at startup so you have time to pg_backend_pid() and attach. [from-wiki: Developer_FAQ]
• For short-lived background workers, insert a temporary spin-loop:

  bool wait = true;
  elog(LOG, "waiting for debugger, pid=%d", MyProcPid);
  while (wait) pg_usleep(1000000L);

  Attach, then set var wait = 0 to release. [from-wiki: Developer_FAQ — pattern shown there with sleep(); using pg_usleep is the PG-idiomatic equivalent, [inferred]]
• Some test paths already sleep deliberately. Example: WAL recovery has pg_usleep(10000L); /* wait for 10 msec */ retry loops. [verified-by-code; source/src/backend/access/transam/xlog.c:7707, 7724]
• True waitpoint infrastructure exists for tests — see source/src/test/authentication/t/007_pre_auth.pl line 38: "Connect to the server and inject a waitpoint." Waitpoints let TAP tests pause the backend at a labeled location. [verified-by-code]

5. Useful breakpoints

Where to break depends on what you're chasing:

[errfinish for trapping errors: from-wiki Developer_FAQ. The rest: verified-by-code, names exist as symbols in source/src/backend/{tcop,executor,storage/buffer,storage/lmgr,access/transam,access/heap}/.]

5.1 Trapping every error (the universal ereport breakpoint)

For SIGSEGV chases or "I don't know which ereport() is firing", break on errfinish — it's the single funnel every ereport/elog call goes through. Filter on elevel >= ERROR (= 21) so you don't stop on every LOG/NOTICE:

(lldb) b errfinish
(lldb) br mod -c 'edata->elevel >= 21'   # ERROR = 21
(gdb)  b errfinish
(gdb)  cond <n> edata->elevel >= 21

[ERROR = 21 verified-by-code: source/src/include/utils/elog.h:53.]

6. Pretty-printing PG internals

Postgres ships pprint() for Node * trees (parse trees, plans). Call it from the debugger:

(gdb) call pprint(parsetree)
(lldb) expr pprint(parsetree)

Output goes to the backend's stderr (the server log, typically). [from-wiki: Developer_FAQ]

Memory inspection without stopping execution:

(gdb) p MemoryContextStats(TopMemoryContext)

Or, from SQL on PG 14+: SELECT * FROM pg_backend_memory_contexts;. [from-wiki: Developer_FAQ; pg_backend_memory_contexts: verified-by-code, view exists.]

There is no gdbpg.py in the current source tree (searched source/src/tools/ — not present). Community pretty-printers exist on GitHub but are not bundled. [verified-by-code: find returned zero matches under source/.]

7. elog / ereport instrumentation pattern

For "I want to know which path got taken" rather than stepping:

elog(DEBUG1, "BufferAlloc: blkno=%u, hit=%d", blkno, found);

Then start the server with log_min_messages = debug1 (or higher number through debug5). See .claude/skills/build-and-run/SKILL.md line 78 for the GUC name. [verified-by-code; ereport/elog are the canonical logging macros throughout the backend — see .claude/skills/error-handling/ for full conventions.]

For ad-hoc tracing, fprintf(stderr, ...) works in --single mode and goes straight to the controlling terminal. Don't ship that.

8. Core dumps on macOS

Prerequisites

# 1. The shell that launches postgres must allow cores.
ulimit -c unlimited
# expected (after setting): "unlimited"

# 2. /cores must exist and be writable.
ls -ld /cores
# expected: drwxrwxrwt  root  wheel   (sticky, world-writable)
sudo chmod 1777 /cores    # only if the perms above don't match

# 3. Verify kernel allows cores.
sysctl kern.coredump
# expected: kern.coredump: 1

Then crash the backend and open the core:

lldb /path/to/postgres /cores/core.<pid>

Caveats (macOS-specific)

• Apple-signed / hardened binaries suppress cores regardless of ulimit. Our locally-built postgres is unsigned, so this isn't a concern here — but if you ever try to core-dump /usr/bin/postgres or a Homebrew bottle, this is why nothing lands in /cores.
• No kern.coredump sysctl on your machine? If sysctl kern.coredump returns unknown oid, your macOS version controls cores via ulimit alone — steps 1 and 2 are sufficient.

[ulimit -c unlimited and /cores location: standard Darwin defaults documented in man 5 core on macOS. kern.coredump sysctl and hardened-binary suppression: [inferred] from well-known Darwin behavior.]

9. SQL-level inspection extensions

When you want to look at shared state without a debugger attach, the contrib extensions are the right tool:

• pg_buffercache — one row per shared buffer with relfilenode, block number, usage count, dirty flag, pin count. Now also exposes pg_buffercache_evict() and pg_buffercache_numa (NUMA-page mapping). [verified-by-code; source/contrib/pg_buffercache/pg_buffercache--1.5--1.6.sql, pg_buffercache_pages.c]
• pageinspect — raw page contents (heap tuples, btree pages, GIN, BRIN). For "why is this page corrupt" or "what's the actual lp_off of this line pointer."
• pg_visibility — visibility map state per page; functions like pg_check_frozen(regclass) and pg_check_visible(regclass) flag VM bits that disagree with the heap. [verified-by-code; source/contrib/pg_visibility/pg_visibility--1.1--1.2.sql]
• amcheck — structural integrity checks for btree and heap. Reproduce-suspected-corruption tool.

All four are in-tree under source/contrib/. [verified-by-code]

10. Reproducing locking bugs

The hard ones. Standard recipe:

-- Session A: get the PID, take the lock you want to study.
SELECT pg_backend_pid();
BEGIN;
LOCK TABLE t IN ACCESS EXCLUSIVE MODE;

-- Session B: from a third session (so you can still query),
-- watch who holds what and who is waiting on whom:
SELECT pid, locktype, mode, granted, relation::regclass, transactionid
FROM   pg_locks
WHERE  NOT granted
   OR  pid IN (<A pid>, <B pid>);

SELECT pid, wait_event_type, wait_event, state, query
FROM   pg_stat_activity
WHERE  pid IN (<A pid>, <B pid>);

The wait_event_type / wait_event columns name the exact wait point inside the backend — usually enough to know which LWLock or heavyweight lock is blocking.

For deadlocks, set deadlock_timeout = '100ms' (default 1s) to make the detector fire quickly, then check the server log for the standard DETAIL: Process N waits for X on Y; blocked by process M. block. [verified-by-code: deadlock_timeout GUC; deadlock log format from source/src/backend/storage/lmgr/deadlock.c and proc.c. The 100ms value: [inferred] reasonable shorter-than-default — adjust to taste.]

To dive deeper into the locking model itself, see .claude/skills/locking/.

11. Memory bugs and leak hunting

Three tools, in increasing order of "bring out the heavy machinery":

11.1 pg_backend_memory_contexts — observe before debugging

Cheapest first step for any "the backend's RSS keeps growing" suspicion. Run on the suspect connection between iterations of a workload:

SELECT name, level, parent, total_bytes/1024 AS kb, used_bytes/1024 AS used_kb
FROM   pg_backend_memory_contexts
ORDER  BY total_bytes DESC
LIMIT  20;

Run the suspect workload N times via \watch, then re-check. A context that grows monotonically across iterations is your leak signature. For another backend's contexts, use pg_log_backend_memory_contexts(<pid>) (PG 14+) — it dumps to dev/data-debug/server.log. [verified-by-code; pg_backend_memory_contexts view and pg_log_backend_memory_contexts function exist in src/backend/utils/adt/mcxtfuncs.c.]

11.2 AddressSanitizer + UndefinedBehaviorSanitizer

For use-after-free, heap-buffer-overflow, double-free, and undefined behavior (signed overflow, alignment violations, bool-from-non-bool). Use the sanitizer build profile — see .claude/skills/build-and-run/SKILL.md "Sanitizer builds":

# One-time build
/setup-pg-asan
# Start the asan cluster (separate data dir from debug cluster)
/pg-start-asan

Important runtime knobs (the /pg-start-asan wrapper sets them):

export ASAN_OPTIONS="abort_on_error=1:detect_leaks=0:detect_stack_use_after_return=1:print_stacktrace=1"
export UBSAN_OPTIONS="print_stacktrace=1:halt_on_error=1"

On a hit, ASan writes a full stack to the server log AND the controlling terminal, then aborts the backend (postmaster restarts a fresh one). Check dev/data-asan/server.log for the report. The stack frames will have file:line directly into the source you built — no addr2line dance.

detect_leaks=0 is required on macOS — LeakSanitizer is not supported on Darwin and setting detect_leaks=1 errors out at startup. For leaks specifically, use 11.3 instead, or rebuild this profile on Linux. [verified: macOS ASan does NOT include LSan, per LLVM sanitizer-platform compat matrix.]

11.3 macOS-native leak detection (leaks / malloc_history)

When LSan isn't an option (i.e., on this Mac), the Darwin tooling does the job — no rebuild required, works against the existing build-debug:

# Before pg_ctl start, enable malloc backtraces.
export MallocStackLogging=1
/pg-restart

# Reproduce the leak workload, then:
PID=<the suspect backend pid>
leaks "$PID"           # one-shot report
leaks --atExit -- /path/to/cmd       # for short-lived subprocesses

# Get allocation backtrace for a specific address found by `leaks`:
malloc_history "$PID" <addr>

MallocStackLogging adds ~10-30% RSS overhead — fine for dev, don't ship.

When pg_backend_memory_contexts is the right tool vs leaks: PG-context allocations (palloc/MemoryContextAlloc) show up in the context view, NOT in leaks — because palloc'd memory is freed via MemoryContextDelete() and is not "leaked" from libc's perspective. For raw malloc() leaks (which do exist — extension code, libpq, third-party libs called from the backend), leaks is the answer.

11.4 Choosing the right tool

12. Quick checklist

When something is wrong, in order:

1. Reproduce against a debug+cassert build. Asserts catch a lot.
2. Check the server log — log_min_messages = debug2 is usually enough to surface the path taken.
3. SELECT pg_backend_pid() → attach lldb/gdb → set the breakpoint you suspect.
4. If it's startup or auth, switch to postgres --single.
5. If it's shared state, ask pg_buffercache / pg_locks / pg_stat_activity before reaching for the debugger.
6. If it crashed, check /cores/core.<pid> and open it with lldb.
7. If it's a memory bug (UAF, OOB, leak), pick the right tool from §11.4.

Cross-references

• .claude/skills/build-and-run/SKILL.md — get a debug+cassert build before debugging anything; ASan profile lives here.
• .claude/skills/psql/SKILL.md — held-PID handoff (PGAPPNAME=hold + pg_sleep), pg_backend_memory_contexts, \errverbose from the SQL side.
• .claude/skills/locking/SKILL.md — interpret pg_locks / wait_event output once §10 surfaces the contention.
• .claude/skills/error-handling/SKILL.md — errfinish + elevel numbering + ereport macro structure (§5.1, §7).
• .claude/skills/memory-contexts/SKILL.md — what pg_backend_memory_contexts shows and how to read it (§11.1).
• .claude/commands/pg-attach.md, .claude/commands/pg-tail-log.md — slash-command wrappers built on top of this skill.