Postgres 設定系統參數 (GUC) 的小小注意事項

PostgreSQL 的設定檔 $PGDATA/postgresql.conf 是一份落落長的文字檔，裡面放了大多數要設定的資料庫參數（稱作 GUC），大多狀況要進行設定的參數僅僅十多項而已，但編輯的時候，總是令人頭昏眼花。

不過，除了直接修改 $PGDATA/postgresql.conf 內容之外，其實還可以用 ALTER SYSTEM 這個 SQL 指令調整這些參數值。

透過 ALTER SYSTEM 指令設定的參數，會將設定值直接寫到 $PGDATA/postgresql.auto.conf 檔案內。紀錄在這份檔案中的設定，在資料庫運作時，會覆蓋掉 $PGDATA/postgresql.conf 的設定。

在這種設置下，$PGDATA/postgresql.conf 可以當成純參考的文件來用（Note：其實在資料庫軟體目錄的 share/ 子目錄，例如，/usr/pgsql-11/share/，裡面的 postgresql.conf.sample 就是原始內容了）

使用 ALTER SYSTEM 調整參數值有幾個好處：

• 會幫忙檢查參數；若在 psql 指令界面下，可以用 Tab 按鍵補完指令～
• Bug Fix 版本若有新增參數時，可能會希望更新資料庫的參數檔：例如，近日為了因應 OS 的 fsync() 在 Edge Case 下的潛在疑慮而增加新的 GUC，data_sync_retry
• 在升大版時，異動參數統一集中，比較方便攜帶到新版資料庫，不用消耗眼睛對照曾經調整的參數值。
• 設定 Streaming Replication 時，Standby 資料庫的參數可能希望微微的與 Master 資料庫不太一樣，可以很方便的比對兩造在設定上的差異
• 想要自動化設定參數時，不用為了如何湊出一句 Regualr Expression 而困擾！（這個最重要～）
• PGSQL 12 開始，recoveryconf 設定將合併到單一個 postgresql.conf 設定檔裡面，使用 ALTER SYSTEM 能夠有更一致的設定方式～

另外，若有多安裝一些外掛（如，內建的 pg_stat_statements 、企業版內建的 SQL/Protect 或是需要額外裝的外掛 pg_audit），這些參數可以透過直接編輯 $PGDATA/postgresql.auto.conf 達成～（這份檔案卷首，寫著 “Do not edit this file manually”，不過這邊指的是會被 ALTER SYSTEM 調整的意思～）

ALTER SYSTEM 的使用方式很簡單，幾乎跟 Runtime 設定參數的方式一樣

ALTER SYSTEM SET some_guc_parameter TO xxx;

其中的 xxx 就是設定值，依照每個參數而有不同，大多都可以用單引號框住。

當然也可以直接編輯 $PGDATA/postgresql.auto.conf。這邊習慣用 Bash 的 heredoc 來處理，範例如下：

[enterprisedb@epaslab ~]$ cat << EOF >> $PGDATA/postgresql.auto.conf
wal_level = 'logical'
max_wal_senders = 15
EOF
[enterprisedb@epaslab ~]$ 

不過，有一個特殊一點的設定方式，會發生在設置載入函式庫（local_preload_libraries, session_preload_libraries, shared_preload_libraries）的時候。
以下的設定方式，直接把一串 Library 檔案路徑，用單個字串設定，如此會出現不恰當的輸出結果

edb=# alter system set shared_preload_libraries to '$libdir/dbms_pipe, $libdir/edb_gen, $libdir/dbms_aq, $libdir/sql-profiler';
ALTER SYSTEM
edb=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
oracle_home = '/usr/lib/oracle/11.2/client64/lib/'
max_wal_senders = '5'
hot_standby = 'on'
full_page_writes = 'on'
wal_log_hints = 'on'
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'gzip < %p > /var/lib/edb/as11/wal_archives/%f.gz'
shared_preload_libraries = '"$libdir/dbms_pipe, $libdir/edb_gen, $libdir/dbms_aq, $libdir/sql-profiler"'
edb=# 

原因是這邊填寫的是作業系統的目錄跟路徑，上面的黃色部份，會被資料庫當作單一個路徑來執行，然後在重新啟動時就會出錯。

正確的方式，要如下面示範，使用個別引號來列舉才行

edb=# show shared_preload_libraries ;
                shared_preload_libraries                 
---------------------------------------------------------------
 $libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,$libdir/plr
(1 row)

edb=# alter system set shared_preload_libraries to "$libdir/dbms_pipe","$libdir/edb_gen","$libdir/dbms_aq","$libdir/sql-profiler";
ALTER SYSTEM
edb=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
oracle_home = '/usr/lib/oracle/11.2/client64/lib/'
max_wal_senders = '5'
hot_standby = 'on'
full_page_writes = 'on'
wal_log_hints = 'on'
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'gzip < %p > /var/lib/edb/as11/wal_archives/%f.gz'
shared_preload_libraries = "$libdir/dbms_pipe", "$libdir/edb_gen", "$libdir/dbms_aq", "$libdir/sql-profiler"
edb=# 

然後重啟後，就看到正常的載入參數了

edb=# show shared_preload_libraries ;
                          shared_preload_libraries                           
-----------------------------------------------------------------------------------
 "$libdir/dbms_pipe", "$libdir/edb_gen", "$libdir/dbms_aq", "$libdir/sql-profiler"
(1 row)

edb=#

最後，雖然 ALTER SYSTEM 算是 SQL 的 DDL 類型語句，但並不會被 Streaming Replication 同步到 Standby DB 內：這種設計，保留了個別資料庫可以有獨立設定的特性～
不過！要留心某些資料庫參數，會要求 Master/Standby DB 要一致才行，不然以後重啟資料庫會拒絕啟動的。