当前位置： 首页 > news >正文

PostgreSQL常用命令与工具指南

news 2025/7/20 9:05:48

文章目录

PostgreSQL常用命令与工具指南

简介

本文档汇总了PostgreSQL数据库的常用命令和工具，涵盖数据库连接、管理、数据操作、权限控制、函数、备份恢复及图形化工具等方面，适用于开发人员、DBA及数据库初学者参考。

1. 连接与基本操作

连接数据库

psql -U username -d dbname -h hostname -p port

示例：连接本地PostgreSQL默认实例

psql -U postgres -d mydatabase -h localhost -p 5432

环境变量设置（避免密码输入）

export PGPASSWORD='your_password'
psql -U username -d dbname

⚠️ 注意：生产环境不建议使用明文环境变量，可配置.pgpass文件

常用元命令

  • \l：列出所有数据库
  • \c dbname：切换到指定数据库
  • \dt：列出当前数据库的所有表
  • \d table_name：查看表结构
  • \du：列出所有角色/用户
  • \df：列出所有函数
  • \x：切换扩展显示模式（适合查看宽表）
  • \q：退出psql终端

2. 数据库与表管理

数据库操作

创建数据库
CREATE DATABASE mydatabase 
WITH OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
删除数据库
DROP DATABASE IF EXISTS mydatabase;
修改数据库属性
ALTER DATABASE mydatabase RENAME TO newdbname;
ALTER DATABASE mydatabase SET CONNECTION LIMIT = 100;

表操作

创建表
    id SERIAL PRIMARY KEY,username VARCHAR(50) NOT NULL UNIQUE,email VARCHAR(100) NOT NULL UNIQUE,created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,age INTEGER CHECK (age >= 0),status VARCHAR(20) DEFAULT 'active'
);
修改表结构
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);-- 修改列
ALTER TABLE users ALTER COLUMN email SET NOT NULL;-- 删除列
ALTER TABLE users DROP COLUMN phone;-- 添加约束
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
删除表
DROP TABLE IF EXISTS users CASCADE; -- CASCADE会删除依赖对象

索引管理

创建索引
-- 普通索引
CREATE INDEX idx_users_username ON users(username);-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);-- 复合索引
CREATE INDEX idx_users_status_created_at ON users(status, created_at);-- 部分索引（只索引满足条件的行）
CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';
删除索引
DROP INDEX IF EXISTS idx_users_username;

3. 数据操作(CRUD)

插入数据

-- 插入单行
INSERT INTO users (username, email, age) 
VALUES ('john_doe', 'john@example.com', 30);-- 插入多行
INSERT INTO users (username, email, age) 
VALUES ('jane_smith', 'jane@example.com', 28),('bob_johnson', 'bob@example.com', 35);

查询数据

-- 基本查询
SELECT id, username, email FROM users WHERE status = 'active';-- 排序
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;-- 聚合查询
SELECT status, COUNT(*) as count FROM users GROUP BY status;-- 连接查询
SELECT u.username, p.product_name 
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.id =

更新数据

-- 更新单行
UPDATE users SET age = 31, status = 'inactive' WHERE id = 1;-- 更新多行
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

删除数据

-- 删除特定行
DELETE FROM users WHERE id = 1;-- 删除满足条件的多行
DELETE FROM users WHERE status = 'inactive' AND created_at < '2023-01-01';

事务控制

BEGIN; -- 开始事务INSERT INTO users (username, email) VALUES ('new_user', 'new@example.com');
UPDATE stats SET user_count = user_count + 1;COMMIT; -- 提交事务，或ROLLBACK; 回滚

4. 账号与权限管理

角色/用户操作

创建角色
-- 创建普通用户（带登录权限）
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';-- 创建超级用户
CREATE ROLE db_admin WITH SUPERUSER LOGIN PASSWORD 'admin_password';-- 创建角色（无登录权限，用于权限分组）
CREATE ROLE reporting;
修改角色
-- 修改密码
ALTER ROLE app_user WITH PASSWORD 'new_secure_password';-- 添加/移除权限
ALTER ROLE app_user WITH CREATEDB; -- 允许创建数据库
ALTER ROLE app_user WITH NOCREATEDB; -- 移除创建数据库权限-- 修改连接限制
ALTER ROLE app_user CONNECTION LIMIT 10;
删除角色
DROP ROLE IF EXISTS app_user;

权限控制

授予权限
-- 授予数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;-- 授予表权限
GRANT SELECT, INSERT, UPDATE ON TABLE users TO app_user;
GRANT ALL PRIVILEGES ON TABLE products TO app_user;-- 授予列级权限
GRANT SELECT (id, username), UPDATE (email) ON TABLE users TO app_user;-- 授予角色给用户（继承权限）
GRANT reporting TO app_user;
撤销权限
REVOKE UPDATE ON TABLE users FROM app_user;
查看权限
-- 查看表权限
\dp users-- 查看角色权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'app_user';

5. 常用函数

字符串函数

