Skip to content

Debug Postgresql

查询客户端连接上的服务进程id

sql
postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
          34255
(1 row)

postgres=# select 1;

开启debug日志

查找配置文件路径

sql
# psql -d postgres -c "SHOW config_file;"
                   config_file
-------------------------------------------------
 /Users/user/local/postgres-data/postgresql.conf
(1 row)

postgresql.conf

bash

# log_min_messages = warning
log_min_messages = debug5

#debug_print_parse = off
debug_print_parse = on

#debug_print_rewritten = off
debug_print_rewritten = on

#debug_print_plan = off
debug_print_plan = on

#debug_pretty_print = on
debug_pretty_print = on
cpp
pg_rewrite_query()
{

    // debug_print_parse = on
    if (Debug_print_parse)
        elog_node_display(LOG, "parse tree", query,
                          Debug_pretty_print);

    // debug_print_rewritten = on
    if (Debug_print_rewritten)
        elog_node_display(LOG, "rewritten parse tree", querytree_list,
                          Debug_pretty_print);

}

pg_plan_query(){
    // debug_print_plan = on
    if (Debug_print_plan)
        elog_node_display(LOG, "plan", plan, Debug_pretty_print);
}

GDB

bash
(gdb) call elog_node_display(17, "what ever", Node * var, 0 or 1)
  • 17代表INFO,详见elog.h,比如希望打印在日志里,将17替换为16,
  • 0或1取决于debug_pretty_print为on还是off

pgNodeGraph

将打印信息图形化工具:

https://github.com/shenyuflying/pgNodeGraph

https://github.com/japinli/pg_node2graph

依赖 graphviz

bash
brew install graphviz

例如:

sql
select 1;

说明:

  • 第一个图基于pgNodeGraph
  • 第二个图基于pg_node2graph

parsetree_list

1、parse tree

2、rewritten

3、plan

ref

PostgreSQL数据库查询——exec_simple_query函数分析

https://www.cnblogs.com/feishujun/p/PostgreSQLSourceAnalysis_query001.html

调试代码

根据错误直接定位代码

shell
postgres=# select * from tb1;
ERROR:  relation "tb1" does not exist
LINE 1: select * from tb1;
                      ^
postgres=# \errverbose
ERROR:  42P01: relation "tb1" does not exist
LINE 1: select * from tb1;
                      ^
LOCATION:  parserOpenTable, parse_relation.c:1452

查看日志

打印日志

cpp
ereport(LOG, (errmsg("PgStartTime: %ld", PgStartTime)));

通过进程号过滤日志

shell
tail -f postgresql.log | grep '\[35004\]'

makefile

shell
.PHONY: start
start:
    pg_ctl -D /Users/wang/local/postgres-data -l logfile start

.PHONY: stop
stop:
    pg_ctl -D /Users/wang/local/postgres-data -l logfile stop

.PHONY: restart
restart:
    make stop && make start

.PHONY: build
build:
    ./configure --prefix=/Users/wang/local/postgres --enable-debug --enable-cassert --enable-depend CFLAGS=-O0 && make && make install

.PHONY: rebuild
make:
    make && make install

.PHONY: reload
reload:
    make stop; make rebuild && make start