Postgres 筆記

2023年7月20日星期四

PostgreSQL 錯誤訊息：Connection to client lost

在監控觀察 PostgreSQL 資料庫的 Logs 時，有時候會觀察到錯誤訊息 Connection to client lost ，這時候會有兩個問題，第一個是這錯誤訊息跟誰有關，第二個是這錯誤訊息代表什麼意思。

2023年7月18日星期二

透過 nmap 檢核 TLS 安全性

透過 nmap 指令可以查看該 Port 支援的 TLS 版本、以及支援的 Ciphers，在處理弱掃問題時可以用來輔助測試。

nmap --script +ssl-enum-ciphers -p 3000 127.0.0.1

[root@localhost ~]# nmap --script +ssl-enum-ciphers -p 3000 127.0.0.1
Starting Nmap 7.70 ( https://nmap.org ) at 2023-07-18 10:44 CST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.00038s latency).

PORT     STATE SERVICE
3000/tcp open  ppp
| ssl-enum-ciphers:
|   TLSv1.2:
|     ciphers:
|       TLS_RSA_WITH_AES_128_GCM_SHA256 (rsa 2048) - A
|       TLS_RSA_WITH_AES_256_GCM_SHA384 (rsa 2048) - A
|       TLS_RSA_WITH_AES_128_CBC_SHA (rsa 2048) - A
|       TLS_RSA_WITH_AES_256_CBC_SHA (rsa 2048) - A
|     compressors:
|       NULL
|     cipher preference: server
|_  least strength: A

參考資料：

https://github.com/grafana/grafana/pull/7347

2023年6月28日星期三

Postgres 14 新功能回顧：Toast 壓縮支援 Lz4 演算法

在 Postgres 每個 Page 的儲存大小為 8kB，由於單筆資料 (tuple) 不允許跨 Page 儲存，當超過此大小限制時，則會壓縮後另外儲存，這塊空間被稱為 Toast。

在 PG 14 對 Toast 功能新增了 Lz4 演算法的支援，使用上對比於原本的 pglz 壓縮方式，在速度上還有佔用空間大小都有肉眼可見接近或超過十倍以上的改善。

想要確認是否有使用到 Toast 的功能可以用以下指令，確認目前 toast 的空間使用量。

select 
  relnamespace::regnamespace, 
  relname, 
  reltoastrelid::regclass, 
  pg_relation_size(reltoastrelid) as toast_size 
from pg_class 
where reltoastrelid != 0 
and relnamespace::regnamespace::text = 'public'  --指定 Schema 可選擇註解掉
order by toast_size desc limit 30;

參考資料：

https://www.postgresql.org/docs/current/storage-toast.html

https://www.dbi-services.com/blog/postgresql-14-lz4-compression-for-toast/

2023年6月20日星期二

PostgreSQL 資料庫執行異常緩慢兇手 LWLock : SubtransSLRU

在前年的時候 Gitlab 發布了一篇文章分享他們如何以及為何要排除 Subtransaction。

https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/

2023年6月6日星期二

Linux 環境變數 $ 符號特殊使用技巧 (取代、預設值)

${VAR:=XXX} 賦予預設值 (可以用來防呆，避免指定目錄時因為環境變數空的導致用錯目錄)

${#VAR} 顯示長度

${VAR/a/b} 取代第一個匹配到的 a 為 b

${VAR//a/b} 取代所有匹配到的 a 為 b

參考顯示結果：

[root@ ~]# echo ${VAR}

[root@ ~]# echo ${VAR:=abcabc}
abcabc
[root@ ~]# echo ${VAR/a/b}
bbcabc
[root@ ~]# echo ${VAR//a/b}
bbcbbc
[root@~]# echo ${#VAR}
6

2023年6月2日星期五

Postgres 備份還原

在 Postgres 有許多種全資料庫備份方式，不管是那種備分方式都會面臨一個相同問題，備分期間資料庫仍持續在運作寫入，而備分只能備走硬碟上的資料，部分仍在記憶體的資訊不會被記錄到，因此這些全備份檔案的資料並不是完整的，需要搭配備分期間產生的交易日誌檔案 WAL 才算是完整備分，這點需要特別注意。

2023年5月31日星期三

PostgreSQL 指令 pg_dump 與 pg_restore 使用方法

PostgreSQL 有許多種資料匯出與備份的方式，其中一種就是透過 pg_dump 進行 file dump 的方式，在較小的資料庫可以作為一種備分與升級手段。

在使用上一般可以搭配 pg_dumpall 來做到快速的備份還原以及升級。

1. 透過 pg_dumpall 匯出 Schema 後還原至目標

pg_dumpall -s -f $(date +%Y%m%d"_pg_dumpall.sql")
psql -f 20230531_pg_dumpall.sql

2. 使用 pg_dump 多執行緒匯出以及 pg_restore 還原資料

/usr/edb/as14/bin/pg_dump --data-only -Fd -j 4  --disable-trigger -f ~/test.sql --dbname=test
/usr/edb/as14/bin/pg_restore --data-only -Fd -j 4  --disable-trigger --dbname=test  ~/test.sql

PostgreSQL 刪除帳號 - 相依物件處理方法

在資料庫管理上，會因為一些人員異動或規劃設計調整有刪除帳號的需求，為了避免有資料庫遺失，我們需要將該帳號所擁有的物件皆轉給其他帳號再安全的刪除，避免透過 CASCADE 方式以免發生誤刪狀況。

在 PostgreSQL 刪除帳號指令如下

DROP USER user_a ;

DROP USER user_a  CASCADE;  -- 除非有把握不然不建議，可能會誤刪除

當有遇到相依問題時，則會報錯如下，如果沒有顯示物件名稱代表可能有多個資料庫要去其他資料庫檢查。

edb=# create user user_a;
CREATE ROLE
Time: 26.105 ms
edb=# grant USAGE on SCHEMA monitor TO user_a ;
GRANT
Time: 26.957 ms
edb=# drop user user_a;
ERROR:  role "user_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema monitor
Time: 0.792 ms
edb=#

主要會有兩種相依性卡到：

1. 物件擁有者

2. 物件的使用權限 (GRANT 給該帳號對個別物件存取的權限，如上面示範)

除了透過上面顯示錯誤訊息一個個處理以外，可以透過以下兩個指令快速處理相依性問題

-- 將該帳號擁有的物件擁有者改成另一個使用者
REASSIGN OWNED BY user_A TO user_B ;
-- 移除該帳號擁有的權限
DROP OWNED BY user_A ;

要注意執行的順序 !!!

先執行 REASSIGN 把物件都轉移後，才執行刪除，此處 DROP OWNED BY 僅會移除權限，不會移除物件。

在所有資料庫執行完以上兩個指令後，就可以正常的把該帳號移除了~

* 以上主要是針對一般使用者，如果有做群組管理 ( GRANT user_a TO user_b) ，不會有相依性問題影響刪除，但是群組關係會直接被移除掉，可能要在注意，雖然一般可能不太會有這種情境

2023年5月29日星期一

LInux 檢查各 Process 開檔案數量方法

檢查 /proc/<x>/fd/ 目錄的檔案數量可以用來查看對應 Process 開檔數量

透過 find 指令查詢全部 Process 開檔狀況

find /proc/*/fd -maxdepth 0 -print0 | xargs -0 -I{} sh -c 'printf "%4d : %s\n" "$(ls {}| wc -l)" "{}"'

參考資料：

https://stackoverflow.com/questions/15216370/how-to-count-number-of-files-in-each-directory

https://blog.csdn.net/weixin_34773479/article/details/112335204?spm=1001.2101.3001.6650.4&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-4-112335204-blog-89020809.235%5Ev36%5Epc_relevant_default_base&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-4-112335204-blog-89020809.235%5Ev36%5Epc_relevant_default_base&utm_relevant_index=3

2023年5月22日星期一

PostgreSQL 找出造成卡住 Blocked 狀況的 Leading SQL

PG 在發生 Locks 時，在一些較複雜的系統常常會看到 Blocked Chain 的狀況，B 使用者被 A 使用者查詢卡住，造成 C 使用者又被 B 使用者卡住，這種時候，雖然會查到很多 Locked 的狀況，但不需要全部砍掉，通常只要將源頭砍掉就會解開了，因此基於原本的 Blocked SQL 做加工。

Blocking 代表卡住造成別人無法執行查詢的 SQL，但這個 SQL 也有可能被前面的 SQL 卡住，而 Bloced 代表被卡住的 SQL，主要透過該 Session 的 Wait Event 來做判斷，如果為 Lock 就代表他有被別人卡住，如果是其他 Client Read 等等就代表正在順利執行或是 idle in transaction (通常是這個)，則可以判斷是卡住的源頭。

查詢 SQL

SELECT
  COALESCE(blockingl.relation::regclASs::text,blockingl.locktype) AS locked_item,
  blockinga.pid AS blocking_pid,
  blockinga.query AS blocking_query, 
  blockingl.mode AS blocking_mode,
  CASE 
    WHEN blockinga.wait_event_type = 'Lock' THEN 'False'
    ELSE 'True'
  END  AS leading_lock,
  blockeda.pid AS blocked_pid, 
  blockeda.query AS blocked_query,
  blockedl.mode AS blocked_mode,
  now() - blockeda.query_start AS blocked_time
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted
order by leading_lock desc;

查詢結果

   locked_item    | blocking_pid |       blocking_query        |    blocking_mode    | leading_lock | blocked_pid |       blocked_query        |    blocked_mode     |  blocked_time
------------------+--------------+-----------------------------+---------------------+--------------+-------------+----------------------------+---------------------+-----------------
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | ShareLock           | True         |      548398 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:29.498179
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | ShareLock           | True         |      548484 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:23.333006
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | AccessExclusiveLock | True         |      548398 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:29.498179
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | AccessExclusiveLock | True         |      548484 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:23.333006
 pgbench_accounts |       548398 | truncate pgbench_accounts;  | AccessExclusiveLock | False        |      548484 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:23.333006
 pgbench_accounts |       548484 | truncate pgbench_accounts;  | AccessExclusiveLock | False        |      548398 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:29.498179

參考資料：

https://ravenonhill.blogspot.com/2019/08/pgsql-who-blocks-my-query.html