Postgresql运维信息(一)
1. 运维系统视图
PostgreSQL 提供了一系列系统视图和函数，可以用于获取数据库的运维统计信息。这些信息对于监控和优化数据库性能非常有用。以下是一些常用的 PostgreSQL 运维统计信息：
1.1. pg_stat_activity
这个系统视图包含了当前数据库连接的活动信息，例如正在执行的查询、连接的用户、查询开始时间等。
SELECT * FROM pg_stat_activity;
pg_stat_activity 是一个系统视图，用于显示当前正在连接到 PostgreSQL 数据库的会话信息和活动查询的详细信息。这个视图提供了有关每个当前数据库连接的一些重要信息，方便进行监控和性能调优。
| 字段名 | 描述 | 详细描述 |
|---|---|---|
| datid | 数据库 OID | 当前数据库的对象标识符（OID）。 |
| datname | 数据库名 | 连接所在的数据库名称。 |
| pid | 进程 ID | 当前会话后端进程的标识符。 |
| usesysid | 用户 OID | 连接的用户的对象标识符（OID）。 |
| usename | 用户名 | 连接的用户名。 |
| application_name | 应用程序名 | 建立连接的应用程序的名称。 |
| client_addr | 客户端地址 | 连接到 PostgreSQL 的客户端的IP地址。 |
| client_hostname | 客户端主机名 | 连接到 PostgreSQL 的客户端的主机名。如果无法解析主机名，则显示IP地址。 |
| client_port | 客户端端口 | 客户端连接到 PostgreSQL 的端口号。 |
| backend_start | 后端启动时间 | 后端进程启动的时间戳。 |
| xact_start | 事务启动时间 | 最近一次事务开始执行的时间戳。 |
| query_start | 查询启动时间 | 最近一次查询开始执行的时间戳。 |
| state | 连接状态 | 连接的当前状态。可能的值包括 “活动”、“空闲”、“等待” 等。 |
| state_change | 状态变更时间 | 最近一次连接状态发生变化的时间戳。 |
| wait_event_type | 等待事件类型 | 如果连接处于等待状态，表示正在等待的事件的类型。可能的值包括 “IO”、“Lock” 等。 |
| wait_event | 等待事件名称 | 如果连接处于等待状态，表示正在等待的具体事件名称。 |
1.2. pg_stat_database
这个系统视图包含了有关每个数据库的统计信息，例如数据库的大小、连接数、提交和回滚次数等。
SELECT * FROM pg_stat_database;
pg_stat_database 是一个 PostgreSQL 的系统视图，提供了关于每个数据库的统计信息。以下是 pg_stat_database 视图中一些重要字段的含义：
| 字段名 | 描述 | 详细描述 |
|---|---|---|
| datid | 数据库 OID | 每个数据库的唯一标识符（OID）。 |
| datname | 数据库名称 | 连接所在的数据库名称。 |
| numbackends | 活动连接数 | 当前数据库的活动连接数。 |
| xact_commit | 提交事务数量 | 自数据库启动以来的提交事务数量。 |
| xact_rollback | 回滚事务数量 | 自数据库启动以来的回滚事务数量。 |
| blks_read | 从磁盘读取的块数 | 自数据库启动以来从磁盘读取的块数量（8KB 块）。 |
| blks_hit | 从缓存读取的块数 | 自数据库启动以来从缓存读取的块数量（8KB 块）。 |
| tup_returned | 检索的行数 | 自数据库启动以来检索的行数。 |
| tup_fetched | 获取的行数 | 自数据库启动以来获取的行数。 |
| tup_inserted | 插入的行数 | 自数据库启动以来插入的行数。 |
| tup_updated | 更新的行数 | 自数据库启动以来更新的行数。 |
| tup_deleted | 删除的行数 | 自数据库启动以来删除的行数。 |
| conflicts | 冲突数 | 自数据库启动以来发生的冲突数。 |
| temp_files | 创建的临时文件数 | 自数据库启动以来创建的临时文件数。 |
| temp_bytes | 使用的临时空间字节数 | 自数据库启动以来使用的临时空间的字节数。 |
| deadlocks | 死锁数 | 自数据库启动以来发生的死锁数。 |
1.3. pg_stat_user_tables 和 pg_stat_user_indexes
这两个系统视图分别提供了用户表和索引的统计信息，包括行数、更新次数、索引扫描次数等。
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_indexes;
1.3.1. pg_stat_user_tables
pg_stat_user_tables 视图是 PostgreSQL 提供的一个系统视图，用于提供关于用户表的统计信息。它包含了与用户表相关的各种统计数据，帮助用户监视和分析数据库中表的活动情况和性能指标。
以下是针对 pg_stat_user_tables 视图字段的详细描述
| 字段名 | 描述 | 详细描述 |
|---|---|---|
| relid | 表的 OID | 表示统计信息相关的表的对象标识符。 |
| schemaname | 模式名称 | 表所在模式的名称。 |
| relname | 表名称 | 表的名称。 |
| seq_scan | 顺序扫描的次数 | 自数据库重启以来执行顺序扫描的次数。 |
| seq_tup_read | 通过顺序扫描读取的行数 | 自数据库重启以来通过顺序扫描读取的行数。 |
| idx_scan | 索引扫描的次数 | 自数据库重启以来执行索引扫描的次数。 |
| idx_tup_fetch | 通过索引扫描获取的行数 | 自数据库重启以来通过索引扫描获取的行数。 |
| n_tup_ins | 插入的行数 | 自数据库重启以来插入的行数。 |
| n_tup_upd | 更新的行数 | 自数据库重启以来更新的行数。 |
| n_tup_del | 删除的行数 | 自数据库重启以来删除的行数。 |
| n_tup_hot_upd | 热更新的行数 | 自数据库重启以来进行热更新的行数。 |
| n_live_tup | 当前存活的行数 | 当前存在的未标记为删除的行数。 |
| n_dead_tup | 当前死亡的行数 | 当前被标记为删除但尚未被清理的行数。 |
| last_vacuum | 最后一次 VACUUM 执行的时间 | 表最后一次执行 VACUUM 操作的时间。 |
| last_autovacuum | 最后一次自动 VACUUM 执行的时间 | 表最后一次自动执行 VACUUM 操作的时间。 |
| last_analyze | 最后一次 ANALYZE 执行的时间 | 表最后一次执行 ANALYZE 操作的时间。 |
| last_autoanalyze | 最后一次自动 ANALYZE 执行的时间 | 表最后一次自动执行 ANALYZE 操作的时间。 |
| vacuum_count | VACUUM 操作的计数 | 自数据库重启以来执行 VACUUM 操作的次数。 |
| autovacuum_count | 自动 VACUUM 操作的计数 | 自数据库重启以来执行自动 VACUUM 操作的次数。 |
| analyze_count | ANALYZE 操作的计数 | 自数据库重启以来执行 ANALYZE 操作的次数。 |
| autoanalyze_count | 自动 ANALYZE 操作的计数 | 自数据库重启以来执行自动 ANALYZE 操作的次数。 |
1.3.2. pg_stat_user_indexes
pg_stat_user_indexes 视图提供了有关用户创建的索引的统计信息，用于监视和分析这些索引的活动情况和性能指标。
| 字段名 | 描述 | 详细描述 |
|---|---|---|
| relid | 索引所属表的 OID | 表示索引所属的表的对象标识符（OID）。 |
| indexrelname | 索引名称 | 索引的名称。 |
| schemaname | 模式名称 | 索引所在的模式的名称。 |
| idx_scan | 索引扫描的次数 | 自数据库启动以来执行索引扫描的次数。 |
| idx_tup_read | 通过索引扫描获取的行数 | 自数据库启动以来通过索引扫描读取的行数。 |
| idx_tup_fetch | 通过索引扫描获取的行数 | 自数据库启动以来通过索引扫描获取的行数（通常与 idx_tup_read 类似，但在某些情况下可能会有差异）。 |
1.4. pg_stat_bgwriter
SELECT * FROM pg_stat_bgwriter;
pg_stat_bgwriter 视图提供了有关后台写入进程（background writer）的统计信息。这个视图包含了后台写入进程的性能指标，帮助用户监视数据库的后台写入活动情况。
| 字段名 | 描述 |
|---|---|
| checkpoints_timed | 周期性检查点的触发次数（通过时间间隔触发） |
| checkpoints_req | 请求性检查点的触发次数（通过请求触发） |
| checkpoint_write_time | 检查点写入到磁盘的时间（以毫秒为单位） |
| checkpoint_sync_time | 检查点同步到磁盘的时间（以毫秒为单位） |
| buffers_checkpoint | 由检查点写入的缓冲区数量 |
| buffers_clean | 后台写入进程释放的脏数据缓冲区数量 |
| maxwritten_clean | 单个检查点中释放的最大脏数据缓冲区数量 |
| buffers_backend | 后台写入进程主动写入的缓冲区数量 |
| buffers_alloc | 分配的共享缓冲区数量 |
| stats_reset | 统计信息重置的时间戳 |
pg_stat_bgwriter 视图中的这些字段提供了关于后台写入进程活动的各种统计信息，例如检查点的触发次数、写入时间、缓冲区写入和释放情况等。这些信息对于监视数据库的后台写入活动并评估数据库性能非常有用。
1.5. pg_stat_replication
pg_stat_replication 是一个系统视图，用于在 PostgreSQL 中查看关于流复制（streaming replication）的统计信息。它提供了有关当前正在进行的流复制连接的统计数据，允许用户监视和管理 PostgreSQL 流复制的状态。
SELECT * FROM pg_stat_replication;
| 字段名 | 描述 |
|---|---|
| pid | 流复制进程的后端进程 ID |
| usesysid | 备库的系统标识符 |
| usename | 连接流复制的用户名 |
| application_name | 连接的应用程序名称 |
| client_addr | 连接的客户端地址 |
| client_hostname | 连接的客户端主机名 |
| client_port | 连接的客户端端口号 |
| backend_start | 流复制进程启动时间 |
| state | 流复制连接状态 |
| sent_location | 主服务器发送到流复制客户端的位置 |
| write_location | 流复制客户端已写入到磁盘的位置 |
| flush_location | 流复制客户端已确认已刷新到磁盘的位置 |
| replay_location | 流复制客户端正在回放的位置 |
| sync_priority | 流复制连接的同步优先级 |
| sync_state | 流复制连接的同步状态 |
1.6. pg_stat_progress_vacuum
pg_stat_progress_vacuum 是一个系统视图，用于在 PostgreSQL 中查看正在执行的 VACUUM 进程的进度信息。它提供了有关正在进行的 VACUUM 操作的统计数据，允许用户监视和了解 VACUUM 操作的进度和状态。
SELECT * FROM pg_stat_progress_vacuum;
| 字段名 | 描述 |
|---|---|
| pid | VACUUM 进程的后端进程 ID |
| datid | 正在执行 VACUUM 的数据库 OID |
| relid | 正在执行 VACUUM 的表的 OID |
| phase | VACUUM 操作的阶段 |
| heap_blks_total | 表的总块数（以块为单位） |
| heap_blks_scanned | 已扫描的块数 |
| heap_blks_vacuumed | 已清理的块数 |
| heap_blks_index_cleaned | 已清理的索引块数 |
在 PostgreSQL 中，VACUUM 是一种重要的数据库维护操作，用于管理数据库中的空间和性能。VACUUM 主要用于以下几个方面：
-
释放过期的行版本空间：PostgreSQL 使用多版本并发控制（MVCC）来管理事务。当一个行被更新或删除时，旧的行版本不会立即从磁盘中删除，而是被标记为可被清理。VACUUM 会释放这些被标记为可清理的行版本所占用的空间。
-
更新统计信息：VACUUM 运行时会更新数据库中的统计信息，这些信息是优化查询计划的基础。更新这些统计信息有助于 PostgreSQL 优化查询，提高查询性能。
-
避免表空间膨胀：长时间运行的数据库操作会产生大量不再使用的空间（如删除、更新等操作会造成存储空间碎片化）。VACUUM 可以帮助回收这些空间，减少表的膨胀，使得表的存储更加紧凑。
-
减少事务ID的消耗：每个活动的事务都会消耗事务ID。通过清理旧的行版本和过期事务的元数据，VACUUM 可以减少事务ID的消耗。
VACUUM 是 PostgreSQL 数据库维护的一个关键操作，对于保持数据库性能和空间管理至关重要。通常，定期运行 VACUUM 是一个良好的实践，特别是在高写入负载的数据库中。
1.6.1. 如何清理 VACUUM
在 PostgreSQL 中执行 VACUUM 可以通过以下几种方式进行：
1. 手动执行 VACUUM
-
VACUUM 整个数据库：运行以下命令可以对整个数据库执行 VACUUM 操作：
VACUUM; -
VACUUM 单个表：指定表名执行 VACUUM 操作：
VACUUM tablename;
2. 自动化执行 VACUUM
- 自动化 VACUUM：可以设置自动执行 VACUUM 的策略。例如，使用 autovacuum 功能，通过配置参数
autovacuum和autovacuum_vacuum_scale_factor等来启用自动 VACUUM。
3. 在命令行执行 VACUUM
- 通过命令行工具执行：使用命令行工具
psql或pg_ctl来执行 VACUUM 操作。
注意事项
- 执行 VACUUM 通常会锁定被清理的对象（表），因此在高负载的生产环境中要谨慎执行，避免对正常业务产生影响。
- 在执行 VACUUM 的同时，建议先备份数据库以防意外情况发生。
- 避免频繁地手动执行 VACUUM，尤其是对于活跃更新频繁的数据库，可以考虑使用自动化的 VACUUM 策略。
总的来说，VACUUM 是一个维护性的操作，可以通过命令行工具、SQL 命令或设置自动执行来管理数据库中的空间和性能。
1.7. pg_stat_progress_analyze
pg_stat_progress_analyze 是 PostgreSQL 中的一个系统视图，用于查看正在进行的 ANALYZE 操作的进度信息。它提供了关于当前 ANALYZE 操作的统计数据，允许用户监视和了解 ANALYZE 操作的执行情况。
SELECT * FROM pg_stat_progress_analyze;
| 字段名 | 描述 |
|---|---|
| pid | ANALYZE 进程的后端进程 ID |
| datid | 正在执行 ANALYZE 的数据库 OID |
| relid | 正在执行 ANALYZE 的表的 OID |
| phase | ANALYZE 操作的阶段 |
| heap_blks_total | 表的总块数（以块为单位） |
| heap_blks_scanned | 已扫描的块数 |
| heap_blks_vacuumed | 已清理的块数 |
| num_index_scans | 已扫描的索引数 |
| max_tid | 最大的可见行版本号 |