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.gitgit下载
# 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.sovi 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 startcreate 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 了。