PostgreSQL 工具命令示例

PostgreSQL提供了丰富的命令行工具，用于数据库管理、备份恢复、性能监控等运维操作。本章节将详细介绍常用工具命令的实际使用示例，包括psql、pg_dump、pg_restore、pg_basebackup等核心工具的具体用法和最佳实践。

psql 命令示例

psql是PostgreSQL的交互式终端工具，用于执行SQL命令、管理数据库和执行脚本。它是DBA日常工作中最常用的工具之一。

1. 连接与基本操作

psql支持多种连接方式，包括传统的参数连接和URL连接字符串（PostgreSQL 14+）。以下是常用的连接和基本操作示例：

bash

# 连接到本地数据库，指定主机、端口、用户名和数据库名
psql -h localhost -p 5432 -U postgres -d mydb

# 使用URL连接字符串连接（PostgreSQL 14+），格式更简洁直观
psql postgresql://postgres:password@localhost:5432/mydb

# 列出所有数据库，快速查看服务器上的数据库列表
psql -l -U postgres

# 列出数据库中的表，使用元命令\dt查看当前数据库中的所有表
psql -c "\dt" -d mydb -U postgres

# 执行SQL文件，批量执行脚本中的SQL命令
psql -f script.sql -d mydb -U postgres

# 将查询结果导出到文件，便于后续分析或报表生成
psql -c "SELECT * FROM users" -d mydb -U postgres -o users.csv

# 使用元命令格式化输出，以扩展模式显示查询结果，便于查看宽表数据
psql -c "\x SELECT * FROM users LIMIT 5" -d mydb -U postgres

2. 数据库管理

psql可以直接执行SQL命令进行数据库管理操作，包括用户管理、权限授予、schema管理等：

bash

# 创建新用户，设置密码，用于数据库访问控制
psql -c "CREATE USER newuser WITH PASSWORD 'password'" -U postgres

# 授予用户数据库权限，将mydb数据库的所有权限授予newuser
psql -c "GRANT ALL PRIVILEGES ON DATABASE mydb TO newuser" -U postgres

# 创建schema，用于组织数据库对象，实现逻辑隔离
psql -c "CREATE SCHEMA newschema" -d mydb -U postgres

# 列出schema中的对象，查看当前数据库中的所有schema
psql -c "\dn" -d mydb -U postgres

# 重命名表，修改表名以适应业务变化
psql -c "ALTER TABLE old_table RENAME TO new_table" -d mydb -U postgres

3. 数据操作

psql可以执行各种数据操作命令，包括插入、更新、删除数据，以及CSV数据的导入导出：

bash

# 插入数据，向users表添加一条新记录
psql -c "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')" -d mydb -U postgres

# 更新数据，修改指定条件的记录
psql -c "UPDATE users SET email = 'john.doe@example.com' WHERE id = 1" -d mydb -U postgres

# 删除数据，删除指定条件的记录
psql -c "DELETE FROM users WHERE id = 1" -d mydb -U postgres

# 导入CSV数据，从外部CSV文件批量导入数据到表中
psql -c "COPY users FROM '/path/to/users.csv' DELIMITER ',' CSV HEADER" -d mydb -U postgres

# 导出表数据为CSV，将表数据导出为CSV格式，便于数据迁移或分析
psql -c "COPY users TO '/path/to/users_export.csv' DELIMITER ',' CSV HEADER" -d mydb -U postgres

pg_dump 命令示例

pg_dump是PostgreSQL的备份工具，用于创建数据库的逻辑备份。它可以生成SQL脚本或归档文件，支持多种备份格式和选项。

1. 完整数据库备份

完整数据库备份是最常用的备份方式，可以备份数据库的所有对象和数据：

bash

# 备份为SQL脚本文件，生成可读的SQL脚本，便于查看和编辑
pg_dump -h localhost -p 5432 -U postgres -d mydb -f mydb_backup.sql

# 备份为自定义格式，支持压缩和并行恢复，是生产环境常用的备份格式
pg_dump -h localhost -p 5432 -U postgres -d mydb -F c -Z 5 -f mydb_backup.dump

