Last active
August 6, 2025 02:14
-
-
Save patshaughnessy/70519495343412504686 to your computer and use it in GitHub Desktop.
How to Debug Postgres using LLDB on a Mac
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| This note explains how to build Postgres from source and setup to debug it using LLDB on a Mac. I used this technique to research this article: | |
| http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals | |
| 1. Shut down existing postgres if necessary - you don’t want to mess up your existing DB or work :) | |
| $ ps aux | grep postgres | |
| pat 456 0.0 0.0 2503812 828 ?? Ss Sun10AM 0:11.59 postgres: stats collector process | |
| pat 455 0.0 0.0 2649692 2536 ?? Ss Sun10AM 0:05.00 postgres: autovacuum launcher process | |
| pat 454 0.0 0.0 2640476 304 ?? Ss Sun10AM 0:00.74 postgres: wal writer process | |
| pat 453 0.0 0.0 2640476 336 ?? Ss Sun10AM 0:00.76 postgres: writer process | |
| pat 452 0.0 0.0 2640476 364 ?? Ss Sun10AM 0:00.03 postgres: checkpointer process | |
| pat 332 0.0 0.0 2643548 1144 ?? S Sun10AM 0:04.43 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log | |
| pat 10687 0.0 0.0 2423360 196 s009 R+ 9:17AM 0:00.00 grep postgres | |
| $ cd /usr/local/opt/postgresql | |
| $ launchctl unload homebrew.mxcl.postgresql.plist | |
| Double check it’s not running: | |
| $ psql some_db | |
| psql: could not connect to server: No such file or directory | |
| Is the server running locally and accepting | |
| connections on Unix domain socket "/tmp/.s.PGSQL.5432"? | |
| 2. Download source code | |
| See: | |
| http://www.postgresql.org/ftp/source/ | |
| $ curl https://ftp.postgresql.org/pub/source/v9.4.4/postgresql-9.4.4.tar.bz2 | bzip2 -d | tar x | |
| $ cd postgresql-9.4.4 | |
| 3. Build with debugging options enabled | |
| $ ./configure --enable-cassert --enable-debug CFLAGS="-ggdb -O0 -fno-omit-frame-pointer" | |
| checking build system type... x86_64-apple-darwin15.0.0 | |
| checking host system type... x86_64-apple-darwin15.0.0 | |
| checking which template to use... darwin | |
| checking whether to build with 64-bit integer date/time support... yes | |
| checking whether NLS is wanted... no | |
| etc… | |
| Above that option “-O0” is: dash, capital O and number zero | |
| See this post for more info: | |
| https://wiki.postgresql.org/wiki/Developer_FAQ#Compile-time | |
| $ make | |
| make | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C src all | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C common all | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../backend submake-errcodes | |
| etc… | |
| cp ../../../contrib/spi/refint.so refint.so | |
| cp ../../../contrib/spi/autoinc.so autoinc.so | |
| cp ../../../contrib/dummy_seclabel/dummy_seclabel.so dummy_seclabel.so | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C config all | |
| make[1]: Nothing to be done for `all'. | |
| All of PostgreSQL successfully made. Ready to install. | |
| $ sudo make install | |
| Password: | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C src install | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C common install | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../backend submake-errcodes | |
| make[3]: Nothing to be done for `submake-errcodes'. | |
| /bin/sh ../../config/install-sh -c -d '/usr/local/pgsql/lib' | |
| /usr/bin/install -c -m 644 libpgcommon.a '/usr/local/pgsql/lib/libpgcommon.a' | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C port install | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../backend submake-errcodes | |
| make[3]: Nothing to be done for `submake-errcodes'. | |
| etc… | |
| /Applications/Xcode.app/Contents/Developer/usr/bin/make -C config install | |
| /bin/sh ../config/install-sh -c -d '/usr/local/pgsql/lib/pgxs/config' | |
| /usr/bin/install -c -m 755 ./install-sh '/usr/local/pgsql/lib/pgxs/config/install-sh' | |
| PostgreSQL installation complete. | |
| 4. Skip the adduser command from the postgres docs - I’ll just run postgres using my own user account to make debugging easier | |
| Instead, I’ll set the data directory to be owned by my account: | |
| $ sudo chown pat /usr/local/pgsql/data | |
| 5. Init the data directory | |
| $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data | |
| 6. Start the server | |
| $ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start | |
| 7. Create a test db | |
| $ /usr/local/pgsql/bin/createdb test | |
| 8. Test some basic SQL using the test db | |
| $ /usr/local/pgsql/bin/psql test | |
| test=# \d | |
| No relations found. | |
| test=# CREATE TABLE posts( | |
| id serial PRIMARY KEY, | |
| title VARCHAR (50) NOT NULL | |
| ); | |
| test=# \d | |
| List of relations | |
| Schema | Name | Type | Owner | |
| --------+-------------+----------+------- | |
| public | posts | table | pat | |
| public | posts_id_seq | sequence | pat | |
| (2 rows) | |
| test=# INSERT INTO posts(title) VALUES ('This is a test.'); | |
| test=# SELECT * from posts; | |
| id | title | |
| ----+----------------- | |
| 1 | This is a test. | |
| (1 row) | |
| test=# \q | |
| Now, let’s run LLDB! | |
| 9. First, look at what processes are running: | |
| $ ps aux | grep postgres | |
| pat 22978 0.0 0.0 2474160 684 ?? SNs 9:37AM 0:00.02 postgres: stats collector process | |
| pat 22977 0.0 0.0 2627208 1756 ?? SNs 9:37AM 0:00.01 postgres: autovacuum launcher process | |
| pat 22976 0.0 0.0 2602632 644 ?? SNs 9:37AM 0:00.01 postgres: wal writer process | |
| pat 22975 0.0 0.0 2602632 1704 ?? SNs 9:37AM 0:00.05 postgres: writer process | |
| pat 22974 0.0 0.0 2610824 2508 ?? SNs 9:37AM 0:00.01 postgres: checkpointer process | |
| pat 22972 0.0 0.1 2599560 12916 s009 SN 9:37AM 0:00.02 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data | |
| pat 23002 0.0 0.0 2434824 760 s009 S+ 9:43AM 0:00.00 grep postgres | |
| 10. Now, create a new connection from another terminal window: | |
| $ /usr/local/pgsql/bin/psql test | |
| psql (9.4.4) | |
| Type "help" for help. | |
| test=# | |
| 11. Return to the server terminal window, and repeat the ps command: | |
| ps aux | grep postgres | |
| pat 38593 0.0 0.0 2598140 3112 ?? Ss 10:09AM 0:00.01 postgres: pat test [local] idle | |
| pat 38589 0.0 0.0 2470304 656 ?? Ss 10:08AM 0:00.00 postgres: stats collector process | |
| pat 38588 0.0 0.0 2614136 1576 ?? Ss 10:08AM 0:00.00 postgres: autovacuum launcher process | |
| pat 38587 0.0 0.0 2597752 684 ?? Ss 10:08AM 0:00.01 postgres: wal writer process | |
| pat 38586 0.0 0.0 2606968 1520 ?? Ss 10:08AM 0:00.04 postgres: writer process | |
| pat 38585 0.0 0.0 2597752 716 ?? Ss 10:08AM 0:00.00 postgres: checkpointer process | |
| pat 38583 0.0 0.1 2599800 13880 s009 S 10:08AM 0:00.02 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data | |
| pat 23065 0.0 0.1 2580296 15332 ?? Ss 9:46AM 0:00.36 /Applications/MacVim-snapshot-76/MacVim.app/Contents/MacOS/Vim -f -g tcop/postgres.c | |
| pat 38595 0.0 0.0 2434824 764 s009 S+ 10:09AM 0:00.00 grep postgres | |
| Notice there’s a new process handling requests for my psql session: | |
| pat 38593 0.0 0.0 2598140 3112 ?? Ss 10:09AM 0:00.01 postgres: pat test [local] idle | |
| 12. Attach to the process handling my PSQL connection using LLDB - I got 38593 from the list above! | |
| $ lldb | |
| (lldb) attach -p 38593 | |
| Process 38593 stopped | |
| * thread #1: tid = 0x12ab21, 0x00007fff8dca317a libsystem_kernel.dylib`__recvfrom + 10, queue = 'com.apple.main-thread', stop reason = signal SIGSTOP | |
| frame #0: 0x00007fff8dca317a libsystem_kernel.dylib`__recvfrom + 10 | |
| libsystem_kernel.dylib`__recvfrom: | |
| -> 0x7fff8dca317a <+10>: jae 0x7fff8dca3184 ; <+20> | |
| 0x7fff8dca317c <+12>: movq %rax, %rdi | |
| 0x7fff8dca317f <+15>: jmp 0x7fff8dc9e2d4 ; cerror | |
| 0x7fff8dca3184 <+20>: retq | |
| Executable module set to "/usr/local/pgsql/bin/postgres". | |
| Architecture set to: x86_64h-apple-macosx. | |
| 13. Set a breakpoint | |
| (lldb) b exec_simple_query | |
| Breakpoint 1: where = postgres`exec_simple_query + 48 at postgres.c:854, address = 0x0000000101450250 | |
| This is the start of where Postgres executes a single SQL statement. See my article for more context. | |
| http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals | |
| 14. Allow the posrtgres to continue - the attach command stops the process. | |
| (lldb) c | |
| Process 38593 resuming | |
| 15. Return to the client window, and type in a SQL statement | |
| test=# select * from posts; | |
| … notice no result will be returned - this is because the server hit the breakpoint! | |
| 16. Return the server window and see Postgres stopped at the breakpoint | |
| Process 38593 stopped | |
| * thread #1: tid = 0x12ab21, 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854, queue = 'com.apple.main-thread', stop reason = breakpoint 1.1 | |
| frame #0: 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854 | |
| 851 static void | |
| 852 exec_simple_query(const char *query_string) | |
| 853 { | |
| -> 854 CommandDest dest = whereToSendOutput; | |
| 855 MemoryContext oldcontext; | |
| 856 List *parsetree_list; | |
| 857 ListCell *parsetree_item; | |
| Get a backtrace/stackdump: | |
| (lldb) bt | |
| * thread #1: tid = 0x12ab21, 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854, queue = 'com.apple.main-thread', stop reason = breakpoint 1.1 | |
| * frame #0: 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854 | |
| frame #1: 0x000000010144f9f4 postgres`PostgresMain(argc=1, argv=0x00007fa7eb803458, dbname=0x00007fa7eb8032c0, username=0x00007fa7eb8032a0) + 2868 at postgres.c:4074 | |
| frame #2: 0x00000001013b579f postgres`BackendRun(port=0x00007fa7eb600230) + 703 at postmaster.c:4164 | |
| frame #3: 0x00000001013b49a4 postgres`BackendStartup(port=0x00007fa7eb600230) + 420 at postmaster.c:3829 | |
| frame #4: 0x00000001013b3b08 postgres`ServerLoop + 616 at postmaster.c:1597 | |
| frame #5: 0x00000001013b138f postgres`PostmasterMain(argc=3, argv=0x00007fa7eb403790) + 5455 at postmaster.c:1244 | |
| frame #6: 0x00000001012f090b postgres`main(argc=3, argv=0x00007fa7eb403790) + 779 at main.c:228 | |
| frame #7: 0x00007fff888e25ad libdyld.dylib`start + 1 | |
| Look at the current location’s C source code: | |
| (lldb) l | |
| 858 bool save_log_statement_stats = log_statement_stats; | |
| 859 bool was_logged = false; | |
| 860 bool isTopLevel; | |
| 861 char msec_str[32]; | |
| 862 | |
| 863 | |
| 864 /* | |
| Step forward by 1 C statement: | |
| (lldb) n | |
| (lldb) Process 38593 stopped | |
| * thread #1: tid = 0x12ab21, 0x000000010145025b postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 59 at postgres.c:858, queue = 'com.apple.main-thread', stop reason = step over | |
| frame #0: 0x000000010145025b postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 59 at postgres.c:858 | |
| 855 MemoryContext oldcontext; | |
| 856 List *parsetree_list; | |
| 857 ListCell *parsetree_item; | |
| -> 858 bool save_log_statement_stats = log_statement_stats; | |
| 859 bool was_logged = false; | |
| 860 bool isTopLevel; | |
| 861 char msec_str[32]; | |
| Have fun!! :) :) | |
Nice! Just updated my .psqlrc file. :) thx
I used GDB to debug postgreSQL in Linux. After psql connects to the target database, the way I used to find which postgres backend process has been running is to input the command "select pg_backend_pid();".
It seems more convenient than ps. :)
wow this is great! thx.
very helpful, thx!
great!!! thx.
But I met one problem: # "source list" command does not work.
I followed about steps.
For step "16. Return the server window and see Postgres stopped at the breakpoint", I got:
`Process 15778 stopped
- thread #1, queue = 'com.apple.main-thread', stop reason = breakpoint 1.1
frame #0: 0x0000000101567070 postgresexec_simple_query postgresexec_simple_query:
-> 0x101567070 <+0>: pushq %rbp
0x101567071 <+1>: movq %rsp, %rbp
0x101567074 <+4>: pushq %r15
0x101567076 <+6>: pushq %r14
Target 0: (postgres) stopped.`
execute command: source list, no output
I tried "-g " flag, it does not work.
I checked the executable: postgres by nm command, it contains debug info
➜ postgresql-10.2 nm -pa /usr/local/pgsql/bin/postgres|grep OSO
000000005a9cd025 - 03 0001 OSO /Users/mh/postgresql-10.2/src/backend/../../src/port/libpgport_srv.a(path_srv.o)
YOU REALLY SAVED MY LIFE!!! THX!!! I missed some parameters for ./configure and that overwhelmed for such a long time.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
You didn't mention the best part, which is the lldb character-based "GUI"! :P
BTW, someone (I forget who :( ) recently pointed out this trick for discovering the backend PID:
cat ~/.psqlrc
SELECT pg_catalog.pg_backend_pid() AS backend_pid \gset
\set PROMPT1 '%/@%
hostname | cut -d. -f1-2/%:backend_pid:%R%#%x '