Reference

PostgreSQLのPL/pgSQLのデバッガpldebuggerを使ってみる
http://qiita.com/sadapon2008/items/627d0649122628c115f6

Prerequisite

PostgreSQL 9.4.x installed

Steps

set PATH for pg_config

PATH=$PATH:/usr/pgsql-9.4/bin
export PATH

# yum install postgresql-devel

# cd /usr/local/src
# git clone git://git.postgresql.org/git/pldebugger.git
# cd pldebugger/

make

# make USE_PGXS=1

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I/usr/pgsql-9.4/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsql-9.4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o plpgsql_debugger.o plpgsql_debugger.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsql-9.4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o plugin_debugger.o plugin_debugger.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsql-9.4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o dbgcomm.o dbgcomm.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsql-9.4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pldbgapi.o pldbgapi.c
In file included from pldbgapi.c:98:0:
/usr/pgsql-9.4/include/server/libpq/libpq-be.h:25:25: fatal error: openssl/ssl.h: No such file or directory
 #include <openssl/ssl.h>
                         ^
compilation terminated.
make: *** [pldbgapi.o] Error 1

yum install openssl-devel

# yum install openssl-devel

Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: ftp.tsukuba.wide.ad.jp
 * epel: ftp.jaist.ac.jp
 * epel-debuginfo: ftp.jaist.ac.jp
 * epel-source: ftp.jaist.ac.jp
 * extras: ftp.tsukuba.wide.ad.jp
 * updates: ftp.tsukuba.wide.ad.jp
Resolving Dependencies
--> Running transaction check
---> Package openssl-devel.x86_64 1:1.0.1e-60.el7_3.1 will be installed
--> Processing Dependency: zlib-devel(x86-64) for package: 1:openssl-devel-1.0.1e-60.el7_3.1.x86_64
--> Processing Dependency: krb5-devel(x86-64) for package: 1:openssl-devel-1.0.1e-60.el7_3.1.x86_64
--> Running transaction check
---> Package krb5-devel.x86_64 0:1.14.1-27.el7_3 will be installed
--> Processing Dependency: libverto-devel for package: krb5-devel-1.14.1-27.el7_3.x86_64
--> Processing Dependency: libselinux-devel for package: krb5-devel-1.14.1-27.el7_3.x86_64
--> Processing Dependency: libcom_err-devel for package: krb5-devel-1.14.1-27.el7_3.x86_64
--> Processing Dependency: keyutils-libs-devel for package: krb5-devel-1.14.1-27.el7_3.x86_64
---> Package zlib-devel.x86_64 0:1.2.7-17.el7 will be installed
--> Running transaction check
---> Package keyutils-libs-devel.x86_64 0:1.5.8-3.el7 will be installed
---> Package libcom_err-devel.x86_64 0:1.42.9-9.el7 will be installed
---> Package libselinux-devel.x86_64 0:2.5-6.el7 will be installed
--> Processing Dependency: libsepol-devel(x86-64) >= 2.5-6 for package: libselinux-devel-2.5-6.el7.x86_64
--> Processing Dependency: pkgconfig(libsepol) for package: libselinux-devel-2.5-6.el7.x86_64
--> Processing Dependency: pkgconfig(libpcre) for package: libselinux-devel-2.5-6.el7.x86_64
---> Package libverto-devel.x86_64 0:0.2.5-4.el7 will be installed
--> Running transaction check
---> Package libsepol-devel.x86_64 0:2.5-6.el7 will be installed
---> Package pcre-devel.x86_64 0:8.32-15.el7_2.1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================================================================================
 Package                                      Arch                            Version                                         Repository                        Size
=====================================================================================================================================================================
Installing:
 openssl-devel                                x86_64                          1:1.0.1e-60.el7_3.1                             updates                          1.2 M
Installing for dependencies:
 keyutils-libs-devel                          x86_64                          1.5.8-3.el7                                     base                              37 k
 krb5-devel                                   x86_64                          1.14.1-27.el7_3                                 updates                          651 k
 libcom_err-devel                             x86_64                          1.42.9-9.el7                                    base                              31 k
 libselinux-devel                             x86_64                          2.5-6.el7                                       base                             186 k
 libsepol-devel                               x86_64                          2.5-6.el7                                       base                              74 k
 libverto-devel                               x86_64                          0.2.5-4.el7                                     base                              12 k
 pcre-devel                                   x86_64                          8.32-15.el7_2.1                                 base                             479 k
 zlib-devel                                   x86_64                          1.2.7-17.el7                                    base                              50 k

Transaction Summary
=====================================================================================================================================================================
Install  1 Package (+8 Dependent packages)