# 备份为tar格式，生成tar归档文件，便于存储和传输
pg_dump -h localhost -p 5432 -U postgres -d mydb -F t -f mydb_backup.tar

# 并行备份，使用4个并行作业加速备份过程，适用于大型数据库
pg_dump -h localhost -p 5432 -U postgres -d mydb -F d -j 4 -f /backup/mydb_parallel

2. 选择性备份

在某些场景下，我们只需要备份数据库的部分内容，如特定表、特定schema或只备份数据/ schema：

bash

# 只备份特定表，备份users和orders表的所有内容
pg_dump -h localhost -p 5432 -U postgres -d mydb -t users -t orders -f tables_backup.sql

# 只备份特定schema，备份public和sales schema下的所有对象
pg_dump -h localhost -p 5432 -U postgres -d mydb -n public -n sales -f schemas_backup.sql

# 只备份数据，不备份schema，适用于相同schema结构下的数据迁移
pg_dump -h localhost -p 5432 -U postgres -d mydb -a -f data_only_backup.sql

# 只备份schema，不备份数据，用于创建测试环境或克隆数据库结构
pg_dump -h localhost -p 5432 -U postgres -d mydb -s -f schema_only_backup.sql

# 排除特定表，备份时排除临时表和审计日志表
pg_dump -h localhost -p 5432 -U postgres -d mydb -T temp_table -T audit_log -f backup_with_exclusions.sql

3. 高级备份选项

pg_dump提供了丰富的高级选项，用于满足不同场景的备份需求：

bash

# 备份时包含创建数据库语句，恢复时自动创建数据库
pg_dump -h localhost -p 5432 -U postgres -d mydb -C -f backup_with_create_db.sql

# 备份前清理现有对象，恢复时先删除同名对象再创建
pg_dump -h localhost -p 5432 -U postgres -d mydb -c -f backup_with_clean.sql

# 使用单事务备份，确保备份的一致性，适用于事务性数据库
pg_dump -h localhost -p 5432 -U postgres -d mydb -1 -f backup_single_transaction.sql

# 备份时包含表空间信息，支持将对象恢复到不同的表空间
pg_dump -h localhost -p 5432 -U postgres -d mydb --tablespace-mapping=old_ts=new_ts -f backup_with_tablespaces.sql

pg_restore 命令示例

pg_restore用于从pg_dump创建的备份文件中恢复PostgreSQL数据库，支持多种恢复选项和选择性恢复。

1. 完整数据库恢复

完整数据库恢复将备份文件中的所有对象和数据恢复到目标数据库：

bash

# 从自定义格式备份恢复，这是最常用的恢复方式
pg_restore -h localhost -p 5432 -U postgres -d mydb -v mydb_backup.dump

# 从tar格式备份恢复，恢复tar归档格式的备份
pg_restore -h localhost -p 5432 -U postgres -d mydb -v mydb_backup.tar

# 从目录格式备份恢复，恢复并行备份生成的目录格式备份
pg_restore -h localhost -p 5432 -U postgres -d mydb -v /backup/mydb_parallel

# 并行恢复，使用4个并行作业加速恢复过程，适用于大型数据库恢复
pg_restore -h localhost -p 5432 -U postgres -d mydb -j 4 -v mydb_backup.dump

2. 选择性恢复

pg_restore支持选择性恢复，允许只恢复备份文件中的部分内容：

bash

# 只恢复特定表，只恢复users和orders表的内容
pg_restore -h localhost -p 5432 -U postgres -d mydb -t users -t orders -v mydb_backup.dump

# 只恢复特定schema，只恢复public schema下的对象
pg_restore -h localhost -p 5432 -U postgres -d mydb -n public -v mydb_backup.dump

# 只恢复数据，不恢复schema，适用于已有schema结构的数据恢复
pg_restore -h localhost -p 5432 -U postgres -d mydb -a -v mydb_backup.dump

