PostgreSQL系列02

PostgreSQL体系结构

PostgreSQL系统表介绍系统表详解系统视图详解管理函数配置函数服务端信号发送函数数据库对象管理函数:PostgreSQL进程结构PostgreSQL物理结构数据库用户密码有效期设置每日英语(Daily English Tips)(3)PostgreSQL系统表介绍

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

系统表详解

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语句:

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=# 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

文件访问函数:

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

PostgreSQL进程结构

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

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

syslogger -- 记录系统日志

pgstat -- 收集统计信息

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

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

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

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

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

PostgreSQL物理结构

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

说明:

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

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

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

pg_user: 用户信息.

pg_shadow: 数据库用户信息.

pg_user或pg_shadow中：

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

修改用户密码过期时间:

修改用户密码永不过期:

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

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

参考地址:

https://www.postgresql.org/docs/11/index.html