ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(162) - Extension(pg_catcheck)

PostgreSQL DBA(162) - Extension(pg_catcheck)

原创 PostgreSQL 作者:husthxd 时间:2020-02-11 17:05:34 0 删除 编辑

本文简单介绍了PostgreSQL的插件:pg_catcheck。该插件检查数据库的catalogs是否出现损坏。

安装
编译安装

[pg12@localhost software]$ cd pg_catcheck/
[pg12@localhost pg_catcheck]$ ls
check_attribute.c  check_depend.c  compat.c  definitions.c  log.c     pg_catcheck.c  pg_catcheck.proj  README.md         typedefs.list
check_class.c      check_oids.c    compat.h  LICENSE        Makefile  pg_catcheck.h  pgrhash.c         settings.projinc
[pg12@localhost pg_catcheck]$ 
[pg12@localhost pg_catcheck]$ gmake
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_catcheck.o pg_catcheck.c -MMD -MP -MF .deps/pg_catcheck.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o check_attribute.o check_attribute.c -MMD -MP -MF .deps/check_attribute.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o check_class.o check_class.c -MMD -MP -MF .deps/check_class.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o check_depend.o check_depend.c -MMD -MP -MF .deps/check_depend.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o check_oids.o check_oids.c -MMD -MP -MF .deps/check_oids.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o compat.o compat.c -MMD -MP -MF .deps/compat.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o definitions.o definitions.c -MMD -MP -MF .deps/definitions.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o log.o log.c -MMD -MP -MF .deps/log.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -I/appdb/pg12/pg12.1/include -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pgrhash.o pgrhash.c -MMD -MP -MF .deps/pgrhash.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS pg_catcheck.o check_attribute.o check_class.o check_depend.o check_oids.o compat.o definitions.o log.o pgrhash.o  -L/appdb/pg12/pg12.1/lib   -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12.1/lib',--enable-new-dtags  -L/appdb/pg12/pg12.1/lib -lpgcommon -lpgport -L/appdb/pg12/pg12.1/lib -lpq  -lpgcommon -lpgport -lpthread -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lrt -lcrypt -ldl -lm  -o pg_catcheck
[pg12@localhost pg_catcheck]$ sudo gmake install
[sudo] password for pg12: 
/usr/bin/mkdir -p '/usr/bin'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c  pg_catcheck '/usr/bin'

体验

[pg12@localhost pg_catcheck]$ pg_catcheck --help
pg_catcheck is catalog table validation tool for PostgreSQL.
Usage:
  pg_catcheck [OPTION]... [DBNAME]
