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

字符串函数

函数描述示例结果
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.pswp.cn/news/915290.shtml
繁体地址，请注明出处：http://hk.pswp.cn/news/915290.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

SpringBoot项目部署至云服务器

SpringBoot项目部署至云服务器

目录 一、后端项目部署 1、修改配置文件 2、清理打包缓存&#xff0c;打jar包&#xff08;两种方式二选一&#xff09; 自动打包 手动打包 打包成功状态 3、将jar包导入宿主机上 jar包位置 jar包上传 jar包运行 浏览器测试 二、前端代码 docker搭建nginx的基本步骤 打…
阅读更多...
Agent-S：重新定义下一代 AI 智能体开发框架

Agent-S：重新定义下一代 AI 智能体开发框架

Agent-S&#xff1a;重新定义下一代 AI 智能体开发框架 —— 探索 simular-ai 的开源革命 引言 2025 年&#xff0c;AI 智能体&#xff08;Agent&#xff09;技术正从概念走向产业核心。从自动化工作流到复杂决策系统&#xff0c;开发者亟需更高效的工具链。在这一背景下&am…
阅读更多...
保持视频二维码不变，如何更新视频内容，节省物料印刷成本

保持视频二维码不变，如何更新视频内容，节省物料印刷成本

保持视频二维码不变&#xff0c;如何更新视频内容&#xff0c;节省物料印刷成本&#xff1f; 视频替换功能&#xff0c;是指在保持视频二维码不变、视频观看地址不变、视频调用代码不变的情况下替换视频内容&#xff0c;从而节省用户印刷物料的成本&#xff0c;满足用户更新视…
阅读更多...
flutter项目调试问题小结

flutter项目调试问题小结

背景 目标是用android studio flutter 跑hello world 下载 android studio 我下载的是2024.3.2.15版本 最新版下载首页就能下&#xff1a;下载 Android Studio 和应用工具 - Android 开发者 | Android Developers 历史版本可在归档列表下载&#xff1a;Android Studio…
阅读更多...
明细列表，明细grid中的默认按钮失效，配置按钮失效

明细列表，明细grid中的默认按钮失效，配置按钮失效

明细列表&#xff0c;明细grid中的默认按钮失效&#xff0c;配置按钮失效原因&#xff1a;采用通配的写法导致的默认按钮失效if(menuDetails){menuDetails.forEach((item) > {const { name, menu_detail_columns, menu_detail_buttons, save_url} item;this.set(${name}Gri…
阅读更多...
Matplotlib 30分钟精通

Matplotlib 30分钟精通

📊 Matplotlib 30分钟精通计划（完整版含输出） ⏰ 时间分配 5分钟：Matplotlib基础概念和简单图表 10分钟：常用图表类型详解 10分钟：图表美化和定制 5分钟：综合实战练习 📚 第一部分：Matplotlib基础概念 (5分钟) 1. 什么是Matplotlib？ import matplotlib.pyplot a…
阅读更多...
7月19日 暴雨蓝色预警：全国多地迎强降雨，需防范次生灾害

7月19日 暴雨蓝色预警：全国多地迎强降雨，需防范次生灾害

中央气象台7月19日10时继续发布暴雨蓝色预警，预计未来24小时（19日14时至20日14时），我国多地将迎来大到暴雨，局地甚至出现大暴雨，并伴有短时强降水、雷暴大风等强对流天气，需加强防范。 强降雨覆盖范围广，多地需警惕极端降水 此次降雨影响范围广泛，涉及华北、华南、西…
阅读更多...
Redis学习-05Redis基本数据结构

Redis学习-05Redis基本数据结构

Redis 数据结构 String 字符串 基本命令表命令执行效果时间复杂度set key value [key value…]设置 key 的值是 valueO(k), k 是键个数get key获取 key 的值O(1)del key [key …]删除指定的 keyO(k), k 是键个数mset key value [key value …]批量设置指定的 key 和 valueO(k),…
阅读更多...
开启modbus tcp模拟调试

开启modbus tcp模拟调试

1、新建modbus tcp服务器 ‌功能差异‌‌客户端功能‌&#xff1a; 生成并发送Modbus请求报文&#xff08;如功能码03读取寄存器&#xff09;。‌‌ 解析服务器响应数据&#xff0c;实现远程监控或控制。‌‌ ‌服务器端功能‌&#xff1a; 监听默认端口&#xff08;如502&…
阅读更多...
昇思+香橙派 AI 开发实践：DeepSeek 全流程指南（基于 openEuler）

昇思+香橙派 AI 开发实践：DeepSeek 全流程指南（基于 openEuler）

一、 环境准备 1. 镜像烧录 镜像烧录可以在任何操作系统内执⾏&#xff0c;这⾥以在Windows系统为例&#xff0c;使用balenaEtcher⼯具&#xff0c;快速烧录镜像到Micro SD卡中。 本章节所需的软/硬件如下&#xff1a; 软件相关&#xff1a;balenaEtcher制卡⼯具、openEul…
阅读更多...
AI生成邮件发送脚本（带附件/HTML排版）与定时爬取网站→邮件通知（价格监控原型）

