postgresql部分健康检查的相关sql

初步学习postgresql，搜集了一些大佬的健康检查的sql，大部分没看懂，分享给大家，仅供学习。

1 启停数据库

1.1 停止数据库

pg_ctl stop -D $PGDATA -l $PGDATA / pglog .log

1.2 启动数据库

pg_ctl start -D -D $PGDATA -l $PGDATA / pglog .log

1.3 检查数据库状态

pg_ctl status -D -D $PGDATA -l $PGDATA / pglog .log

2 数据库登录操作

2.1登录数据库

psql -D $PGDATA -p 5432 -U postgres sncdb

查看控制文件

pg_controldata -D $PGDATA

3. 统计剩余年龄：

```sql
SELECT datname, age(datfrozenxid) AS frozen_xid_age, 
       ROUND(100 * (age(datfrozenxid) / 2000000000::float)) consumed_txid_pct, 
       2 * 1024 ^ 3 - 1 - age(datfrozenxid) AS remaining_txid, 
       current_setting('autovacuum_freeze_max_age')::int - age(datfrozenxid) AS remaining_aggressive_vacuum 
FROM pg_database;
```
此查询检查数据库中每个数据库的事务ID冻结年龄，计算已使用的事务ID百分比，并估计剩余可用事务ID数量。同时比较当前设置的`autovacuum_freeze_max_age`参数与实际的冻结年龄，以判断在达到最大冻结年龄前，是否还有事务ID可用，从而评估可能的清理需求。

4. Checkpoint平均时间间隔：

```sql
SELECT total_checkpoints, seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints 
FROM (
    SELECT EXTRACT(EPOCH FROM (now() - pg_postmaster_start_time())) AS seconds_since_start, 
           (checkpoints_timed+checkpoints_req) AS total_checkpoints 
    FROM pg_stat_bgwriter
) AS sub;
```
通过这个查询可以了解数据库自启动以来的的平均checkpoint时间间隔。Checkpoint是PostgreSQL中非常重要的概念，它是数据页从日志状态到磁盘的同步点。此查询帮助人们理解系统运行的平稳程度和潜在的I/O负载。

5. 查看数据库缓冲区命中率：

```sql
SELECT round(sum(blks_hit) * 100 / sum(blks_hit + blks_read), 2)::numeric 
FROM pg_stat_database 
WHERE datname = current_database();
```
此查询用于查看当前数据库的缓冲区命中率。缓冲区命中率显示了数据库在读取数据时，有多少比例的数据可以直接从内存中获取而无需从磁盘读取，是优化数据库性能的关键指标之一。

6. 预热工具：

```sql
-- 预热工具的使用通常通过命令行或脚本操作，而非SQL查询
```
pgfincore和pg_prewarm是PostgreSQL的预热工具，用于将数据预加载到共享缓冲区，从而减少物理I/O需求。通过操作系统级别的文件映射，这些工具可以提升数据库的读写性能。

7. 查询大于5分钟的长事务：

```sql
SELECT query, state 
FROM pg_stat_activity 
WHERE state <> 'idle' AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL) 
AND now() - xact_start > interval '5 min' 
ORDER BY xact_start;
```
这个查询用于识别那些运行时间超过五分钟的活跃事务。长事务可能锁定资源，消耗系统资源，因此监控这些事务对于维护良好的数据库性能至关重要。

8. 2PC检查：

```sql
SELECT count(*) 
FROM pg_prepared_xacts;
```
二阶段提交（2PC）用于保证事务的原子性。此查询检查当前系统中准备好进行二阶段提交的事务数量，这有助于了解系统中分布式事务的活动程度。