集群中的每个数据库都包含自己独特的表和视图，包括物化、权限和配置参数。

特征

在自动化过程中，可以得出自动化解决方案应具备的以下一组特征：

简单：维护过程应该简单、明显、连贯和统一。尽量减少复杂的指令、从不支持的自定义shell脚本和部落知识。

审计：应记录在集群上执行的每个操作，其中包括操作的作者、操作的目的、日期和执行的确切命令。

历史性：当集群从备份中恢复时，应用到集群的一组更改应该很容易重新应用。

简单：维护过程应该简单、明显、连贯和统一。尽量减少复杂的指令、从不支持的自定义shell脚本和部落知识。

审计：应记录在集群上执行的每个操作，其中包括操作的作者、操作的目的、日期和执行的确切命令。

历史性：当集群从备份中恢复时，应用到集群的一组更改应该很容易重新应用。

执行

基础

为了解决这些原则，决定使用Flyway数据库迁移工具。它允许通过版本化的迁移脚本对数据库模式进行增量演化。

迁移脚本存储在带有持续交付（CD）机制的Git存储库中，配置为在每次提交时运行迁移。这样，为了将更改应用到集群，数据库管理员（DBA）应该使用迁移SQL脚本创建提交并推送它。

这种设置非常常见，甚至适用于普通PostgreSQL，但对于Citus，有一些细微差别：一些命令应该在集群中的所有节点上执行，有时在特定节点上执行。幸运的是，PostgreSQL和Citus中有足够的机制来涵盖几乎所有用例。

单点维护

为了对集群中的数据库进行维护，最好创建一个专用数据库。在进一步的示例中，这将被称为维护。这是一个方便维护相关扩展和功能的地方，但最重要的是它保存了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。

在工作节点上创建同名数据库。

在这些数据库上创建Citus扩展。

使用集群中每个工作节点的地址调用citus_add_node。

这很麻烦，因为它需要人工连接到工作人员或专用的Ansible剧本。幸运的是，维护数据库已经包含了从SQL脚本执行此操作所需的所有工具：

SQL

1-- Create database on every worker

2SELECT run_command_on_workers( $cmd$CREATEDATABASE 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$);

左右滑动查看完整代码

以及创建角色、授予权限和任何其他声明。

维护多个数据库

类似的方法用于在一个脚本中管理几个数据库。例如，假设已经创建了另一个数据库，另一个_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$SELECTTRUE 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$ALTEREXTENSION CITUS UPDATE $cmd$);

左右滑动查看完整代码

所描述的管理方式非常灵活，允许数据库管理员（DBA）实现流畅管理体验所需的每一个逻辑。

注意事项

根据设置，可能需要配置.pgpass文件以便能够通过dblink连接到工作节点。从历史上看，它是作为Citus安全配置的一部分完成的，但随着Citus11的发布，它发生了变化。

综合

以上将前面描述的所有步骤放入迁移脚本中。迁移脚本的顺序在磁盘上可能如下所示：

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$SELECTcreate_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

三连给小编加鸡腿！返回搜狐，查看更多