溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“citus中DDL操作規范有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“citus中DDL操作規范有哪些”吧!
citus是PG的一個插件，插件主要針對普通SQL（非UTILITY）加HOOK進行了一些ROUTE處理，同時使用UDF對表進行新建分區的操作。
如果用戶如果要執行DDL，那么CITUS無法接管，應該如何操作呢？
分兩種情況，一種需要在所有節點（CN以及WORKER）執行，還有一些只需要在CN節點執行。
由于這些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，需要在所有節點執行。
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)
到此，相信大家對“citus中DDL操作規范有哪些”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
