譯者 | 李睿

　　本指南適用于數據庫管理員(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)可以輕松管理各種規模的集群。