PostgreSQL sharding : citus 系列4 - DDL 操作规范 (新增DB，TABLE，SCHEMA，UDF，OP，用户等)...

落日映苍穹つ 2022-02-12 22:51 80阅读 0赞

### 标签 ###

PostgreSQL , citus , 新增对象 , 新增数据库 , 新增用户

--------------------

## 背景 ##

citus是PG的一个插件，插件主要针对普通SQL（非UTILITY）加HOOK进行了一些ROUTE处理，同时使用UDF对表进行新建分区的操作。

如果用户如果要执行DDL，那么CITUS无法接管，应该如何操作呢？

分两种情况，一种需要在所有节点（CN以及WORKER）执行，还有一些只需要在CN节点执行。

## 需要在所有节点（CN以及WORKER）执行的DDL ##

由于这些DDL在CN节点执行时，不会自动在WORKER执行，所以需要在所有节点执行。

常用的DDL包括：

1、新建用户

2、新建数据库

Command:     CREATE DATABASE  
    Description: create a new database  
    Syntax:  
    CREATE DATABASE name  
        [ [ WITH ] [ OWNER [=] user_name ]  
               [ TEMPLATE [=] template ]  
               [ ENCODING [=] encoding ]  
               [ LC_COLLATE [=] lc_collate ]  
               [ LC_CTYPE [=] lc_ctype ]  
               [ TABLESPACE [=] tablespace_name ]  
               [ ALLOW_CONNECTIONS [=] allowconn ]  
               [ CONNECTION LIMIT [=] connlimit ]  
               [ IS_TEMPLATE [=] istemplate ] ]

所有节点新建数据库后一定不要忘记：

2.1、在所有节点新增的DB内添加citus插件

create extension citus;

2.2、仅在CN节点，连到新建数据库里面添加worker节点。（千万不要忘记，每新建一个DB，都需要重复做。所以可以把它做到模板库里面，新建DB时，使用模板新建。）

例如

su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.224', 1921);\""    
    su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.230', 1921);\""    
    su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.231', 1921);\""    
    su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.225', 1921);\""    
    su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.227', 1921);\""    
    su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.232', 1921);\""    
    su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.226', 1921);\""    
    su - postgres -c "psql -c \"SELECT * from master_add_node('xxx.xxx.xxx.229', 1921);\""    
        
    postgres=# SELECT * FROM master_get_active_worker_nodes();    
       node_name    | node_port     
    ----------------+-----------    
     xxx.xxx.xxx.227 |      1921    
     xxx.xxx.xxx.229 |      1921    
     xxx.xxx.xxx.231 |      1921    
     xxx.xxx.xxx.225 |      1921    
     xxx.xxx.xxx.224 |      1921    
     xxx.xxx.xxx.226 |      1921    
     xxx.xxx.xxx.230 |      1921    
     xxx.xxx.xxx.232 |      1921    
    (8 rows)

2.3、如果使用了MX功能，还需要添加一遍。

参考

[《PostgreSQL sharding : citus 系列1 - 多机部署（含OLTP(TPC-B)测试）》][PostgreSQL sharding _ citus _1 - _OLTP_TPC-B]

### CN MX : OLTP 读、写能力扩展

3、新建schema

Command:     CREATE SCHEMA  
    Description: define a new schema  
    Syntax:  
    CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]  
    CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]  
    CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]  
    CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification  
      
    where role_specification can be:  
      
        user_name  
      | CURRENT_USER  
      | SESSION_USER

4、新建函数

自定义函数  
      
    Command:     CREATE FUNCTION  
    Description: define a new function  
    Syntax:  
    CREATE [ OR REPLACE ] FUNCTION  
        name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )  
        [ RETURNS rettype  
          | RETURNS TABLE ( column_name column_type [, ...] ) ]  
      { LANGUAGE lang_name  
        | TRANSFORM { FOR TYPE type_name } [, ... ]  
        | WINDOW  
        | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF  
        | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT  
        | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER  
        | PARALLEL { UNSAFE | RESTRICTED | SAFE }  
        | COST execution_cost  
        | ROWS result_rows  
        | SET configuration_parameter { TO value | = value | FROM CURRENT }  
        | AS 'definition'  
        | AS 'obj_file', 'link_symbol'  
      } ...  
        [ WITH ( attribute [, ...] ) ]

5、新建操作符

自定义操作符

6、新建类型

自定义类型

7、新建插件

Command:     CREATE EXTENSION  
    Description: install an extension  
    Syntax:  
    CREATE EXTENSION [ IF NOT EXISTS ] extension_name  
        [ WITH ] [ SCHEMA schema_name ]  
                 [ VERSION version ]  
                 [ FROM old_version ]  
                 [ CASCADE ]

以上为常用的DDL，需要在所有节点执行。

## 仅需要在CN节点执行的DDL ##

1、新建表，调用citus函数创建分片。

例子

create table test(id int primary key, info text);  
      
    select create_distributed_table('test','id');

2、新建视图

Command:     CREATE VIEW  
    Description: define a new view  
    Syntax:  
    CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]  
        [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]  
        AS query  
        [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

视图仅需在CN节点操作，因为SQL最后会解析成视图里面的内容。例如

create view v2 as select * from pgbench_accounts where aid=1;  
      
    create view v3 as select * from v2;  
      
    postgres=# explain verbose select * from v2;  
                                                                       QUERY PLAN                                                                      
    -------------------------------------------------------------------------------------------------------------------------------------------------  
     Custom Scan (Citus Router)  (cost=0.00..0.00 rows=0 width=0)  
       Output: remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler  
       Task Count: 1  
       Tasks Shown: All  
       ->  Task  
             Node: host=172.24.211.232 port=1921 dbname=postgres  
             ->  Index Scan using pgbench_accounts_pkey_106819 on public.pgbench_accounts_106819 pgbench_accounts  (cost=0.28..2.50 rows=1 width=97)  
                   Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler  
                   Index Cond: (pgbench_accounts.aid = 1)  
    (9 rows)  
      
    postgres=# explain verbose select * from v3;  
                                                                       QUERY PLAN                                                                      
    -------------------------------------------------------------------------------------------------------------------------------------------------  
     Custom Scan (Citus Router)  (cost=0.00..0.00 rows=0 width=0)  
       Output: remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler  
       Task Count: 1  
       Tasks Shown: All  
       ->  Task  
             Node: host=172.24.211.232 port=1921 dbname=postgres  
             ->  Index Scan using pgbench_accounts_pkey_106819 on public.pgbench_accounts_106819 pgbench_accounts  (cost=0.28..2.50 rows=1 width=97)  
                   Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler  
                   Index Cond: (pgbench_accounts.aid = 1)  
    (9 rows)

### 特例 ###

视图虽然仅需在CN操作，但是如果你需要在worker节点使用这个视图（或者说你用了CITUS MX特性，需要使用worker节点承担其他操作），那么也需要在worker节点创建视图。

## 参考 ##

[http://docs.citusdata.com/en/stable/][http_docs.citusdata.com_en_stable]

[PostgreSQL sharding _ citus _1 - _OLTP_TPC-B]: https://github.com/digoal/blog/blob/master/201808/20180824_02.md
[http_docs.citusdata.com_en_stable]: http://docs.citusdata.com/en/stable/