PostgreSQL Developer Options (debug, trace, system table mod and so on…) 详解
背景
PostgreSQL 提供了一些开发参数, 一般在生产库中不推荐使用。主要用于调试环境。
同时有些参数是否支持, 是需要在编译PostgreSQL软件指定的。
正文
所有的开发参数如下 :
Developer Options
The following parameters are intended for work on the PostgreSQL source code, and in some cases to assist with recovery of severely damaged databases.
There should be no reason to use them on a production database. As such, they have been excluded from the sample postgresql.conf file.
Note that many of these parameters require special source compilation flags to work at all.
1. allow_system_table_mods (boolean)
Allows modification of the structure of system tables. This is used by initdb. This parameter can only be set at server start.
在数据库启动前调整, 一般被用于initdb. 当然你如果想修改系统表的表结构, 也可以通过打开这个参数后来调整.
涉及代码 :
src/include/catalog/heap.h
src/include/catalog/index.h
src/backend/catalog/index.c
src/backend/catalog/heap.c
src/backend/utils/misc/guc.c
src/backend/postmaster/postmaster.c
src/backend/tcop/postgres.c
创建索引, 约束, 表时如果定义了allow_system_table_mods, 这个表允许放到系统schema(namespace)中, 并且这个表的更改需要打开allow_system_table_mods参数.
/*
* index_create
*
* heapRelation: table to build index on (suitably locked by caller)
* indexRelationName: what it say
* indexRelationId: normally, pass InvalidOid to let this routine
* generate an OID for the index. During bootstrap this may be
* nonzero to specify a preselected OID.
* relFileNode: normally, pass InvalidOid to get new storage. May be
* nonzero to attach an existing valid build.
* indexInfo: same info executor uses to insert into the index
* indexColNames: column names to use for index (List of char *)
* accessMethodObjectId: OID of index AM to use
* tableSpaceId: OID of tablespace to use
* collationObjectId: array of collation OIDs, one per index column
* classObjectId: array of index opclass OIDs, one per index column
* coloptions: array of per-index-column indoption settings
* reloptions: AM-specific options
* isprimary: index is a PRIMARY KEY
* isconstraint: index is owned by PRIMARY KEY, UNIQUE, or EXCLUSION constraint
* deferrable: constraint is DEFERRABLE
* initdeferred: constraint is INITIALLY DEFERRED
* allow_system_table_mods: allow table to be a system catalog
* skip_build: true to skip the index_build() step for the moment; caller
* must do it later (typically via reindex_index())
* concurrent: if true, do not lock the table against writers. The index
* will be marked "invalid" and the caller must take additional steps
* to fix it up.
* is_internal: if true, post creation hook for new index
*
* Returns the OID of the created index.
*/
Oid
index_create(Relation heapRelation,
const char *indexRelationName,
Oid indexRelationId,
Oid relFileNode,
IndexInfo *indexInfo,
List *indexColNames,
Oid accessMethodObjectId,
Oid tableSpaceId,
Oid *collationObjectId,
Oid *classObjectId,
int16 *coloptions,
Datum reloptions,
bool isprimary,
bool isconstraint,
bool deferrable,
bool initdeferred,
bool allow_system_table_mods,
bool skip_build,
bool concurrent,
bool is_internal)
/*
* index_constraint_create
*
* Set up a constraint associated with an index
*
* heapRelation: table owning the index (must be suitably locked by caller)
* indexRelationId: OID of the index
* indexInfo: same info executor uses to insert into the index
* constraintName: what it say (generally, should match name of index)
* constraintType: one of CONSTRAINT_PRIMARY, CONSTRAINT_UNIQUE, or
* CONSTRAINT_EXCLUSION
* deferrable: constraint is DEFERRABLE
* initdeferred: constraint is INITIALLY DEFERRED
* mark_as_primary: if true, set flags to mark index as primary key
* update_pgindex: if true, update pg_index row (else caller's done that)
* remove_old_dependencies: if true, remove existing dependencies of index
* on table's columns
* allow_system_table_mods: allow table to be a system catalog
*/
void
index_constraint_create(Relation heapRelation,
Oid indexRelationId,
IndexInfo *indexInfo,
const char *constraintName,
char constraintType,
bool deferrable,
bool initdeferred,
bool mark_as_primary,
bool update_pgindex,
bool remove_old_dependencies,
bool allow_system_table_mods)
/* --------------------------------
* heap_create_with_catalog
*
* creates a new cataloged relation. see comments above.
*
* Arguments:
* relname: name to give to new rel
* relnamespace: OID of namespace it goes in
* reltablespace: OID of tablespace it goes in
* relid: OID to assign to new rel, or InvalidOid to select a new OID
* reltypeid: OID to assign to rel's rowtype, or InvalidOid to select one
* reloftypeid: if a typed table, OID of underlying type; else InvalidOid
* ownerid: OID of new rel's owner
* tupdesc: tuple descriptor (source of column definitions)
* cooked_constraints: list of precooked check constraints and defaults
* relkind: relkind for new rel
* relpersistence: rel's persistence status (permanent, temp, or unlogged)
* shared_relation: TRUE if it's to be a shared relation
* mapped_relation: TRUE if the relation will use the relfilenode map
* oidislocal: TRUE if oid column (if any) should be marked attislocal
* oidinhcount: attinhcount to assign to oid column (if any)
* oncommit: ON COMMIT marking (only relevant if it's a temp table)
* reloptions: reloptions in Datum form, or (Datum) 0 if none
* use_user_acl: TRUE if should look for user-defined default permissions;
* if FALSE, relacl is always set NULL
* allow_system_table_mods: TRUE to allow creation in system namespaces
*
* Returns the OID of the new relation
* --------------------------------
*/
Oid
heap_create_with_catalog(const char *relname,
Oid relnamespace,
Oid reltablespace,
Oid relid,
Oid reltypeid,
Oid reloftypeid,
Oid ownerid,
TupleDesc tupdesc,
List *cooked_constraints,
char relkind,
char relpersistence,
bool shared_relation,
bool mapped_relation,
bool oidislocal,
int oidinhcount,
OnCommitAction oncommit,
Datum reloptions,
bool use_user_acl,
bool allow_system_table_mods,
bool is_internal)
2. debug_assertions (boolean)
Turns on various assertion checks. This is a debugging aid.
If you are experiencing strange problems or crashes you might want to turn this on, as it might expose programming mistakes.
To use this parameter, the macro USE_ASSERT_CHECKING must be defined when PostgreSQL is built (accomplished by the configure option --enable-cassert).
Note that debug_assertions defaults to on if PostgreSQL has been built with assertions enabled.
如果编译数据库软件时含–enable-cassert选项, 则这个参数有意义.
3. ignore_system_indexes (boolean)
Ignore system indexes when reading system tables (but still update the indexes when modifying the tables).
This is useful when recovering from damaged system indexes.
This parameter cannot be changed after session start.
当系统表的索引异常时使用, 跳过索引扫描.
4. post_auth_delay (integer)
If nonzero, a delay of this many seconds occurs when a new server process is started, after it conducts the authentication procedure.
This is intended to give developers an opportunity to attach to the server process with a debugger.
This parameter cannot be changed after session start.
5. pre_auth_delay (integer)
If nonzero, a delay of this many seconds occurs just after a new server process is forked, before it conducts the authentication procedure.
This is intended to give developers an opportunity to attach to the server process with a debugger to trace down misbehavior in authentication.
This parameter can only be set in the postgresql.conf file or on the server command line.
6. trace_notify (boolean)
Generates a great amount of debugging output for the LISTEN and NOTIFY commands.
client_min_messages or log_min_messages must be DEBUG1 or lower to send this output to the client or server logs, respectively.
7. trace_recovery_messages (enum)
Enables logging of recovery-related debugging output that otherwise would not be logged.
This parameter allows the user to override the normal setting of log_min_messages, but only for specific messages.
This is intended for use in debugging Hot Standby. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, and LOG.
The default, LOG, does not affect logging decisions at all.
The other values cause recovery-related debug messages of that priority or higher to be logged as though they had LOG priority;
for common settings of log_min_messages this results in unconditionally sending them to the server log.
This parameter can only be set in the postgresql.conf file or on the server command line.
8. trace_sort (boolean)
If on, emit information about resource usage during sort operations.
This parameter is only available if the TRACE_SORT macro was defined when PostgreSQL was compiled.
(However, TRACE_SORT is currently defined by default.)
编译时需要定义TRACE_SORT宏, 默认已经定义了.
src/include/pg_config_manual.h
/*
* Enable tracing of resource consumption during sort operations;
* see also the trace_sort GUC var. For 8.1 this is enabled by default.
*/
#define TRACE_SORT 1
9. trace_locks (boolean)
If on, emit information about lock usage. Information dumped includes the type of lock operation, the type of lock and the unique identifier of the object being locked or unlocked.
Also included are bit masks for the lock types already granted on this object as well as for the lock types awaited on this object.
For each lock type a count of the number of granted locks and waiting locks is also dumped as well as the totals. An example of the log file output is shown here:
LOG: LockAcquire: new: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
wait(0) type(AccessShareLock)
LOG: GrantLock: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(2) req(1,0,0,0,0,0,0)=1 grant(1,0,0,0,0,0,0)=1
wait(0) type(AccessShareLock)
LOG: UnGrantLock: updated: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
wait(0) type(AccessShareLock)
LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1)
grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0
wait(0) type(INVALID)
Details of the structure being dumped may be found in src/include/storage/lock.h.
This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
编译时需要定义LOCK_DEBUG宏, 默认未定义, 修改如下文件, 解除/* #define LOCK_DEBUG */的注释.
src/include/pg_config_manual.h
/*
* Enable debugging print statements for lock-related operations.
*/
#define LOCK_DEBUG
或者定义在src/Makefile.custom中 :
pgdev@db-172-16-3-150-> cat Makefile.custom
CFLAGS+=-DLOCK_DEBUG
CFLAGS+=-DBTREE_BUILD_STATS
CFLAGS+=-DWAL_DEBUG
10. trace_lwlocks (boolean)
If on, emit information about lightweight lock usage.
Lightweight locks are intended primarily to provide mutual exclusion of access to shared-memory data structures.
This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
同上编译时需要定义LOCK_DEBUG宏, 默认未定义.
11. trace_userlocks (boolean)
If on, emit information about user lock usage. Output is the same as for trace_locks, only for advisory locks.
This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
同上编译时需要定义LOCK_DEBUG宏, 默认未定义.
12. trace_lock_oidmin (integer)
If set, do not trace locks for tables below this OID. (use to avoid output on system tables)
This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
13. trace_lock_table (integer)
Unconditionally trace locks on this table (OID).
This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
同上编译时需要定义LOCK_DEBUG宏, 默认未定义.
14. debug_deadlocks (boolean)
If set, dumps information about all current locks when a deadlock timeout occurs.
This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled.
同上编译时需要定义LOCK_DEBUG宏, 默认未定义.
15. log_btree_build_stats (boolean)
If set, logs system resource usage statistics (memory and CPU) on various B-tree operations.
This parameter is only available if the BTREE_BUILD_STATS macro was defined when PostgreSQL was compiled.
编译时需要定义BTREE_BUILD_STATS宏, 默认未定义.
这个可以定义在src/Makefile.custom中 :
pgdev@db-172-16-3-150-> cat Makefile.custom
CFLAGS+=-DLOCK_DEBUG
CFLAGS+=-DBTREE_BUILD_STATS
CFLAGS+=-DWAL_DEBUG
使用如下 :
digoal=# set log_btree_build_stats =on;
SET
16. wal_debug (boolean)
If on, emit WAL-related debugging output. This parameter is only available if the WAL_DEBUG macro was defined when PostgreSQL was compiled.
编译时需要定义WAL_DEBUG宏, 默认未定义, 修改如下文件, 解除/* #define WAL_DEBUG */的注释.
src/include/pg_config_manual.h
/*
* Enable debugging print statements for WAL-related operations; see
* also the wal_debug GUC var.
*/
#define WAL_DEBUG
或者定义在src/Makefile.custom中 :
pgdev@db-172-16-3-150-> cat Makefile.custom
CFLAGS+=-DLOCK_DEBUG
CFLAGS+=-DBTREE_BUILD_STATS
CFLAGS+=-DWAL_DEBUG
17. zero_damaged_pages (boolean)
Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction.
Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing.
This behavior will destroy data, namely all the rows on the damaged page.
However, it does allow you to get past the error and retrieve rows from any undamaged pages that might be present in the table.
It is useful for recovering data if corruption has occurred due to a hardware or software error.
You should generally not set this on until you have given up hope of recovering data from the damaged pages of a table.
Zeroed-out pages are not forced to disk so it is recommended to recreate the table or the index before turning this parameter off again.
The default setting is off, and it can only be changed by a superuser.
在读取磁盘中的数据块信息时, 如果遇到头信息错误的块, 默认情况下将报错.
打开这个参数后, 这些块将以0输出, 跳过从磁盘读取.
因为PostgreSQL的row信息不会跨数据块存储, 所以使用zero_damaged_pages后, 即使有些数据块不能正常使用, 也能取到剩余的数据。
索引的错误直接重建索引即可.
编译举例
pgdev@db-172-16-3-150-> cd postgresql-9.3devel/
pgdev@db-172-16-3-150-> cd src/
pgdev@db-172-16-3-150-> vi Makefile.custom
CFLAGS+=-DLOCK_DEBUG
CFLAGS+=-DBTREE_BUILD_STATS
CFLAGS+=-DWAL_DEBUG
su - root
pgdev@db-172-16-3-150-> cd postgresql-9.3devel/
./configure --prefix=/home/pgdev/pgsql9.3 --with-pgport=9300 --with-perl --with-python --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --enable-cassert --with-wal-blocksize=16 --enable-debug && gmake world
gmake install-world
注意
1. 跟踪和debug的参数, 如果要在当前会话看到效果, 一般建议修改以下 :
VERBOSITY verbose 用于输出代码位置.
digoal=# set client_min_messages=DEBUG1; -- (DEBUG1直到DEBUG5)
SET
digoal=# \set VERBOSITY verbose
参考
1. http://www.postgresql.org/docs/9.2/static/runtime-config-developer.html
2. http://wiki.postgresql.org/wiki/Developer_FAQ
3. src/Makefile.global
##########################################################################
#
# Customization
#
# This includes your local customizations if Makefile.custom exists
# in the source directory. This file doesn't exist in the original
# distribution so that it doesn't get overwritten when you upgrade.
#
# NOTE: Makefile.custom is from the pre-Autoconf days of PostgreSQL.
# You are liable to shoot yourself in the foot if you use it without
# knowing exactly what you're doing. The preferred (and more
# reliable) method is to communicate what you want to do to the
# configure script, and leave the makefiles alone.
-include $(top_srcdir)/src/Makefile.custom