PostgreSQL 列印詳細錯誤調用棧 - pg_backtrace

标簽

PostgreSQL , pg_backtrace , 錯誤調用棧 , core

https://github.com/digoal/blog/blob/master/201902/20190216_02.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL 使用backtrace，讓PG的user process支援self-debugging。

NAME
       backtrace, backtrace_symbols, backtrace_symbols_fd - support for application self-debugging

SYNOPSIS
       #include <execinfo.h>

       int backtrace(void **buffer, int size);

       char **backtrace_symbols(void *const *buffer, int size);

       void backtrace_symbols_fd(void *const *buffer, int size, int fd);
           

支援：

1、列印錯誤SQL的調用棧内容。

2、了解LONG QUERY正在執行什麼，慢在什麼地方。 通過發送信号 (SIGINT)。

3、向日志中輸出CORE的資訊，列印調用棧資訊，通過發送信号 (SIGSEGV or SIGBUS)。

This PostgreSQL extension can be used to get more information

about PostgreSQL backend execution when no debugger is available.

It allows to dump stack trace when error is reported or exception is caught.

So there three used cases of using this extension:

1. Find out the source of error. pg_backtrace extension provides

"pg_backtrace.level" GUC which selects error level for which backtrace information

is attached. Default value is ERROR. So once this extension is initialized,

all errors will include backtrace information which is dumped both in log

file and delivered to the client:

postgres=# select count(*)/0.0 from pg_class;  
ERROR:  division by zero  
CONTEXT:  	postgres: knizhnik postgres [local] SELECT(numeric_div+0xbc) [0x7c5ebc]  
	postgres: knizhnik postgres [local] SELECT() [0x5fe4e2]  
	postgres: knizhnik postgres [local] SELECT() [0x610730]  
	postgres: knizhnik postgres [local] SELECT() [0x6115ca]  
	postgres: knizhnik postgres [local] SELECT(standard_ExecutorRun+0x15a) [0x60193a]  
	postgres: knizhnik postgres [local] SELECT() [0x74168c]  
	postgres: knizhnik postgres [local] SELECT(PortalRun+0x29e) [0x742a7e]  
	postgres: knizhnik postgres [local] SELECT() [0x73e922]  
	postgres: knizhnik postgres [local] SELECT(PostgresMain+0x1189) [0x73fde9]  
	postgres: knizhnik postgres [local] SELECT() [0x47d5e0]  
	postgres: knizhnik postgres [local] SELECT(PostmasterMain+0xd28) [0x6d0448]  
	postgres: knizhnik postgres [local] SELECT(main+0x421) [0x47e511]  
	/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0) [0x7f6361a13830]  
	postgres: knizhnik postgres [local] SELECT(_start+0x29) [0x47e589]  
           

2. Determine current state of backend (assume that there is some long running query

and you do not know where it spends most of time). It is possible to send SIGINT signal

to backend and it print current stack in logfile:

2018-11-12 18:24:12.222 MSK [24457] LOG:  Caught signal 2  
2018-11-12 18:24:12.222 MSK [24457] CONTEXT:  	/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7f63624e3390]  
		/lib/x86_64-linux-gnu/libc.so.6(epoll_wait+0x13) [0x7f6361afa9f3]  
		postgres: knizhnik postgres [local] SELECT(WaitEventSetWait+0xbe) [0x71e4de]  
		postgres: knizhnik postgres [local] SELECT(WaitLatchOrSocket+0x8b) [0x71e93b]  
		postgres: knizhnik postgres [local] SELECT(pg_sleep+0x98) [0x7babd8]  
		postgres: knizhnik postgres [local] SELECT() [0x5fe4e2]  
		postgres: knizhnik postgres [local] SELECT() [0x6266a8]  
		postgres: knizhnik postgres [local] SELECT(standard_ExecutorRun+0x15a) [0x60193a]  
		postgres: knizhnik postgres [local] SELECT() [0x74168c]  
		postgres: knizhnik postgres [local] SELECT(PortalRun+0x29e) [0x742a7e]  
		postgres: knizhnik postgres [local] SELECT() [0x73e922]  
		postgres: knizhnik postgres [local] SELECT(PostgresMain+0x1189) [0x73fde9]  
		postgres: knizhnik postgres [local] SELECT() [0x47d5e0]  
		postgres: knizhnik postgres [local] SELECT(PostmasterMain+0xd28) [0x6d0448]  
		postgres: knizhnik postgres [local] SELECT(main+0x421) [0x47e511]  
		/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0) [0x7f6361a13830]  
		postgres: knizhnik postgres [local] SELECT(_start+0x29) [0x47e589]  
           

3. Get stack trace for SIGSEGV or SIGBUS signals

(if dumping cores is disabled for some reasons):

2018-11-12 18:25:52.636 MSK [24518] LOG:  Caught signal 11  
2018-11-12 18:25:52.636 MSK [24518] CONTEXT:  	/home/knizhnik/postgresql/dist/lib/pg_backtrace.so(+0xe37) [0x7f6358838e37]  
		/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7f63624e3390]  
		/home/knizhnik/postgresql/dist/lib/pg_backtrace.so(pg_backtrace_sigsegv+0) [0x7f6358838fb0]  
		postgres: knizhnik postgres [local] SELECT() [0x5fe474]  
		postgres: knizhnik postgres [local] SELECT() [0x6266a8]  
		postgres: knizhnik postgres [local] SELECT(standard_ExecutorRun+0x15a) [0x60193a]  
		postgres: knizhnik postgres [local] SELECT() [0x74168c]  
		postgres: knizhnik postgres [local] SELECT(PortalRun+0x29e) [0x742a7e]  
		postgres: knizhnik postgres [local] SELECT() [0x73e922]  
		postgres: knizhnik postgres [local] SELECT(PostgresMain+0x1189) [0x73fde9]  
		postgres: knizhnik postgres [local] SELECT() [0x47d5e0]  
		postgres: knizhnik postgres [local] SELECT(PostmasterMain+0xd28) [0x6d0448]  
		postgres: knizhnik postgres [local] SELECT(main+0x421) [0x47e511]  
		/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0) [0x7f6361a13830]  
		postgres: knizhnik postgres [local] SELECT(_start+0x29) [0x47e589]  
           

As far as Postgres extension is loaded and initialized on first access to

its functions, it is necessary to call pg_backtrace_init() function to

be able to use this extension. This function function actually does nothing

and _PG_init() registers signal handlers for SIGSEGV, SIGBUS and SIGINT and

executor run hook which setups exception context.

This extension is using backtrace function which is available at most Unixes.

As it was mentioned in backtrace documentation:

The symbol names may be unavailable without the use of special linker options.  
For systems using the GNU linker, it is necessary to use the -rdynamic  
linker option.  Note that names of "static" functions are not exposed,  
and won't be available in the backtrace.  
           

Postgres is built without -rdynamic option. This is why not all function addresses

in the stack trace above are resolved. It is possible to use GDB (at development

host with correspondent postgres binaries) or Linux addr2line utility to

get resolve function addresses:

$ addr2line -e ~/postgresql/dist/bin/postgres -a 0x5fe4e2  
    0x00000000005fe4e2  
    execExprInterp.c:?  
           

用法

Usage:

create extension pg_backtrace;  
select pg_backtrace_init();  
           

https://github.com/digoal/blog/blob/master/201902/20190216_02.md#%E5%8F%82%E8%80%83 參考

pg_backtrace

https://github.com/postgrespro/pg_backtrace

https://github.com/digoal/blog/blob/master/201902/20190216_02.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機