PostgreSQL sharding : citus 系列4 - DDL 操作规范 (新增DB,TABLE,SCHEMA,UDF,OP,用户等)-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

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

简介: 标签 PostgreSQL , citus , 新增对象 , 新增数据库 , 新增用户 背景 citus是PG的一个插件,插件主要针对普通SQL(非UTILITY)加HOOK进行了一些ROUTE处理,同时使用UDF对表进行新建分区的操作。

标签

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


背景

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

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

分两种情况,一种需要在所有节点(CN以及WORKER)执行,还有一些只需要在CN节点执行。

需要在所有节点(CN以及WORKER)执行的DDL

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

常用的DDL包括:

1、新建用户

Command:     CREATE ROLE  
Description: define a new database role  
Syntax:  
CREATE ROLE name [ [ WITH ] option [ ... ] ]  
  
where option can be:  
  
      SUPERUSER | NOSUPERUSER  
    | CREATEDB | NOCREATEDB  
    | CREATEROLE | NOCREATEROLE  
    | INHERIT | NOINHERIT  
    | LOGIN | NOLOGIN  
    | REPLICATION | NOREPLICATION  
    | BYPASSRLS | NOBYPASSRLS  
    | CONNECTION LIMIT connlimit  
    | [ ENCRYPTED ] PASSWORD 'password'  
    | VALID UNTIL 'timestamp'  
    | IN ROLE role_name [, ...]  
    | IN GROUP role_name [, ...]  
    | ROLE role_name [, ...]  
    | ADMIN role_name [, ...]  
    | USER role_name [, ...]  
    | SYSID uid  

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)测试)》

### 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/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
最新文章
相关文章
链接