Total download size: 2.7 M
Installed size: 5.7 M
Is this ok [y/d/N]: y
Downloading packages:
(1/9): keyutils-libs-devel-1.5.8-3.el7.x86_64.rpm                                                                                             |  37 kB  00:00:00     
(2/9): libcom_err-devel-1.42.9-9.el7.x86_64.rpm                                                                                               |  31 kB  00:00:00     
(3/9): libselinux-devel-2.5-6.el7.x86_64.rpm                                                                                                  | 186 kB  00:00:00     
(4/9): libsepol-devel-2.5-6.el7.x86_64.rpm                                                                                                    |  74 kB  00:00:00     
(5/9): libverto-devel-0.2.5-4.el7.x86_64.rpm                                                                                                  |  12 kB  00:00:00     
(6/9): krb5-devel-1.14.1-27.el7_3.x86_64.rpm                                                                                                  | 651 kB  00:00:00     
(7/9): pcre-devel-8.32-15.el7_2.1.x86_64.rpm                                                                                                  | 479 kB  00:00:00     
(8/9): zlib-devel-1.2.7-17.el7.x86_64.rpm                                                                                                     |  50 kB  00:00:00     
(9/9): openssl-devel-1.0.1e-60.el7_3.1.x86_64.rpm                                                                                             | 1.2 MB  00:00:00     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                4.0 MB/s | 2.7 MB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : keyutils-libs-devel-1.5.8-3.el7.x86_64                                                                                                            1/9 
  Installing : pcre-devel-8.32-15.el7_2.1.x86_64                                                                                                                 2/9 
  Installing : libsepol-devel-2.5-6.el7.x86_64                                                                                                                   3/9 
  Installing : libselinux-devel-2.5-6.el7.x86_64                                                                                                                 4/9 
  Installing : libcom_err-devel-1.42.9-9.el7.x86_64                                                                                                              5/9 
  Installing : libverto-devel-0.2.5-4.el7.x86_64                                                                                                                 6/9 
  Installing : krb5-devel-1.14.1-27.el7_3.x86_64                                                                                                                 7/9 
  Installing : zlib-devel-1.2.7-17.el7.x86_64                                                                                                                    8/9 
  Installing : 1:openssl-devel-1.0.1e-60.el7_3.1.x86_64                                                                                                          9/9 
  Verifying  : zlib-devel-1.2.7-17.el7.x86_64                                                                                                                    1/9 
  Verifying  : krb5-devel-1.14.1-27.el7_3.x86_64                                                                                                                 2/9 
  Verifying  : libverto-devel-0.2.5-4.el7.x86_64                                                                                                                 3/9 
  Verifying  : libcom_err-devel-1.42.9-9.el7.x86_64                                                                                                              4/9 
  Verifying  : libsepol-devel-2.5-6.el7.x86_64                                                                                                                   5/9 
  Verifying  : pcre-devel-8.32-15.el7_2.1.x86_64                                                                                                                 6/9 
  Verifying  : 1:openssl-devel-1.0.1e-60.el7_3.1.x86_64                                                                                                          7/9 
  Verifying  : libselinux-devel-2.5-6.el7.x86_64                                                                                                                 8/9 
  Verifying  : keyutils-libs-devel-1.5.8-3.el7.x86_64                                                                                                            9/9 

Installed:
  openssl-devel.x86_64 1:1.0.1e-60.el7_3.1                                                                                                                           

Dependency Installed:
  keyutils-libs-devel.x86_64 0:1.5.8-3.el7    krb5-devel.x86_64 0:1.14.1-27.el7_3    libcom_err-devel.x86_64 0:1.42.9-9.el7    libselinux-devel.x86_64 0:2.5-6.el7   
  libsepol-devel.x86_64 0:2.5-6.el7           libverto-devel.x86_64 0:0.2.5-4.el7    pcre-devel.x86_64 0:8.32-15.el7_2.1       zlib-devel.x86_64 0:1.2.7-17.el7      

Complete!

# make USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsql-9.4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pldbgapi.o pldbgapi.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -shared -o plugin_debugger.so plpgsql_debugger.o plugin_debugger.o dbgcomm.o pldbgapi.o -L/usr/pgsql-9.4/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.4/lib',--enable-new-dtags

finally ...

# make USE_PGXS=1 install

/usr/bin/mkdir -p '/usr/pgsql-9.4/lib'
/usr/bin/mkdir -p '/usr/pgsql-9.4/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.4/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.4/doc/extension'
/usr/bin/install -c -m 755  plugin_debugger.so '/usr/pgsql-9.4/lib/plugin_debugger.so'
/usr/bin/install -c -m 644 pldbgapi.control '/usr/pgsql-9.4/share/extension/'
/usr/bin/install -c -m 644 pldbgapi--1.0.sql pldbgapi--unpackaged--1.0.sql '/usr/pgsql-9.4/share/extension/'
/usr/bin/install -c -m 644 README.pldebugger '/usr/pgsql-9.4/doc/extension/'

# vi postgresql.conf

shared_preload_libraries = 'plugin_debugger'

# systemctl stop postgresql-9.4
# systemctl start postgresql-9.4

enable debugger extension

CREATE EXTENSION pldbgapi;