PostgreSQL9.4 新增 pg_stat_archiver 系统视图用来记录 WAL 归档信息。
关于 pg_stat_archiver
备注：字段含义依次为：已归档的 wal文件总数，最近成功归档的wal文件名, 最近成功归档时间，归档失败 wal 数量，最近归档失败的 wal 文件名, 最近归档失败时间, 最近统计信息重置时间。
查询 pg_stat_archiver
1
2
3
4
5
6
7
8
9
|
francs=# select * from pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
archived_count | 132466
last_archived_wal | 00000001000000000000003F
last_archived_time | 2014-05-23 10:20:29.668785-07
failed_count | 0
last_failed_wal |
last_failed_time |
stats_reset | 2014-05-21 01:57:03.518831-07
|
切换 WAL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
francs=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/400199C0
(1 row)
francs=# select * from pg_stat_archiver ;
-[ RECORD 1 ]------+------------------------------
archived_count | 132467
last_archived_wal | 000000010000000000000040
last_archived_time | 2014-05-23 13:40:37.291166-07
failed_count | 0
last_failed_wal |
last_failed_time |
stats_reset | 2014-05-21 01:57:03.518831-07
|
备注：切换xlog 后，last_archived_wal 和 last_archived_time 信息已改变。
查看 pg_stat_archiver 数据来源
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
francs=# /d+ pg_stat_archiver
View
Column | Type | Modifiers | Storage | Description
--------------------+--------------------------+-----------+----------+-------------
archived_count | bigint | | plain |
last_archived_wal | text | | extended |
last_archived_time | timestamp with time zone | | plain |
failed_count | bigint | | plain |
last_failed_wal | text | | extended |
last_failed_time | timestamp with time zone | | plain |
stats_reset | timestamp with time zone | | plain |
View definition:
SELECT s.archived_count,
s.last_archived_wal,
s.last_archived_time,
s.failed_count,
s.last_failed_wal,
s.last_failed_time,
s.stats_reset
FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
francs=# select pg_stat_get_archiver();
pg_stat_get_archiver
-------------------------------------------------------------------------------------------------------
(132467,000000010000000000000040,,0,,,)
|
备注：pg_stat_archiver 数据来源于一个名为 pg_stat_get_archiver() 函数。
参考
原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/238063.html
