Debugging

PostgreSQL is a long-running multi-process server in C. The interesting bugs are concurrency bugs, memory-corruption bugs, and "this happens once a week in production" bugs. The tooling reflects that.

gdb on a running backend

The most common backend-debugging entry point is to attach gdb to a live backend.

psql       # find your backend pid
SELECT pg_backend_pid();
# in another shell:
sudo gdb -p <pid>

Once attached, the standard breakpoints are:

(gdb) b ExecMain          # entry of executor
(gdb) b standard_planner  # planner entry
(gdb) b errfinish         # break on every ereport(ERROR)
(gdb) b log_line_prefix   # not very useful, just an example
(gdb) c

Backends drop into WaitEventSetWait between client messages; if you attach an idle one and continue, it just sits there until you psql> SELECT 1;.

Asserts and --enable-cassert

Build with --enable-cassert (autoconf) or -Dcassert=true (Meson). Asserts are scattered through the codebase and catch many bugs early. Examples:

• Assert(LWLockHeldByMe(lock)); — catches missed locking.
• Assert(IsTransactionState()); — catches calls outside a transaction.
• Assert(IsA(node, FuncExpr)); — catches type-tag mismatches.

Asserts must never have side effects; the compiler may strip them in release builds.

ereport / elog logs

The server log (default stderr or pg_log/postgresql-*.log) is the primary diagnostic channel. Useful settings:

SET log_min_messages = DEBUG1;       -- or DEBUG5 for the firehose
SET log_min_duration_statement = 0;  -- log every query with duration
SET log_lock_waits = on;
SET deadlock_timeout = '100ms';      -- catches deadlocks faster in tests
SET log_statement = 'all';
SET client_min_messages = LOG;       -- send LOG to the client too

For ad-hoc debug output from your own code, elog(NOTICE, "x = %d", x) is the quick path; use DEBUG1..DEBUG5 if you don't want it client-visible.

Crash dumps and core files

When a backend dies with SIGSEGV or SIGABRT, the postmaster restarts the cluster (kicking all other backends — they would otherwise see possibly-corrupt shared memory). A core file is left behind if the OS limits permit:

ulimit -c unlimited
echo "/tmp/core-%e-%p" | sudo tee /proc/sys/kernel/core_pattern

gdb /path/to/postgres /tmp/core-postgres-12345 then opens the dump. On modern Linux systems, coredumpctl is the preferred way to capture cores from systemd-managed services.

Common failure modes

Backend hung in a LWLockAcquire

(gdb) bt
#0 0x... in __lll_lock_wait
#1 0x... in pthread_mutex_lock_full
#2 0x... in PGSemaphoreLock
#3 0x... in LWLockAcquire (lock=0x..., mode=LW_EXCLUSIVE)

Look at lock->tranche and the surrounding source to identify which lock. Then: who holds it? select * from pg_stat_activity and select * from pg_locks will tell you, but if everything's hung you may need to attach gdb to other backends and look at their stacks.

Endless WaitEventSetWait loop

Sometimes a backend looks idle but is "supposed" to be doing work. Check whether it is in ProcSleep waiting on a heavy lock, or ConditionVariableSleep waiting on a CV, or WalSndLoop. The wait event name in pg_stat_activity.wait_event_type / wait_event is your guide.

Could not read block X in file Y

Almost always means a buffer was evicted with the wrong contents, the file was truncated out from under the buffer, or the FSM/VM disagrees with the heap. Reproduce in an --enable-cassert build; the asserts in bufmgr.c, md.c, and smgr.c usually catch the actual cause.

"memory leak" growing during a single statement

Allocations in the wrong memory context. Switch into executor_top_context for things that should live until end of statement, into per_tuple_memory for things that should reset between input rows. See Patterns and conventions for context lifetimes.

Assertion failure in ProcArrayEndTransaction

Usually means a transaction state machine bug — a missing AbortTransaction, double-CommitTransaction, or a control flow that exits a transaction without going through the proper teardown. The transaction states are defined in src/backend/access/transam/xact.c.

Tracing system catalog cache lookups

Set CACHEDEBUG=1 (with -DCACHEDEBUG at compile time). Every sys cache lookup is printed. Useful when chasing an unexpected cache lookup failed for relation N error — the actual culprit is usually a release of the cache reference too early.

Tracing planner choices

SET debug_print_parse = on;
SET debug_print_rewritten = on;
SET debug_print_plan = on;
SET debug_pretty_print = on;

Each of these dumps the corresponding tree to the server log in serialized Node form. For real plans, EXPLAIN (ANALYZE, VERBOSE, BUFFERS) is usually faster.

Reproducing replication bugs

The TAP framework under src/test/recovery/ has examples of every replication scenario. Copy a similar test file, modify it to reproduce, and let the framework start/stop nodes for you. Tests left in a failed state preserve their tmp_check/ directories with logs from every node.

Tooling

• valgrind — works for the backend with --track-origins=yes. Run a backend under valgrind via pg_ctl -o "--enable-valgrind". Slow but worth it when chasing a memory-corruption bug.
• clang -fsanitize=address — works; rebuild with CC=clang CFLAGS="-fsanitize=address -O1 -ggdb". Catches use-after-free immediately.
• perf record -p <pid> and perf report — for profiling. CPU bottlenecks usually show up in ExecInterpExpr, heap_getnext, or somewhere in the optimizer.
• pg_stat_statements (in contrib/) — aggregated query statistics. Useful in a "why is the database slow" diagnostic.
• pg_stat_activity, pg_locks, pg_stat_replication — runtime state queries.

When all else fails

Reduce the failing case to a self-contained .sql script and a brief reproduction recipe. Post it to pgsql-hackers with a build configuration and the exact commit hash. Someone will probably know which commit broke it.