29、深入探索PostgreSQL命令行操作与数据库维护-优快云博客

本文链接：https://blog.youkuaiyun.com/transformer2023/article/details/150989721

深入探索PostgreSQL命令行操作与数据库维护

1. 运行存储在文件中的SQL

我们可以通过命令行执行 psql 并在 -f 参数后提供文件名来运行存储在文本文件中的SQL。这种语法能让我们从命令行快速运行查询或更新表，还能结合系统调度器定期执行任务。

例如，若将 SELECT * FROM grades; 查询保存到名为 display-grades.sql 的文件中，可在命令行使用以下 psql 语法运行保存的查询：

psql -d analysis -U postgres -f display-grades.sql

按下回车键后， psql 会启动，运行文件中存储的查询，显示结果，然后退出。对于重复性任务，这种工作流程能节省大量时间，因为无需启动 pgAdmin 或重写查询。此外，还可以在文件中堆叠多个查询，让它们依次运行。

2. 其他加快任务的命令行实用工具

PostgreSQL 包含一些额外的命令行实用工具，在连接到远程服务器或想通过命令行节省时间（而非启动 pgAdmin 或其他图形用户界面）时非常有用。这些命令可在不启动 psql 的情况下在命令行界面输入。

下面介绍两个特别有用的工具：
- 使用 createdb 创建数据库 ：之前我们使用 CREATE DATABASE 语句向 PostgreSQL 服务器添加数据库。现在可以使用 createdb 命令行实用工具执行类似操作。例如，要在服务器上创建名为 box_office 的新数据库，可在命令行运行：

createdb -U postgres -e box_office

-U 参数指定使用 postgres 账户连接到 PostgreSQL 服务器， -e 参数（“echo”）会将 SQL 语句打印到屏幕。运行此命令会创建数据库，并显示 CREATE DATABASE box_office; 响应。之后可通过以下命令使用 psql 连接到新数据库：

psql -d box_office -U postgres

createdb 命令接受连接到远程服务器的参数（与 psql 类似），并可设置新数据库的选项。完整的参数列表可查看相关文档。
- 使用 shp2pgsql 加载形状文件 ：之前我们使用 PostGIS 套件中的 Shapefile Import/Export Manager 将形状文件导入数据库。现在可以使用 PostGIS 命令行工具 shp2pgsql 通过单个文本命令完成相同操作。

从命令行将形状文件导入新表的语法如下：

shp2pgsql -I -s SRID -W encoding shapefile_name table_name | psql -d database -U user

各参数含义如下：
| 参数 | 含义 |
| ---- | ---- |
| -I | 在新表的几何列上添加 GiST 索引 |
| -s | 指定几何数据的 SRID |
| -W | 指定编码（如人口普查形状文件使用 Latin1 ） |
| shapefile_name | 以 .shp 扩展名结尾的文件名称（包括完整路径） |
| table_name | 形状文件导入的表名 |

例如，要将 tl_2010_us_county10.shp 形状文件加载到 gis_analysis 数据库的 us_counties_2010_shp 表中，可运行以下命令（注意在命令行应输入为一行）：

shp2pgsql -I -s 4269 -W Latin1 tl_2010_us_county10.shp us_counties_2010_shp | psql -d gis_analysis -U postgres

服务器会在创建索引并返回命令行之前响应一系列 SQL INSERT 语句。首次构建完整参数集可能需要一些时间，但之后只需替换文件和表名即可。

3. 回收未使用空间

为防止数据库文件失控增长，可使用 PostgreSQL 的 VACUUM 命令。在更新表时，PostgreSQL 会创建包含更新值的新行，但不会删除旧行（即“死”行）。同样，删除行时，该行仍作为死行存在于表中。

运行 VACUUM 会将死行占用的空间标记为可供数据库再次使用，但不会将空间返回给系统磁盘。要将未使用空间返回磁盘，需使用 VACUUM FULL 选项，它会创建不包含释放的死行空间的新表版本。

默认情况下，PostgreSQL 运行 autovacuum 后台进程，该进程会监控数据库并在需要时运行 VACUUM 。

4. 跟踪表大小

