維實(shí)戰(zhàn))
1. 為什么我勸你千萬別跳過 psql很多剛接觸 PostgreSQL 的人裝完數(shù)據(jù)庫第一件事就是打開 pgAdmin、Navicat 或者 DataGrip點(diǎn)幾下鼠標(biāo)建個(gè)表、跑個(gè)查詢覺得這樣才算“會用數(shù)據(jù)庫”。我自己早期也這么干直到有一次在無桌面環(huán)境的 Linux 服務(wù)器上排查慢查詢才發(fā)現(xiàn)離開 psql 我?guī)缀醮绮诫y行。從那時(shí)起我下定決心把 psql 練熟后來工作中幾乎所有 PostgreSQL 運(yùn)維和調(diào)優(yōu)操作都在這個(gè)黑乎乎的終端里完成。psql 是 PostgreSQL 自帶的交互式命令行工具它不只是“能執(zhí)行 SQL”這么簡單。它內(nèi)置了元命令以反斜杠開頭的命令、變量替換、事務(wù)控制、腳本執(zhí)行、結(jié)果格式化、自動補(bǔ)全、歷史記錄、快捷鍵等一系列能力。只要你的機(jī)器上有 PostgreSQL 客戶端甚至不一定裝服務(wù)端單獨(dú)裝個(gè) psql 就行就能連到遠(yuǎn)端數(shù)據(jù)庫做完大部分管理和查詢工作。這篇文章適合誰如果你是剛接觸 PostgreSQL 的新手可以把它當(dāng)成 psql 的入門實(shí)操手冊如果你已經(jīng)用圖形工具很久想轉(zhuǎn)到命令行提高效率同樣建議細(xì)看快捷鍵和元命令部分。我盡量把每個(gè)操作背后“為什么要這么用”也講清楚而不是單純羅列命令。2. 連接數(shù)據(jù)庫與基礎(chǔ)元命令先把地基打牢2.1 連接參數(shù)的幾種姿勢psql 最基礎(chǔ)的用法是psql -h 127.0.0.1 -p 5432 -U postgres -d mydb分別對應(yīng)主機(jī)、端口、用戶、數(shù)據(jù)庫。如果在本機(jī)且使用默認(rèn)端口可以直接psql -U postgres mydb也可以偷懶用環(huán)境變量比如export PGHOST192.168.1.10 export PGPORT5432 export PGUSERpostgres export PGDATABASEmydb psql這樣 psql 會自動讀取這些變量。我習(xí)慣在 ~/.bashrc 里配一套默認(rèn)變量日常連接省掉一大串參數(shù)。注意密碼默認(rèn)不寫在命令行里否則容易被 history 泄露可以用 .pgpass 文件或 PGPASSWORD 環(huán)境變量但后者同樣有安全風(fēng)險(xiǎn)生產(chǎn)環(huán)境我更推薦 .pgpass。連接成功后你會看到類似這樣的提示符mydb#超級用戶是#普通用戶是。想確認(rèn)當(dāng)前連接信息執(zhí)行SELECT version();或者用元命令\conninfo查看連接詳情它會告訴你當(dāng)前數(shù)據(jù)庫、用戶、端口、連接協(xié)議等。2.2 最常用的幾個(gè)元命令psql 的元命令以反斜杠開頭不需要分號結(jié)尾。剛?cè)腴T建議先記住這幾個(gè)命令作用備注\l列出所有數(shù)據(jù)庫等價(jià)于SELECT datname FROM pg_database;\c dbname切換數(shù)據(jù)庫也支持\c dbname username\dt列出當(dāng)前 schema 的表想看視圖用\dv想看序列用\ds\d table_name查看表結(jié)構(gòu)包含字段、類型、約束、索引、觸發(fā)器\dn列出 schema\du列出角色/用戶\x切換擴(kuò)展顯示行太多擠在一起時(shí)用\q退出 psql也可以 CtrlD\d是個(gè)很值得深挖的命令。不帶參數(shù)時(shí)它列出所有“可顯示的”關(guān)系帶表名時(shí)展示的信息比很多圖形工具還詳細(xì)。比如查看一個(gè)分區(qū)表\d parent_table會列出分區(qū)鍵、分區(qū)子表列表查看一個(gè)索引\d index_name能看到它的定義語句和所屬表。我排查鎖等待或表結(jié)構(gòu)變更時(shí)基本全靠\d。\x是我特別想強(qiáng)調(diào)的元命令。默認(rèn)查詢結(jié)果以列對齊方式顯示如果字段特別多或者某個(gè)字段是 JSON、文本超長結(jié)果會擠到?jīng)]法看。執(zhí)行\(zhòng)x之后每條記錄轉(zhuǎn)成“字段名值”的縱向展示閱讀體驗(yàn)好太多。也可以臨時(shí)用\x on或\x off單獨(dú)控制某一次查詢。2.3 查看幫助的萬能鑰匙psql 自帶的幫助體系做得相當(dāng)完善\?列出所有元命令的簡短說明\h列出所有 SQL 命令關(guān)鍵字\h SELECT查看具體語法\g執(zhí)行上一條命令或者直接分號二者等價(jià)。我經(jīng)常用\h查某個(gè) SQL 子句的準(zhǔn)確語法比如\h CREATE TABLE、\h COPY。它輸出的語法比網(wǎng)上很多二手教程嚴(yán)謹(jǐn)?shù)枚喽沂潜緳C(jī)版本匹配的語法。這個(gè)習(xí)慣能讓你少踩很多坑畢竟 PostgreSQL 10 和 16 的某些語法已經(jīng)不一樣了。提示\h顯示的語法中方括號表示可選花括號表示必選其一豎線表示“或”。學(xué)會讀這個(gè)語法圖比死記硬背命令管用得多。3. 常用 SQL 操作的進(jìn)階技巧與結(jié)果格式化3.1 分段執(zhí)行與事務(wù)控制psql 默認(rèn)在每條帶分號的 SQL 后自動提交事務(wù)。如果你想手動控制事務(wù)最直接的方式是BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果中間某一步出錯(cuò)且你希望回滾不要直接輸ROLLBACK可以先觀察當(dāng)前事務(wù)狀態(tài)。有個(gè)小技巧如果忘了是否在事務(wù)中可以查看提示符事務(wù)中提示符會變成mydb*#多了一個(gè)*號。這個(gè)細(xì)節(jié)在寫復(fù)雜腳本時(shí)很救命。另外psql 里可以用\set AUTOCOMMIT off關(guān)閉自動提交之后每條 SQL 都需要你手動COMMIT才會生效。切換回自動提交用\set AUTOCOMMIT on。不過我個(gè)人不太建議日常關(guān)閉自動提交容易忘記提交導(dǎo)致鎖表只有做批量數(shù)據(jù)修復(fù)時(shí)才會臨時(shí)關(guān)掉。3.2 查詢結(jié)果的格式化控制默認(rèn)的查詢結(jié)果格式叫“aligned”字段名和值用豎線分隔。實(shí)際場景中我們經(jīng)常需要把結(jié)果導(dǎo)出成 CSV 或?qū)R格式這里有幾個(gè)實(shí)用設(shè)置\pset format csv SELECT * FROM users LIMIT 10;執(zhí)行后結(jié)果直接以 CSV 形式輸出不帶表頭時(shí)用\pset format unaligned \pset tuples_only ontuples_only設(shè)置為 on 后只顯示數(shù)據(jù)行不顯示列名。這個(gè)組合常用于腳本處理。如果你想恢復(fù)默認(rèn)\pset format aligned \pset tuples_only off我遇到過不少同事喜歡把查詢結(jié)果粘貼到 Excel 里處理但默認(rèn)格式粘貼過去會錯(cuò)位。其實(shí) psql 自帶\copy命令可以直接把查詢結(jié)果導(dǎo)出為 CSV\copy (SELECT id, name, created_at FROM users) TO /tmp/users.csv WITH CSV HEADER注意\copy是 psql 客戶端本地讀寫文件權(quán)限和路徑都以你本地機(jī)器為準(zhǔn)而COPY是服務(wù)端讀寫文件文件必須在數(shù)據(jù)庫服務(wù)器上且對 postgres 用戶可寫。新手經(jīng)常搞混這兩個(gè)命令報(bào)“權(quán)限不足”或者“文件不存在”時(shí)先確認(rèn)自己用的是哪個(gè)。3.3 用 \gset 把查詢結(jié)果變成變量psql 支持變量替換最經(jīng)典的用法是SELECT count(*) AS cnt FROM users; \gset SELECT :cnt;\gset會把查詢結(jié)果的每一列賦值給同名變量之后用:cnt引用。這個(gè)能力在寫自動化腳本時(shí)非常有用。比如你想在腳本里獲取當(dāng)前序列值再插入SELECT nextval(order_seq) AS new_id; \gset INSERT INTO orders (id, detail) VALUES (:new_id, test);還可以用\if、\elif、\else做條件判斷結(jié)合\gset實(shí)現(xiàn)簡單的流程控制。雖然 psql 不是編程語言但做輕量自動化足夠了。4. 快捷鍵與命令行編輯技巧效率翻倍的關(guān)鍵4.1 行編輯與歷史記錄psql 底層依賴 readline 庫所以它支持大部分 bash 風(fēng)格的快捷鍵。我實(shí)際高頻使用的主要這幾個(gè)快捷鍵作用使用場景上/下箭頭瀏覽歷史命令執(zhí)行過的 SQL 按順序翻CtrlA / CtrlE光標(biāo)跳到行首/行尾修改長 SQL 的開頭或結(jié)尾CtrlW刪除光標(biāo)前一個(gè)單詞去掉寫錯(cuò)的條件字段CtrlU刪除光標(biāo)到行首全部內(nèi)容整條命令寫廢時(shí)快速清空CtrlK刪除光標(biāo)到行尾全部內(nèi)容只保留已輸入的前半段CtrlY粘貼剛才刪掉的文本配合 CtrlU / CtrlK 使用CtrlL清屏屏幕太亂時(shí)Tab 鍵自動補(bǔ)全表名、列名、函數(shù)名寫長表名時(shí)尤其好用Tab補(bǔ)全是我強(qiáng)烈推薦新手盡早習(xí)慣的功能。psql 能補(bǔ)全的關(guān)鍵字范圍很廣輸入sel再按 Tab會補(bǔ)全為SELECT輸入表名前幾個(gè)字母再 Tab會列出匹配的表名輸入\d后按 Tab 也能補(bǔ)全關(guān)系名。有了這個(gè)功能很長的表名根本不用手敲既快又不容易寫錯(cuò)。歷史記錄默認(rèn)存在~/.psql_history文件。如果你不希望某個(gè)敏感 SQL 被記錄可以在輸入之前加一個(gè)空格前提是設(shè)置變量HISTCONTROLignorespace。不過這個(gè)變量是 psql 的\set還是環(huán)境變量實(shí)際是 psql 的\set HISTCONTROL ignorespace執(zhí)行后以空格開頭的命令不會進(jìn)歷史。但說實(shí)話我很少用更建議敏感操作在事務(wù)中執(zhí)行并謹(jǐn)慎確認(rèn)。4.2 多行編輯與重新編輯psql 里 SQL 可以跨多行寫直到分號才執(zhí)行。如果你寫了一行發(fā)現(xiàn)想改上面某行最簡單的辦法是直接按 CtrlC 取消當(dāng)前輸入回到提示符重新開始。但如果你已經(jīng)連續(xù)寫了好幾行CtrlC 會全部清掉。此時(shí)按\e或\ev、\ef可以打開系統(tǒng)默認(rèn)編輯器通常是 vi 或 nano來編輯當(dāng)前的查詢緩沖區(qū)。舉個(gè)例子SELECT * FROM users WHERE created_at 2024-01-01;在分號前按\e就會用編輯器打開這段 SQL改完后保存退出psql 會把內(nèi)容重新送進(jìn)緩沖區(qū)讓你繼續(xù)執(zhí)行。\ef可以直接編輯函數(shù)定義\ev編輯視圖定義這兩個(gè)命令對快速修改數(shù)據(jù)庫對象非常方便。4.3 修改默認(rèn)編輯器\e調(diào)用的是EDITOR環(huán)境變量指定的編輯器。如果你更喜歡 vimexport EDITORvim psql或者在 psql 內(nèi)部執(zhí)行\(zhòng)setenv EDITOR vim同樣可以為VISUAL設(shè)置優(yōu)先級不同。建議統(tǒng)一設(shè)置EDITOR和VISUAL。我在服務(wù)器上更喜歡用 vim 做復(fù)雜編輯因?yàn)?vim 有語法高亮、列編輯模式改多行 SQL 比在終端直接敲舒服得多。5. 腳本化執(zhí)行與自動化運(yùn)維實(shí)戰(zhàn)5.1 通過 -f 和 -c 執(zhí)行外部腳本psql 最常見的腳本執(zhí)行方式是psql -h host -U user -d db -f /path/to/script.sql腳本里可以包含 SQL 和元命令混合內(nèi)容。比如一個(gè)初始化腳本init.sql\set ON_ERROR_STOP on BEGIN; CREATE TABLE IF NOT EXISTS app_user ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT now() ); COMMIT;加上ON_ERROR_STOP后一旦腳本中某條語句報(bào)錯(cuò)psql 立即停止后續(xù)執(zhí)行不加這個(gè)設(shè)置默認(rèn)會報(bào)錯(cuò)后繼續(xù)跑下一條在批量執(zhí)行時(shí)非常危險(xiǎn)。我建議在所有生產(chǎn)環(huán)境變更腳本里都寫上\set ON_ERROR_STOP on并且配合事務(wù)使用。單條命令直接執(zhí)行可以用psql -d mydb -c TRUNCATE TABLE temp_log;-c后面跟分號也可以。區(qū)別在于-c執(zhí)行完立即退出而-f執(zhí)行完也退出。想連進(jìn)去再繼續(xù)操作就用普通的交互模式。5.2 在 shell 腳本中安全調(diào)用 psql寫 Shell 腳本時(shí)我習(xí)慣把所有數(shù)據(jù)庫參數(shù)放到一個(gè)配置文件里然后用變量傳遞。舉一個(gè)實(shí)際備份歸檔的例子#!/bin/bash DB_HOST192.168.1.20 DB_PORT5432 DB_USERops DB_NAMEappdb export PGPASSWORDS3curePass! psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME EOF \set ON_ERROR_STOP on \pset tuples_only on SELECT backup_start, now(); -- 這里執(zhí)行需要?dú)w檔的數(shù)據(jù)操作 EOF這里用 heredoc 傳 SQL注意結(jié)束符EOF加了單引號避免 shell 對$、反引號做變量替換。如果 SQL 里需要用到 psql 的變量就去掉單引號并小心轉(zhuǎn)義。關(guān)于密碼PGPASSWORD寫在腳本里有泄露風(fēng)險(xiǎn)更好的做法是使用.pgpass文件并設(shè)置權(quán)限為 600。5.3 使用變量與回調(diào)處理動態(tài)腳本psql 支持-v參數(shù)向腳本傳遞變量psql -d mydb -v start_date2024-06-01 -v end_date2024-06-30 -f report.sql腳本report.sql中可以用:start_date引用變量并自動添加單引號SELECT * FROM orders WHERE created_at BETWEEN :start_date AND :end_date;這里注意:start_date是帶引號的安全插入方式如果直接寫:start_date值會按字面插入可能在 SQL 注入或類型錯(cuò)誤。變量還能配合條件判斷做動態(tài)分支\if :is_debug SELECT debug mode; \else SELECT release mode; \endif這個(gè)能力在部署腳本里很實(shí)用同一套腳本測試環(huán)境傳is_debugtrue生產(chǎn)傳false。6. 提升效率的高級配置與個(gè)性化設(shè)置6.1 通過 .psqlrc 定制啟動環(huán)境每次進(jìn)入 psql 都會自動執(zhí)行~/.psqlrc文件存在則加載。我個(gè)人的.psqlrc長這樣\pset null NULL \pset pager on \x auto \set HISTSIZE 10000 \set COMPKEYWORD_STATUS 1逐行解釋一下\pset null NULL將數(shù)據(jù)庫中的 NULL 顯示為字符串NULL否則默認(rèn)顯示為空白容易和空字符串混淆\pset pager on查詢結(jié)果超過一屏?xí)r自動用分頁器less顯示。但我不太喜歡它頻繁翻頁有時(shí)直接\pset pager off關(guān)掉容忍結(jié)果滾動\x auto僅當(dāng)結(jié)果列太寬時(shí)才自動切換擴(kuò)展顯示這是我在 psql 16 里最喜歡的設(shè)置\set HISTSIZE 10000歷史命令保留 10000 條默認(rèn)只有 500 條\set COMPKEYWORD_STATUS 1啟用關(guān)鍵字大小寫自動補(bǔ)全行為。注意如果想臨時(shí)跳過.psqlrc啟動時(shí)加-X參數(shù)psql -X -d mydb這在排查某些用戶環(huán)境問題時(shí)很有用避免個(gè)人配置干擾判斷。6.2 使用 \timing 觀察執(zhí)行耗時(shí)\timing是另一把好用的尺子。開啟后每條 SQL 執(zhí)行完會自動顯示耗時(shí)mydb# \timing on Timing is on. mydb# SELECT count(*) FROM big_table; count ------- 1000000 (1 row) Time: 235.812 ms這個(gè)信息對于定位慢查詢非常直接。配合EXPLAIN ANALYZE使用的話EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table WHERE id 500000;輸出的執(zhí)行計(jì)劃里有實(shí)際執(zhí)行時(shí)間、掃描行數(shù)、緩沖命中情況。我一般先開\timing記錄整體時(shí)間再做EXPLAIN ANALYZE看細(xì)節(jié)兩者互補(bǔ)。6.3 自定義提示符與顏色提示符可以顯示當(dāng)前數(shù)據(jù)庫、用戶名、主機(jī)、甚至事務(wù)狀態(tài)。默認(rèn)提示符%/%R%#已經(jīng)包含數(shù)據(jù)庫名和事務(wù)標(biāo)志。想自定義的話\set PROMPT1 %n%M:% %echo -n \$? %/%R%# 這個(gè)會把提示符顯示為userhost:port dbname#。不過我更推薦一種極簡配置\set PROMPT1 %/%R%# 如果你有多個(gè)環(huán)境比如開發(fā)庫、測試庫、生產(chǎn)庫可以把數(shù)據(jù)庫名或連接信息用顏色區(qū)分。psql 16 支持在提示符中使用顏色轉(zhuǎn)義序列例如\set PROMPT1 %[%033[1;31m%]%/%R%#%[%033[0m%] 紅色的提示符能直觀提醒你“當(dāng)前在關(guān)鍵環(huán)境”降低誤操作風(fēng)險(xiǎn)。不過這個(gè)顏色方案在部分終端下可能不生效建議先在實(shí)際終端里測試。7. 常見問題排查與避坑實(shí)錄7.1 報(bào)錯(cuò) “FATAL: database does not exist”很多新手連接時(shí)直接psql -U postgres如果沒指定-d默認(rèn)會嘗試連接與用戶名同名的庫。比如你的系統(tǒng)用戶是rootpsql會嘗試連一個(gè)叫root的數(shù)據(jù)庫然后報(bào)錯(cuò)psql: error: connection to server on socket /var/run/postgresql/.s.PGSQL.5432 failed: FATAL: database root does not exist解決辦法明確指定數(shù)據(jù)庫-d postgres或者在環(huán)境變量里設(shè)置PGDATABASEpostgres。出現(xiàn)這種報(bào)錯(cuò)時(shí)先檢查自己當(dāng)前是什么用戶、要連哪個(gè)庫別急著懷疑服務(wù)沒啟動。7.2 口令認(rèn)證失敗與 .pgpass 配置password authentication failed for user xx是最常見的認(rèn)證報(bào)錯(cuò)之一。除了密碼真的輸錯(cuò)還有可能是 pg_hba.conf 里規(guī)定了認(rèn)證方式不是 md5/scram。排查順序確認(rèn)用戶名和密碼查看連接是否走到預(yù)期的 pg_hba 規(guī)則可以用SHOW hba_file;找到文件位置嘗試psql -h 127.0.0.1和psql -h /var/run/postgresql分別連確認(rèn) socket 和 TCP 是否都允許。日常自動化中我建議配置.pgpass# ~/.pgpass localhost:5432:mydb:username:supersecret然后設(shè)置權(quán)限chmod 600 ~/.pgpass注意.pgpass文件每行格式是hostname:port:database:user:password其中數(shù)據(jù)庫也可以用*匹配所有庫。這個(gè)文件比PGPASSWORD安全一截因?yàn)椴粫霈F(xiàn)在進(jìn)程環(huán)境變量里。7.3 psql 卡住不動可能是等待鎖執(zhí)行SELECT * FROM some_table一直不返回大概率是表被其他事務(wù)鎖住了。這時(shí)候不要直接 CtrlC 了事應(yīng)該另開一個(gè) psql 查鎖和阻塞SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state IS NOT NULL;看到wait_event_type為Lock時(shí)基本可以判斷在等鎖。再進(jìn)一步查阻塞鏈SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid ANY(pg_blocking_pids(blocked.pid));這個(gè)查詢很實(shí)用能直接揪出誰是“攔路虎”。碰上長時(shí)間持鎖的事務(wù)和業(yè)務(wù)方確認(rèn)后可以用pg_terminate_backend(pid)終止。7.4 亂碼與編碼問題遇到中文亂碼時(shí)先檢查客戶端編碼SHOW client_encoding;正常應(yīng)該是UTF8。如果顯示SQL_ASCII或其他執(zhí)行SET client_encoding TO UTF8;也可以啟動時(shí)加環(huán)境變量PGCLIENTENCODINGUTF8。另外文件導(dǎo)入時(shí)注意文件本身的編碼要和數(shù)據(jù)庫字符集匹配通常建議用 UTF-8 無 BOM 格式。8. 從交互到自動化構(gòu)建自己的 psql 工作流8.1 與常見運(yùn)維場景結(jié)合psql 的價(jià)值遠(yuǎn)不止敲幾條 SQL。舉幾個(gè)我實(shí)際長期使用的場景定期清理歸檔結(jié)合 crontab 和-f腳本每周清理過期分區(qū)表監(jiān)控連接數(shù)一條 SQL 輸出當(dāng)前連接池用量直接管道給監(jiān)控腳本生成建表語句用pg_dump --schema-only配合 psql 還原校驗(yàn)兩個(gè)環(huán)境的表結(jié)構(gòu)差異批量導(dǎo)出數(shù)據(jù)\copy導(dǎo)出 CSV 后再通過 Python pandas 處理或直接加載到數(shù)倉。下面是一個(gè)簡單的 shell 函數(shù)方便快速連到測試庫執(zhí)行任意 SQLdbq() { local db$1 local sql$2 psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $db -tAX -c $sql }調(diào)用方式dbq app select count(*) from users;-tAX三個(gè)參數(shù)含義-t只輸出行數(shù)據(jù)-A關(guān)閉對齊無空格填充-X跳過 .psqlrc。組合起來輸出就是純文本方便繼續(xù)用 grep、awk 處理。8.2 復(fù)雜腳本的調(diào)試方法寫長 SQL 腳本時(shí)建議先打開\set ECHO all效果是 psql 會回顯每條執(zhí)行的 SQL方便對照日志排查。配合\set VERBOSITY verbose報(bào)錯(cuò)信息會更完整包含錯(cuò)誤位置和提示。如果腳本里有循環(huán)或條件邏輯可以用\timing和\echo打印過程信息\echo Start cleanup at :current_time -- 業(yè)務(wù)邏輯 \echo Cleanup finished \echo類似 shell 的 echo但可以用 psql 變量。這樣執(zhí)行時(shí)能看到進(jìn)度不至于面對黑屏到底跑了多少狀態(tài)一無所知。8.3 在 Windows 和遠(yuǎn)程環(huán)境下的注意點(diǎn)Windows 上使用 psql 時(shí)默認(rèn)終端可能是 cmd 或 PowerShellreadline 快捷鍵不完全支持。建議在 Windows 上安裝 Git Bash 或 WSL這樣能拿到和 Linux 一致的補(bǔ)全和歷史體驗(yàn)。另外Windows 的換行符是 CRLF如果編寫的 SQL 腳本是從 Windows 傳到 Linux 服務(wù)器上執(zhí)行偶爾會遇到\r導(dǎo)致的語法錯(cuò)誤可以用sed -i s/\r$// script.sql清理一下。如果是通過 SSH 遠(yuǎn)程連接注意長期空閑的連接可能被服務(wù)器斷開?？梢栽?psql 里執(zhí)行一些輕量查詢保持活躍或者配置 SSH 的ServerAliveInterval。9. 一些我踩過坑之后才明白的小技巧最后想分享幾個(gè)不太常見但很實(shí)用的細(xì)節(jié)這些是我用了多年 psql 之后才慢慢積累下來的。第一個(gè)是\watch命令。在 SQL 語句后面輸入\watch 2psql 會每 2 秒重復(fù)執(zhí)行一次同一個(gè)查詢直到你按 CtrlC。我在排查鎖等待、觀察 count 變化、盯某個(gè)指標(biāo)是否回暖時(shí)經(jīng)常用它比反復(fù)按上箭頭回車舒服太多。第二個(gè)是\sf和\df配合查看函數(shù)定義。\df function_name能看到函數(shù)的源碼位置、參數(shù)、返回類型、安全屬性等\sf function_name直接輸出函數(shù)完整定義可以直接復(fù)制到編輯器里改。這兩個(gè)命令在別人維護(hù)的庫里接手時(shí)極其好用。第三個(gè)是關(guān)于NULL的陷阱。在 psql 里執(zhí)行SELECT NULL;默認(rèn)顯示一個(gè)空字符串如果不仔細(xì)看會誤以為結(jié)果為空。配置\pset null [NULL]后所有 NULL 都會顯示成[NULL]一眼就能分辨。這個(gè)配置我強(qiáng)烈建議寫進(jìn).psqlrc。第四個(gè)是善用\gexec。比如你想對所有以2023_開頭的分區(qū)表執(zhí)行同一個(gè)操作可以先生成動態(tài) SQL然后交給\gexec執(zhí)行SELECT format(VACUUM (ANALYZE) %I;, tablename) FROM pg_tables WHERE tablename LIKE 2023\_% \gexec它會逐條執(zhí)行生成的每條 SQL省去手寫循環(huán)的麻煩。我個(gè)人的體會是psql 這門手藝不值得刻意去背命令清單最有效的學(xué)習(xí)方式是把它當(dāng)成日常操作的主要入口遇到問題先想“psql 能不能做到”再去查\?和\h。用一兩個(gè)星期后你會發(fā)現(xiàn)自己已經(jīng)離不開它的補(bǔ)全、歷史、變量和腳本能力。等哪天真要在大促凌晨處理一個(gè)線上緊急變更能讓你穩(wěn)住陣腳的往往不是圖形界面里那個(gè)鼠標(biāo)而是這個(gè)樸素命令行里飛快跑過的幾行綠色輸出。 psql命令行完全指南：PostgreSQL高效管理與運(yùn)維實(shí)戰(zhàn)
1. 為什么我勸你千萬別跳過 psql很多剛接觸 PostgreSQL 的人裝完數(shù)據(jù)庫第一件事就是打開 pgAdmin、Navicat 或者 DataGrip點(diǎn)幾下鼠標(biāo)建個(gè)表、跑個(gè)查詢覺得這樣才算“會用數(shù)據(jù)庫”。我自己早期也這么干直到有一次在無桌面環(huán)境的 Linux 服務(wù)器上排查慢查詢才發(fā)現(xiàn)離開 psql 我?guī)缀醮绮诫y行。從那時(shí)起我下定決心把 psql 練熟后來工作中幾乎所有 PostgreSQL 運(yùn)維和調(diào)優(yōu)操作都在這個(gè)黑乎乎的終端里完成。psql 是 PostgreSQL 自帶的交互式命令行工具它不只是“能執(zhí)行 SQL”這么簡單。它內(nèi)置了元命令以反斜杠開頭的命令、變量替換、事務(wù)控制、腳本執(zhí)行、結(jié)果格式化、自動補(bǔ)全、歷史記錄、快捷鍵等一系列能力。只要你的機(jī)器上有 PostgreSQL 客戶端甚至不一定裝服務(wù)端單獨(dú)裝個(gè) psql 就行就能連到遠(yuǎn)端數(shù)據(jù)庫做完大部分管理和查詢工作。這篇文章適合誰如果你是剛接觸 PostgreSQL 的新手可以把它當(dāng)成 psql 的入門實(shí)操手冊如果你已經(jīng)用圖形工具很久想轉(zhuǎn)到命令行提高效率同樣建議細(xì)看快捷鍵和元命令部分。我盡量把每個(gè)操作背后“為什么要這么用”也講清楚而不是單純羅列命令。2. 連接數(shù)據(jù)庫與基礎(chǔ)元命令先把地基打牢2.1 連接參數(shù)的幾種姿勢psql 最基礎(chǔ)的用法是psql -h 127.0.0.1 -p 5432 -U postgres -d mydb分別對應(yīng)主機(jī)、端口、用戶、數(shù)據(jù)庫。如果在本機(jī)且使用默認(rèn)端口可以直接psql -U postgres mydb也可以偷懶用環(huán)境變量比如export PGHOST192.168.1.10 export PGPORT5432 export PGUSERpostgres export PGDATABASEmydb psql這樣 psql 會自動讀取這些變量。我習(xí)慣在 ~/.bashrc 里配一套默認(rèn)變量日常連接省掉一大串參數(shù)。注意密碼默認(rèn)不寫在命令行里否則容易被 history 泄露可以用 .pgpass 文件或 PGPASSWORD 環(huán)境變量但后者同樣有安全風(fēng)險(xiǎn)生產(chǎn)環(huán)境我更推薦 .pgpass。連接成功后你會看到類似這樣的提示符mydb#超級用戶是#普通用戶是。想確認(rèn)當(dāng)前連接信息執(zhí)行SELECT version();或者用元命令\conninfo查看連接詳情它會告訴你當(dāng)前數(shù)據(jù)庫、用戶、端口、連接協(xié)議等。2.2 最常用的幾個(gè)元命令psql 的元命令以反斜杠開頭不需要分號結(jié)尾。剛?cè)腴T建議先記住這幾個(gè)命令作用備注\l列出所有數(shù)據(jù)庫等價(jià)于SELECT datname FROM pg_database;\c dbname切換數(shù)據(jù)庫也支持\c dbname username\dt列出當(dāng)前 schema 的表想看視圖用\dv想看序列用\ds\d table_name查看表結(jié)構(gòu)包含字段、類型、約束、索引、觸發(fā)器\dn列出 schema\du列出角色/用戶\x切換擴(kuò)展顯示行太多擠在一起時(shí)用\q退出 psql也可以 CtrlD\d是個(gè)很值得深挖的命令。不帶參數(shù)時(shí)它列出所有“可顯示的”關(guān)系帶表名時(shí)展示的信息比很多圖形工具還詳細(xì)。比如查看一個(gè)分區(qū)表\d parent_table會列出分區(qū)鍵、分區(qū)子表列表查看一個(gè)索引\d index_name能看到它的定義語句和所屬表。我排查鎖等待或表結(jié)構(gòu)變更時(shí)基本全靠\d。\x是我特別想強(qiáng)調(diào)的元命令。默認(rèn)查詢結(jié)果以列對齊方式顯示如果字段特別多或者某個(gè)字段是 JSON、文本超長結(jié)果會擠到?jīng)]法看。執(zhí)行\(zhòng)x之后每條記錄轉(zhuǎn)成“字段名值”的縱向展示閱讀體驗(yàn)好太多。也可以臨時(shí)用\x on或\x off單獨(dú)控制某一次查詢。2.3 查看幫助的萬能鑰匙psql 自帶的幫助體系做得相當(dāng)完善\?列出所有元命令的簡短說明\h列出所有 SQL 命令關(guān)鍵字\h SELECT查看具體語法\g執(zhí)行上一條命令或者直接分號二者等價(jià)。我經(jīng)常用\h查某個(gè) SQL 子句的準(zhǔn)確語法比如\h CREATE TABLE、\h COPY。它輸出的語法比網(wǎng)上很多二手教程嚴(yán)謹(jǐn)?shù)枚喽沂潜緳C(jī)版本匹配的語法。這個(gè)習(xí)慣能讓你少踩很多坑畢竟 PostgreSQL 10 和 16 的某些語法已經(jīng)不一樣了。提示\h顯示的語法中方括號表示可選花括號表示必選其一豎線表示“或”。學(xué)會讀這個(gè)語法圖比死記硬背命令管用得多。3. 常用 SQL 操作的進(jìn)階技巧與結(jié)果格式化3.1 分段執(zhí)行與事務(wù)控制psql 默認(rèn)在每條帶分號的 SQL 后自動提交事務(wù)。如果你想手動控制事務(wù)最直接的方式是BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果中間某一步出錯(cuò)且你希望回滾不要直接輸ROLLBACK可以先觀察當(dāng)前事務(wù)狀態(tài)。有個(gè)小技巧如果忘了是否在事務(wù)中可以查看提示符事務(wù)中提示符會變成mydb*#多了一個(gè)*號。這個(gè)細(xì)節(jié)在寫復(fù)雜腳本時(shí)很救命。另外psql 里可以用\set AUTOCOMMIT off關(guān)閉自動提交之后每條 SQL 都需要你手動COMMIT才會生效。切換回自動提交用\set AUTOCOMMIT on。不過我個(gè)人不太建議日常關(guān)閉自動提交容易忘記提交導(dǎo)致鎖表只有做批量數(shù)據(jù)修復(fù)時(shí)才會臨時(shí)關(guān)掉。3.2 查詢結(jié)果的格式化控制默認(rèn)的查詢結(jié)果格式叫“aligned”字段名和值用豎線分隔。實(shí)際場景中我們經(jīng)常需要把結(jié)果導(dǎo)出成 CSV 或?qū)R格式這里有幾個(gè)實(shí)用設(shè)置\pset format csv SELECT * FROM users LIMIT 10;執(zhí)行后結(jié)果直接以 CSV 形式輸出不帶表頭時(shí)用\pset format unaligned \pset tuples_only ontuples_only設(shè)置為 on 后只顯示數(shù)據(jù)行不顯示列名。這個(gè)組合常用于腳本處理。如果你想恢復(fù)默認(rèn)\pset format aligned \pset tuples_only off我遇到過不少同事喜歡把查詢結(jié)果粘貼到 Excel 里處理但默認(rèn)格式粘貼過去會錯(cuò)位。其實(shí) psql 自帶\copy命令可以直接把查詢結(jié)果導(dǎo)出為 CSV\copy (SELECT id, name, created_at FROM users) TO /tmp/users.csv WITH CSV HEADER注意\copy是 psql 客戶端本地讀寫文件權(quán)限和路徑都以你本地機(jī)器為準(zhǔn)而COPY是服務(wù)端讀寫文件文件必須在數(shù)據(jù)庫服務(wù)器上且對 postgres 用戶可寫。新手經(jīng)常搞混這兩個(gè)命令報(bào)“權(quán)限不足”或者“文件不存在”時(shí)先確認(rèn)自己用的是哪個(gè)。3.3 用 \gset 把查詢結(jié)果變成變量psql 支持變量替換最經(jīng)典的用法是SELECT count(*) AS cnt FROM users; \gset SELECT :cnt;\gset會把查詢結(jié)果的每一列賦值給同名變量之后用:cnt引用。這個(gè)能力在寫自動化腳本時(shí)非常有用。比如你想在腳本里獲取當(dāng)前序列值再插入SELECT nextval(order_seq) AS new_id; \gset INSERT INTO orders (id, detail) VALUES (:new_id, test);還可以用\if、\elif、\else做條件判斷結(jié)合\gset實(shí)現(xiàn)簡單的流程控制。雖然 psql 不是編程語言但做輕量自動化足夠了。4. 快捷鍵與命令行編輯技巧效率翻倍的關(guān)鍵4.1 行編輯與歷史記錄psql 底層依賴 readline 庫所以它支持大部分 bash 風(fēng)格的快捷鍵。我實(shí)際高頻使用的主要這幾個(gè)快捷鍵作用使用場景上/下箭頭瀏覽歷史命令執(zhí)行過的 SQL 按順序翻CtrlA / CtrlE光標(biāo)跳到行首/行尾修改長 SQL 的開頭或結(jié)尾CtrlW刪除光標(biāo)前一個(gè)單詞去掉寫錯(cuò)的條件字段CtrlU刪除光標(biāo)到行首全部內(nèi)容整條命令寫廢時(shí)快速清空CtrlK刪除光標(biāo)到行尾全部內(nèi)容只保留已輸入的前半段CtrlY粘貼剛才刪掉的文本配合 CtrlU / CtrlK 使用CtrlL清屏屏幕太亂時(shí)Tab 鍵自動補(bǔ)全表名、列名、函數(shù)名寫長表名時(shí)尤其好用Tab補(bǔ)全是我強(qiáng)烈推薦新手盡早習(xí)慣的功能。psql 能補(bǔ)全的關(guān)鍵字范圍很廣輸入sel再按 Tab會補(bǔ)全為SELECT輸入表名前幾個(gè)字母再 Tab會列出匹配的表名輸入\d后按 Tab 也能補(bǔ)全關(guān)系名。有了這個(gè)功能很長的表名根本不用手敲既快又不容易寫錯(cuò)。歷史記錄默認(rèn)存在~/.psql_history文件。如果你不希望某個(gè)敏感 SQL 被記錄可以在輸入之前加一個(gè)空格前提是設(shè)置變量HISTCONTROLignorespace。不過這個(gè)變量是 psql 的\set還是環(huán)境變量實(shí)際是 psql 的\set HISTCONTROL ignorespace執(zhí)行后以空格開頭的命令不會進(jìn)歷史。但說實(shí)話我很少用更建議敏感操作在事務(wù)中執(zhí)行并謹(jǐn)慎確認(rèn)。4.2 多行編輯與重新編輯psql 里 SQL 可以跨多行寫直到分號才執(zhí)行。如果你寫了一行發(fā)現(xiàn)想改上面某行最簡單的辦法是直接按 CtrlC 取消當(dāng)前輸入回到提示符重新開始。但如果你已經(jīng)連續(xù)寫了好幾行CtrlC 會全部清掉。此時(shí)按\e或\ev、\ef可以打開系統(tǒng)默認(rèn)編輯器通常是 vi 或 nano來編輯當(dāng)前的查詢緩沖區(qū)。舉個(gè)例子SELECT * FROM users WHERE created_at 2024-01-01;在分號前按\e就會用編輯器打開這段 SQL改完后保存退出psql 會把內(nèi)容重新送進(jìn)緩沖區(qū)讓你繼續(xù)執(zhí)行。\ef可以直接編輯函數(shù)定義\ev編輯視圖定義這兩個(gè)命令對快速修改數(shù)據(jù)庫對象非常方便。4.3 修改默認(rèn)編輯器\e調(diào)用的是EDITOR環(huán)境變量指定的編輯器。如果你更喜歡 vimexport EDITORvim psql或者在 psql 內(nèi)部執(zhí)行\(zhòng)setenv EDITOR vim同樣可以為VISUAL設(shè)置優(yōu)先級不同。建議統(tǒng)一設(shè)置EDITOR和VISUAL。我在服務(wù)器上更喜歡用 vim 做復(fù)雜編輯因?yàn)?vim 有語法高亮、列編輯模式改多行 SQL 比在終端直接敲舒服得多。5. 腳本化執(zhí)行與自動化運(yùn)維實(shí)戰(zhàn)5.1 通過 -f 和 -c 執(zhí)行外部腳本psql 最常見的腳本執(zhí)行方式是psql -h host -U user -d db -f /path/to/script.sql腳本里可以包含 SQL 和元命令混合內(nèi)容。比如一個(gè)初始化腳本init.sql\set ON_ERROR_STOP on BEGIN; CREATE TABLE IF NOT EXISTS app_user ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT now() ); COMMIT;加上ON_ERROR_STOP后一旦腳本中某條語句報(bào)錯(cuò)psql 立即停止后續(xù)執(zhí)行不加這個(gè)設(shè)置默認(rèn)會報(bào)錯(cuò)后繼續(xù)跑下一條在批量執(zhí)行時(shí)非常危險(xiǎn)。我建議在所有生產(chǎn)環(huán)境變更腳本里都寫上\set ON_ERROR_STOP on并且配合事務(wù)使用。單條命令直接執(zhí)行可以用psql -d mydb -c TRUNCATE TABLE temp_log;-c后面跟分號也可以。區(qū)別在于-c執(zhí)行完立即退出而-f執(zhí)行完也退出。想連進(jìn)去再繼續(xù)操作就用普通的交互模式。5.2 在 shell 腳本中安全調(diào)用 psql寫 Shell 腳本時(shí)我習(xí)慣把所有數(shù)據(jù)庫參數(shù)放到一個(gè)配置文件里然后用變量傳遞。舉一個(gè)實(shí)際備份歸檔的例子#!/bin/bash DB_HOST192.168.1.20 DB_PORT5432 DB_USERops DB_NAMEappdb export PGPASSWORDS3curePass! psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME EOF \set ON_ERROR_STOP on \pset tuples_only on SELECT backup_start, now(); -- 這里執(zhí)行需要?dú)w檔的數(shù)據(jù)操作 EOF這里用 heredoc 傳 SQL注意結(jié)束符EOF加了單引號避免 shell 對$、反引號做變量替換。如果 SQL 里需要用到 psql 的變量就去掉單引號并小心轉(zhuǎn)義。關(guān)于密碼PGPASSWORD寫在腳本里有泄露風(fēng)險(xiǎn)更好的做法是使用.pgpass文件并設(shè)置權(quán)限為 600。5.3 使用變量與回調(diào)處理動態(tài)腳本psql 支持-v參數(shù)向腳本傳遞變量psql -d mydb -v start_date2024-06-01 -v end_date2024-06-30 -f report.sql腳本report.sql中可以用:start_date引用變量并自動添加單引號SELECT * FROM orders WHERE created_at BETWEEN :start_date AND :end_date;這里注意:start_date是帶引號的安全插入方式如果直接寫:start_date值會按字面插入可能在 SQL 注入或類型錯(cuò)誤。變量還能配合條件判斷做動態(tài)分支\if :is_debug SELECT debug mode; \else SELECT release mode; \endif這個(gè)能力在部署腳本里很實(shí)用同一套腳本測試環(huán)境傳is_debugtrue生產(chǎn)傳false。6. 提升效率的高級配置與個(gè)性化設(shè)置6.1 通過 .psqlrc 定制啟動環(huán)境每次進(jìn)入 psql 都會自動執(zhí)行~/.psqlrc文件存在則加載。我個(gè)人的.psqlrc長這樣\pset null NULL \pset pager on \x auto \set HISTSIZE 10000 \set COMPKEYWORD_STATUS 1逐行解釋一下\pset null NULL將數(shù)據(jù)庫中的 NULL 顯示為字符串NULL否則默認(rèn)顯示為空白容易和空字符串混淆\pset pager on查詢結(jié)果超過一屏?xí)r自動用分頁器less顯示。但我不太喜歡它頻繁翻頁有時(shí)直接\pset pager off關(guān)掉容忍結(jié)果滾動\x auto僅當(dāng)結(jié)果列太寬時(shí)才自動切換擴(kuò)展顯示這是我在 psql 16 里最喜歡的設(shè)置\set HISTSIZE 10000歷史命令保留 10000 條默認(rèn)只有 500 條\set COMPKEYWORD_STATUS 1啟用關(guān)鍵字大小寫自動補(bǔ)全行為。注意如果想臨時(shí)跳過.psqlrc啟動時(shí)加-X參數(shù)psql -X -d mydb這在排查某些用戶環(huán)境問題時(shí)很有用避免個(gè)人配置干擾判斷。6.2 使用 \timing 觀察執(zhí)行耗時(shí)\timing是另一把好用的尺子。開啟后每條 SQL 執(zhí)行完會自動顯示耗時(shí)mydb# \timing on Timing is on. mydb# SELECT count(*) FROM big_table; count ------- 1000000 (1 row) Time: 235.812 ms這個(gè)信息對于定位慢查詢非常直接。配合EXPLAIN ANALYZE使用的話EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table WHERE id 500000;輸出的執(zhí)行計(jì)劃里有實(shí)際執(zhí)行時(shí)間、掃描行數(shù)、緩沖命中情況。我一般先開\timing記錄整體時(shí)間再做EXPLAIN ANALYZE看細(xì)節(jié)兩者互補(bǔ)。6.3 自定義提示符與顏色提示符可以顯示當(dāng)前數(shù)據(jù)庫、用戶名、主機(jī)、甚至事務(wù)狀態(tài)。默認(rèn)提示符%/%R%#已經(jīng)包含數(shù)據(jù)庫名和事務(wù)標(biāo)志。想自定義的話\set PROMPT1 %n%M:% %echo -n \$? %/%R%# 這個(gè)會把提示符顯示為userhost:port dbname#。不過我更推薦一種極簡配置\set PROMPT1 %/%R%# 如果你有多個(gè)環(huán)境比如開發(fā)庫、測試庫、生產(chǎn)庫可以把數(shù)據(jù)庫名或連接信息用顏色區(qū)分。psql 16 支持在提示符中使用顏色轉(zhuǎn)義序列例如\set PROMPT1 %[%033[1;31m%]%/%R%#%[%033[0m%] 紅色的提示符能直觀提醒你“當(dāng)前在關(guān)鍵環(huán)境”降低誤操作風(fēng)險(xiǎn)。不過這個(gè)顏色方案在部分終端下可能不生效建議先在實(shí)際終端里測試。7. 常見問題排查與避坑實(shí)錄7.1 報(bào)錯(cuò) “FATAL: database does not exist”很多新手連接時(shí)直接psql -U postgres如果沒指定-d默認(rèn)會嘗試連接與用戶名同名的庫。比如你的系統(tǒng)用戶是rootpsql會嘗試連一個(gè)叫root的數(shù)據(jù)庫然后報(bào)錯(cuò)psql: error: connection to server on socket /var/run/postgresql/.s.PGSQL.5432 failed: FATAL: database root does not exist解決辦法明確指定數(shù)據(jù)庫-d postgres或者在環(huán)境變量里設(shè)置PGDATABASEpostgres。出現(xiàn)這種報(bào)錯(cuò)時(shí)先檢查自己當(dāng)前是什么用戶、要連哪個(gè)庫別急著懷疑服務(wù)沒啟動。7.2 口令認(rèn)證失敗與 .pgpass 配置password authentication failed for user xx是最常見的認(rèn)證報(bào)錯(cuò)之一。除了密碼真的輸錯(cuò)還有可能是 pg_hba.conf 里規(guī)定了認(rèn)證方式不是 md5/scram。排查順序確認(rèn)用戶名和密碼查看連接是否走到預(yù)期的 pg_hba 規(guī)則可以用SHOW hba_file;找到文件位置嘗試psql -h 127.0.0.1和psql -h /var/run/postgresql分別連確認(rèn) socket 和 TCP 是否都允許。日常自動化中我建議配置.pgpass# ~/.pgpass localhost:5432:mydb:username:supersecret然后設(shè)置權(quán)限chmod 600 ~/.pgpass注意.pgpass文件每行格式是hostname:port:database:user:password其中數(shù)據(jù)庫也可以用*匹配所有庫。這個(gè)文件比PGPASSWORD安全一截因?yàn)椴粫霈F(xiàn)在進(jìn)程環(huán)境變量里。7.3 psql 卡住不動可能是等待鎖執(zhí)行SELECT * FROM some_table一直不返回大概率是表被其他事務(wù)鎖住了。這時(shí)候不要直接 CtrlC 了事應(yīng)該另開一個(gè) psql 查鎖和阻塞SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state IS NOT NULL;看到wait_event_type為Lock時(shí)基本可以判斷在等鎖。再進(jìn)一步查阻塞鏈SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid ANY(pg_blocking_pids(blocked.pid));這個(gè)查詢很實(shí)用能直接揪出誰是“攔路虎”。碰上長時(shí)間持鎖的事務(wù)和業(yè)務(wù)方確認(rèn)后可以用pg_terminate_backend(pid)終止。7.4 亂碼與編碼問題遇到中文亂碼時(shí)先檢查客戶端編碼SHOW client_encoding;正常應(yīng)該是UTF8。如果顯示SQL_ASCII或其他執(zhí)行SET client_encoding TO UTF8;也可以啟動時(shí)加環(huán)境變量PGCLIENTENCODINGUTF8。另外文件導(dǎo)入時(shí)注意文件本身的編碼要和數(shù)據(jù)庫字符集匹配通常建議用 UTF-8 無 BOM 格式。8. 從交互到自動化構(gòu)建自己的 psql 工作流8.1 與常見運(yùn)維場景結(jié)合psql 的價(jià)值遠(yuǎn)不止敲幾條 SQL。舉幾個(gè)我實(shí)際長期使用的場景定期清理歸檔結(jié)合 crontab 和-f腳本每周清理過期分區(qū)表監(jiān)控連接數(shù)一條 SQL 輸出當(dāng)前連接池用量直接管道給監(jiān)控腳本生成建表語句用pg_dump --schema-only配合 psql 還原校驗(yàn)兩個(gè)環(huán)境的表結(jié)構(gòu)差異批量導(dǎo)出數(shù)據(jù)\copy導(dǎo)出 CSV 后再通過 Python pandas 處理或直接加載到數(shù)倉。下面是一個(gè)簡單的 shell 函數(shù)方便快速連到測試庫執(zhí)行任意 SQLdbq() { local db$1 local sql$2 psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $db -tAX -c $sql }調(diào)用方式dbq app select count(*) from users;-tAX三個(gè)參數(shù)含義-t只輸出行數(shù)據(jù)-A關(guān)閉對齊無空格填充-X跳過 .psqlrc。組合起來輸出就是純文本方便繼續(xù)用 grep、awk 處理。8.2 復(fù)雜腳本的調(diào)試方法寫長 SQL 腳本時(shí)建議先打開\set ECHO all效果是 psql 會回顯每條執(zhí)行的 SQL方便對照日志排查。配合\set VERBOSITY verbose報(bào)錯(cuò)信息會更完整包含錯(cuò)誤位置和提示。如果腳本里有循環(huán)或條件邏輯可以用\timing和\echo打印過程信息\echo Start cleanup at :current_time -- 業(yè)務(wù)邏輯 \echo Cleanup finished \echo類似 shell 的 echo但可以用 psql 變量。這樣執(zhí)行時(shí)能看到進(jìn)度不至于面對黑屏到底跑了多少狀態(tài)一無所知。8.3 在 Windows 和遠(yuǎn)程環(huán)境下的注意點(diǎn)Windows 上使用 psql 時(shí)默認(rèn)終端可能是 cmd 或 PowerShellreadline 快捷鍵不完全支持。建議在 Windows 上安裝 Git Bash 或 WSL這樣能拿到和 Linux 一致的補(bǔ)全和歷史體驗(yàn)。另外Windows 的換行符是 CRLF如果編寫的 SQL 腳本是從 Windows 傳到 Linux 服務(wù)器上執(zhí)行偶爾會遇到\r導(dǎo)致的語法錯(cuò)誤可以用sed -i s/\r$// script.sql清理一下。如果是通過 SSH 遠(yuǎn)程連接注意長期空閑的連接可能被服務(wù)器斷開?？梢栽?psql 里執(zhí)行一些輕量查詢保持活躍或者配置 SSH 的ServerAliveInterval。9. 一些我踩過坑之后才明白的小技巧最后想分享幾個(gè)不太常見但很實(shí)用的細(xì)節(jié)這些是我用了多年 psql 之后才慢慢積累下來的。第一個(gè)是\watch命令。在 SQL 語句后面輸入\watch 2psql 會每 2 秒重復(fù)執(zhí)行一次同一個(gè)查詢直到你按 CtrlC。我在排查鎖等待、觀察 count 變化、盯某個(gè)指標(biāo)是否回暖時(shí)經(jīng)常用它比反復(fù)按上箭頭回車舒服太多。第二個(gè)是\sf和\df配合查看函數(shù)定義。\df function_name能看到函數(shù)的源碼位置、參數(shù)、返回類型、安全屬性等\sf function_name直接輸出函數(shù)完整定義可以直接復(fù)制到編輯器里改。這兩個(gè)命令在別人維護(hù)的庫里接手時(shí)極其好用。第三個(gè)是關(guān)于NULL的陷阱。在 psql 里執(zhí)行SELECT NULL;默認(rèn)顯示一個(gè)空字符串如果不仔細(xì)看會誤以為結(jié)果為空。配置\pset null [NULL]后所有 NULL 都會顯示成[NULL]一眼就能分辨。這個(gè)配置我強(qiáng)烈建議寫進(jìn).psqlrc。第四個(gè)是善用\gexec。比如你想對所有以2023_開頭的分區(qū)表執(zhí)行同一個(gè)操作可以先生成動態(tài) SQL然后交給\gexec執(zhí)行SELECT format(VACUUM (ANALYZE) %I;, tablename) FROM pg_tables WHERE tablename LIKE 2023\_% \gexec它會逐條執(zhí)行生成的每條 SQL省去手寫循環(huán)的麻煩。我個(gè)人的體會是psql 這門手藝不值得刻意去背命令清單最有效的學(xué)習(xí)方式是把它當(dāng)成日常操作的主要入口遇到問題先想“psql 能不能做到”再去查\?和\h。用一兩個(gè)星期后你會發(fā)現(xiàn)自己已經(jīng)離不開它的補(bǔ)全、歷史、變量和腳本能力。等哪天真要在大促凌晨處理一個(gè)線上緊急變更能讓你穩(wěn)住陣腳的往往不是圖形界面里那個(gè)鼠標(biāo)而是這個(gè)樸素命令行里飛快跑過的幾行綠色輸出。