譯者 | 李睿

審校 | 孫淑娟

本指南適用于數(shù)據(jù)庫管理員（DBA），他們使用Citus管理PostgreSQL節(jié)點的內(nèi)部集群，Citus是PostgreSQL的擴展，可實現(xiàn)水平可擴展性和列式存儲。 

當(dāng)人工維護集群成為一項艱巨的工作時，每個數(shù)據(jù)庫管理員（DBA）都會在某個時候達到一個閾值，并且一些自動化解決方案的必要性變得越來越明顯。這里將討論這種自動化解決方案的一個示例。

一、設(shè)置

使用以下Citus集群進行分析：

• PostgreSQL 14.2，Citus 10.2。
• Btrfs文件系統(tǒng)上的21個節(jié)點（包括一個協(xié)調(diào)節(jié)點），zstd壓縮級別為10。
• 集群內(nèi)36個數(shù)據(jù)庫，遵循“一個租戶一個數(shù)據(jù)庫”的原則。
• pg_database_size報告的數(shù)據(jù)總量為151TB，btrfs fi使用報告的數(shù)據(jù)量為30TB。

集群中的每個數(shù)據(jù)庫都包含自己獨特的表和視圖，包括物化、權(quán)限和配置參數(shù)。

二、特征

在自動化過程中，可以得出自動化解決方案應(yīng)具備的以下一組特征：

簡單：維護過程應(yīng)該簡單、明顯、連貫和統(tǒng)一。盡量減少復(fù)雜的指令、從不支持的自定義shell腳本和部落知識。

審計：應(yīng)記錄在集群上執(zhí)行的每個操作，其中包括操作的作者、操作的目的、日期和執(zhí)行的確切命令。

歷史性：當(dāng)集群從備份中恢復(fù)時，應(yīng)用到集群的一組更改應(yīng)該很容易重新應(yīng)用。

三、執(zhí)行

1.基礎(chǔ)

為了解決這些原則，決定使用Flyway數(shù)據(jù)庫遷移工具。它允許通過版本化的遷移腳本對數(shù)據(jù)庫模式進行增量演化。

遷移腳本存儲在帶有持續(xù)交付（CD）機制的Git存儲庫中，配置為在每次提交時運行遷移。這樣，為了將更改應(yīng)用到集群，數(shù)據(jù)庫管理員（DBA）應(yīng)該使用遷移SQL腳本創(chuàng)建提交并推送它。

這種設(shè)置非常常見，甚至適用于普通PostgreSQL，但對于Citus，有一些細微差別：一些命令應(yīng)該在集群中的所有節(jié)點上執(zhí)行，有時在特定節(jié)點上執(zhí)行。幸運的是，PostgreSQL和Citus中有足夠的機制來涵蓋幾乎所有用例。

2.單點維護

為了對集群中的數(shù)據(jù)庫進行維護，最好創(chuàng)建一個專用數(shù)據(jù)庫。在進一步的示例中，這將被稱為維護。這是一個方便維護相關(guān)擴展和功能的地方，但最重要的是它保存了Flyway的歷史表，反映了集群中所有數(shù)據(jù)庫的演變，而不是在每個數(shù)據(jù)庫中都有單獨的歷史表。

將在維護時執(zhí)行的遷移腳本應(yīng)該能夠創(chuàng)建其他數(shù)據(jù)庫，以及在它們上執(zhí)行SQL。這就是dblink擴展發(fā)揮重要作用的地方：它允許連接到任何其他PostgreSQL服務(wù)器，包括localhost，并在那里執(zhí)行任意SQL?？紤]到這一點，創(chuàng)建帶有Citus擴展的數(shù)據(jù)庫的遷移腳本如下所示：

SQL

1 CREATE DATABASE new_citus_database;
2 SELECT DBLINK_EXEC('dbname=new_citus_database user=postgres', $remote$
3CREATE EXTENSION citus;
4 $remote$);