AI生成邮件发送脚本（带附件/HTML排版）与定时爬取网站→邮件通知（价格监控原型）

想象一下&#xff1a;每天早晨咖啡还没喝完&#xff0c;你的邮箱就自动收到了心仪商品的最新价格&#xff1b;重要报告准时带着专业排版的附件发送到客户手中——这一切不需要你手动操作。本文将用不到100行代码带你实现这两个自动化神器&#xff01; 一、为什么我们需要自动化…
阅读更多...
【vLLM 学习】Encoder Decoder Multimodal

【vLLM 学习】Encoder Decoder Multimodal

vLLM 是一款专为大语言模型推理加速而设计的框架&#xff0c;实现了 KV 缓存内存几乎零浪费&#xff0c;解决了内存管理瓶颈问题。 更多 vLLM 中文文档及教程可访问 →https://vllm.hyper.ai/ *在线运行 vLLM 入门教程&#xff1a;零基础分步指南 源码 examples/offline_inf…
阅读更多...
【MySQL笔记】视图

【MySQL笔记】视图

目录一、什么是视图&#xff1f;二、使用视图的优势三、视图的创建与使用四、不能更新视图的场景五、删除视图六、总结一、什么是视图&#xff1f; 视图&#xff08;View&#xff09;是一种虚拟表&#xff0c;不存储实际数据&#xff0c;而是通过执行预定义的查询动态生成数据…
阅读更多...
【RK3576】【Android14】分区划分

【RK3576】【Android14】分区划分

获取更多相关的【RK3576】【Android14】驱动开发&#xff0c;可收藏系列博文&#xff0c;持续更新中&#xff1a; 【RK3576】Android 14 驱动开发实战指南
阅读更多...
Datawhale 25年7月组队学习coze-ai-assistant Task1学习笔记：动手实践第一个AI Agent—英伦生活口语陪练精灵

Datawhale 25年7月组队学习coze-ai-assistant Task1学习笔记：动手实践第一个AI Agent—英伦生活口语陪练精灵

Chap1 了解AI工作流 1.1什么是工作流 工作流 就像是一条流水线&#xff0c;把复杂的任务拆分成多个简单的步骤&#xff0c;每一步都有明确的目标和流程。1.2智能体和工作流的区别 智能体&#xff08;AI Agent&#xff09; **是什么 &#xff1a;**智能体是一个自动化的“助手”…
阅读更多...
Webpack插件开发深度指南：从原理到实战

Webpack插件开发深度指南：从原理到实战

Webpack插件是前端工程化的核心引擎&#xff0c;本文将带你深入插件开发全流程&#xff0c;实现一个功能完整的资源清单插件&#xff0c;并揭示Tapable事件系统的核心原理。 一、Webpack插件机制解析 1.1 插件架构核心&#xff1a;Tapable事件系统 Webpack基于Tapable构建了…
阅读更多...
2、Redis持久化详解

2、Redis持久化详解

Redis持久化详解 文章目录 Redis持久化详解 前言 RDB和AOF的区别 RDB和AOF的优缺点 Redis 持久化配置 1、RDB持久化配置 2、AOF持久化配置（尝试修复会删除aof文件内容） 3、AOF 重写功能 新增知识点： 新增知识点： 前言 Redis是一种高级 key-value 型的NoSQL数据库。它跟mem…
阅读更多...
curl 命令详解

curl 命令详解

curl 命令的 -d/–data 和 --data-urlencode 的区别 curl 命令的 -d/–data 和 --data-urlencode 都用于发送 HTTP POST 请求的数据&#xff0c;但关键区别在于 是否自动对数据进行 URL 编码。以下是详细对比&#xff1a; curl 命令的 -d/--data 和 --data-urlencode 都用于发送…
阅读更多...
ubuntu下好用的录屏工具

ubuntu下好用的录屏工具

以下是 vokoscreen 的安装教程&#xff0c;适用于 Linux 系统。vokoscreen 是一款简单易用的屏幕录制工具&#xff0c;支持录制屏幕、摄像头和音频。 安装 vokoscreen vokoscreen 提供了多种安装方式&#xff0c;包括通过包管理器、Deb 包或 AppImage 文件。 方法 1&#xf…
阅读更多...
笔试大题20分值（用两个栈实现队列）

笔试大题20分值（用两个栈实现队列）

目录前言一、原题二、解题思路三、代码实现&#xff08;c/c&#xff09;C语言代码C代码实现结语前言 目前博主在处于秋招求职的关键时期&#xff0c;在暑假这段时间会频繁更新博客&#xff0c;想在暑假期间把一些常考的面试和笔试题过一下&#xff0c;利用这两个月沉淀一下技术…
阅读更多...
最新文章

Copyright @ 2022~2023