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