譯者 | 李睿
審校 | 孫淑娟
本指南適用于數據庫管理員（DBA），他們使用Citus管理PostgreSQL節點的內部集群，Citus是PostgreSQL的擴展，可實現水平可擴展性和列式存儲。
當人工維護集群成為一項艱巨的工作時，每個數據庫管理員（DBA）都會在某個時候達到一個閾值，并且一些自動化解決方案的必要性變得越來越明顯。這里將討論這種自動化解決方案的一個示例。
一、設置
使用以下Citus集群進行分析：
- PostgreSQL 14.2，Citus 10.2。
- Btrfs文件系統上的21個節點（包括一個協調節點），zstd壓縮級別為10。
- 集群內36個數據庫，遵循“一個租戶一個數據庫”的原則。
- pg_database_size報告的數據總量為151TB，btrfs fi使用報告的數據量為30TB。
集群中的每個數據庫都包含自己獨特的表和視圖，包括物化、權限和配置參數。
二、特征
在自動化過程中，可以得出自動化解決方案應具備的以下一組特征：
簡單：維護過程應該簡單、明顯、連貫和統一。盡量減少復雜的指令、從不支持的自定義shell腳本和部落知識。
審計：應記錄在集群上執行的每個操作，其中包括操作的作者、操作的目的、日期和執行的確切命令。
歷史性：當集群從備份中恢復時，應用到集群的一組更改應該很容易重新應用。
三、執行
1.基礎
為了解決這些原則，決定使用Flyway數據庫遷移工具。它允許通過版本化的遷移腳本對數據庫模式進行增量演化。
遷移腳本存儲在帶有持續交付（CD）機制的Git存儲庫中，配置為在每次提交時運行遷移。這樣，為了將更改應用到集群，數據庫管理員（DBA）應該使用遷移SQL腳本創建提交并推送它。
這種設置非常常見，甚至適用于普通PostgreSQL，但對于Citus，有一些細微差別：一些命令應該在集群中的所有節點上執行，有時在特定節點上執行。幸運的是，PostgreSQL和Citus中有足夠的機制來涵蓋幾乎所有用例。
2.單點維護
為了對集群中的數據庫進行維護，最好創建一個專用數據庫。在進一步的示例中，這將被稱為維護。這是一個方便維護相關擴展和功能的地方，但最重要的是它保存了Flyway的歷史表，反映了集群中所有數據庫的演變，而不是在每個數據庫中都有單獨的歷史表。
將在維護時執行的遷移腳本應該能夠創建其他數據庫，以及在它們上執行SQL。這就是dblink擴展發揮重要作用的地方：它允許連接到任何其他PostgreSQL服務器，包括localhost，并在那里執行任意SQL。考慮到這一點，創建帶有Citus擴展的數據庫的遷移腳本如下所示：
SQL
1 CREATE DATABASE new_citus_database;
2 SELECT DBLINK_EXEC('dbname=new_citus_database user=postgres', $remote$
3CREATE EXTENSION citus;
4 $remote$);
需要注意的一點：數據庫不能在事務中創建，因此需要通過腳本配置文件禁用它進行遷移。
在新數據庫上創建Citus擴展是不夠的。根據文檔，為了讓Citus工作，有必要：
- 在工作節點上創建同名數據庫。
- 在這些數據庫上創建Citus擴展。
- 使用集群中每個工作節點的地址調用citus_add_node()。
這很麻煩，因為它需要人工連接到工作人員或專用的Ansible劇本。幸運的是，維護數據庫已經包含了從SQL腳本執行此操作所需的所有工具：
SQL
1-- Create database on every worker
2SELECT run_command_on_workers($cmd$CREATE DATABASE new_citus_database;$cmd$);
3
4-- Connect to the fresh database on worker nodes and create the Citus extension
5 WITH citus_workers AS (SELECT node_name FROM citus_get_active_worker_nodes())
6 SELECT DBLINK_EXEC(FORMAT('host=%s dbname=new_citus_database user=postgres', node_name), $remote$
7 CREATE EXTENSION citus;
8 $remote$)
9 FROM citus_workers;
10
11 -- Add workers to the fresh database on the coordinator
12 WITH citus_workers AS (SELECT node_name FROM citus_get_active_worker_nodes() ORDER BY node_name)
13 SELECT DBLINK_EXEC('dbname=new_citus_database user=postgres', format($remote$
14 START TRANSACTION;
15 SELECT citus_add_node('%s', 5432);
16 COMMIT ;
17 $remote$, node_name))
18 FROM citus_workers;
在這里，DBLINK_EXEC用于連接集群中的所有工作節點，以及協調器。對于DBLINK_EXEC不支持的SELECT語句，有一個解決方法：START TRANSACTION; ... COMMIT;。
可以以類似的方式配置新創建的數據庫：
SQL
1 ALTER DATABASE new_citus_database SET WORK_MEM = '256MB';
2 SELECT run_command_on_workers($cmd$
3 ALTER DATABASE new_citus_database SET WORK_MEM = '256MB';
4 $cmd$);
以及創建角色、授予權限和任何其他聲明。
3.維護多個數據庫
類似的方法用于在一個腳本中管理幾個數據庫。例如，假設已經創建了另一個數據庫，另一個_citus_database，并且有必要在那里創建相同的表和視圖。使用CTE可以輕松實現：
SQL
1 WITH databases AS (SELECT *
2 FROM (VALUES ('new_citus_database'),
3 ('another_citus_database')) AS t(db_name))
4 SELECT DBLINK_EXEC(FORMAT('dbname=%I user=postgres', db_name), $remote$
5 START TRANSACTION;
6 CREATE TABLE test_table
7 (user_id TEXT, data jsonb);
8 SELECT create_distributed_table('test_table', 'user_id');
9 CREATE VIEW test_table_view AS SELECT * FROM test_table;
10COMMIT;
11 $remote$)
12 FROM databases;
在實踐中，視圖的創建應該被提取到一個特殊的可重復腳本中。
而代替CTE，創建實用程序PL/SQL函數是可能的并且更可取。例如，當在同一個實例上存在安裝和未安裝Citus擴展的數據庫時，擁有一個僅在安裝了Citus的數據庫上運行SQL語句的函數會很方便。此類函數的示例如下所示：
SQL
CREATE OR REPLACE PROCEDURE public.execute_on_databases_with_citus(statement TEXT)
LANGUAGE plpgsql AS
$$
DECLARE
db_name TEXT;
BEGIN
FOREACH db_name IN ARRAY (SELECT ARRAY_AGG(datname)
FROM pg_database
WHERE EXISTS(SELECT *
FROM DBLINK(FORMAT('dbname=%s', datname),
$cmd$SELECT TRUE FROM pg_extension WHERE extname = 'citus'$cmd$) AS t(citus_installed BOOLEAN))
AND datname NOT IN ('template0', 'template1'))
LOOP
RAISE NOTICE 'EXECUTING ON %', db_name;
EXECUTE FORMAT('SELECT * FROM dblink_exec(''dbname=%s'', $_CMD_$%s$_CMD_$);', db_name,
statement);
END LOOP;
END
$$;
有了這樣的功能，運行ALTER EXTENSION citus UPDATE就很容易了。例如：
SQL
CALL execute_on_databases_with_citus($cmd$ALTER EXTENSION CITUS UPDATE$cmd$);
所描述的管理方式非常靈活，允許數據庫管理員（DBA）實現流暢管理體驗所需的每一個邏輯。
4.注意事項
根據設置，可能需要配置.pgpass文件以便能夠通過dblink連接到工作節點。從歷史上看，它是作為Citus安全配置的一部分完成的，但隨著Citus11的發布，它發生了變化。
5.綜合
以上將前面描述的所有步驟放入遷移腳本中。遷移腳本的順序在磁盤上可能如下所示：
Shell
.
└── db
└── migration
├── R__test_table_view.sql
├── V1__init.sql
├── V2.0__create_new_citus_database.sql
├── V2.0__create_new_citus_database.sql.conf
├── V2.1__new_citus_database_configuration.sql
├── V3__another_citus_database.sql
├── V3__another_citus_database.sql.conf
├── V4__no_citus_database.sql
├── V5__common_table.sql
└── V6__update_citus_extension.sql
有了這樣的結構，如果使用CLI工具，現在可以調用flywaymigrate，或者如果喜歡Gradle插件，可以調用./gradlewflywayMigrate-i。將其推送到Git并配置最喜歡的持續集成（CI）/持續交付（CD）工具，例如GitLab或GitHubActions，將獲得具有所需特征的解決方案。
四、局限性
以上所描述的方法有一個嚴重的局限性：由于DBLINK_EXEC的性質，多數據庫語句是非事務性的。它要求遷移腳本以某種方式是冪等的：或者通過數據操作語言(DML)語句中的IFNOTEXISTS類子句，或者通過DROP重新創建對象。用Citus對象實現可能有點棘手，但幾乎總是存在一種解決方法。例如，可以像這樣使表的創建具有冪等性：
SQL
WITH databases AS (SELECT *
FROM (VALUES ('new_citus_database'),
('another_citus_database')) AS t(db_name))
SELECT DBLINK_EXEC(FORMAT('dbname=%I user=postgres', db_name), $remote$
START TRANSACTION;
CREATE TABLE IF NOT EXISTS test_table (user_id TEXT, data jsonb);
DO $$
BEGIN
EXECUTE $cmd$SELECT create_distributed_table('test_table', 'user_id');$cmd$;
EXCEPTION
WHEN SQLSTATE '42P16' THEN
RETURN;
END;$$;
COMMIT;
$remote$)
FROM databases;
五、結語
這一指南展示了使用Citus集群獲得最佳管理體驗的基本原則和工具。Flyway工具的功能與Citus、dblink和PL/pgSQL提供的功能相結合，使數據庫管理員（DBA）可以輕松管理各種規模的集群。
原文鏈接：https://dzone.com/articles/maintenance-of-a-citus-cluster
