centos 6.8 + postgresql 9.6 + pldebugger/pldbgapi

pgsql 本身没有调试功能(伤心了)，但是可以通过extension方式实现(开心了)

目前这个插件放在 https://git.postgresql.org/gitweb/?p=pldebugger.git;a=summary

简述

description PL/pgSQL debugger server-side code. Formerly hosted on pgfoundry.org as edb-debugger. Maintained by EnterpriseDB, but contributions are welcome.
owner   Dave Page
last change Sat, 9 Dec 2017 13:58:10 +0800 (05:58 +0000)
URL git://git.postgresql.org/git/pldebugger.git
    https://git.postgresql.org/git/pldebugger.git
    ssh://git@git.postgresql.org/pldebugger.git

git下载

# yum install git

# cd /usr/pgsql-9.6/share/
# mkdir contrib
# cd contrib
# git clone git://git.postgresql.org/git/pldebugger.git
Initialized empty Git repository in /tmp/pldebugger/pldebugger/.git/
remote: Counting objects: 512, done.
remote: Compressing objects: 100% (408/408), done.
remote: Total 512 (delta 332), reused 171 (delta 104)
Receiving objects: 100% (512/512), 187.37 KiB | 101 KiB/s, done.
Resolving deltas: 100% (332/332), done.

# cd pldebugger
# ls -l
total 212
-rw-r--r-- 1 root root 19012 Dec 20 15:05 dbgcomm.c
-rw-r--r-- 1 root root   694 Dec 20 15:05 dbgcomm.h
-rw-r--r-- 1 root root  1978 Dec 20 15:05 globalbp.h
-rw-r--r-- 1 root root  2585 Dec 20 15:05 Makefile
-rw-r--r-- 1 root root  7457 Dec 20 15:05 pldbgapi--1.0.sql
-rw-r--r-- 1 root root 48087 Dec 20 15:05 pldbgapi.c
-rw-r--r-- 1 root root   181 Dec 20 15:05 pldbgapi.control
-rw-r--r-- 1 root root  2258 Dec 20 15:05 pldbgapi--unpackaged--1.0.sql
-rw-r--r-- 1 root root  3087 Dec 20 15:05 pldebugger.h
-rw-r--r-- 1 root root  5437 Dec 20 15:05 pldebugger.proj
-rw-r--r-- 1 root root 40430 Dec 20 15:05 plpgsql_debugger.c
-rw-r--r-- 1 root root 48860 Dec 20 15:05 plugin_debugger.c
-rw-r--r-- 1 root root   753 Dec 20 15:05 plugin_debugger.def
-rw-r--r-- 1 root root  3794 Dec 20 15:05 README.pldebugger
-rw-r--r-- 1 root root   792 Dec 20 15:05 settings.projinc
-rw-r--r-- 1 root root  1387 Dec 20 15:05 uninstall_pldbgapi.sql

仔细阅读 README.pldebugger

# cat README.pldebugger

PostgreSQL pl/pgsql Debugger API
================================

