近期在研究 Postgresql 升版的手段，在沒有升級 OS 需求下可以很簡單透過 pg_upgrade 完成升版作業，本處主要討論跨 OS 版本升版的可能性，一般小型資料庫如果使用 pg_dump/pg_restore 或是採用 Logical Replication 的手法做升級就算是跨 OS 版本也可以很簡單的進行升級，但採用內建同步機制時則會需要注意跨 OS 版本的影響。
(考量整體執行速度、執行步驟等因素，pg_upgrade 為當前最穩定建議的升級手法)
PostgreSQL 的同步機制有以下限制：
1. 不能跨 DB 大版本間同步。
2. 可以跨 DB 小版本同步。
3. 不能跨平台同步，例如 Windows 和 Linux 間同步 (locale 不同、甚至是資料檔案結構的不同)
4. 可以跨 OS 版本進行同步，但會部分功能可能會有受到影響
這也是本篇文章想研究的議題。
Linux 系統下的 PostgreSQL 的文字排序仰賴於 OS 的 glibc 套件，
而在 CentOS 6,7 與 CentOS 8 之間 glibc 提供的 Locale 資料有所差異導致排序結果不同，此影響也會反映至 DB 內查詢結果。
CentOS 7.9
-bash-4.2$ ( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort
11
1-1
Rocky Linux 8.4
-bash-4.2$ ( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort
11
1-1
下面實際建立環境來測試看看同步後狀況。
Primary 主機為 CentOS 7.9
Standby 主機為 Rocky Linux 8.4
透過 pg_basebackup 建立 Standby 並啟動，確認同步有正常。
edb=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 3180
usesysid | 10
usename | enterprisedb
application_name | walreceiver
client_addr | 192.168.56.109
client_hostname |
client_port | 48640
backend_start | 2022-07-11 17:29:51.142143+08
backend_xmin | 1848064
state | streaming
sent_lsn | 61/9A084400
write_lsn | 61/9A084400
flush_lsn | 61/9A084400
replay_lsn | 61/9A084400
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2022-07-11 17:52:03.10258+08
建立測試表格兩邊查看
edb=# create table ta ( col text );
CREATE TABLE
edb=# insert into ta values ('11'), ('1-1');
INSERT 0 2
直接查看時兩邊查詢結果相同。
edb=# select * from ta;
col
-----
11
1-1
(2 rows)
加入排序後可以看到兩邊查詢結果不同。
edb=# -- CentOS 7.9 (Primary)
edb=# select * from ta order by col desc ;
col
-----
1-1
11
(2 rows)
edb=# -- Rocky Linux 8.4 (Standby)
edb=# select * from ta order by col desc ;
col
-----
1-1
11
(2 rows)
建立 Index 並額外產生一些測試資料進行查詢。
edb=# create index ON ta ( col );
CREATE INDEX
edb=# insert into ta select '11' from generate_series(1,1000000);
INSERT 0 1000000
edb=# analyze ta;
ANALYZE
查看執行計畫確認會走到 Index
edb=# explain select * from ta where col = '1-1';
QUERY PLAN
--------------------------------------------------------------------------
Index Only Scan using ta_col_idx on ta (cost=0.43..4.45 rows=1 width=3)
Index Cond: (col = '1-1'::text)
(2 rows)
兩邊查詢，可以看到，在 Standby 端無法查詢到資料。
由於此限制導致實際上 Rhel 系列 7 和 8 之間是不適合直接進行 Streaming Replication 同步的。
edb=# -- CentOS 7.9 (Primary)
edb=# select * from ta where col = '1-1';
col
-----
1-1
(1 row)
edb=# -- Rocky Linux 8.4 (Standby)
edb=# create index ON ta ( col );
edb=# select * from ta where col = '1-1';
col
-----
(0 rows)
如果希望能夠避免此問題會需要在建立 Database 時指定 ICU 排序規則如下語法。
create database icucollatest
with template=template0
encoding='utf8'
lc_collate='zh_TW.utf8'
ICU_SHORT_FORM='ZH-HANT-TW-X-ICU';
兩個不同 OS 版本的資料庫皆可正確查到資料。
icucollatest=# select * from ta where col = '1-1';
col
-----
1-1
(1 row)
透過上面測試得到結論，跨 RHEL 7 / 8 之間長時間同步是不可行的，但作為升版的一個過渡手段應該是可以考慮的，不過可能會需要評估在作業過程中可能會需要在升版前先 DROP INDEX 再升版，完成後再重建，或是升版完成後執行 REINDEX會是必要的 (目前有看到一些案例無法正常 REINDEX)，也建議做過完整測試後再執行避免有些非預期的狀況發生。
另外補充提到如果跨版本升級欲使用 DUMP / RESTORE 的手法，採用 pg_dump -j 倒資料的話會需要注意到 pg_dump 必須要採用欲升級的新版本 Binary 而非舊版本的 Binary 以避免還原過程中遇到問題。
參考資料：
https://wiki.postgresql.org/wiki/Locale_data_changes
https://elephanttamer.net/?p=61#brokenindexes
https://ravenonhill.blogspot.com/2019/07/postgres-10-zh-tw-icu-collation.html
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.h
