Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save davidglassborow/bbf5ef9d1cdacf66c55b to your computer and use it in GitHub Desktop.
Save davidglassborow/bbf5ef9d1cdacf66c55b 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!! :) :)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment