centos 6.8 + pgsql 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 Pagelast 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://[e-mail]/pldebugger.git

# yum install git# cd /tmp# mkdir pldebugger# cd ./pldebugger# git clone git://git.postgresql.org/git/pldebugger.gitInitialized 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/# pwd/tmp/pldebugger/pldebugger# ls -ltotal 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.pldebuggerPostgreSQL pl/pgsql Debugger API================================This module is a set of shared libraries which implement an API for debuggingpl/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 checkoutthe 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 anddebug the function immediately, or select Debugging->Set Global Breakpoint toset a breakpoint on the function. This will cause the debugger to wait foranother session (such as a backend servicing a web app) to execute the functionand 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 byfailing to add (or incorrectly adding) the debugger plugin library to theshared_preload_libraries configuration directive in postgresql.conf (followingwhich, the server *must* be restarted). This will prevent global breakpointsworking 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 libpqconnection. When a debugging session is active, the proxy is connectedto the target via a socket. The protocol between the proxy and the targetbackend 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 backendLicence-------The pl/pgsql debugger API is released under the Artistic Licence.    http://www.opensource.org/licenses/artistic-license.phpCopyright (c) 2004-2017 EnterpriseDB Corporation. All Rights Reserved.Contact-------For support, please email the pgAdmin support mailing list. Seehttp://www.pgadmin.org/support/ for more details.

安装

# cd /usr/pgsql-9.6/share# mkdir contrib# cd contrib/# mv /tmp/pldebugger/* ./# cd pldebugger/# source /var/lib/pgsql/.bash_profile# USE_PGXS=1 make clean  # USE_PGXS=1 make  # USE_PGXS=1 make install  # 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    shared_preload_libraries = 'plugin_debugger'  

重启数据库

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

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

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

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