PostgreSQL 数据库维护

介绍

在这个实验中，我们将探索必要的 PostgreSQL 数据库维护任务。目标是了解如何监控数据库对象的大小，执行例行维护，以及排除潜在问题。

我们将首先使用 pg_size_pretty 和 pg_relation_size 函数查询表和索引的大小。然后，我们将学习如何运行 ANALYZE（分析）和 VACUUM（清理）来优化查询性能并回收存储空间。最后，我们将介绍如何列出活动的客户端连接并检查服务器日志中的错误，以诊断和解决数据库问题。

Skills Graph

连接到 PostgreSQL 并查询表大小

在这一步中，我们将连接到 PostgreSQL 数据库并查询表的大小。了解数据库对象的大小对于性能调优和容量规划至关重要。

首先，在你的 ~/project 目录中打开一个终端。

要以 postgres 用户身份连接到 PostgreSQL 服务器并访问 mydatabase 数据库，请执行以下命令：

sudo -u postgres psql mydatabase

你应该会看到类似 mydatabase=# 的提示符。这表明你已连接到 mydatabase 数据库。

如果你没有 mydatabase 数据库和 mytable 表，请使用以下 SQL 命令创建它们：

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    data TEXT
);

INSERT INTO mytable (name, data) SELECT 'Name ' || i, 'Data ' || i FROM generate_series(1, 1000) AS i;

现在，让我们确定 mytable 的大小。我们将使用 pg_size_pretty 和 pg_relation_size 函数。pg_relation_size 函数返回表的大小（以字节为单位），而 pg_size_pretty 将大小格式化为人类可读的格式（例如，KB、MB、GB）。

执行以下 SQL 查询以获取 mytable 的大小：

SELECT pg_size_pretty(pg_relation_size('mytable'));

你应该会看到类似于这样的输出：

 pg_size_pretty
------------------
 128 kB
(1 row)

这表明表 mytable 当前占用 128 KB 的磁盘空间。

检查索引大小

在这一步中，我们将创建一个索引，然后查询它的大小。索引用于加速查询性能，但它们也会消耗磁盘空间。我们还将检查表的总大小，包括索引。

首先，让我们在 mytable 的 name 列上创建一个索引：

CREATE INDEX idx_mytable_name ON mytable (name);

现在，让我们检查索引的大小。我们可以使用相同的函数，但我们需要指定索引名称。

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

输出可能如下所示：

 pg_size_pretty
------------------
 48 kB
(1 row)

这表明索引 idx_mytable_name 占用 48 KB 的磁盘空间。

最后，让我们获取表的总大小，包括索引。我们可以使用 pg_total_relation_size 函数来实现：

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

输出可能如下所示：

 pg_size_pretty
------------------
 176 kB
(1 row)

这表明表 mytable 的总大小（包括所有索引）为 176 KB。

运行 ANALYZE 和 VACUUM 进行维护

在这一步中，我们将学习 PostgreSQL 中的 ANALYZE 和 VACUUM 命令，它们对于维护数据库性能至关重要。

ANALYZE 会更新查询规划器使用的数据库统计信息，以便选择最有效的执行计划。这些统计信息描述了数据库中表的内容。如果没有准确的统计信息，查询规划器可能会做出错误的选择，从而导致查询性能下降。

VACUUM 回收被死亡元组（dead tuples）占用的存储空间。在 PostgreSQL 中，当一行被更新或删除时，该行的旧版本不会立即被删除。相反，它会被标记为死亡。VACUUM 回收这些死亡元组占用的空间，使其可供重用。它还会更新可见性映射（visibility map），这有助于查询规划器确定哪些行对事务可见。

让我们在 mytable 表上运行 ANALYZE：

ANALYZE mytable;

此命令分析 mytable 表并更新统计信息。你不会看到任何输出，但统计信息将在后台更新。

接下来，让我们在 mytable 表上运行 VACUUM：

VACUUM mytable;

此命令回收 mytable 表中被死亡元组占用的存储空间。同样，你不会看到任何输出，但清理过程将在后台运行。

为了更彻底的清理，你可以使用 VACUUM FULL。但是，VACUUM FULL 会独占锁定表，阻止在清理过程中对表执行其他操作。通常建议使用 VACUUM 而不是 VACUUM FULL，除非你有使用 VACUUM FULL 的特定理由。

-- VACUUM FULL mytable; -- 取消注释此行以运行 VACUUM FULL（谨慎使用）

最后，你可以将 ANALYZE 和 VACUUM 合并为一个命令：

VACUUM ANALYZE mytable;

此命令首先回收被死亡元组占用的存储空间，然后更新统计信息。这通常是维护数据库性能的最有效方法。

列出活动客户端连接并检查服务器日志

在这一步中，我们将探索如何列出活动客户端连接并检查服务器日志以查找错误。

要列出活动客户端连接，你可以查询 pg_stat_activity 视图。此视图提供有关每个服务器进程的信息，包括用户、数据库和正在执行的查询。

执行以下 SQL 查询：

SELECT datname, usename, client_addr, state, query FROM pg_stat_activity WHERE state != 'idle';

此查询将显示所有非空闲的活动连接。输出将包括数据库名称 (datname)、用户名 (usename)、客户端地址 (client_addr)、当前状态 (state) 和正在执行的查询 (query)。

接下来，让我们检查服务器日志以查找错误。PostgreSQL 日志通常位于 /var/log/postgresql 目录中。日志文件名通常遵循 postgresql-VERSION-main.log 模式，其中 VERSION 是 PostgreSQL 版本号。

要检查日志中的错误，你可以使用 grep 命令。例如，要在日志文件中搜索错误，请在新终端（在 psql 之外）中执行以下命令：

grep ERROR /var/log/postgresql/postgresql-14-main.log

注意： 将 postgresql-14-main.log 替换为你的 PostgreSQL 日志文件的实际名称。你可以列出 /var/log/postgresql 目录中的文件以找到正确的日志文件名。

此命令将显示日志文件中包含单词“ERROR”的任何行。检查服务器日志对于诊断和解决数据库问题至关重要。

总结

在这个实验中，你已经学习了如何执行基本的 PostgreSQL 数据库维护任务。你现在了解了如何监控数据库对象大小、运行 ANALYZE 和 VACUUM 以优化查询性能和回收存储空间、列出活动客户端连接，以及检查服务器日志以查找错误。这些技能对于确保你的 PostgreSQL 数据库的健康和性能至关重要。