我们创建一个小的测试表 vacuum_test 并监控其大小增长：

CREATE TABLE vacuum_test (
    integer_column integer
);

在填充测试数据之前，可通过两种方式检查表占用的磁盘空间：
- 通过 pgAdmin 界面检查表属性，点击表并选择“Statistics” 选项卡。
- 使用 PostgreSQL 管理函数运行查询，例如：

SELECT pg_size_pretty(
           pg_total_relation_size('vacuum_test')
       );

由于此时表为空，运行上述代码应返回 0 bytes 。也可使用命令行，启动 psql 后输入 \dt+ vacuum_test 查看表大小。

添加数据后，使用 generate_series() 函数向表中插入 500,000 行：

INSERT INTO vacuum_test
SELECT * FROM generate_series(1,500000);

再次检查表大小，会发现使用了 17MB 磁盘空间。

更新数据后，使用以下代码更新 vacuum_test 表中的每一行：

UPDATE vacuum_test
SET integer_column = integer_column + 1;

再次测试表大小，会发现表大小从 17MB 增加到 35MB。这是因为每次更新值时，PostgreSQL 都会创建新行，旧行（死行）仍保留在表中。

5. 监控 `autovacuum` 进程

PostgreSQL 的 autovacuum 进程会监控数据库，当检测到表中有大量死行时会自动启动 VACUUM 。默认情况下 autovacuum 是启用的，但可通过修改 postgresql.conf 文件进行配置。

要检查 autovacuum 的活动，可查询 pg_stat_all_tables 视图：

SELECT relname,
       last_vacuum,
       last_autovacuum,
       vacuum_count,
       autovacuum_count
FROM pg_stat_all_tables
WHERE relname = 'vacuum_test';

默认情况下， autovacuum 每分钟检查一次表。如果自上次更新 vacuum_test 已过去一分钟，运行上述查询应能看到 vacuum 活动的详细信息。

6. 手动运行 `VACUUM`

根据使用的服务器，可关闭 autovacuum 。若 autovacuum 关闭或想手动运行 VACUUM ，可使用以下代码：

VACUUM vacuum_test;

运行此命令后，服务器会返回 VACUUM 消息。再次获取统计信息时， last_vacuum 列会反映手动 vacuum 的日期和时间， vacuum_count 列的值会增加 1。

也可在不指定表名的情况下对整个数据库运行 VACUUM ，还可添加 VERBOSE 关键字以获取更详细的信息。

7. 使用 `VACUUM FULL` 减小表大小

VACUUM FULL 与默认的 VACUUM 不同，它会将空间返回给磁盘。但 VACUUM FULL 完成所需时间更长，且在重写表时需要对表进行独占访问，这意味着在操作期间任何人都不能更新数据。

要查看 VACUUM FULL 的工作原理，可运行以下命令：

VACUUM FULL vacuum_test;

命令执行后，再次测试表大小，应会回到插入数据时的 17MB。

8. 更改服务器设置

可通过编辑 postgresql.conf 文件中的值来更改 PostgreSQL 服务器的数十个设置。该文件包含文件位置、安全性、信息日志记录等设置。

首先，使用以下命令查找 postgresql.conf 文件的位置：

SHOW config_file;

找到文件后，使用纯文本编辑器（而非富文本编辑器）打开。建议保存一份 postgresql.conf 文件的副本，以防更改导致系统故障时可恢复到原始版本。

例如，要关闭 autovacuum ，可将以下行：

#autovacuum = on               # Enable autovacuum subprocess? 'on'

修改为：

autovacuum = off               # Enable autovacuum subprocess? 'on'

以下是 postgresql.conf 文件中的一些其他设置示例：

datestyle = 'iso, mdy'
timezone = 'US/Eastern'
default_text_search_config = 'pg_catalog.english'

datestyle 设置可指定 PostgreSQL 在查询结果中显示日期的方式。
timezone 参数设置服务器时区。
default_text_search_config 值设置全文搜索操作使用的语言。

修改 postgresql.conf 文件后，需保存文件并使用 pg_ctl 命令重新加载设置：
- 在 Windows 上，使用：

pg_ctl reload -D "C:\path\to\data\directory\"

