centos postgresql使用技巧 - 问答

在CentOS上使用PostgreSQL时，掌握一些使用技巧可以帮助你更高效地管理数据库。以下是一些常用的技巧：

命令行操作技巧

使用 -c 参数执行单行命令：你可以使用 -c 或 --command=command 参数来发送单行命令并退出 psql。例如：
```
psql -c "SELECT 1 AS foo" foo
```
这将返回 1 作为结果。
连续使用 -c 参数执行多个命令：你可以连续使用 -c 参数来发送多个命令或查询。例如：
```
psql -c 'SELECT * FROM test' -c 'SELECT count(*) FROM test'
```
这将首先执行 SELECT * FROM test，然后执行 SELECT count(*) FROM test。
使用 --csv 选项：从 PostgreSQL 12 开始，你可以使用 --csv 选项将结果以 CSV 格式输出。例如：
```
psql --csv -c 'SELECT * FROM test' id,col2
```
这将以 CSV 格式返回 id 和 col2 列。
使用 -e 参数展示隐藏元命令：使用 -e 或 --echo-hidden 参数可以展示以 \ 开头的元命令的真实查询语句，这对于了解 PostgreSQL 或 psql 的内部原理非常有用。例如：
```
psql -e -c '\l'
```
这将列出所有数据库的信息。
使用 -f 参数执行 SQL 文件：你可以使用 -f 或 --file=filename 参数来执行一个 SQL 文件。例如：
```
psql -f tmp.sql
```
其中 tmp.sql 是包含 SQL 命令的文件。
组合使用 -f 和 -c 参数：你也可以组合使用 -f 和 -c 参数来执行 SQL 文件中的特定命令。例如：
```
psql -f tmp.sql -c 'SELECT * FROM test'
```
这将执行 tmp.sql 文件中的命令，并额外执行 SELECT * FROM test。

服务管理技巧

自动服务管理：在 CentOS 中，你可以将 PostgreSQL 添加为自启动服务。首先，将 PostgreSQL 的启动脚本复制到 /etc/init.d 目录下，然后使用 chkconfig 命令将其添加到系统服务中。例如：
```
sudo cp /postgresql/soft/postgresql-14.12/contrib/start-scripts/linux /etc/init.d/postgres
sudo chkconfig --add postgres
```
你可以通过 chkconfig --list 命令检查是否添加成功。
使用 pg_ctl 命令管理数据库：pg_ctl 命令提供了启动、关闭、重新加载和重启 PostgreSQL 数据库的功能。例如：
```
sudo pg_ctl start -w -d /var/lib/pgsql/data -s
```
这将启动 PostgreSQL 服务，并等待所有客户端断开连接。

性能优化技巧

禁用自动提交：在批量插入数据时，关闭自动提交功能，改为手动管理事务，可以显著提高数据加载速度。例如：
```
BEGIN;
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
COMMIT;
```
使用 COPY 命令：COPY 命令专门设计用于高效数据加载，它绕过了许多标准 SQL 语句中的开销，从而大幅提高数据导入速度。例如：
```
COPY table_name FROM '/path/to/file.csv' WITH CSV HEADER;
```

删除索引和外键约束：在数据加载过程中，临时删除索引和外键约束，待数据加载完毕后再重建，可以大幅提升效率。例如：

ALTER TABLE table_name DROP INDEX index_name;
-- 加载数据
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table(column_name);

增加 maintenance_work_mem：增加 maintenance_work_mem 参数可以提高执行维护操作（如创建索引）的速度。例如：
```
shared_buffers = 25% OF SYSTEM Memory
maintenance_work_mem = 64MB
```
增加 max_wal_size：在大量数据加载时，增加 max_wal_size 可以避免 WAL 文件频繁切换，减少 I/O 操作。例如：
```
max_wal_size = 1GB
```
禁用 WAL 存档和流复制：在数据加载期间，禁用 WAL 归档和流式复制可以减少不必要的 I/O 操作和网络传输。例如：
```
wal_level = minimal
archive_mode = off
max_wal_senders = 0
```
运行 ANALYZE：数据加载完成后，运行 ANALYZE 命令更新统计信息，有助于查询优化器更好地规划查询计划。例如：
```
ANALYZE table_name;
```。
```

希望这些技巧能帮助你更高效地在 CentOS 上使用 PostgreSQL。

0 赞

0 踩