PostgreSQL系统表介绍

系统表,系统表之间基本都是以oid关联。

系统表详解

SELECT relkind,relname from pg_class where relnamespace=(select oid from pg_namespace where nspname='pg_catalog') and relkind='r' order by 1,2;

• pg_aggregate：聚合函数信息,包括聚合函数的中间函数,中间函数的初始值,最终函数等。

• pg_am ：系统支持的索引访问方法(如btree,hash,gist,gin,spgist)

• pg_amop ： 存储每个索引访问方法操作符家族(pg_opfamily)中的详细操作符信息。

• pg_amproc：存储每个索引访问方法操作符家族(pg_opfamily)支持的函数信息。

• pg_attrdef：存储数据表列的默认值

• pg_attribute ： 存储数据表列的详细信息,包括隐含的列(ctid,cmin,cmax,xmin,xmax)

• pg_auth_members： 数据库用户的成员关系信息。

• pg_authid ： 存储数据库用户的详细信息(包括超级用户,登录,密码，密码加密，失效时间)。

• pg_cast ：数据库的显性类型转换路径信息,包括内建的和自定义的。

• pg_class：几乎包括了数据库的所有对象信息. index,view, 外表。

• pg_collation ：集信息,包括encoding,collate,ctype等。

• pg_constraint：存储列上定义的约束信息(排他约束,check约束,不包括非空约束)。

• pg_conversion : 字符集之间的转换的相关信息。

• pg_database : 集群中的数据库信息。

• pg_db_role_setting:基于角色和数据库组合的定制参数信息。

• pg_default_acl: 存储新建对象的初始权限信息。

• pg_depend : 数据库对象之间的依赖信息.

• pg_description : 数据库对象的描述信息。

• pg_enum : 枚举类型信息。

• pg_event_trigger:事件触发器信息。

• pg_extension :扩展插件信息。

• pg_foreign_data_wrapper : FDW信息。

• pg_foreign_server:外部服务器信息。

• pg_foreign_table: 外部表信息。

• pg_index: 索引信息。

• pg_inherits: 继承表的继承关系信息。

• pg_language: 过程语言信息

• pg_largeobject : 大对象的切片后的真实数据存储在这个表里.

• pg_largeobject_metadata 大对象的元信息,包括大对象的owner,访问权限。

• pg_namespace: 数据库中的schema信息。(pg中称为namespace)

• pg_opclass: 索引访问方法的操作符分类信息。

• pg_operator: 操作符信息

• pg_opfamily: 操作符家族信息。

• pg_pltemplate: 过程语言的模版信息。

• pg_proc: 数据库服务端函数信息。

• pg_range: 范围类型信息

• pg_rewrite: 表和视图的重写规则信息.

• pg_seclabel: 安全标签信息(selinux)

• pg_shdepend: 数据库中的对象之间或者集群中的共享对象之间的依赖关系。

• pg_shdescription: 共享对象的描述信息。

• pg_shseclabel: 共享对象的安全标签信息(selinux).

• pg_statistic: analyze生成的统计信息,用于查询计划器计算成本。

• pg_tablespace: 表空间相关的信息。

• pg_trigger: 表上的触发器信息。

• pg_ts_config: 全文检索的配置信息.

• pg_ts_config_map: 全文检索配置映射信息。

• pg_ts_dict： 全文检索字典信息。

• pg_ts_parser： 全文检索解析器信息。

• pg_ts_template: 全文检索模版信息

• pg_type: 数据库中的类型信息。

• pg_user_mapping: foreign server的用户配置信息。

系统视图详解

执行以下SQL语句:

select relkind,relname from pg_class where relnamespace=(select oid from pg_na
mespace where nspname='pg_catalog') and relkind='v' order by 1,2; 

• pg_available_extension_versions: 当前系统已经编译的扩展插件的版本信息。

• pg_available_extensions: 显示当前系统已经编译的扩展插件信息。

• pg_cursors: 显示当前可用的游标。

• pg_group: 用户组信息。

• pg_indexes: 索引信息

• pg_locks: 锁信息

• pg_matviews: 物化视图信息。

• pg_prepared_statements: 当前会话中使用prepare语法学的预处理SQL信息

• pg_prepared_xacts: 二阶事务信息

• pg_roles:数据库角色信息

• pg_rules: 数据库中使用create rule创建的规则信息。

• pg_seclabels: 安全标签信息

• pg_settings: 当前数据库集群的参数设置信息。

• pg_shadow: 数据库用户信息。

• pg_stat_activity: 会话活动信息。

• pg_stat_all_indexes: 查询用户权限范围内的所有索引的统计信息