# 只恢复schema，不恢复数据，用于创建新环境的数据库结构
pg_restore -h localhost -p 5432 -U postgres -d mydb -s -v mydb_backup.dump

3. 高级恢复选项

pg_restore提供了多种高级恢复选项，用于满足不同场景的恢复需求：

bash

# 恢复前创建数据库，自动创建目标数据库后恢复数据
pg_restore -h localhost -p 5432 -U postgres -C -d postgres -v mydb_backup.dump

# 恢复前清理现有对象，恢复前先删除同名对象，避免冲突
pg_restore -h localhost -p 5432 -U postgres -d mydb -c -v mydb_backup.dump

# 不恢复所有者和权限，恢复时使用当前用户和默认权限，适用于跨环境恢复
pg_restore -h localhost -p 5432 -U postgres -d mydb -O -x -v mydb_backup.dump

# 恢复到不同的表空间，将数据和索引恢复到不同的表空间，优化存储布局
pg_restore -h localhost -p 5432 -U postgres -d mydb --tablespace-mapping=old_data=new_data --tablespace-mapping=old_index=new_index -v mydb_backup.dump

pg_basebackup 命令示例

pg_basebackup用于创建PostgreSQL数据库集群的基础备份，是物理备份的主要工具，用于搭建复制环境或灾难恢复。

1. 基础备份

基础备份是pg_basebackup最基本的使用方式，可以生成不同格式的备份文件：

bash

# 基础备份（plain格式），生成原始文件格式的备份，用于搭建从服务器
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -v

# 基础备份（tar格式，压缩），生成压缩的tar归档文件，节省存储空间
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -F t -z -v

# 基础备份（流式WAL），使用流式方式获取WAL日志，确保备份的一致性
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -F t -X stream -z -P

# 基础备份（fetch WAL），备份完成后复制WAL日志，适用于低带宽环境
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -F p -X fetch -v

2. 高级备份选项

pg_basebackup提供了多种高级选项，用于优化备份过程和结果：

bash

# 使用快速检查点，减少备份对主服务器的影响
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -c fast -v

# 自定义压缩级别，设置压缩级别为3，平衡压缩率和CPU使用率
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -F t -Z 3 -v

# 包含WAL文件，备份时包含所有必要的WAL文件，确保备份的完整性
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -X stream -C -v

# 使用复制槽，确保备份过程中产生的WAL日志不会被过早删除
pg_basebackup -h master.example.com -p 5432 -U replication -D /backup/base -X stream -S replica_slot -v

其他常用工具命令示例

除了核心的备份恢复工具外，PostgreSQL还提供了其他常用的管理工具，用于数据库创建、用户管理、服务器控制等。

1. 数据库管理

bash

# 创建数据库，指定所有者、编码和模板
createdb -h localhost -p 5432 -U postgres -O newuser -E UTF8 -T template0 newdb

# 删除数据库，交互式确认，避免误操作
# -i选项会提示确认，提高操作安全性
# -h指定主机，-p指定端口，-U指定用户名
# newdb是要删除的数据库名
dropdb -h localhost -p 5432 -U postgres -i newdb

# 创建用户，具有创建数据库、角色和超级用户权限
createuser -h localhost -p 5432 -U postgres -P -d -r -s newadmin

# 删除用户，交互式确认
# -P提示输入密码
# -d允许创建数据库
# -r允许创建角色
# -s授予超级用户权限
dropuser -h localhost -p 5432 -U postgres -i newuser

2. 服务器管理

pg_ctl是PostgreSQL服务器的控制工具，用于启动、停止和重启服务器：

bash

# 启动PostgreSQL服务器，指定数据目录和日志文件
pg_ctl -D /var/lib/postgresql/14/main start -l /var/log/postgresql/14-main.log

# 停止PostgreSQL服务器，使用快速模式，等待当前事务完成后停止
pg_ctl -D /var/lib/postgresql/14/main stop -m fast

# 重启PostgreSQL服务器，先停止再启动
pg_ctl -D /var/lib/postgresql/14/main restart

