PostgreSQL数据库的维护与优化：减少停机时间

### PostgreSQL数据库的维护与优化：减少停机时间 在管理PostgreSQL数据库时，减少停机时间并确保数据库的高可用性是至关重要的。本文将介绍如何通过查看数据库设置、识别重要表以及处理缓存中毒等方法来实现这一目标。 #### 1. 查看PostgreSQL设置 PostgreSQL提供了许多有用的视图，其中`pg_settings`视图可以提供当前服务器设置、默认值和使用上下文的丰富数据。以下是一些查看设置的方法： - **获取需要重启服务器的设置及其当前值**： ```sql SELECT name, setting FROM pg_settings WHERE context = 'postmaster'; ``` - **获取未更改且需要重启的设置**： ```sql SELECT name, setting, boot_val FROM pg_settings WHERE context = 'postmaster' AND boot_val = setting; ``` - **获取所有设置及其管理方式的翻译**： ```sql SELECT name, CASE context WHEN 'postmaster' THEN 'REQUIRES RESTART' WHEN 'sighup' THEN 'Reload Config' WHEN 'backend' THEN 'Reload Config' WHEN 'superuser' THEN 'Reload Config / Superuser' WHEN 'user' THEN 'Reload Config / User SET' END AS when_changed FROM pg_settings WHERE context != 'internal' ORDER BY when_changed; ``` 这些查询的作用如下： | 查询 | 作用 | | --- | --- | | 第一个查询 | 识别只能通过重启PostgreSQL修改的设置的名称和值，如`wal_level`、`shared_buffers`等，还包括与SSL和WAL存档相关的参数。当SSL证书受损时，需要重启数据库。 | | 第二个查询 | 显示未更改但需要重启服务器的设置，可能更有针对性，可帮助寻找可修改的参数。 | | 第三个查询 | 使用`CASE`语句简化视图内容，排除内部设置（只能在编译PostgreSQL时设置），获取设置名称和修改方式。 | 此外，还可以使用以下查询获取已更改的设置列表： ```sql SELECT name, setting FROM pg_settings WHERE boot_val IS DISTINCT FROM setting; ``` 这里使用`IS DISTINCT FROM`子句是因为`!=`或`<>`在等式一侧为`NULL`时会返回`NULL`，而该子句将`NULL`视为不同的值，允许直接比较。 `pg_settings`视图还提供了`short_desc`和`extra_desc`列，可作为快捷方式，帮助记住更改设置的原因。 #### 2. 识别重要表 为了维护高可用性的数据库，需要了解数据库中表和索引的活动情况。PostgreSQL会收集插入、更新、删除和选择等操作的统计信息，还能跟踪索引或表的扫描频率以及受影响的行数，同时可以获取对象占用的磁盘空间。通过这些统计信息，可以将表分配到高性能表空间，对活跃表进行额外维护，或删除低效索引。 以下是一些查询示例： - **获取当前数据库中前20大的表**： ```sql SELECT oid::REGCLASS::TEXT AS table_name, pg_size_pretty( pg_total_relation_size(oid) ) AS total_size FROM pg_class WHERE relkind = 'r' AND relpages > 0 ORDER BY pg_total_relation_size(oid) DESC LIMIT 20; ``` - **获取当前数据库中前20大的索引及其父表**： ```sql SELECT indexrelid::REGCLASS::TEXT AS index_name, indrelid::REGCLASS::TEXT AS table_name, ```