pg_stat_archiver 是一个 统计视图，显示有关通过 archive_command 参数归档的 WAL 文件的状态信息，包括最近成功和失败的归档尝试时间。
pg_stat_archiver 在 PostgreSQL 9.4 中添加。
用法
pg_stat_archiver 收集了成功归档的 WAL 文件和归档失败的 WAL 文件的累计总数，并记录每个成功/失败的文件名和最后时间。
可以通过执行 pg_stat_reset_shared('archiver') 重置统计信息。
按 PostgreSQL 版本定义
pg_stat_archiver (PostgreSQL 17)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 16)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 15)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 14)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 13)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 12)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 11)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 10)
View "pg_catalog.pg_stat_archiver"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
archived_count | bigint | | |
last_archived_wal | text | | |
last_archived_time | timestamp with time zone | | |
failed_count | bigint | | |
last_failed_wal | text | | |
last_failed_time | timestamp with time zone | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 9.6)
View "pg_catalog.pg_stat_archiver"
Column | Type | Modifiers
--------------------+--------------------------+-----------
archived_count | bigint |
last_archived_wal | text |
last_archived_time | timestamp with time zone |
failed_count | bigint |
last_failed_wal | text |
last_failed_time | timestamp with time zone |
stats_reset | timestamp with time zone |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 9.5)
View "pg_catalog.pg_stat_archiver"
Column | Type | Modifiers
--------------------+--------------------------+-----------
archived_count | bigint |
last_archived_wal | text |
last_archived_time | timestamp with time zone |
failed_count | bigint |
last_failed_wal | text |
last_failed_time | timestamp with time zone |
stats_reset | timestamp with time zone |
文档: pg_stat_archiver
pg_stat_archiver (PostgreSQL 9.4)
View "pg_catalog.pg_stat_archiver"
Column | Type | Modifiers
--------------------+--------------------------+-----------
archived_count | bigint |
last_archived_wal | text |
last_archived_time | timestamp with time zone |
failed_count | bigint |
last_failed_wal | text |
last_failed_time | timestamp with time zone |
stats_reset | timestamp with time zone |
文档: pg_stat_archiver
更改历史记录
自 PostgreSQL 9.4 中引入以来，此视图没有被修改过。
- PostgreSQL 9.4
- 添加 (提交 9132b189)
示例
postgres=# SELECT * FROM pg_stat_archiver\gx Expanded display is on. -[ RECORD 1 ]------+------------------------------ archived_count | 0 last_archived_wal | last_archived_time | failed_count | 135 last_failed_wal | 000000010000000000000001 last_failed_time | 2019-06-10 22:34:09.730097+02 stats_reset | 2019-06-10 21:50:35.053854+02
参考文献
- PostgreSQL 文档： pg_stat_archiver 视图
有用链接
- PostgreSQL 9.4 和 pg_stat_archiver 如何改进 WAL 归档监控 - 2ndQuadrant 于 2015 年 1 月发布的博文
- 等待 9.4 – 添加 pg_stat_archiver 统计视图。 - “select * from depesz;” 于 2014 年 1 月发布的博文