Debugging Your PostgreSQL Database Binaries

Why ?

Knowing how to debug is a critical aspect of every application development life cycle. Debugging database bianaries allow you to not only recognize that an exception has occurred, but also traverses through the database binaries execution until the culprit code can be located. Whether the resolution requires modifying a minor typo or even rewriting a massive component, the act of debugging is tremendously helpful for database server developers to (eventually) track down and solve the issue.

How ?

Following are the simple steps that can help you start debugging your code:

1> Get debug symbols for the database server:
Debug symbols are separately generated and maintained for each release so for debugging EnterpriseDB's PostgreSQL Advanced Server you need to contact EnterpriseDB or you can reach to "support@enterprisedb.com" for specific version's of symbols.

We need to install the debug symbol for the specific version. Here as and example I will try to install the debug symbols for PPAS-9.5 binaries.

[root@localhost Desktop]# rpm -ivh ppas95-server-debuginfo-9.5.9.14-1.rhel6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:ppas95-server-debuginfo########################################### [100%]
[root@localhost Desktop]#

2> Once we are done with the installation, we need to attach the process for debugging:

Terminal 1 -  Start the session and check the pid of backend,

[root@localhost bin]# ./psql -p 5333 -U enterprisedb -d edb
psql.bin (9.5.9.14)
Type "help" for help.

edb=# select pg_backend_pid();
 pg_backend_pid
----------------
          36755
(1 row)

edb=# select pg_sleep(600);
 pg_sleep
----------
 
(1 row)

Terminal 2 - Attach the process for debugging,

[root@localhost edb]# gdb -p 36755
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-92.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 36755
Reading symbols from /usr/ppas-9.5/bin/edb-postgres...Reading symbols from /usr/lib/debug/usr/ppas-9.5/bin/edb-postgres.debug...done.
done.
Reading symbols from /usr/lib64/libmemcached.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libmemcached.so.2
Reading symbols from /usr/lib64/libxml2.so.2...Reading symbols from /usr/lib/debug/usr/lib64/libxml2.so.2.7.6.debug...done.
done.
Loaded symbols for /usr/lib64/libxml2.so.2
Reading symbols from /lib64/libpam.so.0...Reading symbols from /usr/lib/debug/lib64/libpam.so.0.82.2.debug...done.
.
.
Loaded symbols for /usr/ppas-9.5/lib/edb_gen.so
Reading symbols from /lib64/libnss_files.so.2...Reading symbols from /usr/lib/debug/lib64/libnss_files-2.12.so.debug...done.
done.
Loaded symbols for /lib64/libnss_files.so.2
0x000000318f8df248 in __poll (fds=0x7fff0e83d850, nfds=1, timeout=<value optimized out>) at ../sysdeps/unix/sysv/linux/poll.c:83
83        return INLINE_SYSCALL (poll, 3, CHECK_N (fds, nfds), nfds, timeout);
Missing separate debuginfos, use: debuginfo-install libicu-ppas-53.1-11.rhel6.x86_64 libmemcached-0.31-1.1.el6.x86_64
(gdb) bt
#0  0x000000318f8df248 in __poll (fds=0x7fff0e83d850, nfds=1, timeout=<value optimized out>) at ../sysdeps/unix/sysv/linux/poll.c:83
#1  0x00000000006a32f7 in WaitLatchOrSocket (latch=0x7f4f9e44b5a4, wakeEvents=<value optimized out>, sock=-1, timeout=600000) at pg_latch.c:333
#2  0x000000000078458c in pg_sleep (fcinfo=<value optimized out>) at misc.c:441
#3  0x000000000081c05c in FunctionCallInvokeCheckSPL (fcinfo=<value optimized out>) at fmgr.c:2543
#4  0x0000000000607214 in ExecMakeFunctionResultNoSets (fcache=0x12bcef8, econtext=0x12bcc88, isNull=0x12be100 "", isDone=<value optimized out>) at execQual.c:2244
#5  0x0000000000602d22 in ExecTargetList (projInfo=<value optimized out>, isDone=0x7fff0e83da4c) at execQual.c:5844
#6  ExecProject (projInfo=<value optimized out>, isDone=0x7fff0e83da4c) at execQual.c:6068
#7  0x000000000061db3b in ExecResult (node=0x12bcb78) at nodeResult.c:159
#8  0x00000000006021f8 in ExecProcNode (node=0x12bcb78) at execProcnode.c:386
#9  0x00000000006009db in ExecutePlan (queryDesc=0x12b6eb8, direction=ForwardScanDirection, count=0) at execMain.c:1616
#10 standard_ExecutorRun (queryDesc=0x12b6eb8, direction=ForwardScanDirection, count=0) at execMain.c:361
#11 0x0000000000719de7 in PortalRunSelect (portal=0x12b8a48, forward=<value optimized out>, count=0, dest=<value optimized out>) at pquery.c:1175
#12 0x000000000071b231 in PortalRun (portal=0x12b8a48, count=9223372036854775807, isTopLevel=1 '\001', dest=0x12bafe0, altdest=0x12bafe0, completionTag=0x7fff0e83ddb0 "") at pquery.c:1018
#13 0x000000000071574f in exec_simple_query (query_string=0x127abd8 "select pg_sleep(600);") at postgres.c:1333
#14 0x00000000007188f0 in PostgresMain (argc=<value optimized out>, argv=<value optimized out>, dbname=0x12060d8 "edb", username=<value optimized out>) at postgres.c:5424
#15 0x00000000006b5992 in BackendRun (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:4335
#16 BackendStartup (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:3953
#17 ServerLoop (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:1716
#18 PostmasterMain (argc=<value optimized out>, argv=<value optimized out>) at postmaster.c:1324
#19 0x000000000063bc60 in main (argc=3, argv=0x1205060) at main.c:232
(gdb) quit