-
-
Save patshaughnessy/70519495343412504686 to your computer and use it in GitHub Desktop.
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 postgres
exec_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.
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 '