通过掌握这些命令行操作和数据库维护技巧，我们能更高效地管理 PostgreSQL 数据库，节省时间并克服在不支持图形工具的环境中工作时遇到的障碍。

深入探索PostgreSQL命令行操作与数据库维护

9. 操作总结与流程梳理

为了更清晰地理解上述的各种操作，下面通过流程图和表格对关键操作进行总结。

创建数据库及连接流程

graph LR
    A[开始] --> B[使用createdb创建数据库]
    B --> C{数据库是否创建成功}
    C -- 是 --> D[使用psql连接数据库]
    C -- 否 --> E[检查参数并重新创建]
    D --> F[结束]
    E --> B

操作步骤	命令示例	说明
创建数据库	`createdb -U postgres -e box_office`	`-U` 指定用户， `-e` 打印 SQL 语句
连接数据库	`psql -d box_office -U postgres`	`-d` 指定数据库名， `-U` 指定用户

加载形状文件流程

graph LR
    A[开始] --> B[准备形状文件]
    B --> C[使用shp2pgsql生成 SQL]
    C --> D[通过管道将 SQL 传递给 psql]
    D --> E{导入是否成功}
    E -- 是 --> F[结束]
    E -- 否 --> G[检查参数并重新导入]
    G --> C

操作步骤	命令示例	说明
生成 SQL	`shp2pgsql -I -s 4269 -W Latin1 tl_2010_us_county10.shp us_counties_2010_shp`	`-I` 添加索引， `-s` 指定 SRID， `-W` 指定编码
导入数据	`... \| psql -d gis_analysis -U postgres`	通过管道将生成的 SQL 传递给 psql 执行

10. 表大小管理总结

在表大小管理方面，我们通过一系列操作对 vacuum_test 表进行了监控和处理，下面是相关操作的总结表格。

操作阶段	操作内容	表大小变化	原因说明
创建表	`CREATE TABLE vacuum_test (integer_column integer);`	0 bytes	表为空
插入数据	`INSERT INTO vacuum_test SELECT * FROM generate_series(1,500000);`	17MB	插入 500,000 行数据
更新数据	`UPDATE vacuum_test SET integer_column = integer_column + 1;`	35MB	每次更新创建新行，旧行作为死行保留
自动真空	`autovacuum` 自动触发 `VACUUM`	35MB	仅标记死行空间可复用，未释放磁盘空间
手动真空	`VACUUM vacuum_test;`	35MB	仅标记死行空间可复用，未释放磁盘空间
全量真空	`VACUUM FULL vacuum_test;`	17MB	创建不包含死行的新表版本，释放磁盘空间

11. 服务器设置更改总结

更改服务器设置主要涉及 postgresql.conf 文件的编辑和重新加载，下面是相关操作的总结。

查找配置文件位置

SHOW config_file;

编辑配置文件
使用纯文本编辑器打开 postgresql.conf 文件，修改相关设置。例如：
- 关闭 autovacuum ：将 #autovacuum = on 改为 autovacuum = off
- 修改日期显示格式： datestyle = 'iso, mdy'
- 修改时区： timezone = 'US/Eastern'
- 修改全文搜索语言： default_text_search_config = 'pg_catalog.english'

重新加载设置
- 在 Windows 上：

pg_ctl reload -D "C:\path\to\data\directory\"

12. 实践建议

为了更好地掌握和运用上述知识，建议选择之前的示例，仅使用命令行进行操作。例如，可以参考之前章节中使用 psql 和 shp2pgsql 加载形状文件的示例进行实践。

在实际操作过程中，要注意以下几点：
- 对于 createdb 和 shp2pgsql 等命令，要仔细检查参数，确保其正确性。
- 在编辑 postgresql.conf 文件时，建议先备份原文件，避免因修改错误导致系统故障。
- 对于频繁更新的数据库，要定期监控表大小和 autovacuum 进程，及时进行 VACUUM 或 VACUUM FULL 操作，以避免磁盘空间耗尽。

通过不断实践和总结，我们能够更加熟练地使用 PostgreSQL 的命令行工具，高效地管理数据库，为数据处理和分析工作提供有力支持。