# 重新加载配置文件，不重启服务器即可应用配置变更
pg_ctl -D /var/lib/postgresql/14/main reload

# 查看服务器状态，检查服务器是否正常运行
pg_ctl -D /var/lib/postgresql/14/main status

3. 表空间管理

表空间用于管理数据库对象的存储位置，可以将不同的表和索引存储在不同的磁盘上，优化存储性能：

bash

# 创建表空间，指定存储位置
psql -c "CREATE TABLESPACE ts_data LOCATION '/data/postgres/ts_data'" -U postgres

# 将表移动到表空间，优化大表的存储位置
psql -c "ALTER TABLE users SET TABLESPACE ts_data" -d mydb -U postgres

# 将索引移动到表空间，将索引和数据分离存储，提高查询性能
psql -c "ALTER INDEX users_pkey SET TABLESPACE ts_index" -d mydb -U postgres

# 删除表空间，删除不再使用的表空间
psql -c "DROP TABLESPACE ts_old" -U postgres

4. 统计信息

统计信息对于PostgreSQL优化器生成高效的查询计划至关重要，定期更新统计信息可以提高查询性能：

bash

# 分析表以更新统计信息，详细输出分析过程
psql -c "ANALYZE VERBOSE users" -d mydb -U postgres

# 分析数据库中的所有表，更新整个数据库的统计信息
psql -c "ANALYZE VERBOSE" -d mydb -U postgres

# 查看表的统计信息，了解表的使用情况和数据分布
psql -c "SELECT * FROM pg_stat_user_tables WHERE relname = 'users'" -d mydb -U postgres

# 查看索引的使用情况，分析索引的有效性和使用频率
psql -c "SELECT * FROM pg_stat_user_indexes WHERE relname = 'users'" -d mydb -U postgres

实用脚本示例

在实际运维工作中，我们通常会编写脚本自动化常见的数据库操作，提高工作效率和准确性。

1. 自动备份脚本

自动备份脚本用于定期备份数据库，并管理备份文件的生命周期：

bash

#!/bin/bash
# PostgreSQL 自动备份脚本
# 本脚本用于定期备份PostgreSQL数据库，支持自动清理过期备份

# 配置参数
BACKUP_DIR="/backup/postgresql"  # 备份存储目录
DB_HOST="localhost"             # 数据库主机
DB_PORT="5432"                  # 数据库端口
DB_USER="postgres"              # 备份用户
DB_NAME="mydb"                  # 要备份的数据库
RETENTION_DAYS=7                # 备份保留天数
DATE=$(date +%Y%m%d_%H%M%S)     # 当前时间，用于备份文件名

# 创建备份目录，确保备份目录存在
mkdir -p $BACKUP_DIR

# 执行备份，使用自定义格式，压缩级别5，详细输出
pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -F c -Z 5 -v -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 删除过期备份，只保留指定天数的备份
find $BACKUP_DIR -name "${DB_NAME}_*.dump" -type f -mtime +$RETENTION_DAYS -delete

# 记录备份日志，便于后续查看备份历史
# 将备份完成信息写入日志文件
echo "$(date): Backup completed successfully for $DB_NAME" >> $BACKUP_DIR/backup.log

2. 数据库健康检查脚本

数据库健康检查脚本用于定期检查数据库的运行状态，及时发现潜在问题：

bash

#!/bin/bash
# PostgreSQL 健康检查脚本
# 本脚本用于检查PostgreSQL数据库的基本健康状态

# 配置参数
DB_HOST="localhost"  # 数据库主机
DB_PORT="5432"       # 数据库端口
DB_USER="postgres"   # 检查用户
DB_NAME="postgres"   # 检查使用的数据库

# 检查数据库连接
# 尝试连接数据库并执行简单查询
# > /dev/null 2>&1 将输出重定向到/dev/null，只保留退出码
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT 1" > /dev/null 2>&1
if [ $? -ne 0 ]; then
    echo "ERROR: Cannot connect to PostgreSQL database"
    exit 1
fi

# 检查数据库版本
# 使用-t选项只输出结果，去除表头和空格
echo "PostgreSQL Version: $(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT version()")"

