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
英文地址，請注明出處：http://en.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