Postgres 筆記

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

2023年5月10日星期三

2023年4月6日星期四

Postgres 匯出 Idx, pkey, fkey DDL 方法

可以透過 pg_dump -s 搭配 grep 取得，也可以直接在資料內依據需求查詢出來，由於有相依關係，在還原時會需要依序 idx → pkey → fkey 的順序建立

2023年2月18日星期六

PostgreSQL Windows psql 指令介面安裝與 UTF-8 編碼異常排解

2022年9月26日星期一

Postgres 查找 Temp Table 建立者

Postgres 目前無系統表可以查詢 Temp Table 的建立者，

當某個 Temp Table 使用過多資源或是引發一些異常行為需要清除時，

又無法透過詢問使用者確認具體是哪個連線造成，

就需要透過一些小手段來達成。

2022年7月19日星期二

PostgreSQL WAL 交易量大小查詢

資料庫在進行相關的效能調教評估，像是 checkpoint_timeout 與 max_wal_size 的大小設定，就會需要了解資料庫的一段時間內的交易量大小。

一般來說最簡單查看的方式會是對實際的交易檔案 WAL 產出時間/大小來判斷，但這樣的方式會受到 Archive 狀況影響，也不是很好查看。

在 PostgreSQL 內建相關的 WAL 操作語法可以很簡單的查看交易量，透過 pg_wal_lsn_diff (pg_xlog_location_diff) 這個 Function 去比對不同時段 lsn 位置差異即可以得出交易量大小，平常僅需要定期透過排程去收集 pg_current_wal_lsn(pg_current_xlog_location) 的紀錄，就可以根據需要查看的時間區間進行比對查看。

edb=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 20/3B000148
(1 row)

edb=# – 一些交易
edb=# create table ta (id int);
CREATE TABLE

edb=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 20/3B014C40
(1 row)

edb=# select pg_size_pretty(pg_wal_lsn_diff('20/3B014C40','20/3B000148'));
 pg_size_pretty
----------------
 83 kB
(1 row)

2022年7月11日星期一

PostgreSQL 跨 OS (Rhel) 版本 Streaming Replication 同步測試

近期在研究 Postgresql 升版的手段，在沒有升級 OS 需求下可以很簡單透過 pg_upgrade 完成升版作業，本處主要討論跨 OS 版本升版的可能性，一般小型資料庫如果使用 pg_dump/pg_restore 或是採用 Logical Replication 的手法做升級就算是跨 OS 版本也可以很簡單的進行升級，但採用內建同步機制時則會需要注意跨 OS 版本的影響。

(考量整體執行速度、執行步驟等因素，pg_upgrade 為當前最穩定建議的升級手法)

訂閱：文章 (Atom)

Postgres 筆記 - Ken

2023年6月20日星期二

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

2023年6月6日星期二

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

2023年6月2日星期五

Postgres 備份還原

2023年5月31日星期三

PostgreSQL 指令 pg_dump 與 pg_restore 使用方法

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

2023年5月29日星期一

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

2023年5月22日星期一

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

2023年5月10日星期三

iptable 設定方式筆記

2023年4月6日星期四

Postgres 匯出 Idx, pkey, fkey DDL 方法

2023年2月18日星期六

PostgreSQL Windows psql 指令介面安裝與 UTF-8 編碼異常排解

2022年9月26日星期一

Postgres 查找 Temp Table 建立者

2022年7月19日星期二

PostgreSQL WAL 交易量大小查詢

2022年7月11日星期一

PostgreSQL 跨 OS (Rhel) 版本 Streaming Replication 同步測試

2023年6月20日 星期二

2023年6月6日 星期二

2023年6月2日 星期五

2023年5月31日 星期三

2023年5月29日 星期一

2023年5月22日 星期一

2023年5月10日 星期三

2023年4月6日 星期四

2023年2月18日 星期六

2022年9月26日 星期一

2022年7月19日 星期二

2022年7月11日 星期一

2023年6月20日星期二

2023年6月6日星期二

2023年6月2日星期五

2023年5月31日星期三

2023年5月29日星期一

2023年5月22日星期一

2023年5月10日星期三

2023年4月6日星期四

2023年2月18日星期六

2022年9月26日星期一

2022年7月19日星期二

2022年7月11日星期一