函数描述示例结果
CONCAT(str1, str2)连接字符串CONCAT('Hello', ' ', 'World')‘Hello World’
SUBSTRING(str FROM start FOR len)截取子串SUBSTRING('PostgreSQL' FROM 1 FOR 4)‘Post’
LENGTH(str)字符串长度LENGTH('test')4
TRIM(str)去除首尾空格TRIM(' test ')‘test’
UPPER(str)/LOWER(str)大小写转换UPPER('test')‘TEST’
REPLACE(str, old, new)替换字符串REPLACE('abc', 'a', 'x')‘xbc’

日期时间函数

-- 当前时间
SELECT CURRENT_TIMESTAMP; -- 带时区
SELECT NOW(); -- 同上
SELECT CURRENT_DATE; -- 仅日期-- 日期运算
SELECT NOW() + INTERVAL '1 day'; -- 明天此时
SELECT NOW() - INTERVAL '2 hours'; -- 两小时前-- 日期格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- '2023-11-15 14:30:45'-- 日期截断
SELECT DATE_TRUNC('month', NOW()); -- 当月第一天 00:00:00

数学与聚合函数

-- 数学函数
SELECT ROUND(3.1415, 2); -- 3.14
SELECT CEIL(3.2); -- 4
SELECT FLOOR(3.8); -- 3
SELECT RANDOM(); -- 0-1随机数-- 聚合函数
SELECT AVG(age) FROM users; -- 平均值
SELECT SUM(amount) FROM orders; -- 总和
SELECT COUNT(*) FROM users; -- 总行数
SELECT MAX(created_at) FROM posts; -- 最大值
SELECT MIN(price) FROM products; -- 最小值-- 分组聚合
SELECT status, COUNT(*) as count FROM users GROUP BY status;

数组与JSON函数

-- 数组函数
SELECT ARRAY_AGG(id) FROM users WHERE status = 'active'; -- 聚合为数组
SELECT UNNEST(ARRAY[1,2,3]); -- 数组展开为多行
SELECT ARRAY_LENGTH(ARRAY[1,2,3], 1); -- 数组长度-- JSON函数
SELECT '{"name": "John", "age": 30}'::jsonb -> 'name'; -- 获取JSON字段
SELECT jsonb_object_keys('{"a": 1, "b": 2}'); -- 获取所有键
SELECT jsonb_extract_path_text('{"user": {"name": "John"}}', 'user', 'name'); -- 嵌套获取

窗口函数

-- 行号
SELECT id, username, ROW_NUMBER() OVER (ORDER BY age) as row_num FROM users;-- 排名
SELECT id, score, RANK() OVER (ORDER BY score DESC) as rank FROM students;-- 分区排名
SELECT department, id, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank 
FROM employees;

6. 数据导出与备份

使用pg_dump导出

基本用法
# 导出整个数据库
pg_dump -U username -d dbname -f backup.sql# 导出为自定义格式（压缩，支持恢复时选择对象）
pg_dump -U username -d dbname -F c -f backup.dump# 导出为目录格式（支持并行备份）
pg_dump -U username -d dbname -F d -f backup_dir
高级选项
# 仅导出数据（无表结构）
pg_dump -U username -d dbname -a -f data_only.sql# 仅导出表结构
pg_dump -U username -d dbname -s -f schema_only.sql# 导出特定表
pg_dump -U username -d dbname -t table1 -t table2 -f tables_backup.sql# 排除特定表
pg_dump -U username -d dbname --exclude-table=logs --exclude-table=tmp_data -f backup.sql# 并行导出（4个工作进程）
pg_dump -U username -d dbname -j4 -F d -f parallel_backup

导出为CSV

-- 服务器端导出（需要文件系统权限）
COPY users TO '/var/lib/postgresql/users.csv' WITH (FORMAT csv, HEADER, DELIMITER ',');-- 客户端导出（无需服务器文件权限）
\copy (SELECT id, username, email FROM users WHERE status = 'active') TO 'active_users.csv' WITH (FORMAT csv, HEADER);
```### 大数据量导出策略1. **分批次导出**
```bash
# 使用WHERE条件分批次导出
pg_dump -t "users" -c "WHERE id BETWEEN 1 AND 100000" -f users_part1.sql
pg_dump -t "users" -c "WHERE id BETWEEN 100001 AND 200000" -f users_part2.sql
  1. 并行导出与恢复
# 并行备份
pg_dump -j 8 -F d -f backup_dir dbname# 并行恢复
pg_restore -j 8 -d dbname backup_dir
  1. 使用COPY命令优化
-- 导出前禁用触发器和索引
ALTER TABLE large_table DISABLE TRIGGER ALL;
DROP INDEX idx_large_table;-- 执行导出
COPY large_table TO 'data.csv' CSV;-- 重新启用触发器和索引
ALTER TABLE large_table ENABLE TRIGGER ALL;
CREATE INDEX idx_large_table ON large_table(column);

备份恢复示例

# 从SQL文件恢复
psql -U username -d dbname -f backup.sql# 从自定义格式恢复
pg_restore -U username -d dbname backup.dump# 恢复到新数据库
createdb -U username new_db
pg_restore -U username -d new_db backup.dump

