最近連續(xù)有好幾個剛接觸PostgreSQL的朋友跑來問我同一個問題PG database相關(guān)命令到底該怎么記這東西和MySQL不一樣命令散落在shell、psql、系統(tǒng)視圖里查個東西不知道查哪。這篇就把我實際用下來最頻繁、最容易繞暈的PG命令完整梳理一遍覆蓋連接、日常操作、信息查詢、備份恢復、權(quán)限、性能診斷和維護不光給命令還把每類命令背后的設(shè)計邏輯和踩過的坑一并講清楚。1. 先把命令體系捋清楚PG的命令其實分三個入口很多人在PG上覺得命令又多又散根本原因不是命令量大而是沒有意識到PG的命令分布在三個完全不同的入口里。三個入口搞清楚了后面所有的記憶負擔都會小一大截。第一個入口是操作系統(tǒng)層面的命令行工具。PG安裝完以后bin目錄下會有一批以pg_開頭或者獨立命名的可執(zhí)行文件psql、createdb、dropdb、pg_dump、pg_restore、pg_basebackup、pg_ctl、pg_controldata這種。這一層命令的特點是你在Linux終端或者Windows的cmd里直接敲它們負責的是對數(shù)據(jù)庫實例整體做操作比如創(chuàng)建庫、連接庫、備份、初始化實例。寫自動化腳本的時候打交道的主要是這一層。第二個入口是psql交互終端里的元命令。當你已經(jīng)用psql連上數(shù)據(jù)庫輸入以反斜杠\開頭的指令比如\l、\dt、\d、\x、\timing。這些命令不是SQL是psql這個客戶端程序自己提供的快捷鍵本質(zhì)上是幫你拼SQL查系統(tǒng)表。很多新手容易在這里迷路因為文檔里一提到命令就給你一堆反斜杠你以為這些是PG的SQL語法其實它們只活在psql里。第三個入口才是標準SQL包括CREATE TABLE、SELECT、INSERT、UPDATE、GRANT、VACUUM這類。這才是真正對數(shù)據(jù)庫內(nèi)容生效的東西在任何PG客戶端、任何語言驅(qū)動里都能執(zhí)行只是有些命令的名字會讓你誤以為它不是SQL比如VACUUM、ANALYZE、CHECKPOINT它們確實是SQL命令只是日常用得少。1.1 三個入口怎么配合使用我日常的工作流可以概括成一句話shell層做連接和備份psql層做快速瀏覽SQL層做真正的操作。舉個例子要排查一個線上庫為什么慢我會在shell里執(zhí)行psql連上去然后用psql的\x開啟縱向顯示再用SQL去查pg_stat_activity視圖。整個過程三個入口都會用到誰也不會替代誰。你如果只會其中一個入口就好比只會用手機上的計算器不會用電腦Excel活也能干但效率差很多。還有一件事容易被忽略psql元命令其實是可以傳參數(shù)的。比如\d requests表名后面可以跟上表名的模糊匹配\dt public.*這樣的寫法也支持通配符。你不需要精確記住表名單詞先用\d通過模糊查詢找到想看的表再按回車細化信息這個習慣比死記硬背命令高效得多。1.2 和MySQL的習慣差異端口、大小寫、幫助系統(tǒng)從MySQL轉(zhuǎn)過來的人第一個坑就是端口。MySQL默認3306PG默認5432用習慣MySQL的人寫連接命令端口經(jīng)常填錯報錯以后第一反應(yīng)是服務(wù)沒起來實際上服務(wù)好好的只是你連錯了地方。第二個坑是密碼選項。MySQL的-p是指輸入密碼PG里的-p是指端口。PG里輸入密碼用的是-W選項或者等它提示。如果你照著MySQL的習慣敲psql -p xxxPG會把你輸入的內(nèi)容當成端口號解析要么報數(shù)字格式錯誤要么直接連接失敗。這個真的是高頻翻車點。第三個坑是大小寫敏感。PG對不帶雙引號的標識符會強制轉(zhuǎn)為小寫。你建表時寫CREATE TABLE Users實際存儲的名是users如果你真的想要一個大寫開頭的表名必須寫CREATE TABLE Users而且之后每次查詢都得帶雙引號寫Users。這個設(shè)計和MySQL的寬松策略完全相反SQL看起來一樣行為卻完全不同。至于幫助系統(tǒng)psql里輸入?可以看所有元命令的幫助輸入\help SELECT可以看某個SQL語句的語法輸入\h可以列出所有SQL命令。這套幫助系統(tǒng)比很多圖形化工具還好用強烈建議養(yǎng)成先help再動手的習慣。1.3 搭建自己的速查路徑在學習階段不需要把每個命令背下來但是腦子里要有一張索引表想創(chuàng)建數(shù)據(jù)庫去哪查、想改密碼去哪查、想備份去哪查、想殺會話去哪查。每類問題只需要記住一個入口和兩三個關(guān)鍵命令就夠了剩下的相信?、\h和系統(tǒng)視圖。我自己在服務(wù)器上長期保存著一個筆記文件里面分了四塊日常管理命令、診斷查詢SQL、備份恢復命令、權(quán)限模板。每個星期都會往里補充一條新學到的東西。半年下來這個文件幾乎成了我的PG速查手冊。你自己也完全可以用同樣的方式積累這篇文章里的所有命令都可以作為前一百條內(nèi)容。2. 連接、建庫與日常增刪改查先把活干起來2.1 psql連接命令的完整參數(shù)與常見報錯連接PG最基礎(chǔ)的一條命令長這樣psql -h 127.0.0.1 -p 5432 -U myuser -d mydb-h后面是數(shù)據(jù)庫地址-p是端口-U是用戶名-d是數(shù)據(jù)庫名。不加-d的話PG會用你當前的操作系統(tǒng)用戶名作為默認庫名去連接經(jīng)常報database xxx does not exist很多新手在這個報錯上卡了很久實際上就是因為沒指定庫名。如果想要密碼自動提示加一個-W參數(shù)。如果服務(wù)器配置了本地信任認證不需要密碼那個別糾結(jié)。還有一點要注意psql的參數(shù)順序是寬松的-d mydb -U myuser和-U myuser -d mydb完全等價這點比很多命令行工具友好。連接過程中常見的報錯我整理了一下報錯信息常見原因處理方向could not connect to server端口沒通、防火墻攔截、服務(wù)沒起來檢查pg_isready、netstat、防火墻database xxx does not exist沒指定-d或庫名拼錯確認庫名或用\l查看password authentication failed密碼錯誤、認證方式不是md5/scram檢查pg_hba.conf認證方法no pg_hba.conf entry客戶端IP不被允許在pg_hba.conf增加對應(yīng)網(wǎng)段規(guī)則并reload以上這些報錯文本雖然不是完整的原文但特征基本一致。遇到問題先看最后一行PG的錯誤信息其實寫得很清楚只是很多人喜歡忽略英文直接抄日志問別人。連接成功以后psql提示符會變成mydb#或者mydb。前者代表你有超級用戶權(quán)限后者代表普通用戶。這個細微差別其實很重要看到你就該意識到很多管理命令會執(zhí)行失敗。連上以后第一時間做的事我建議執(zhí)行兩條SELECT version(); SELECT current_database(), current_user, inet_server_addr();這兩條可以確認你連到了哪個版本、哪個庫、哪個用戶、哪臺服務(wù)器。特別是多環(huán)境混用的時候這個操作能救你一條命。2.2 創(chuàng)建庫與表結(jié)構(gòu)的常用SQL創(chuàng)建一個數(shù)據(jù)庫SQL方式CREATE DATABASE mydb ENCODING UTF8;也可以在shell里直接createdb -h 127.0.0.1 -U myuser mydb刪除數(shù)據(jù)庫DROP DATABASE mydb;刪除之前建議先確認沒有連接占用否則會報being accessed by other users。我遇到過好幾次開發(fā)環(huán)境里一堆IDEA的數(shù)據(jù)庫面板自動掛著連接DROP DATABASE怎么都執(zhí)行不了最后是把那些連接斷開才成功。建表就沒什么特異功能了CREATE TABLE requests ( id BIGSERIAL PRIMARY KEY, user_id INT NOT NULL, api_path TEXT NOT NULL, status_code INT DEFAULT 200, created_at TIMESTAMPTZ DEFAULT now() );BIGSERIAL是PG里常用的自增方式底層是一個序列。也可以直接用GENERATED AS IDENTITY這是SQL標準做法PG 12以后都支持得不錯。二者差別不是很大但新項目我個人更推薦GENERATED AS IDENTITY語義更清晰也更方便后續(xù)遷移。查看表結(jié)構(gòu)psql里直接\d requests它會顯示每個字段的類型、默認值、約束還會列出索引、外鍵、觸發(fā)器等附屬結(jié)構(gòu)比寫SQL查information_schema快得多。想看到更細的存儲屬性用\d requests2.3 psql里高頻元命令速查連接數(shù)據(jù)庫這個動作在psql里用\c\c otherdb切換用戶/庫/端口也可以一步到位\c mydb myuser 127.0.0.1 5432列出所有數(shù)據(jù)庫用\l列出當前庫所有表用\dt想帶上表注釋和大小用\dt。看一個查詢結(jié)果如果列太多被截斷先開啟擴展顯示\x on開啟后每一行字段會豎著排同一條SQL再執(zhí)行一遍就清爽很多。這個模式在排查問題時幾乎是必開的。日常寫SQL的時候我很依賴兩個元命令\timing on \e\timing會打印每次SQL的執(zhí)行耗時不用自己手動記秒表。\e會打開默認編輯器通常是vi適合寫多行SQL寫完退出自動執(zhí)行。在有臨時復雜查詢的場景下這個組合比一條條敲要舒服太多。執(zhí)行外部SQL腳本用\i\i /path/to/script.sql導出查詢結(jié)果到文件在psql里可以用\o或者直接在shell里重定向psql -U myuser -d mydb -c SELECT * FROM requests; output.csv3. 信息查詢命令排查問題時的救火地圖日常運維中你真正需要手寫SQL去查的地方集中在幾個系統(tǒng)視圖和統(tǒng)計視圖里。這一節(jié)的內(nèi)容可以說是PG數(shù)據(jù)庫相關(guān)命令里最有價值的部分因為大部分故障的第一現(xiàn)場都是從這里找到的。3.1 pg_catalog 與 information_schema 的選擇PG里有兩個元數(shù)據(jù)來源information_schema是SQL標準視圖跨數(shù)據(jù)庫通用pg_catalog是PG自身系統(tǒng)表視圖信息更全更底層。查詢表結(jié)構(gòu)、列名、約束這類通用信息用information_schema比較穩(wěn)。但查詢物理存儲、膨脹情況、邏輯復制狀態(tài)這些PG特性信息必須用pg_catalog下的視圖比如pg_class、pg_namespace、pg_attribute、pg_stat_activity等。從使用頻率來看我80%的查詢都在pg_catalog體系里。information_schema更多是給報表系統(tǒng)或者圖形化工具用的標準化接口。查看一個庫下所有表和對應(yīng)的注釋SELECT c.relname AS table_name, obj_description(c.oid) AS comment FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname public AND c.relkind IN (r, p) ORDER BY c.relname;這個查詢雖然啰嗦但理解了pg_class和pg_namespace之后PG的系統(tǒng)表結(jié)構(gòu)就通了一半。3.2 當前連接、鎖等待與活動查詢排查數(shù)據(jù)庫突然變慢最常用的視圖絕對是pg_stat_activity。一條查詢跑遍所有問題SELECT pid, usename, datname, client_addr, state, wait_event_type, wait_event, query_start, now() - query_start AS runtime, query FROM pg_stat_activity WHERE state ! idle ORDER BY runtime DESC;看到state字段是active的進程結(jié)合query_start算一下運行時長基本就能判斷是正常慢查詢還是異常堵塞。如果大量會話卡在某個狀態(tài)且wait_event帶有Lock字眼說明存在鎖等待。鎖定位到具體是哪個進程堵了之后可以用這個函數(shù)查鏈條SELECT pg_blocking_pids(4567);把4567替換成你看到的被阻塞pid返回的結(jié)果就是堵住它的pid列表。然后再連查一層直到源頭。終止一個會話SELECT pg_cancel_backend(4567);取消不掉就用SELECT pg_terminate_backend(4567);兩者的區(qū)別是cancel是溫和取消當前查詢事務(wù)本身可以被應(yīng)用捕獲錯誤后回滾比較適合對付慢查詢terminate是直接斷開進程會把整個客戶端連接殺掉適合對付僵死連接。生產(chǎn)環(huán)境用terminate之前最好先確認這個連接是誰的、能不能斷。3.3 表大小、膨脹與索引使用統(tǒng)計磁盤空間到底被哪些表吃掉了很多人第一反應(yīng)是去看文件目錄其實一條SQL就出來了SELECT nspname || . || relname AS relation, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS table_size, pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind r AND n.nspname public ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;pg_size_pretty會把字節(jié)數(shù)換算成人類的可讀格式這個函數(shù)在你處理空間問題時是高頻使用的。檢查表的膨脹情況看pg_stat_user_tablesSELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(100.0 * n_dead_tup / n_live_tup, 1) ELSE 0 END AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 20;n_dead_tup代表死元組數(shù)量這個值長期偏高說明vacuum沒跟上表膨脹風險很高。索引命中率查詢SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, pg_size_pretty(pg_relation_size(indexrelname)) AS index_size FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;idx_scan長期為0的索引基本可以視為無效索引但注意先確認沒有唯一性約束不能只看使用率就刪。這類統(tǒng)計視圖是PG排查性能問題時最重要的地圖把它們當成常備工具勝過去背各種優(yōu)化口訣。4. 備份與恢復生產(chǎn)環(huán)境最常翻車的命令組合備份恢復這塊網(wǎng)上教程不少但大多數(shù)人只講了pg_dump一條命令。這里我重點講的是命令怎么組合、恢復到另一個環(huán)境時有哪些隱藏的坑這些才是生產(chǎn)環(huán)境真正要命的東西。4.1 pg_dump的格式選擇與并行參數(shù)pg_dump最基礎(chǔ)的用法pg_dump -h 127.0.0.1 -p 5432 -U myuser -d mydb -f mydb.sql這會生成一個純SQL文本文件里面是建表語句和數(shù)據(jù)插入語句。這種格式最大的好處是人眼可讀適合小庫、跨小版本遷移。但大庫用純SQL恢復特別慢并且中途出錯很難續(xù)跑。我更推薦custom格式pg_dump -h 127.0.0.1 -U myuser -d mydb -F c -f mydb.dump-F c 生成的是一種壓縮的二進制格式可以用pg_restore靈活控制恢復哪些對象。custom格式還支持并行恢復恢復大庫時速度快非常多。并行恢復pg_restore -h 127.0.0.1 -U myuser -d targetdb -j 4 mydb.dump-j 4代表開4個并發(fā)任務(wù)恢復。任務(wù)數(shù)建議不超過CPU核心數(shù)兩倍不然反而會因為資源競爭變慢。只備份數(shù)據(jù)不帶表結(jié)構(gòu)用pg_dump -a -F c -f data_only.dump只備份表結(jié)構(gòu)不帶數(shù)據(jù)用pg_dump -s -F c -f schema_only.dump這兩種方式在給測試環(huán)境搭建、生成對照庫時特別實用。4.2 pg_restore與psql回放的核心邏輯說清楚一個問題custom格式不能用psql直接回放必須用pg_restore。這是很多新手卡住的點。拿個大半天研究為什么psql讀dump文件報錯其實只是格式選錯了。一個常規(guī)的恢復流程# 先建目標庫如果還不存在 createdb -U myuser -h 127.0.0.1 targetdb # 恢復custom格式備份 pg_restore -h 127.0.0.1 -U myuser -d targetdb -j 4 mydb.dump如果dump里包含了建庫語句你可以用--create選項讓它先建庫再連庫恢復但前提是執(zhí)行pg_restore的用戶必須有建庫權(quán)限。查看一個dump文件里都包含了什么對象不用恢復就能列出pg_restore -l mydb.dump這個列表輸出很長可以配合grep只看某類對象比如只看函數(shù)pg_restore -l mydb.dump | grep FUNCTION如果你只想恢復其中一張表pg_restore -t public.requests -d targetdb mydb.dump4.3 恢復翻車現(xiàn)場權(quán)限、擴展、版本三大坑我實際在恢復dump到測試環(huán)境時遇到最多的問題歸納成三個。第一個坑是owner不一致。原庫的對象owner是prod_user目標環(huán)境里根本沒有這個用戶恢復時直接報錯。解決辦法是加上--no-ownerpg_restore --no-owner -d targetdb mydb.dump這個選項讓恢復后的對象owner統(tǒng)一變成當前執(zhí)行用戶規(guī)避掉一大部分權(quán)限問題。第二個坑是擴展缺依賴。比如表里有jsonb字段或者用了citext類型恢復時目標庫沒安裝對應(yīng)擴展就會報type does not exist。普通文本格式的dump里如果包含CREATE EXTENSION需要執(zhí)行者具有超級用戶權(quán)限否則也會被跳過。恢復之前先把該裝的核心擴展裝上CREATE EXTENSION IF NOT EXISTS citext; CREATE EXTENSION IF NOT EXISTS uuid-ossp;第三個坑是版本跨度過大。PG大版本之間的二進制格式不兼容pg_dump出來的dump文件最好在大版本內(nèi)恢復。跨大版本遷移建議先用低版本自帶的pg_dump導出再到高版本用pg_restore導入方向不能反。高版本導出的文件去低版本恢復基本都會失敗。還有一個很實用的細節(jié)給備份文件做完整性校驗恢復完以后立刻跑一遍行數(shù)抽查SELECT COUNT(*) FROM public.requests;這個動作成本不高但能快速發(fā)現(xiàn)數(shù)據(jù)中途丟失。5. 權(quán)限與角色命令從能跑到管得住數(shù)據(jù)庫能在本地跑起來和能在生產(chǎn)環(huán)境管得住之間的分界線就是權(quán)限命令。PG的權(quán)限體系比MySQL更細致但也因此容易踩到一些給了權(quán)限卻還是報錯的坑。5.1 角色與用戶的本質(zhì)區(qū)別在PG里角色ROLE和用戶USER本質(zhì)上是同一個東西。CREATE USER只是CREATE ROLE的一個便捷寫法區(qū)別僅在CREATE USER會默認帶著LOGIN屬性而CREATE ROLE默認不帶。CREATE ROLE app_user LOGIN PASSWORD xxx;等價于CREATE USER app_user PASSWORD xxx;查看當前實例下所有角色\du這個命令會列出角色名、屬性列表和成員關(guān)系一屏看全好人壞人。改密碼ALTER ROLE app_user WITH PASSWORD newpassword;角色繼承關(guān)系CREATE ROLE read_only; GRANT read_only TO app_user;app_user登錄后默認繼承read_only的權(quán)限。PG的權(quán)限檢查加上繼承特性理解起來需要一點疊加思維但實際使用中用繼承方式管理是很優(yōu)雅的。5.2 一組可以直接照抄的授權(quán)模板我根據(jù)實際場景整理了三個常用模板你可以直接搬走用。給一個只讀賬號CREATE ROLE read_only LOGIN PASSWORD xxx; GRANT CONNECT ON DATABASE mydb TO read_only; GRANT USAGE ON SCHEMA public TO read_only; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;注意這里GRANT CONNECT是讓你能連上這個庫GRANT USAGE ON SCHEMA是讓你能看到并使用這個schema下的對象。很多人只記得給表授權(quán)忘了給schema授權(quán)結(jié)果用戶連上庫之后一執(zhí)行SELECT就報permission denied for schema public。給一個讀寫賬號CREATE ROLE read_write LOGIN PASSWORD xxx; GRANT CONNECT ON DATABASE mydb TO read_write; GRANT USAGE, CREATE ON SCHEMA public TO read_write; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write; GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO read_write;ALTER DEFAULT PRIVILEGES這段很多新手會忽略它的作用是以后在這個schema里新建的表默認自動授權(quán)給指定角色。不加這個今天你給用戶授了權(quán)明天業(yè)務(wù)方新加了一張表用戶又查不了看起來就是權(quán)限神秘失效。只讀賬號順便給序列的usage是因為如果查詢要走某些帶序列的視圖或函數(shù)可能會被卡住。實際場景中遇到這類報錯就明白了。查看某個表當前權(quán)限用\dp tablename或者SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name tablename;5.3 權(quán)限問題上最容易忽略的三個場景第一個場景前面提到過的schema權(quán)限。很多團隊在給一個新賬號授權(quán)時列了一長串表自認為都覆蓋了結(jié)果用戶一登錄就報permission denied。問題不在表上而是USAGE ON SCHEMA沒給。這就像你給了別人一把房間鑰匙卻不讓對方進走廊。第二個場景函數(shù)執(zhí)行權(quán)限。PG里函數(shù)默認為PUBLIC可執(zhí)行但如果你在函數(shù)內(nèi)部訪問了其他表這個用戶必須同時擁有那些表的權(quán)限否則調(diào)用函數(shù)時依然會被拒絕。這個報錯藏得比較深往往不是在授權(quán)時發(fā)現(xiàn)而是應(yīng)用跑到一半才炸。第三個場景REVOKE之后還有殘留。想徹底收回一個用戶對某張表的所有權(quán)限不能只REVOKE一次需要把SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER全部回收REVOKE ALL PRIVILEGES ON TABLE public.requests FROM app_user;REVOKE ALL這種寫法一次清干凈不要分開寫。6. 性能診斷命令EXPLAIN、慢SQL與鎖等待性能診斷這一塊是你把PG從能跑帶到跑得快的核心。我會先把EXPLAIN怎么讀講透再說慢SQL定位三板斧最后說鎖等待處置。6.1 EXPLAIN ANALYZE到底怎么讀寫SQL的時候如果懷疑查詢慢直接在SQL前面加EXPLAIN ANALYZE比如EXPLAIN ANALYZE SELECT u.name, count(*) FROM requests r JOIN users u ON u.id r.user_id WHERE r.created_at now() - interval 7 days GROUP BY u.name;輸出會是一棵執(zhí)行計劃樹從縮進最深的節(jié)點開始看每一行都有三個關(guān)鍵數(shù)字actual time、rows、actual loops。actual time字段的第一個數(shù)字是啟動時間獲得第一行前的耗時第二個數(shù)字是整個過程耗時單位毫秒rows是實際掃描的行數(shù)要和EXPLAIN里預估的行數(shù)對比rows大比例偏差說明統(tǒng)計信息失效需要ANALYZE或者檢查索引判斷執(zhí)行計劃好壞最簡單的方法是找兩個詞Seq Scan和Index Scan。看到Seq Scan在表上掃了幾十萬行而你還期望它走索引那基本就是索引缺失、統(tǒng)計信息過期、或者SQL寫得讓優(yōu)化器沒法用索引?？匆粋€例子上面SQL如果輸出里顯示requests表做了Seq Scan且掃描了50萬行user表也做了Seq Scan那這個查詢大概率會慢。解決辦法一般是在requests.user_id和requests.created_at上建復合索引并確保統(tǒng)計信息是最新的。加索引的命令CREATE INDEX CONCURRENTLY idx_requests_user_created ON public.requests (user_id, created_at DESC);CONCURRENTLY關(guān)鍵字表示不阻塞寫操作。生產(chǎn)環(huán)境建索引時我強烈建議不要省略它雖然它會讓索引創(chuàng)建更慢且不能在事務(wù)里執(zhí)行但換來的是不打斷應(yīng)用寫入值得。6.2 慢SQL定位三板斧第一板斧是看pg_stat_activity找到正在跑的query和運行時長。這個前面已經(jīng)給過SQL區(qū)別是要記住它只看得到正在執(zhí)行的歷史慢SQL它不會記錄。第二板斧是安裝pg_stat_statements擴展。這個擴展是慢SQL統(tǒng)計的利器CREATE EXTENSION IF NOT EXISTS pg_stat_statements;安裝之后可能需要數(shù)據(jù)庫管理員在配置里先開啟shared_preload_libraries并重啟實例默認情況下有些環(huán)境已經(jīng)預加載了直接查詢即可。查詢執(zhí)行總次數(shù)多、總耗時長、平均耗時長的SQLSELECT queryid, calls, round(mean_exec_time::numeric, 2) AS avg_ms, round(total_exec_time::numeric, 2) AS total_ms, round(max_exec_time::numeric, 2) AS max_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;這個查詢會告訴你過去這段時間里到底哪幾條SQL在燃燒你的數(shù)據(jù)庫CPU和IO。有幾次線上事故最終定位到源頭就是pg_stat_statements里某條應(yīng)用生成的動態(tài)SQL沒有用上索引。第三板斧是開慢查詢?nèi)罩尽Ｐ薷呐渲肁LTER SYSTEM SET log_min_duration_statement 1000; SELECT pg_reload_conf();意思是執(zhí)行超過1000毫秒的SQL都會寫入日志文件。這個參數(shù)在生產(chǎn)環(huán)境非常有用改完后不用重啟reload即可生效。6.3 鎖等待的定位與處置命令鎖等待和慢查詢經(jīng)常伴生不是SQL本身慢而是卡在等鎖。定位步驟通常這樣走。第一步看pg_stat_activity里wait_event有沒有Lock字樣SELECT pid, state, wait_event_type, wait_event, query_start, query FROM pg_stat_activity WHERE wait_event_type Lock;第二步用pg_blocking_pids找到鎖源SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE wait_event_type Lock;第三步定位到源頭會話后用前面說過的pg_cancel_backend或者pg_terminate_backend處理。這里想特別提醒一件事lock_timeout這個參數(shù)值得在設(shè)計表結(jié)構(gòu)時就考慮。在長時間持鎖的DDL場景下給應(yīng)用會話設(shè)置SET lock_timeout 5s;可以讓等待鎖的會話在5秒后自動放棄而不是無限期掛在那里。很多數(shù)據(jù)庫無響應(yīng)事故就是被無限等鎖拖死的。7. 維護類命令避坑清單日常運維繞不開的幾件事最后這部分講幾個日常維護中繞不開的命令每個命令都有它典型的為什么和別亂動。7.1 VACUUM、ANALYZE與膨脹處理PG的并發(fā)控制靠MVCC刪除一行數(shù)據(jù)并不會立刻從物理文件里抹掉而是標記為死元組。這些死元組要靠VACUUM清理。VACUUM public.requests; VACUUM ANALYZE public.requests;普通VACUUM不鎖表可以在線跑。ANALYZE會更新統(tǒng)計信息優(yōu)化器等會兒執(zhí)行計劃就能更準確。兩者一起跑是常規(guī)操作。生產(chǎn)環(huán)境其實不必頻繁手動執(zhí)行因為PG默認開著autovacuum。但對一些超大表autovacuum可能來不及清理這時候可以在業(yè)務(wù)低谷手動跑一次。千萬注意不要輕易在生產(chǎn)環(huán)境用VACUUM FULLVACUUM FULL public.requests;VACUUM FULL會重寫整張表并且全程持鎖大表跑起來期間應(yīng)用寫入全部阻塞。我見過有人因為一張幾億行的表膨脹嚴重直接在高峰時段執(zhí)行VACUUM FULL結(jié)果整個業(yè)務(wù)停擺。正確的思路是優(yōu)先確認autovacuum配置正常實在需要物理收縮時才考慮VACUUM FULL而且放在維護窗口執(zhí)行或者直接通過新表遷移來替代。7.2 配置修改與生效方式ALTER SYSTEM與reload修改PG配置不建議直接去編輯postgresql.conf因為容易出錯。PG 9.4以后支持ALTER SYSTEMALTER SYSTEM SET max_connections 500;修改完后一部分參數(shù)需要reload生效SELECT pg_reload_conf();還有一部分參數(shù)必須重啟實例才生效。重要參數(shù)大致可以分兩類參數(shù)是否需要重啟典型場景max_connections重啟連接數(shù)不足shared_buffers重啟內(nèi)存緩沖不足work_mem無需會話級生效排序/哈希操作慢log_min_duration_statement無需reload慢SQL追蹤effective_cache_size無需reload優(yōu)化器選錯計劃work_mem有個坑值得單獨說它表示單個排序或哈希操作能使用的內(nèi)存不是整個查詢。如果你把它設(shè)置成1GB而并發(fā)50個會話都在排序瞬間就能吃掉50GB內(nèi)存。所以修改這類參數(shù)要結(jié)合并發(fā)量不能只看單次操作。7.3 連接數(shù)打滿時的急救流程連接數(shù)打滿是一種特別常見的故障表現(xiàn)為新連接報too many connections。急救步驟一般是這樣第一步看當前連接分布SELECT datname, usename, count(*) FROM pg_stat_activity GROUP BY datname, usename ORDER BY count(*) DESC;第二步殺掉明顯異常的連接。如果是一些應(yīng)用的重試連接堆積可以用pg_terminate_backend批量清理掉非關(guān)鍵連接。謹慎一點的做法是先把它們cancel了再看。第三步調(diào)整連接池的配置。生產(chǎn)環(huán)境應(yīng)用不應(yīng)該直連PG中間一定套一個連接池應(yīng)用層控制連接池上限PG端用pgbouncer或者proxysql做集中池化。如果沒接連接池max_connections再大也只是慢性死亡。這個屬于架構(gòu)問題但和命令使用的關(guān)聯(lián)很深。另一個維護命令是CHECKPOINTCHECKPOINT;它作用是把WAL緩沖區(qū)的內(nèi)容刷到數(shù)據(jù)文件讓崩潰恢復更快。平時不需要手動執(zhí)行但如果在做某些備份或要關(guān)閉實例之前執(zhí)行一次可以縮短恢復時間。這也算是我見過的命令存在但很少被正確使用的一類。最后說個經(jīng)驗這篇文章列的命令我在真實項目里都實際用過。如果你現(xiàn)在剛起步我的建議是先別急著把每條命令背下來動手執(zhí)行一遍然后按自己的使用場景把這個速查文檔沉淀下來。把五個場景練熟就夠了連接、查詢信息、備份恢復、權(quán)限、慢查詢排查。等這五塊熟了剩下的都是隨用隨查。另外想強調(diào)一個總的習慣凡是涉及生產(chǎn)環(huán)境的命令先在一個空庫里試一遍。特別是DROP、TRUNCATE、VACUUM FULL這類重量級操作試完你才會對它的輸出和影響有切身體感。數(shù)據(jù)庫命令的價值不在知道而在用對。 PostgreSQL常用命令全解析：從連接到備份恢復與性能診斷
最近連續(xù)有好幾個剛接觸PostgreSQL的朋友跑來問我同一個問題PG database相關(guān)命令到底該怎么記這東西和MySQL不一樣命令散落在shell、psql、系統(tǒng)視圖里查個東西不知道查哪。這篇就把我實際用下來最頻繁、最容易繞暈的PG命令完整梳理一遍覆蓋連接、日常操作、信息查詢、備份恢復、權(quán)限、性能診斷和維護不光給命令還把每類命令背后的設(shè)計邏輯和踩過的坑一并講清楚。1. 先把命令體系捋清楚PG的命令其實分三個入口很多人在PG上覺得命令又多又散根本原因不是命令量大而是沒有意識到PG的命令分布在三個完全不同的入口里。三個入口搞清楚了后面所有的記憶負擔都會小一大截。第一個入口是操作系統(tǒng)層面的命令行工具。PG安裝完以后bin目錄下會有一批以pg_開頭或者獨立命名的可執(zhí)行文件psql、createdb、dropdb、pg_dump、pg_restore、pg_basebackup、pg_ctl、pg_controldata這種。這一層命令的特點是你在Linux終端或者Windows的cmd里直接敲它們負責的是對數(shù)據(jù)庫實例整體做操作比如創(chuàng)建庫、連接庫、備份、初始化實例。寫自動化腳本的時候打交道的主要是這一層。第二個入口是psql交互終端里的元命令。當你已經(jīng)用psql連上數(shù)據(jù)庫輸入以反斜杠\開頭的指令比如\l、\dt、\d、\x、\timing。這些命令不是SQL是psql這個客戶端程序自己提供的快捷鍵本質(zhì)上是幫你拼SQL查系統(tǒng)表。很多新手容易在這里迷路因為文檔里一提到命令就給你一堆反斜杠你以為這些是PG的SQL語法其實它們只活在psql里。第三個入口才是標準SQL包括CREATE TABLE、SELECT、INSERT、UPDATE、GRANT、VACUUM這類。這才是真正對數(shù)據(jù)庫內(nèi)容生效的東西在任何PG客戶端、任何語言驅(qū)動里都能執(zhí)行只是有些命令的名字會讓你誤以為它不是SQL比如VACUUM、ANALYZE、CHECKPOINT它們確實是SQL命令只是日常用得少。1.1 三個入口怎么配合使用我日常的工作流可以概括成一句話shell層做連接和備份psql層做快速瀏覽SQL層做真正的操作。舉個例子要排查一個線上庫為什么慢我會在shell里執(zhí)行psql連上去然后用psql的\x開啟縱向顯示再用SQL去查pg_stat_activity視圖。整個過程三個入口都會用到誰也不會替代誰。你如果只會其中一個入口就好比只會用手機上的計算器不會用電腦Excel活也能干但效率差很多。還有一件事容易被忽略psql元命令其實是可以傳參數(shù)的。比如\d requests表名后面可以跟上表名的模糊匹配\dt public.*這樣的寫法也支持通配符。你不需要精確記住表名單詞先用\d通過模糊查詢找到想看的表再按回車細化信息這個習慣比死記硬背命令高效得多。1.2 和MySQL的習慣差異端口、大小寫、幫助系統(tǒng)從MySQL轉(zhuǎn)過來的人第一個坑就是端口。MySQL默認3306PG默認5432用習慣MySQL的人寫連接命令端口經(jīng)常填錯報錯以后第一反應(yīng)是服務(wù)沒起來實際上服務(wù)好好的只是你連錯了地方。第二個坑是密碼選項。MySQL的-p是指輸入密碼PG里的-p是指端口。PG里輸入密碼用的是-W選項或者等它提示。如果你照著MySQL的習慣敲psql -p xxxPG會把你輸入的內(nèi)容當成端口號解析要么報數(shù)字格式錯誤要么直接連接失敗。這個真的是高頻翻車點。第三個坑是大小寫敏感。PG對不帶雙引號的標識符會強制轉(zhuǎn)為小寫。你建表時寫CREATE TABLE Users實際存儲的名是users如果你真的想要一個大寫開頭的表名必須寫CREATE TABLE Users而且之后每次查詢都得帶雙引號寫Users。這個設(shè)計和MySQL的寬松策略完全相反SQL看起來一樣行為卻完全不同。至于幫助系統(tǒng)psql里輸入?可以看所有元命令的幫助輸入\help SELECT可以看某個SQL語句的語法輸入\h可以列出所有SQL命令。這套幫助系統(tǒng)比很多圖形化工具還好用強烈建議養(yǎng)成先help再動手的習慣。1.3 搭建自己的速查路徑在學習階段不需要把每個命令背下來但是腦子里要有一張索引表想創(chuàng)建數(shù)據(jù)庫去哪查、想改密碼去哪查、想備份去哪查、想殺會話去哪查。每類問題只需要記住一個入口和兩三個關(guān)鍵命令就夠了剩下的相信?、\h和系統(tǒng)視圖。我自己在服務(wù)器上長期保存著一個筆記文件里面分了四塊日常管理命令、診斷查詢SQL、備份恢復命令、權(quán)限模板。每個星期都會往里補充一條新學到的東西。半年下來這個文件幾乎成了我的PG速查手冊。你自己也完全可以用同樣的方式積累這篇文章里的所有命令都可以作為前一百條內(nèi)容。2. 連接、建庫與日常增刪改查先把活干起來2.1 psql連接命令的完整參數(shù)與常見報錯連接PG最基礎(chǔ)的一條命令長這樣psql -h 127.0.0.1 -p 5432 -U myuser -d mydb-h后面是數(shù)據(jù)庫地址-p是端口-U是用戶名-d是數(shù)據(jù)庫名。不加-d的話PG會用你當前的操作系統(tǒng)用戶名作為默認庫名去連接經(jīng)常報database xxx does not exist很多新手在這個報錯上卡了很久實際上就是因為沒指定庫名。如果想要密碼自動提示加一個-W參數(shù)。如果服務(wù)器配置了本地信任認證不需要密碼那個別糾結(jié)。還有一點要注意psql的參數(shù)順序是寬松的-d mydb -U myuser和-U myuser -d mydb完全等價這點比很多命令行工具友好。連接過程中常見的報錯我整理了一下報錯信息常見原因處理方向could not connect to server端口沒通、防火墻攔截、服務(wù)沒起來檢查pg_isready、netstat、防火墻database xxx does not exist沒指定-d或庫名拼錯確認庫名或用\l查看password authentication failed密碼錯誤、認證方式不是md5/scram檢查pg_hba.conf認證方法no pg_hba.conf entry客戶端IP不被允許在pg_hba.conf增加對應(yīng)網(wǎng)段規(guī)則并reload以上這些報錯文本雖然不是完整的原文但特征基本一致。遇到問題先看最后一行PG的錯誤信息其實寫得很清楚只是很多人喜歡忽略英文直接抄日志問別人。連接成功以后psql提示符會變成mydb#或者mydb。前者代表你有超級用戶權(quán)限后者代表普通用戶。這個細微差別其實很重要看到你就該意識到很多管理命令會執(zhí)行失敗。連上以后第一時間做的事我建議執(zhí)行兩條SELECT version(); SELECT current_database(), current_user, inet_server_addr();這兩條可以確認你連到了哪個版本、哪個庫、哪個用戶、哪臺服務(wù)器。特別是多環(huán)境混用的時候這個操作能救你一條命。2.2 創(chuàng)建庫與表結(jié)構(gòu)的常用SQL創(chuàng)建一個數(shù)據(jù)庫SQL方式CREATE DATABASE mydb ENCODING UTF8;也可以在shell里直接createdb -h 127.0.0.1 -U myuser mydb刪除數(shù)據(jù)庫DROP DATABASE mydb;刪除之前建議先確認沒有連接占用否則會報being accessed by other users。我遇到過好幾次開發(fā)環(huán)境里一堆IDEA的數(shù)據(jù)庫面板自動掛著連接DROP DATABASE怎么都執(zhí)行不了最后是把那些連接斷開才成功。建表就沒什么特異功能了CREATE TABLE requests ( id BIGSERIAL PRIMARY KEY, user_id INT NOT NULL, api_path TEXT NOT NULL, status_code INT DEFAULT 200, created_at TIMESTAMPTZ DEFAULT now() );BIGSERIAL是PG里常用的自增方式底層是一個序列。也可以直接用GENERATED AS IDENTITY這是SQL標準做法PG 12以后都支持得不錯。二者差別不是很大但新項目我個人更推薦GENERATED AS IDENTITY語義更清晰也更方便后續(xù)遷移。查看表結(jié)構(gòu)psql里直接\d requests它會顯示每個字段的類型、默認值、約束還會列出索引、外鍵、觸發(fā)器等附屬結(jié)構(gòu)比寫SQL查information_schema快得多。想看到更細的存儲屬性用\d requests2.3 psql里高頻元命令速查連接數(shù)據(jù)庫這個動作在psql里用\c\c otherdb切換用戶/庫/端口也可以一步到位\c mydb myuser 127.0.0.1 5432列出所有數(shù)據(jù)庫用\l列出當前庫所有表用\dt想帶上表注釋和大小用\dt。看一個查詢結(jié)果如果列太多被截斷先開啟擴展顯示\x on開啟后每一行字段會豎著排同一條SQL再執(zhí)行一遍就清爽很多。這個模式在排查問題時幾乎是必開的。日常寫SQL的時候我很依賴兩個元命令\timing on \e\timing會打印每次SQL的執(zhí)行耗時不用自己手動記秒表。\e會打開默認編輯器通常是vi適合寫多行SQL寫完退出自動執(zhí)行。在有臨時復雜查詢的場景下這個組合比一條條敲要舒服太多。執(zhí)行外部SQL腳本用\i\i /path/to/script.sql導出查詢結(jié)果到文件在psql里可以用\o或者直接在shell里重定向psql -U myuser -d mydb -c SELECT * FROM requests; output.csv3. 信息查詢命令排查問題時的救火地圖日常運維中你真正需要手寫SQL去查的地方集中在幾個系統(tǒng)視圖和統(tǒng)計視圖里。這一節(jié)的內(nèi)容可以說是PG數(shù)據(jù)庫相關(guān)命令里最有價值的部分因為大部分故障的第一現(xiàn)場都是從這里找到的。3.1 pg_catalog 與 information_schema 的選擇PG里有兩個元數(shù)據(jù)來源information_schema是SQL標準視圖跨數(shù)據(jù)庫通用pg_catalog是PG自身系統(tǒng)表視圖信息更全更底層。查詢表結(jié)構(gòu)、列名、約束這類通用信息用information_schema比較穩(wěn)。但查詢物理存儲、膨脹情況、邏輯復制狀態(tài)這些PG特性信息必須用pg_catalog下的視圖比如pg_class、pg_namespace、pg_attribute、pg_stat_activity等。從使用頻率來看我80%的查詢都在pg_catalog體系里。information_schema更多是給報表系統(tǒng)或者圖形化工具用的標準化接口。查看一個庫下所有表和對應(yīng)的注釋SELECT c.relname AS table_name, obj_description(c.oid) AS comment FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname public AND c.relkind IN (r, p) ORDER BY c.relname;這個查詢雖然啰嗦但理解了pg_class和pg_namespace之后PG的系統(tǒng)表結(jié)構(gòu)就通了一半。3.2 當前連接、鎖等待與活動查詢排查數(shù)據(jù)庫突然變慢最常用的視圖絕對是pg_stat_activity。一條查詢跑遍所有問題SELECT pid, usename, datname, client_addr, state, wait_event_type, wait_event, query_start, now() - query_start AS runtime, query FROM pg_stat_activity WHERE state ! idle ORDER BY runtime DESC;看到state字段是active的進程結(jié)合query_start算一下運行時長基本就能判斷是正常慢查詢還是異常堵塞。如果大量會話卡在某個狀態(tài)且wait_event帶有Lock字眼說明存在鎖等待。鎖定位到具體是哪個進程堵了之后可以用這個函數(shù)查鏈條SELECT pg_blocking_pids(4567);把4567替換成你看到的被阻塞pid返回的結(jié)果就是堵住它的pid列表。然后再連查一層直到源頭。終止一個會話SELECT pg_cancel_backend(4567);取消不掉就用SELECT pg_terminate_backend(4567);兩者的區(qū)別是cancel是溫和取消當前查詢事務(wù)本身可以被應(yīng)用捕獲錯誤后回滾比較適合對付慢查詢terminate是直接斷開進程會把整個客戶端連接殺掉適合對付僵死連接。生產(chǎn)環(huán)境用terminate之前最好先確認這個連接是誰的、能不能斷。3.3 表大小、膨脹與索引使用統(tǒng)計磁盤空間到底被哪些表吃掉了很多人第一反應(yīng)是去看文件目錄其實一條SQL就出來了SELECT nspname || . || relname AS relation, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS table_size, pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS index_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind r AND n.nspname public ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;pg_size_pretty會把字節(jié)數(shù)換算成人類的可讀格式這個函數(shù)在你處理空間問題時是高頻使用的。檢查表的膨脹情況看pg_stat_user_tablesSELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(100.0 * n_dead_tup / n_live_tup, 1) ELSE 0 END AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 20;n_dead_tup代表死元組數(shù)量這個值長期偏高說明vacuum沒跟上表膨脹風險很高。索引命中率查詢SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, pg_size_pretty(pg_relation_size(indexrelname)) AS index_size FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;idx_scan長期為0的索引基本可以視為無效索引但注意先確認沒有唯一性約束不能只看使用率就刪。這類統(tǒng)計視圖是PG排查性能問題時最重要的地圖把它們當成常備工具勝過去背各種優(yōu)化口訣。4. 備份與恢復生產(chǎn)環(huán)境最常翻車的命令組合備份恢復這塊網(wǎng)上教程不少但大多數(shù)人只講了pg_dump一條命令。這里我重點講的是命令怎么組合、恢復到另一個環(huán)境時有哪些隱藏的坑這些才是生產(chǎn)環(huán)境真正要命的東西。4.1 pg_dump的格式選擇與并行參數(shù)pg_dump最基礎(chǔ)的用法pg_dump -h 127.0.0.1 -p 5432 -U myuser -d mydb -f mydb.sql這會生成一個純SQL文本文件里面是建表語句和數(shù)據(jù)插入語句。這種格式最大的好處是人眼可讀適合小庫、跨小版本遷移。但大庫用純SQL恢復特別慢并且中途出錯很難續(xù)跑。我更推薦custom格式pg_dump -h 127.0.0.1 -U myuser -d mydb -F c -f mydb.dump-F c 生成的是一種壓縮的二進制格式可以用pg_restore靈活控制恢復哪些對象。custom格式還支持并行恢復恢復大庫時速度快非常多。并行恢復pg_restore -h 127.0.0.1 -U myuser -d targetdb -j 4 mydb.dump-j 4代表開4個并發(fā)任務(wù)恢復。任務(wù)數(shù)建議不超過CPU核心數(shù)兩倍不然反而會因為資源競爭變慢。只備份數(shù)據(jù)不帶表結(jié)構(gòu)用pg_dump -a -F c -f data_only.dump只備份表結(jié)構(gòu)不帶數(shù)據(jù)用pg_dump -s -F c -f schema_only.dump這兩種方式在給測試環(huán)境搭建、生成對照庫時特別實用。4.2 pg_restore與psql回放的核心邏輯說清楚一個問題custom格式不能用psql直接回放必須用pg_restore。這是很多新手卡住的點。拿個大半天研究為什么psql讀dump文件報錯其實只是格式選錯了。一個常規(guī)的恢復流程# 先建目標庫如果還不存在 createdb -U myuser -h 127.0.0.1 targetdb # 恢復custom格式備份 pg_restore -h 127.0.0.1 -U myuser -d targetdb -j 4 mydb.dump如果dump里包含了建庫語句你可以用--create選項讓它先建庫再連庫恢復但前提是執(zhí)行pg_restore的用戶必須有建庫權(quán)限。查看一個dump文件里都包含了什么對象不用恢復就能列出pg_restore -l mydb.dump這個列表輸出很長可以配合grep只看某類對象比如只看函數(shù)pg_restore -l mydb.dump | grep FUNCTION如果你只想恢復其中一張表pg_restore -t public.requests -d targetdb mydb.dump4.3 恢復翻車現(xiàn)場權(quán)限、擴展、版本三大坑我實際在恢復dump到測試環(huán)境時遇到最多的問題歸納成三個。第一個坑是owner不一致。原庫的對象owner是prod_user目標環(huán)境里根本沒有這個用戶恢復時直接報錯。解決辦法是加上--no-ownerpg_restore --no-owner -d targetdb mydb.dump這個選項讓恢復后的對象owner統(tǒng)一變成當前執(zhí)行用戶規(guī)避掉一大部分權(quán)限問題。第二個坑是擴展缺依賴。比如表里有jsonb字段或者用了citext類型恢復時目標庫沒安裝對應(yīng)擴展就會報type does not exist。普通文本格式的dump里如果包含CREATE EXTENSION需要執(zhí)行者具有超級用戶權(quán)限否則也會被跳過。恢復之前先把該裝的核心擴展裝上CREATE EXTENSION IF NOT EXISTS citext; CREATE EXTENSION IF NOT EXISTS uuid-ossp;第三個坑是版本跨度過大。PG大版本之間的二進制格式不兼容pg_dump出來的dump文件最好在大版本內(nèi)恢復。跨大版本遷移建議先用低版本自帶的pg_dump導出再到高版本用pg_restore導入方向不能反。高版本導出的文件去低版本恢復基本都會失敗。還有一個很實用的細節(jié)給備份文件做完整性校驗恢復完以后立刻跑一遍行數(shù)抽查SELECT COUNT(*) FROM public.requests;這個動作成本不高但能快速發(fā)現(xiàn)數(shù)據(jù)中途丟失。5. 權(quán)限與角色命令從能跑到管得住數(shù)據(jù)庫能在本地跑起來和能在生產(chǎn)環(huán)境管得住之間的分界線就是權(quán)限命令。PG的權(quán)限體系比MySQL更細致但也因此容易踩到一些給了權(quán)限卻還是報錯的坑。5.1 角色與用戶的本質(zhì)區(qū)別在PG里角色ROLE和用戶USER本質(zhì)上是同一個東西。CREATE USER只是CREATE ROLE的一個便捷寫法區(qū)別僅在CREATE USER會默認帶著LOGIN屬性而CREATE ROLE默認不帶。CREATE ROLE app_user LOGIN PASSWORD xxx;等價于CREATE USER app_user PASSWORD xxx;查看當前實例下所有角色\du這個命令會列出角色名、屬性列表和成員關(guān)系一屏看全好人壞人。改密碼ALTER ROLE app_user WITH PASSWORD newpassword;角色繼承關(guān)系CREATE ROLE read_only; GRANT read_only TO app_user;app_user登錄后默認繼承read_only的權(quán)限。PG的權(quán)限檢查加上繼承特性理解起來需要一點疊加思維但實際使用中用繼承方式管理是很優(yōu)雅的。5.2 一組可以直接照抄的授權(quán)模板我根據(jù)實際場景整理了三個常用模板你可以直接搬走用。給一個只讀賬號CREATE ROLE read_only LOGIN PASSWORD xxx; GRANT CONNECT ON DATABASE mydb TO read_only; GRANT USAGE ON SCHEMA public TO read_only; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;注意這里GRANT CONNECT是讓你能連上這個庫GRANT USAGE ON SCHEMA是讓你能看到并使用這個schema下的對象。很多人只記得給表授權(quán)忘了給schema授權(quán)結(jié)果用戶連上庫之后一執(zhí)行SELECT就報permission denied for schema public。給一個讀寫賬號CREATE ROLE read_write LOGIN PASSWORD xxx; GRANT CONNECT ON DATABASE mydb TO read_write; GRANT USAGE, CREATE ON SCHEMA public TO read_write; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write; GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO read_write;ALTER DEFAULT PRIVILEGES這段很多新手會忽略它的作用是以后在這個schema里新建的表默認自動授權(quán)給指定角色。不加這個今天你給用戶授了權(quán)明天業(yè)務(wù)方新加了一張表用戶又查不了看起來就是權(quán)限神秘失效。只讀賬號順便給序列的usage是因為如果查詢要走某些帶序列的視圖或函數(shù)可能會被卡住。實際場景中遇到這類報錯就明白了。查看某個表當前權(quán)限用\dp tablename或者SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name tablename;5.3 權(quán)限問題上最容易忽略的三個場景第一個場景前面提到過的schema權(quán)限。很多團隊在給一個新賬號授權(quán)時列了一長串表自認為都覆蓋了結(jié)果用戶一登錄就報permission denied。問題不在表上而是USAGE ON SCHEMA沒給。這就像你給了別人一把房間鑰匙卻不讓對方進走廊。第二個場景函數(shù)執(zhí)行權(quán)限。PG里函數(shù)默認為PUBLIC可執(zhí)行但如果你在函數(shù)內(nèi)部訪問了其他表這個用戶必須同時擁有那些表的權(quán)限否則調(diào)用函數(shù)時依然會被拒絕。這個報錯藏得比較深往往不是在授權(quán)時發(fā)現(xiàn)而是應(yīng)用跑到一半才炸。第三個場景REVOKE之后還有殘留。想徹底收回一個用戶對某張表的所有權(quán)限不能只REVOKE一次需要把SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER全部回收REVOKE ALL PRIVILEGES ON TABLE public.requests FROM app_user;REVOKE ALL這種寫法一次清干凈不要分開寫。6. 性能診斷命令EXPLAIN、慢SQL與鎖等待性能診斷這一塊是你把PG從能跑帶到跑得快的核心。我會先把EXPLAIN怎么讀講透再說慢SQL定位三板斧最后說鎖等待處置。6.1 EXPLAIN ANALYZE到底怎么讀寫SQL的時候如果懷疑查詢慢直接在SQL前面加EXPLAIN ANALYZE比如EXPLAIN ANALYZE SELECT u.name, count(*) FROM requests r JOIN users u ON u.id r.user_id WHERE r.created_at now() - interval 7 days GROUP BY u.name;輸出會是一棵執(zhí)行計劃樹從縮進最深的節(jié)點開始看每一行都有三個關(guān)鍵數(shù)字actual time、rows、actual loops。actual time字段的第一個數(shù)字是啟動時間獲得第一行前的耗時第二個數(shù)字是整個過程耗時單位毫秒rows是實際掃描的行數(shù)要和EXPLAIN里預估的行數(shù)對比rows大比例偏差說明統(tǒng)計信息失效需要ANALYZE或者檢查索引判斷執(zhí)行計劃好壞最簡單的方法是找兩個詞Seq Scan和Index Scan。看到Seq Scan在表上掃了幾十萬行而你還期望它走索引那基本就是索引缺失、統(tǒng)計信息過期、或者SQL寫得讓優(yōu)化器沒法用索引?？匆粋€例子上面SQL如果輸出里顯示requests表做了Seq Scan且掃描了50萬行user表也做了Seq Scan那這個查詢大概率會慢。解決辦法一般是在requests.user_id和requests.created_at上建復合索引并確保統(tǒng)計信息是最新的。加索引的命令CREATE INDEX CONCURRENTLY idx_requests_user_created ON public.requests (user_id, created_at DESC);CONCURRENTLY關(guān)鍵字表示不阻塞寫操作。生產(chǎn)環(huán)境建索引時我強烈建議不要省略它雖然它會讓索引創(chuàng)建更慢且不能在事務(wù)里執(zhí)行但換來的是不打斷應(yīng)用寫入值得。6.2 慢SQL定位三板斧第一板斧是看pg_stat_activity找到正在跑的query和運行時長。這個前面已經(jīng)給過SQL區(qū)別是要記住它只看得到正在執(zhí)行的歷史慢SQL它不會記錄。第二板斧是安裝pg_stat_statements擴展。這個擴展是慢SQL統(tǒng)計的利器CREATE EXTENSION IF NOT EXISTS pg_stat_statements;安裝之后可能需要數(shù)據(jù)庫管理員在配置里先開啟shared_preload_libraries并重啟實例默認情況下有些環(huán)境已經(jīng)預加載了直接查詢即可。查詢執(zhí)行總次數(shù)多、總耗時長、平均耗時長的SQLSELECT queryid, calls, round(mean_exec_time::numeric, 2) AS avg_ms, round(total_exec_time::numeric, 2) AS total_ms, round(max_exec_time::numeric, 2) AS max_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;這個查詢會告訴你過去這段時間里到底哪幾條SQL在燃燒你的數(shù)據(jù)庫CPU和IO。有幾次線上事故最終定位到源頭就是pg_stat_statements里某條應(yīng)用生成的動態(tài)SQL沒有用上索引。第三板斧是開慢查詢?nèi)罩尽Ｐ薷呐渲肁LTER SYSTEM SET log_min_duration_statement 1000; SELECT pg_reload_conf();意思是執(zhí)行超過1000毫秒的SQL都會寫入日志文件。這個參數(shù)在生產(chǎn)環(huán)境非常有用改完后不用重啟reload即可生效。6.3 鎖等待的定位與處置命令鎖等待和慢查詢經(jīng)常伴生不是SQL本身慢而是卡在等鎖。定位步驟通常這樣走。第一步看pg_stat_activity里wait_event有沒有Lock字樣SELECT pid, state, wait_event_type, wait_event, query_start, query FROM pg_stat_activity WHERE wait_event_type Lock;第二步用pg_blocking_pids找到鎖源SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE wait_event_type Lock;第三步定位到源頭會話后用前面說過的pg_cancel_backend或者pg_terminate_backend處理。這里想特別提醒一件事lock_timeout這個參數(shù)值得在設(shè)計表結(jié)構(gòu)時就考慮。在長時間持鎖的DDL場景下給應(yīng)用會話設(shè)置SET lock_timeout 5s;可以讓等待鎖的會話在5秒后自動放棄而不是無限期掛在那里。很多數(shù)據(jù)庫無響應(yīng)事故就是被無限等鎖拖死的。7. 維護類命令避坑清單日常運維繞不開的幾件事最后這部分講幾個日常維護中繞不開的命令每個命令都有它典型的為什么和別亂動。7.1 VACUUM、ANALYZE與膨脹處理PG的并發(fā)控制靠MVCC刪除一行數(shù)據(jù)并不會立刻從物理文件里抹掉而是標記為死元組。這些死元組要靠VACUUM清理。VACUUM public.requests; VACUUM ANALYZE public.requests;普通VACUUM不鎖表可以在線跑。ANALYZE會更新統(tǒng)計信息優(yōu)化器等會兒執(zhí)行計劃就能更準確。兩者一起跑是常規(guī)操作。生產(chǎn)環(huán)境其實不必頻繁手動執(zhí)行因為PG默認開著autovacuum。但對一些超大表autovacuum可能來不及清理這時候可以在業(yè)務(wù)低谷手動跑一次。千萬注意不要輕易在生產(chǎn)環(huán)境用VACUUM FULLVACUUM FULL public.requests;VACUUM FULL會重寫整張表并且全程持鎖大表跑起來期間應(yīng)用寫入全部阻塞。我見過有人因為一張幾億行的表膨脹嚴重直接在高峰時段執(zhí)行VACUUM FULL結(jié)果整個業(yè)務(wù)停擺。正確的思路是優(yōu)先確認autovacuum配置正常實在需要物理收縮時才考慮VACUUM FULL而且放在維護窗口執(zhí)行或者直接通過新表遷移來替代。7.2 配置修改與生效方式ALTER SYSTEM與reload修改PG配置不建議直接去編輯postgresql.conf因為容易出錯。PG 9.4以后支持ALTER SYSTEMALTER SYSTEM SET max_connections 500;修改完后一部分參數(shù)需要reload生效SELECT pg_reload_conf();還有一部分參數(shù)必須重啟實例才生效。重要參數(shù)大致可以分兩類參數(shù)是否需要重啟典型場景max_connections重啟連接數(shù)不足shared_buffers重啟內(nèi)存緩沖不足work_mem無需會話級生效排序/哈希操作慢log_min_duration_statement無需reload慢SQL追蹤effective_cache_size無需reload優(yōu)化器選錯計劃work_mem有個坑值得單獨說它表示單個排序或哈希操作能使用的內(nèi)存不是整個查詢。如果你把它設(shè)置成1GB而并發(fā)50個會話都在排序瞬間就能吃掉50GB內(nèi)存。所以修改這類參數(shù)要結(jié)合并發(fā)量不能只看單次操作。7.3 連接數(shù)打滿時的急救流程連接數(shù)打滿是一種特別常見的故障表現(xiàn)為新連接報too many connections。急救步驟一般是這樣第一步看當前連接分布SELECT datname, usename, count(*) FROM pg_stat_activity GROUP BY datname, usename ORDER BY count(*) DESC;第二步殺掉明顯異常的連接。如果是一些應(yīng)用的重試連接堆積可以用pg_terminate_backend批量清理掉非關(guān)鍵連接。謹慎一點的做法是先把它們cancel了再看。第三步調(diào)整連接池的配置。生產(chǎn)環(huán)境應(yīng)用不應(yīng)該直連PG中間一定套一個連接池應(yīng)用層控制連接池上限PG端用pgbouncer或者proxysql做集中池化。如果沒接連接池max_connections再大也只是慢性死亡。這個屬于架構(gòu)問題但和命令使用的關(guān)聯(lián)很深。另一個維護命令是CHECKPOINTCHECKPOINT;它作用是把WAL緩沖區(qū)的內(nèi)容刷到數(shù)據(jù)文件讓崩潰恢復更快。平時不需要手動執(zhí)行但如果在做某些備份或要關(guān)閉實例之前執(zhí)行一次可以縮短恢復時間。這也算是我見過的命令存在但很少被正確使用的一類。最后說個經(jīng)驗這篇文章列的命令我在真實項目里都實際用過。如果你現(xiàn)在剛起步我的建議是先別急著把每條命令背下來動手執(zhí)行一遍然后按自己的使用場景把這個速查文檔沉淀下來。把五個場景練熟就夠了連接、查詢信息、備份恢復、權(quán)限、慢查詢排查。等這五塊熟了剩下的都是隨用隨查。另外想強調(diào)一個總的習慣凡是涉及生產(chǎn)環(huán)境的命令先在一個空庫里試一遍。特別是DROP、TRUNCATE、VACUUM FULL這類重量級操作試完你才會對它的輸出和影響有切身體感。數(shù)據(jù)庫命令的價值不在知道而在用對。