PostgreSQL常用命令与工具指南

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. 常用函数

字符串函数

日期时间函数

-- 当前时间
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

2. 并行导出与恢复

# 并行备份
pg_dump -j 8 -F d -f backup_dir dbname

# 并行恢复
pg_restore -j 8 -d dbname backup_dir

3. 使用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. 图形化管理工具

主流工具对比

工具推荐

• 开发人员：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性能优化指南