李锐审稿人|SunShujuan本指南适用于使用Citus（可扩展性和列式存储的扩展）管理PostgreSQL节点内部集群的数据库管理员(DBA)。当手动维护集群成为一项艰巨的工作时，每个数据库管理员(DBA)都会在某个时刻达到一个阈值，并且对某些自动化解决方案的需求变得越来越明显。此处讨论了此类自动化解决方案的示例。1.设置使用以下Citus集群进行分析：PostgreSQL14.2、Citus10.2。Btrfs文件系统上有21个节点（包括协调器节点），zstd压缩级别为10。集群中有36个数据库，遵循“每个租户一个数据库”的原则。pg_database_size上报的数据总量为151TB，btrfsfiusage上报的数据量为30TB。集群中的每个数据库都包含自己独特的表和视图，包括具体化、权限和配置参数。2.特点在自动化过程中，可以得出自动化解决方案应该具备的以下一组特点：简单：维护过程应该简单、明显、连贯和统一。尽量减少从不支持的复杂指令、自定义shell脚本和部落传说。审计：应该记录在集群上执行的每个操作，其中包括操作的作者、操作的目的、日期和执行的确切命令。历史性：当集群从备份中恢复时，应该可以轻松地重新应用应用于集群的一组更改。三、实现1.基本为了解决这些原理，决定使用Flyway数据库迁移工具。它允许通过版本化的迁移脚本对数据库模式进行增量演化。迁移脚本存储在Git存储库中，该存储库具有配置为在每次提交时运行迁移的持续交付(CD)机制。因此，为了将更改应用到集群，数据库管理员(DBA)应该使用迁移SQL脚本来创建提交并推送它。这种设置非常常见，甚至适用于普通PostgreSQL，但对于Citus，有一些细微差别：一些命令应该在集群中的所有节点上执行，有时在特定节点上执行。幸运的是，PostgreSQL和Citus中的机制足以涵盖几乎所有用例。2.单点维护为了维护集群中的数据库，最好创建一个专用的数据库。在进一步的例子中，这将被称为维护。这是一个方便维护相关扩展和特性的地方，但最重要的是它保存了Flyway的历史表，反映了集群中所有数据库的演变，而不是每个数据库都有一个单独的历史表。将在维护时执行的迁移脚本应该能够创建额外的数据库，以及在它们上执行SQL。这就是dblink扩展发挥作用的地方：它允许连接到任何其他PostgreSQL服务器，包括本地主机，并在那里执行任意SQL。考虑到这一点，创建带有Citus扩展的数据库的迁移脚本如下所示：SQL1CREATEDATABASEnew_citus_database;2SELECTDBLINK_EXEC('dbname=new_citus_databaseuser=postgres',$remote$3CREATEEXTENSIONcitus;4$remote$);需要注意一点：数据库不能在事务中创建，所以需要通过迁移脚本配置文件禁用。在新数据库上创建Citus扩展是不够的。根据文档，为了让Citus工作，必须：在工作节点上创建一个同名的数据库。在这些数据库上创建Citus扩展。使用集群中每个工作节点的地址调用citus_add_node()。这很麻烦，因为它需要与工作人员或专用的Ansible剧本建立人际关系。幸运的是，维护数据库已经包含了从SQL脚本执行此操作所需的所有工具：SQL1--在每个worker2SELECT上创建数据库run_command_on_workers($cmd$CREATEDATABASEnew_citus_database;$cmd$);34--连接到新数据库工作节点并创建Citus扩展5WITHcitus_workersAS(SELECTnode_nameFROMcitus_get_active_worker_nodes())6SELECTDBLINK_EXEC(FORMAT('host=%sdbname=new_citus_databaseuser=postgres',node_name),$remote$7CREATEEXTENSIONcitus$remote$)9FROMcitus_workers;1011--将工作人员添加到协调器上的新数据库12WITHcitus_workersAS(SELECTnode_nameFROMcitus_get_active_worker_nodes()ORDERBYnode_name)13SELECTDBLINK_EXEC('dbname=new_citus_databaseuser=postgres',formatremote$14STARTTRANSACTION;15选择citus_add_node('%s',5432);16COMMIT;17$remote$,node_name))18FROMcitus_workers;这里，DBLINK_EXEC用于连接集群中的所有工作节点，以及协调器。对于DBLINK_EXEC不支持的SELECT语句，有一个解决方法：STARTTRANSACTION；...犯罪;。可以用类似的方式配置新创建的数据库：SQL1ALTERDATABASEnew_citus_databaseSETWORK_MEM='256MB';2SELECTrun_command_on_workers($cmd$3ALTERDATABASEnew_citus_databaseSETWORK_MEM='256MB';4$cmd$);并创建角色、授予权限和任何其他通知。3.维护多个数据库类似的方法用于在一个脚本中管理多个数据库。例如，假设已经创建了另一个数据库，另一个_citus_database，并且有必要在那里创建相同的表和视图。这很容易通过CTE实现：SQL1WITHdatabasesAS(SELECT*2FROM(VALUES('new_citus_database'),3('another_citus_database'))ASt(db_name))4SELECTDBLINK_EXEC(FORMAT('dbname=%Iuser=postgres',db_name),$remote$5STARTTRANSACTION;6CREATETABLEtest_table7(user_idTEXT,datajsonb);8SELECTcreate_distributed_table('test_table','user_id');9CREATEVIEWtest_table_viewASSELECT*FROMtest_table;10COMMIT;11$remote$)12来自数据库；实际上，视图的创建应该被提取到一个特殊的可重复脚本中。代替CTE，创建实用的PL/SQL函数是可能且更可取的。例如，当在同一个实例上安装有和没有安装Citus扩展的数据库时，拥有一个只在安装了Citus的数据库上运行SQL语句的功能会很方便。这种函数的一个例子如下：,datname),$cmd$SELECTTRUEFROMpg_extensionWHEREextname='citus'$cmd$)ASt(citus_installedBOOLEAN))ANDdatnameNOTIN('template0','template1'))LOOPRAISENOTICE'EXECUTINGON%',db_name;EXECUTEFORMAT('SELECT*FROMdblink_exec(''dbname=%s'',$_CMD_$%s$_CMD_$);',db_name,statement);ENDLOOP;END$$;有这样的功能，运行ALTEREXTENSIONcitusUPDATE非常容易。例如：SQLCALLexecute_on_databases_with_citus($cmd$ALTEREXTENSIONCITUSUPDATE$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对象实施可能有点棘手，但几乎总有解决方法。例如，可以像这样使表创建是幂等的：=postgres',db_name),$remote$STARTTRANSACTION;如果不存在则创建表test_table(user_idTEXT,datajsonb);EXCEPTIONWHENSQLSTATE'42P16'THENRETURN;END;$$;COMMIT;$remote$)FROMdatabases;五、结论本指南展示了使用Citus集群以获得最佳管理体验的基本原则和工具。Flyway工具的功能与Citus、dblink和PL/pgSQL提供的功能相结合，使数据库管理员(DBA)可以轻松管理各种规模的集群。原文链接：https://dzone.com/articles/maintenance-of-a-citus-cluster