7. 性能优化与维护

数据库分析与优化

-- 更新统计信息（帮助查询优化器）
ANALYZE users;
ANALYZE VERBOSE users; -- 详细输出-- 真空清理（回收空间，更新可见性映射）
VACUUM users; -- 普通真空
VACUUM ANALYZE users; -- 真空并分析
VACUUM FULL users; -- 彻底清理（需要更多资源，会锁表）

查询性能分析

-- 查看查询计划
EXPLAIN SELECT * FROM users WHERE status = 'active';-- 执行并分析（实际运行查询）
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active' ORDER BY created_at;

连接管理

-- 查看当前连接
SELECT pid, usename, datname, state, wait_event_type, wait_event 
FROM pg_stat_activity;-- 终止连接
SELECT pg_terminate_backend(12345); -- 12345为pid

系统参数设置

-- 查看参数
SHOW work_mem;
SELECT name, setting, unit FROM pg_settings WHERE name LIKE 'work_mem';-- 修改参数（会话级）
SET work_mem = '64MB';-- 修改参数（全局，需要重启或重载）
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf(); -- 重载配置

8. 图形化管理工具

主流工具对比

工具授权类型支持平台主要特点适用场景
pgAdmin开源免费Windows/Linux/Mac官方工具，功能全面，支持查询构建、性能监控、备份恢复DBA、开发人员
DBeaver开源免费（社区版）/商业（企业版）跨平台多数据库支持，ER图，数据导入导出，高级元数据管理多数据库环境，开发/分析
Navicat商业跨平台界面直观，易用性强，数据可视化，模型设计，数据同步数据库初学者，快速操作
phpPgAdmin开源免费Web应用基于Web，适合服务器端部署，远程管理无本地客户端环境，多用户共享
OmniDB开源免费跨平台/Web支持团队协作，可视化查询构建，监控仪表板团队协作，远程数据库管理

工具推荐

  • 开发人员：DBeaver（免费功能足够）或DataGrip（集成开发体验）
  • DBA：pgAdmin（官方工具，功能全面）
  • 初学者：Navicat（界面友好，学习曲线低）
  • 服务器管理：phpPgAdmin（Web访问，无需客户端安装）

9. 常用维护命令

系统状态检查

-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('mydb'));-- 查看表大小（含索引）
SELECT pg_size_pretty(pg_total_relation_size('users'));-- 查看连接数
SELECT count(*) FROM pg_stat_activity;
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

数据库一致性检查

# 需要关闭数据库
pg_checksums -c -d /var/lib/postgresql/14/main

日志管理

-- 查看日志配置
SHOW log_directory;
SHOW log_filename;
SHOW log_min_duration_statement;-- 临时设置日志级别
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 记录执行时间>1秒的查询
pg_reload_conf();

参考资料

  • PostgreSQL官方文档
  • PostgreSQL中文社区
  • pg_dump官方手册
  • PostgreSQL性能优化指南
http://www.lryc.cn/news/593322.html

相关文章：

  • 发明专利怎么写，与学术文章异同点与注意事项
  • 从0开始学习R语言--Day51--PH检验
  • HAMR硬盘高温写入的可靠性问题
  • 数字图像处理（三：图像如果当作矩阵，那加减乘除处理了矩阵，那图像咋变）：从LED冬奥会、奥运会及春晚等等大屏，到手机小屏，快来挖一挖里面都有什么
  • 我用Cursor，1周上线了一个虚拟资料流量主小程序技术选型
  • 图解系统-小林coding笔记
  • view和pure的区别
  • 电脑windows系统深度维护指南
  • Validation - Spring Boot项目中参数检验的利器
  • 前端开发技巧：浏览器模拟弱网络环境
  • 中间件安全攻防全解：从Tomcat到Weblogic反序列化漏洞介绍
  • 暑假--作业3
  • Redis的持久化-RDB
  • 关于个人博客系统的测试报告
  • 【2025最新】使用neo4j实现GraphRAG所需的向量检索
  • BeanFactory 和 FactoryBean 的区别
  • Netty网络聊天室及扩展序列化算法
  • （后者可以节约内存/GPU显存）Pytorch中求逆torch.inverse和解线性方程组torch.linalg.solve有什么关系
  • 单调队列深度解析(下)
  • 7.19 换根dp | vpp |滑窗
  • 物联网-规则引擎的定义
  • LeetCode中等题--167.两数之和II-输入有序数组
  • RT-Thread的概念和移植
  • Spring AI 项目实战（十八）：Spring Boot + AI + Vue3 + OSS + DashScope 实现高效语音识别系统（附完整源码）
  • OpenCV 官翻7 - 对象检测
  • Edge浏览器设置网页自动翻译
  • #Datawhale组队学习#7月-强化学习Task2
  • 医疗AI与融合数据库的整合：挑战、架构与未来展望（上）
  • 高压电工作业证考试核心考点：电气安全基础篇
  • MCP 协议详细分析一 initialize ping tools/list tools/call