# 检查连接数
# 查询当前连接数和最大连接数
CONNECTIONS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity")
MAX_CONNECTIONS=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SHOW max_connections")
echo "Connections: $CONNECTIONS/$MAX_CONNECTIONS"

# 检查慢查询
# 查询运行时间超过5分钟的活跃查询数量
SLOW_QUERIES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes'")
echo "Slow Queries (>5min): $SLOW_QUERIES"

# 检查数据库大小
# 查询当前数据库的大小
db_size=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c "SELECT pg_size_pretty(pg_database_size('$DB_NAME'))")
echo "Database Size: $db_size"

# 检查完成，输出成功信息
echo "Health check completed successfully"
exit 0

3. 数据迁移脚本

数据迁移脚本用于在不同数据库之间迁移数据，确保数据的完整性和一致性：

bash

#!/bin/bash
# PostgreSQL 数据迁移脚本
# 本脚本用于在源数据库和目标数据库之间迁移数据

# 源数据库配置
SOURCE_HOST="source.example.com"  # 源数据库主机
SOURCE_PORT="5432"               # 源数据库端口
SOURCE_USER="source_user"        # 源数据库用户
SOURCE_DB="source_db"            # 源数据库名

# 目标数据库配置
TARGET_HOST="target.example.com"  # 目标数据库主机
TARGET_PORT="5432"               # 目标数据库端口
TARGET_USER="target_user"        # 目标数据库用户
TARGET_DB="target_db"            # 目标数据库名

# 迁移表结构
# 先备份源数据库的表结构
pg_dump -h $SOURCE_HOST -p $SOURCE_PORT -U $SOURCE_USER -d $SOURCE_DB -s -f schema.sql
# 然后在目标数据库中创建表结构
psql -h $TARGET_HOST -p $TARGET_PORT -U $TARGET_USER -d $TARGET_DB -f schema.sql

# 迁移数据（使用并行）
# 使用目录格式和4个并行作业备份源数据库数据
pg_dump -h $SOURCE_HOST -p $SOURCE_PORT -U $SOURCE_USER -d $SOURCE_DB -a -F d -j 4 -f /tmp/data
# 使用4个并行作业恢复数据到目标数据库
pg_restore -h $TARGET_HOST -p $TARGET_PORT -U $TARGET_USER -d $TARGET_DB -j 4 -v /tmp/data

# 验证数据完整性
# 比较源数据库和目标数据库的表行数，确保数据一致
echo "Verifying data integrity..."
# 示例：比较users表的行数
SOURCE_COUNT=$(psql -h $SOURCE_HOST -p $SOURCE_PORT -U $SOURCE_USER -d $SOURCE_DB -t -c "SELECT count(*) FROM users")
TARGET_COUNT=$(psql -h $TARGET_HOST -p $TARGET_PORT -U $TARGET_USER -d $TARGET_DB -t -c "SELECT count(*) FROM users")

if [ "$SOURCE_COUNT" -eq "$TARGET_COUNT" ]; then
    echo "Data integrity check passed for users table"
else
    echo "ERROR: Data integrity check failed for users table ($SOURCE_COUNT != $TARGET_COUNT)"
    exit 1
fi

# 清理临时文件
# 删除迁移过程中生成的临时文件
rm -rf /tmp/data
rm schema.sql

# 迁移完成，输出成功信息
echo "Data migration completed successfully"

常见问题（FAQ）

Q1：如何自动化执行备份脚本？

A1：可以使用crontab来定期执行备份脚本：

bash

# 编辑crontab配置
crontab -e

# 添加每天凌晨2点执行备份脚本
0 2 * * * /path/to/backup_script.sh

Q2：如何在不同版本的PostgreSQL之间迁移数据？

A2：可以使用pg_dump和pg_restore进行跨版本迁移，建议使用较新版本的工具来备份旧版本数据库：

bash

# 使用新版本pg_dump备份旧版本数据库
/path/to/new/pg_dump -h old_host -p 5432 -U postgres -d old_db -F c -f old_db_backup.dump

