多數(shù)據(jù)庫Citus集群的維護(hù)

　　譯者 | 李睿

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

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

　　一、設(shè)置

　　使用以下Citus集群進(jìn)行分析：

　　PostgreSQL 14.2，Citus 10.2。

　　Btrfs文件系統(tǒng)上的21個(gè)節(jié)點(diǎn)(包括一個(gè)協(xié)調(diào)節(jié)點(diǎn))，zstd壓縮級別為10。

　　集群內(nèi)36個(gè)數(shù)據(jù)庫，遵循“一個(gè)租戶一個(gè)數(shù)據(jù)庫”的原則。

　　pg_database_size報(bào)告的數(shù)據(jù)總量為151TB，btrfs fi使用報(bào)告的數(shù)據(jù)量為30TB。

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

　　二、特征

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

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

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

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

　　三、執(zhí)行

　　1.基礎(chǔ)

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

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

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

　　2.單點(diǎn)維護(hù)

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

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

　　SQL

　　復(fù)制

　　1 CREATE DATABASE new_citus_database;

　　2 SELECT DBLINK_EXEC('dbname=new_citus_database user=postgres', $remote$

　　3CREATE EXTENSION citus;

　　4 $remote$);

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

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

　　在工作節(jié)點(diǎn)上創(chuàng)建同名數(shù)據(jù)庫。

　　在這些數(shù)據(jù)庫上創(chuàng)建Citus擴(kuò)展。

　　使用集群中每個(gè)工作節(jié)點(diǎn)的地址調(diào)用citus_add_node()。

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

　　SQL

　　復(fù)制

　　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é)點(diǎn)，以及協(xié)調(diào)器。對于DBLINK_EXEC不支持的SELECT語句，有一個(gè)解決方法：START TRANSACTION; ... COMMIT;。

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

　　SQL

　　復(fù)制

　　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.維護(hù)多個(gè)數(shù)據(jù)庫

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

　　SQL

　　復(fù)制

　　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;

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

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

　　SQL

　　復(fù)制

　　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

　　$$;

　　有了這樣的功能，運(yùn)行ALTER EXTENSION citus UPDATE就很容易了。例如：

　　SQL

　　復(fù)制

　　CALL execute_on_databases_with_citus($cmd$ALTER EXTENSION CITUS UPDATE$cmd$);

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

　　4.注意事項(xiàng)

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

　　5.綜合

　　以上將前面描述的所有步驟放入遷移腳本中。遷移腳本的順序在磁盤上可能如下所示：

　　復(fù)制

　　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，將獲得具有所需特征的解決方案。

　　四、局限性

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

　　SQL

　　復(fù)制

　　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集群獲得最佳管理體驗(yàn)的基本原則和工具。Flyway工具的功能與Citus、dblink和PL/pgSQL提供的功能相結(jié)合，使數(shù)據(jù)庫管理員(DBA)可以輕松管理各種規(guī)模的集群。