PostgreSQL 常用命令行工具

PostgreSQL 提供了一系列命令行工具来管理和操作数据库，以下是主要的命令行工具及其用途：

核心管理工具

psql - PostgreSQL 交互式终端
- 主要用途：执行 SQL 命令、管理数据库
- 示例：psql -U username -d dbname
createdb - 创建新数据库
- 示例：createdb mydatabase
dropdb - 删除数据库
- 示例：dropdb mydatabase
createuser - 创建新用户角色
- 示例：createuser -P username(带密码提示)
dropuser - 删除用户角色
- 示例：dropuser username

备份与恢复工具

pg_dump - 数据库备份工具
- 示例：pg_dump -U username -F c -f backup.dump dbname(自定义格式备份)
pg_dumpall - 备份整个PostgreSQL集群
- 示例：pg_dumpall -U postgres -f alldb.sql
pg_restore - 从备份恢复数据库
- 示例：pg_restore -U username -d dbname backup.dump

维护与监控工具

vacuumdb - 清理和分析数据库
- 示例：vacuumdb -a -z(分析所有数据库)
reindexdb - 重建数据库索引
- 示例：reindexdb --all
clusterdb - 对表进行物理重新排序
- 示例：clusterdb -a
pg_basebackup - 基础备份工具(用于复制)
- 示例：pg_basebackup -D /backup/location -X stream -P -U replicator

诊断与配置工具

pg_config - 显示PostgreSQL安装信息
- 示例：pg_config --bindir
pg_isready - 检查服务器状态
- 示例：pg_isready -h localhost -p 5432
pg_ctl - 控制PostgreSQL服务
- 示例：pg_ctl start -D /path/to/data/directory
pg_top - 类似top的PostgreSQL监控工具(需单独安装)

复制与高可用工具

pg_recvlogical - 管理逻辑解码流
pg_archivecleanup - 清理WAL归档文件
pg_rewind - 在时间线分叉后同步备用服务器

其他实用工具

postgres - PostgreSQL服务器可执行文件
initdb - 初始化新的数据库集群
pg_test_fsync - 测试不同的同步方法
pg_test_timing - 测量计时开销

这些工具通常位于PostgreSQL的bin目录下，可以通过pg_config --bindir命令查找具体位置。大多数工具都有详细的帮助信息，可以通过--help选项查看。

PostgreSQL 常用命令示例
1. 连接与登录命令
基本连接
# 连接到本地默认数据库(5432端口)
psql -U username

# 连接到指定数据库
psql -U username -d dbname

# 连接到远程服务器
psql -h hostname -p port -U username -d dbname

# 连接时不提示密码(适合脚本中使用)
psql "host=localhost port=5432 user=username dbname=dbname password=mypassword"
带密码连接
# 方法1：通过环境变量
export PGPASSWORD="mypassword"
psql -U username -d dbname

# 方法2：使用连接字符串
psql postgresql://username:mypassword@localhost:5432/dbname
2. 数据库管理命令
创建与删除
# 创建数据库
createdb -U postgres -O owner -E UTF8 -T template0 mydb

# 删除数据库
dropdb -U postgres mydb

# 创建用户
createuser -U postgres -P -s -e newuser

# 删除用户
dropuser -U postgres olduser
权限管理
-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- 撤销权限
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

-- 修改用户密码
ALTER USER username WITH PASSWORD 'newpassword';
3. 运维常用命令
备份与恢复
# 备份单个数据库(自定义格式)
pg_dump -U username -F c -f backup.dump dbname

# 备份单个数据库(纯SQL)
pg_dump -U username -f backup.sql dbname

# 备份所有数据库
pg_dumpall -U postgres -f alldb.sql

# 从备份恢复
pg_restore -U username -d dbname -C backup.dump

# 仅恢复数据结构
pg_restore -U username -d dbname -s backup.dump
维护命令
# 分析所有数据库
vacuumdb -U username -a -z

# 重建所有索引
reindexdb -U username --all

# 检查数据库状态
pg_isready -h localhost -p 5432

# 启动/停止服务
pg_ctl start -D /path/to/data
pg_ctl stop -D /path/to/data -m fast
4. psql 内部常用命令
-- 列出所有数据库
\l

-- 列出所有表
\dt

-- 列出所有视图
\dv

-- 查看表结构
\d tablename

-- 切换数据库
\c dbname

-- 执行外部SQL文件
\i /path/to/file.sql

-- 显示查询执行计划
EXPLAIN ANALYZE SELECT * FROM mytable;

-- 查看连接信息
SELECT * FROM pg_stat_activity;

-- 终止连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';
5. 监控与诊断
-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('dbname'));

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('tablename'));

-- 查看最耗时的查询
SELECT query, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

-- 查看锁信息
SELECT * FROM pg_locks;

-- 查看复制状态(主从)
SELECT * FROM pg_stat_replication;
这些命令涵盖了PostgreSQL日常管理的大部分场景，可以根据实际需求组合使用。在生产环境中执行删除或修改操作前，建议先进行备份。

posted @ 2025-08-14 09:56 你说夕阳很美阅读(164) 评论(0) 收藏举报

刷新页面返回顶部

daizichuan

PostgreSQL 常用命令行工具

核心管理工具

备份与恢复工具

维护与监控工具

诊断与配置工具

复制与高可用工具

其他实用工具

公告