多数据库Citus集群的维护

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

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;

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

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;

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

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

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

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;