Options:
  -c, --column             check only the named columns
  -t, --table              check only columns in the named tables
  -T, --exclude-table      do NOT check the named tables
  -C, --exclude-column     do NOT check the named columns
  --target-version=VERSION assume specified target version
  --enterprisedb           assume EnterpriseDB database
  --postgresql             assume PostgreSQL database
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -q, --quiet              do not display progress messages
  -U, --username=USERNAME  connect as specified database user
  -v, --verbose            enable verbose internal logging
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit
Report bugs to <support@enterprisedb.com>.
[pg12@localhost pg_catcheck]$ pg_catcheck --postgresql -p 5120 -U pg12 testdb  -v
verbose: detected server version 120001
verbose: assuming PostgreSQL server
verbose: preloading table pg_authid because it is required in order to check pg_namespace
verbose: loading table pg_namespace
verbose: checking table pg_namespace (6 rows)
verbose: loading table pg_collation
verbose: checking table pg_collation (923 rows)
verbose: loading table pg_tablespace
verbose: checking table pg_tablespace (2 rows)
verbose: loading table pg_language
verbose: checking table pg_language (4 rows)
verbose: loading table pg_database
verbose: checking table pg_database (7 rows)
verbose: loading table pg_largeobject_metadata
verbose: checking table pg_largeobject_metadata (0 rows)
verbose: loading table pg_publication
verbose: checking table pg_publication (0 rows)
verbose: loading table pg_subscription
verbose: checking table pg_subscription (0 rows)
verbose: loading table pg_default_acl
verbose: checking table pg_default_acl (0 rows)
verbose: loading table pg_largeobject
verbose: checking table pg_largeobject (0 rows)
verbose: loading table pg_db_role_setting
verbose: checking table pg_db_role_setting (0 rows)
verbose: loading table pg_auth_members
verbose: checking table pg_auth_members (3 rows)
verbose: preloading table pg_class because it is required in order to check pg_type
verbose: loading table pg_type
verbose: checking table pg_type (463 rows)
verbose: loading table pg_proc
verbose: checking table pg_proc (2964 rows)
verbose: loading table pg_operator
verbose: checking table pg_operator (770 rows)
verbose: loading table pg_ts_parser
verbose: checking table pg_ts_parser (1 rows)
verbose: loading table pg_ts_config
verbose: checking table pg_ts_config (22 rows)
verbose: loading table pg_ts_template
verbose: checking table pg_ts_template (5 rows)
verbose: loading table pg_ts_dict
verbose: checking table pg_ts_dict (22 rows)
verbose: loading table pg_foreign_data_wrapper
verbose: checking table pg_foreign_data_wrapper (0 rows)
verbose: loading table pg_foreign_server
verbose: checking table pg_foreign_server (0 rows)
verbose: loading table pg_cast
verbose: checking table pg_cast (216 rows)
verbose: loading table pg_conversion
verbose: checking table pg_conversion (132 rows)
verbose: loading table pg_extension
verbose: checking table pg_extension (1 rows)
verbose: loading table pg_enum
verbose: checking table pg_enum (0 rows)
verbose: loading table pg_user_mapping
verbose: checking table pg_user_mapping (0 rows)
verbose: loading table pg_event_trigger
verbose: checking table pg_event_trigger (0 rows)
verbose: loading table pg_rewrite
verbose: checking table pg_rewrite (126 rows)
verbose: loading table pg_attrdef
verbose: checking table pg_attrdef (0 rows)
verbose: loading table pg_policy
verbose: checking table pg_policy (0 rows)
verbose: loading table pg_publication_rel
verbose: checking table pg_publication_rel (0 rows)
verbose: loading table pg_statistic_ext
verbose: checking table pg_statistic_ext (0 rows)
verbose: loading table pg_transform
verbose: checking table pg_transform (0 rows)
verbose: loading table pg_attribute
verbose: checking table pg_attribute (3193 rows)
verbose: loading table pg_foreign_table
verbose: checking table pg_foreign_table (0 rows)
verbose: loading table pg_inherits
verbose: checking table pg_inherits (0 rows)
verbose: loading table pg_aggregate
verbose: checking table pg_aggregate (136 rows)
verbose: loading table pg_ts_config_map
verbose: checking table pg_ts_config_map (418 rows)
verbose: loading table pg_statistic
verbose: checking table pg_statistic (474 rows)
verbose: loading table pg_init_privs
verbose: checking table pg_init_privs (171 rows)
verbose: loading table pg_sequence
verbose: checking table pg_sequence (0 rows)
verbose: loading table pg_subscription_rel
verbose: checking table pg_subscription_rel (0 rows)
verbose: preloading table pg_am because it is required in order to check pg_opfamily
verbose: loading table pg_opfamily
verbose: checking table pg_opfamily (107 rows)
verbose: checking table pg_class (435 rows)
verbose: loading table pg_opclass
verbose: checking table pg_opclass (128 rows)
verbose: loading table pg_amop
verbose: checking table pg_amop (715 rows)
verbose: loading table pg_amproc
verbose: checking table pg_amproc (447 rows)
verbose: loading table pg_index
verbose: checking table pg_index (168 rows)
verbose: loading table pg_constraint
verbose: checking table pg_constraint (6 rows)
verbose: loading table pg_trigger
verbose: checking table pg_trigger (4 rows)
verbose: loading table pg_range
verbose: checking table pg_range (6 rows)
verbose: loading table pg_depend
verbose: checking table pg_depend (7722 rows)
verbose: loading table pg_shdepend
verbose: checking table pg_shdepend (10 rows)
verbose: loading table pg_description
verbose: checking table pg_description (4003 rows)
verbose: loading table pg_shdescription
verbose: checking table pg_shdescription (3 rows)
verbose: loading table pg_seclabel
verbose: checking table pg_seclabel (0 rows)
verbose: loading table pg_shseclabel
verbose: checking table pg_shseclabel (0 rows)
verbose: loading table pg_partitioned_table
verbose: checking table pg_partitioned_table (0 rows)
progress: done (0 inconsistencies, 0 warnings, 0 errors)
[pg12@localhost pg_catcheck]$ pg_catcheck --postgresql -p 5120 -U pg12 testdb
progress: done (0 inconsistencies, 0 warnings, 0 errors)

执行显示0 inconsistencies, 0 warnings, 0 errors,说明数据库的catalogs没有问题。

参考资料
pgcatcheck

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2675148/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1488
  • 访问量
    3925439