PostgreSQL 的 Logical Replication 還有很多限制...

雖然之前提過很多次 PostgreSQL 的 logical replication,但最近總算是有空實際架設起來測試,發現目前的 logical replication 還在進化的過程,只能算是階段性的產品。

PostgreSQL 16 的「31.6. Restrictions」裡面有列出了目前 logical replication 的限制。

第一條其實是最痛的,不支援各種 DDL 操作,所以像是 CREATE TABLE 或是 ALTER TABLE 都不會同步,這牽扯到 DBOps 的動作需要配合,DB schema 的改變會變得很詭異,需要 case by case 處理,甚至 application 端可能也會需要配合。

The database schema and DDL commands are not replicated.

另外一個頭痛的點是 sequence 資料居然不會同步,這個工具常被用到 SERIAL 類的設計 (雖然 SERIAL 被 deprecated 了),這代表當偵測到 master 掛掉時無法直接 failover,除非有另外處理 sequence 的資料:

Sequence data is not replicated.

翻了資料發現官方 wiki 上有「Logical replication of DDLs」,裡面有今年六月的投影片:「Logical Replication of DDLs」,看起來 DDL 的部分有已經 patch 丟出來 (對 PostgreSQL 15 的 patch),但看了 PostgreSQL 16 的 release notes 裡面還沒看到,看起來還要等...

所以 logical replication 看起來還在演進的過程,目前的限制使得 logical replication 還不到能用的成熟度。

可以繼續觀察看看...

PostgreSQL 上對應 pt-online-schema-change 的工具 pg-osc

翻資料的時候翻到「pg-osc: Zero downtime schema changes in PostgreSQL」這篇文章,可以在 PostgreSQL 上做到類似 pt-online-schema-change 的事情,這邊先提一下 pt-online-schema-change 的說明:

ALTER tables without locking them.

不管是 MySQL 還是 PostgreSQL,都會遇到 ALTER TABLE 常常會 lock 的問題,這點主要的影響就是 db migration。

在 dev 環境的機器應該沒什麼問題,資料量都不大,應該是很快就可以跑完;但在 stage 環境時就會開始有狀況了 (假設是從 production 複製過來的資料,表格的大小可能偏大),但應該還是可以用 downtime 換,慢慢跑,花幾個小時把 db migration 跑完。

可是到了 production 環境時就不太能這樣搞了,這也是一般不太建議在 production 環境裡用現成的 db migration 工具,尤其當資料量偏大的時候。

解這個問題的方法就是透過繞路的方式,不要直接動原來的 table:基本的想法是開一個新的 table,然後一直從舊的 table 搬資料到新的 table 上 (包括應用程式下指令寫到舊的 table 上的資料),直到最後用一個短暫的 lock 機制來切換 table。

在 MySQL 的世界裡比較有名的是 Percona 的 pt-online-schema-change (trigger-based) 以及 GitHubgh-ost (replication-based),另外找資料的時候有發現 Facebook 也有丟 OnlineSchemaChange (trigger-based) 出來。

在 PostgreSQL 的世界裡似乎是 pg_repack 這個方案,用了 trigger-based 的方式處理,但之前沒有注意到,是翻 pg-osc 的時候被提到才知道有這個工具。

而這次提到的 pg-osc 則是 2022 年才出的軟體,也是 trigger-based 的方式:

pg-osc uses the concept of shadow tables to perform schema changes. At a high level, it creates a shadow table that looks structurally the same as the primary table, performs the schema change on the shadow table (avoiding any locks since nothing is using this table), copies contents from the primary table to the shadow table and swaps the table names in the end while preserving all changes to the primary table using triggers (via audit table).

另外從 PostgreSQL 的 wiki 上看到「Change management tools and techniques」這頁,裡面看到「Metagration: Logical PostgreSQL Migration」這個工具,看起來好像是 replication-based 的方案,不過還是有用到一些 trigger 做事。

這些方案都先記錄起來好了...

PostgreSQL 的 command line 工具調整

在「Optimizing Your psql」這篇介紹了 PostgreSQL 的 command line 工具 psql 要怎麼調整讓他變好用。

這篇文章是入門的調整,藉由 $HOME/.psqlrc 裡的幾行設定,就可以讓系統變得好用很多,可以看到最後面給的 .psqlrc 也才八行...

PostgreSQL 筆記...

純粹是筆記...

對於架設 server 的文件可以參考 Ubuntu 這份「PostgreSQL - Community Ubuntu Documentation」,雖然 Debian 官方也有一份「PostgreSql - Debian Wiki」,不過沒講到遠端這塊...

設定的部份:

  • 要讓遠端可以存取有兩個地方要開,一個是 postgresql.conflisten_addresses 改成 "*",另外一個是增加 pg_hba.conf 遠端連線的權限。
  • CREATE USER test WITH PASSWORD 'test_password'; 以及 CREATE DATABASE test WITH OWNER = test; 把基本的東西建好。

然後把 firewall 打開,接下來就可以從其他台連進去了。