# 使用新版本pg_restore恢复到新版本数据库
/path/to/new/pg_restore -h new_host -p 5432 -U postgres -d new_db -v old_db_backup.dump

Q3：如何监控PostgreSQL工具命令的执行？

A3：可以使用以下方法监控工具命令执行：

在脚本中添加日志记录
使用系统监控工具（如systemd-cgtop、top）监控资源使用
对于长时间运行的命令，使用nohup和&将命令放入后台执行
使用pg_stat_activity监控数据库会话

Q4：如何优化pg_dump和pg_restore的性能？

A4：优化建议：

使用并行备份/恢复（-j选项）
使用合适的压缩级别，平衡压缩率和CPU使用率
使用自定义格式（-F c）或目录格式（-F d）
避免在业务高峰期执行大型备份/恢复操作
确保备份设备有足够的I/O带宽
对于大型数据库，考虑使用pg_basebackup进行物理备份

Q5：如何确保备份的安全性？

A5：确保备份安全的建议：

限制备份文件的访问权限（chmod 600 backup.dump）
加密敏感数据的备份
使用专用的备份用户，授予最小必要权限
定期测试备份的可恢复性
将备份存储在多个位置，包括异地存储
定期轮换备份介质

Q6：如何使用psql命令批量执行SQL语句？

A6：可以使用以下方法批量执行SQL语句：

bash

# 从文件执行
psql -f script.sql -d mydb -U postgres

# 从标准输入执行
echo "SELECT * FROM users; SELECT * FROM orders;" | psql -d mydb -U postgres

# 使用here-document
psql -d mydb -U postgres << EOF
SELECT * FROM users;
SELECT * FROM orders;
EOF

PostgreSQL 工具命令示例 ​

psql 命令示例 ​

1. 连接与基本操作 ​

2. 数据库管理 ​

3. 数据操作 ​

pg_dump 命令示例 ​

1. 完整数据库备份 ​

2. 选择性备份 ​

3. 高级备份选项 ​

pg_restore 命令示例 ​

1. 完整数据库恢复 ​

2. 选择性恢复 ​

3. 高级恢复选项 ​

pg_basebackup 命令示例 ​

1. 基础备份 ​

2. 高级备份选项 ​

其他常用工具命令示例 ​

1. 数据库管理 ​

2. 服务器管理 ​

3. 表空间管理 ​

4. 统计信息 ​

实用脚本示例 ​

1. 自动备份脚本 ​

2. 数据库健康检查脚本 ​

3. 数据迁移脚本 ​

常见问题（FAQ） ​

Q1：如何自动化执行备份脚本？ ​

Q2：如何在不同版本的PostgreSQL之间迁移数据？ ​

Q3：如何监控PostgreSQL工具命令的执行？ ​

Q4：如何优化pg_dump和pg_restore的性能？ ​

Q5：如何确保备份的安全性？ ​

Q6：如何使用psql命令批量执行SQL语句？ ​

PostgreSQL 工具命令示例

psql 命令示例

1. 连接与基本操作

2. 数据库管理

3. 数据操作

pg_dump 命令示例

1. 完整数据库备份

2. 选择性备份

3. 高级备份选项

pg_restore 命令示例

1. 完整数据库恢复

2. 选择性恢复

3. 高级恢复选项

pg_basebackup 命令示例

1. 基础备份

2. 高级备份选项

其他常用工具命令示例

1. 数据库管理

2. 服务器管理

3. 表空间管理

4. 统计信息

实用脚本示例

1. 自动备份脚本

2. 数据库健康检查脚本

3. 数据迁移脚本

常见问题（FAQ）

Q1：如何自动化执行备份脚本？

Q2：如何在不同版本的PostgreSQL之间迁移数据？

Q3：如何监控PostgreSQL工具命令的执行？

Q4：如何优化pg_dump和pg_restore的性能？

Q5：如何确保备份的安全性？

Q6：如何使用psql命令批量执行SQL语句？