需要注意的一點：數(shù)據(jù)庫不能在事務(wù)中創(chuàng)建，因此需要通過腳本配置文件禁用它進行遷移。

在新數(shù)據(jù)庫上創(chuàng)建Citus擴展是不夠的。根據(jù)文檔，為了讓Citus工作，有必要：

• 在工作節(jié)點上創(chuàng)建同名數(shù)據(jù)庫。
• 在這些數(shù)據(jù)庫上創(chuàng)建Citus擴展。
• 使用集群中每個工作節(jié)點的地址調(diào)用citus_add_node()。

這很麻煩，因為它需要人工連接到工作人員或?qū)Ｓ玫腁nsible劇本。幸運的是，維護數(shù)據(jù)庫已經(jīng)包含了從SQL腳本執(zhí)行此操作所需的所有工具：

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用于連接集群中的所有工作節(jié)點，以及協(xié)調(diào)器。對于DBLINK_EXEC不支持的SELECT語句，有一個解決方法：START TRANSACTION; ... COMMIT;。

可以以類似的方式配置新創(chuàng)建的數(shù)據(jù)庫：

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$);

以及創(chuàng)建角色、授予權(quán)限和任何其他聲明。

3.維護多個數(shù)據(jù)庫

類似的方法用于在一個腳本中管理幾個數(shù)據(jù)庫。例如，假設(shè)已經(jīng)創(chuàng)建了另一個數(shù)據(jù)庫，另一個_citus_database，并且有必要在那里創(chuàng)建相同的表和視圖。使用CTE可以輕松實現(xiàn)：

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;

在實踐中，視圖的創(chuàng)建應(yīng)該被提取到一個特殊的可重復(fù)腳本中。

而代替CTE，創(chuàng)建實用程序PL/SQL函數(shù)是可能的并且更可取。例如，當(dāng)在同一個實例上存在安裝和未安裝Citus擴展的數(shù)據(jù)庫時，擁有一個僅在安裝了Citus的數(shù)據(jù)庫上運行SQL語句的函數(shù)會很方便。此類函數(shù)的示例如下所示：

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$);

所描述的管理方式非常靈活，允許數(shù)據(jù)庫管理員（DBA）實現(xiàn)流暢管理體驗所需的每一個邏輯。

4.注意事項

根據(jù)設(shè)置，可能需要配置.pgpass文件以便能夠通過dblink連接到工作節(jié)點。從歷史上看，它是作為Citus安全配置的一部分完成的，但隨著Citus11的發(fā)布，它發(fā)生了變化。

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

有了這樣的結(jié)構(gòu)，如果使用CLI工具，現(xiàn)在可以調(diào)用flywaymigrate，或者如果喜歡Gradle插件，可以調(diào)用./gradlewflywayMigrate-i。將其推送到Git并配置最喜歡的持續(xù)集成（CI）/持續(xù)交付（CD）工具，例如GitLab或GitHubActions，將獲得具有所需特征的解決方案。 

四、局限性

以上所描述的方法有一個嚴重的局限性：由于DBLINK_EXEC的性質(zhì)，多數(shù)據(jù)庫語句是非事務(wù)性的。它要求遷移腳本以某種方式是冪等的：或者通過數(shù)據(jù)操作語言(DML)語句中的IFNOTEXISTS類子句，或者通過DROP重新創(chuàng)建對象。用Citus對象實現(xiàn)可能有點棘手，但幾乎總是存在一種解決方法。例如，可以像這樣使表的創(chuàng)建具有冪等性：

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;

五、結(jié)語

這一指南展示了使用Citus集群獲得最佳管理體驗的基本原則和工具。Flyway工具的功能與Citus、dblink和PL/pgSQL提供的功能相結(jié)合，使數(shù)據(jù)庫管理員（DBA）可以輕松管理各種規(guī)模的集群。

原文鏈接：https://dzone.com/articles/maintenance-of-a-citus-cluster