• pg_stat_all_tables: 查询用户权限范围内的所有表的统计信息。

• pg_stat_bgwriter: bgwriter进程的统计信息。

• pg_stat_database: 数据库级别的统计信息

• pg_stat_database_conflicts: 数据库

• pg_stat_replication: 流复制相关的统计信息。

• pg_stat_sys_indexes: 系统表相关的索引统计信息.

• pg_stat_sys_tables: 系统表统计信息。

• pg_stat_user_functions: 用户函数统计信息。

• pg_stat_user_indexes: 用户表的索引相关的统计信息。

• pg_stat_user_tables: 用户表统计信息。

• pg_stat_xact_all_tables 当前事务的表级统计信息,显示用户可以访问的所有表。

• pg_stat_xact_sys_tables 当前事务的表级统计信息,仅显示系统表。

• pg_stat_xact_user_functions 当前事务的用户函数的统计信息。

• pg_stat_xact_user_tables: 当前事务的用户表的统计信息。

• pg_statio_all_indexes: io相关的统计信息。

• pg_stats: 数据库中的统计信息,以列为最小统计单位输出。

• pg_tables : 数据库中的表对象的信息

• pg_timezone_abbrevs: 时区缩写信息。

• pg_timezone_names: 时区信息,包含全名。

• pg_user: 用户信息.

• pg_user_mappings:外部表的用户映射权限信息

• pg_views: 视图信息。

管理函数

• 配置函数

  • 查看是否启用扫描：

postgres=#  show enable_seqscan;
postgres=# select * from current_setting('enable_seqscan');
postgres=# select * from set_config('enable_seqscan','off',true);

• 关闭扫描

postgres=# select * from set_config('enable_seqscan','off',false);

• 服务端信号发送函数

  • 重载配置文件:

$ pg_ctl reload
postgres=# select pg_reload_conf();
$ kill -s SIGHUP PID 

• 日志管理:postgres=# SELECT pg_rotate_logfile();

• 查看数据库大小: postgres=# select pg_database_size('databasename');

• **查看索引大小: postgres=# select pg_indexes_size(oid),relname from pg_class where relkind='i' limit 1;

• 数据库对象管理函数:

  • pg_column_size(any) int

  • pg_database_size(oid) bigint

  • pg_database_size(name) bigint

  • pg_indexes_size(regclass) bigint

  • pg_relation_size(relation regclass, fork text) bigint

  • pg_relation_size(relation regclass) bigint

  • pg_size_bytes(text) bigint

  • pg_size_pretty(bigint) text

  • pg_size_pretty(numeric) text

  • pg_table_size(regclass) bigint

  • pg_tablespace_size(oid) bigint

  • pg_tablespace_size(name) bigint

  • pg_total_relation_size(regclass) bigint

• 文件访问函数:postgres=# select * from pg_read_file('postgresql.conf');

  应用锁函数:对于长时间持锁的应用有效,长时间的数据库重量锁会带来垃圾回收的问题。

PostgreSQL进程结构

• postmaster -- 所有数据库进程的主进程(负责监听和fork子进程)

• startup -- 主要用于数据库恢复的进程。

• syslogger -- 记录系统日志

• pgstat -- 收集统计信息

• pgarch -- 如果开启了归档,那么postmaster会fork一个归档进程。

• checkpointer -- 负责检查点的进程。

• bgwriter -- 负责把shared buffer中的脏数据写入磁盘的进程。

• autovacuum lanucher -- 负责回收垃圾数据的进程,如果开启了autovacuum的话,那么postmaster会fork这个进程。

• autovacuum worker --负责回收垃圾数据的worker进程,是lanucher进程fork出来的。

PostgreSQL物理结构

• 对象对应的物理文件在哪里?

postgres=# select * from  pg_relation_filepath('pg_class'::regclass);
 pg_relation_filepath 
----------------------
 base/12328/1259
(1 row)

说明:

1. base/12328/1259 : pg_class表对应的物理文件的存储路径: data/base/12328/1259

2. base 是默认表空间，12328:是数据库的oid，1259是filenode

数据库用户密码有效期设置

• pg_user: 用户信息.

• pg_shadow: 数据库用户信息.

• pg_user或pg_shadow中：

  • 列valuntil值为infinity或空值表示用户密码永不过期；默认为空

• 修改用户密码过期时间:

alter user user_name with valid until '2018-01-01 08:00:00';

• 修改用户密码永不过期:

alter user user_name with valid until 'infinity';

每日英语(Daily English Tips)(3)

有关月份的英文单词如下: