虚拟主机搭建网站,专门卖电子产品的网站,西安十大网站制作公司,舅舅建筑网文章目录PostgreSQL常用命令与工具指南简介1. 连接与基本操作连接数据库环境变量设置#xff08;避免密码输入#xff09;常用元命令2. 数据库与表管理数据库操作创建数据库删除数据库修改数据库属性表操作创建表修改表结构删除表索引管理创建索引删除索引3. 数据操作(CRUD)插…
文章目录PostgreSQL常用命令与工具指南简介1. 连接与基本操作连接数据库环境变量设置避免密码输入常用元命令2. 数据库与表管理数据库操作创建数据库删除数据库修改数据库属性表操作创建表修改表结构删除表索引管理创建索引删除索引3. 数据操作(CRUD)插入数据查询数据更新数据删除数据事务控制4. 账号与权限管理角色/用户操作创建角色修改角色删除角色权限控制授予权限撤销权限查看权限5. 常用函数字符串函数日期时间函数数学与聚合函数数组与JSON函数窗口函数6. 数据导出与备份使用pg_dump导出基本用法高级选项导出为CSV备份恢复示例7. 性能优化与维护数据库分析与优化查询性能分析连接管理系统参数设置8. 图形化管理工具主流工具对比工具推荐9. 常用维护命令系统状态检查数据库一致性检查日志管理参考资料PostgreSQL常用命令与工具指南
简介
本文档汇总了PostgreSQL数据库的常用命令和工具涵盖数据库连接、管理、数据操作、权限控制、函数、备份恢复及图形化工具等方面适用于开发人员、DBA及数据库初学者参考。
1. 连接与基本操作
连接数据库
psql -U username -d dbname -h hostname -p port示例连接本地PostgreSQL默认实例
psql -U postgres -d mydatabase -h localhost -p 5432环境变量设置避免密码输入
export PGPASSWORDyour_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, johnexample.com, 30);-- 插入多行
INSERT INTO users (username, email, age)
VALUES (jane_smith, janeexample.com, 28),(bob_johnson, bobexample.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, newexample.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)4TRIM(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-tablelogs --exclude-tabletmp_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并行导出与恢复
# 并行备份
pg_dump -j 8 -F d -f backup_dir dbname# 并行恢复
pg_restore -j 8 -d dbname backup_dir使用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.dump7. 性能优化与维护
数据库分析与优化
-- 更新统计信息帮助查询优化器
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集成开发体验DBApgAdmin官方工具功能全面初学者Navicat界面友好学习曲线低服务器管理phpPgAdminWeb访问无需客户端安装
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性能优化指南