This module is a set of shared libraries which implement an API for debugging
pl/pgsql functions on PostgreSQL 8.4 and above. The pgAdmin project
(http://www.pgadmin.org/) provides a client user interface as part of pgAdmin 
III v1.10.0 and above, and pgAdmin 4.

If you wish to debug functions on PostgreSQL 8.4, 9.0 or 9.1, please checkout
the PRE-9_2 branch from GIT.

If you wish to debug functions on PostgreSQL 8.2 or 8.3, please checkout the 
PRE_8_4_SERVER branch from CVS.


Installation
------------

- Copy this directory to contrib/ in your PostgreSQL source tree.

- Run 'make; make install'

- Edit your postgresql.conf file, and modify the shared_preload_libraries config
  option to look like:

  shared_preload_libraries = '$libdir/plugin_debugger'

- Restart PostgreSQL for the new setting to take effect.

- Run the following command in the database or databases that you wish to
  debug functions in:

  CREATE EXTENSION pldbgapi;

  (on server versions older than 9.1, you must instead run the pldbgapi--1.0.sql
  script directly using psql).


Usage
-----

Connect pgAdmin to the database containing the functions you wish to debug.
Right-click the function to debug, and select Debugging->Debug to execute and
debug the function immediately, or select Debugging->Set Global Breakpoint to
set a breakpoint on the function. This will cause the debugger to wait for
another session (such as a backend servicing a web app) to execute the function
and allow you to debug in-context.

For further information, please see the pgAdmin documentation.


Troubleshooting
---------------

The majority of problems we've encountered with the plugin are caused by
failing to add (or incorrectly adding) the debugger plugin library to the
shared_preload_libraries configuration directive in postgresql.conf (following
which, the server *must* be restarted). This will prevent global breakpoints
working on all platforms, and on some (notably Windows) may prevent the 
pldbgapi.sql script from executing correctly.


Architecture
------------

The debugger consists of three parts:

1. The client. This is typically a GUI displays the source code, current
   stack frame, variables etc, and allows the user to set breakpoints and
   step throught the code. The client can reside on a different host than
   the database server.

2. The target backend. This is the backend that runs the code being debugged.
   The plugin_debugger.so library must be loaded into the target backend.

3. Debugging proxy. This is another backend process that the client is
   connected to. The API functions, pldbg_* in pldbgapi.so library, are
   run in this backend.

The client is to connected to the debugging proxy using a regular libpq
connection. When a debugging session is active, the proxy is connected
to the target via a socket. The protocol between the proxy and the target
backend is not visible to others, and is subject to change. The pldbg_*
API functions form the public interface to the debugging facility.


debugger client  *------ libpq --------* Proxy backend
  (pgAdmin)                                 *
                                            |
                                  pldebugger socket connection
                                            |
                                            *
application client *----- libpq -------* Target backend


Licence
-------

The pl/pgsql debugger API is released under the Artistic Licence.

    http://www.opensource.org/licenses/artistic-license.php

Copyright (c) 2004-2017 EnterpriseDB Corporation. All Rights Reserved.


Contact
-------

For support, please email the pgAdmin support mailing list. See

http://www.pgadmin.org/support/ 

for more details.

README.pldebugger 明确说明 PostgreSQL 8.4, 9.0 or 9.1 需要使用 PRE-9_2 分支，PostgreSQL 8.2 or 8.3 使用 PRE_8_4_SERVER 分支，PostgreSQL 9.2 及之后 使用 master分支。因为 PostgreSQL 9.2 开始使用 PGXS 来简化extension的编译安装。如果在 PostgreSQL 9.1 环境下就必须在源码环境下编译 pldebugger，然后拷贝相应的.so、.sql文件到指定的目录下。

The pgAdmin project
(http://www.pgadmin.org/) provides a client user interface as part of pgAdmin 
III v1.10.0 and above, and pgAdmin 4.

If you wish to debug functions on PostgreSQL 8.4, 9.0 or 9.1, please checkout
the PRE-9_2 branch from GIT.

If you wish to debug functions on PostgreSQL 8.2 or 8.3, please checkout the 
PRE_8_4_SERVER branch from CVS.

git branch

# git status
# On branch master
nothing to commit (working directory clean)

# git branch -a
* master
  remotes/origin/HEAD -> origin/master
  remotes/origin/PRE_8_4_SERVER
  remotes/origin/PRE_9_2
  remotes/origin/master  

make、make install

# cd /usr/pgsql-9.6/share/contrib/pldebugger
# source /var/lib/pgsql/.bash_profile

# USE_PGXS=1 make clean  
# USE_PGXS=1 make  
# USE_PGXS=1 make install  

这里必须使用 USE_PGXS=1，原因参考另外一篇blog。

# USE_PGXS=1 make clean  
rm -f plugin_debugger.so   libplugin_debugger.a  libplugin_debugger.pc
rm -f plpgsql_debugger.o plugin_debugger.o dbgcomm.o pldbgapi.o

# USE_PGXS=1 make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fPIC -I/usr/pgsql-9.6/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/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 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/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 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/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 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/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 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -fPIC -shared -o plugin_debugger.so plpgsql_debugger.o plugin_debugger.o dbgcomm.o pldbgapi.o -L/usr/pgsql-9.6/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags  

# USE_PGXS=1 make install  
/bin/mkdir -p '/usr/pgsql-9.6/lib'
/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/bin/mkdir -p '/usr/pgsql-9.6/doc/extension'
/usr/bin/install -c -m 755  plugin_debugger.so '/usr/pgsql-9.6/lib/plugin_debugger.so'
/usr/bin/install -c -m 644 .//pldbgapi.control '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//pldbgapi--1.0.sql .//pldbgapi--unpackaged--1.0.sql  '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//README.pldebugger '/usr/pgsql-9.6/doc/extension/'

查看

# cd /usr/pgsql-9.6/share/extension
# ls -l |grep -i pldbgapi
-rw-r--r-- 1 root root  7457 Dec 20 15:52 pldbgapi--1.0.sql
-rw-r--r-- 1 root root   181 Dec 20 15:52 pldbgapi.control
-rw-r--r-- 1 root root  2258 Dec 20 15:52 pldbgapi--unpackaged--1.0.sql

# cd /usr/pgsql-9.6/lib
# ls -l |grep -i debug
-rwxr-xr-x 1 root     root      238312 Dec 20 15:52 plugin_debugger.so

vi postgresql.conf

$ vi postgresql.conf    
shared_preload_libraries = '$libdir/plugin_debugger.so'  

重启数据库

# /etc/init.d/postgresql-9.6 stop
# /etc/init.d/postgresql-9.6 start

create extension pldbgapi

进入目标数据库，创建pldbgapi插件

mondb=# select * 
mondb-#   from pg_available_extensions
mondb-#  where 1=1
mondb-#    and name like '%pldbgapi%';
   name   | default_version | installed_version |                       comment                        
----------+-----------------+-------------------+------------------------------------------------------
 pldbgapi | 1.0             |                   | server-side support for debugging PL/pgSQL functions
(1 row)


mondb=# create extension pldbgapi ; 
CREATE EXTENSION


mondb=# select *
mondb-#   from pg_extension 
mondb-#  where 1=1
mondb-#    and extname like '%pldbgapi%';
 extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
----------+----------+--------------+----------------+------------+-----------+--------------
 pldbgapi |       10 |         2200 | t              | 1.0        |           | 
(1 row)

使用pgadmin 4 就可以开心的 debug 了。

转载于:https://www.cnblogs.com/ctypyb2002/p/9793084.html