前幾天在 Hacker News 首頁上看到的文章，講怎麼把一個 4TB 的 PostgreSQL 從 9.6 升級到 13 的故事：「How we upgraded our 4TB Postgres database (retool.com)」，原文在「How Retool upgraded our 4 TB main application PostgreSQL database」，翻了一下 LinkedIn，這篇文章的作者 Peter Johnston 在 Retool 掛的是 Security Software Engineer，另外他也有在 Hacker News 上的討論出現 (帳號是 mrbabbage)，可以搜尋翻翻看他的回覆。

看完文章後發現方法的概念其實不難，主要是要找到對的工具來用。基本的想法是先生出一個 initial dump，然後架構 logical replication，接下來就是處理各種因為在 4TB data 這個 scale 下會遇到的問題。

主要用到的工具是 Citus Data 的 Warp：「Citus warp: Database migrations without the pain」，不過這個工具的限制是表格必須都有 single column primary key，所以他們為了這次轉移也有小改 database schema 配合 Warp 的要求：

We had to do a bit of finagling to coax Warp into processing our database. Warp expects all tables to have a single column primary key, so we had to convert compound primary keys into unique constraints and add scalar primary keys. Otherwise, Warp was very straightforward to use.

另外針對比較大的兩個 append-only 的表格 (分別是 2TB 與 x00 GB) 做處理，在 initial dump 的階段不對這兩個表格做 replication，而是透過自製的 Python script 搬移：

To handle the two massive tables we skipped in Warp, we wrote a Python script to bulk transfer data from the old database server to the new.

然後是 foreign key 相關的關閉與重啟，這算是 RDBMS 在大量資料的 dump & restore 的標準作業了：

As you can see from the runbook above, one of the steps we had to do was to turn off and then re-enable foreign key constraint checks.

最後執行下去，整個 downtime 只有十五分鐘：

We scheduled the maintenance window late on Saturday, October 23rd, at the lowest period of Retool cloud traffic. With the configuration described above, we were able to bring up a new database server at version 13 in around 15 minutes, subscribed to changes at our 9.6 primary with logical decoding.

另外也有提到有計畫要 sharding，之後 main database 就有機會被拆小：

We anticipate we’ll have sharded our database by the end of that support window, and be performing our next substantial version upgrades incrementally.

整個計畫的核心概念不難，主要是要怎麼順出來並且執行...

To respond on your own website, enter the URL of your response which should contain a link to this post's permalink URL. Your response will then appear (possibly after moderation) on this page. Want to update or remove your response? Update or delete your post and re-enter your post's URL again. (Learn More)