Label
PostgreSQL, citus, new objects, new databases, new users
background
citus is a plug-in of PG. The plug-in mainly deals with ordinary SQL (non-UTILITY) plus HOOK, and uses UDF to create new partitions for tables.
If the user wants to execute DDL, then CITUS can not take over, how should it operate?
In two cases, one needs to be executed on all nodes (CNs and WORKER), and the other only needs to be executed on CNs.
DDL that needs to be executed on all nodes (CN and WORKER)
Since these DDL s are not automatically executed in WORKER when executed on CN nodes, they need to be executed on all nodes.
Common DDL s include:
1. New users
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. New database
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 ] ]
Don't forget after all nodes create a new database:
2.1. Add citus plug-in to all new DB nodes
create extension citus;
2.2. Add worker nodes to the new database only on the CN node. (Don't forget that every new DB needs to be repeated. So it can be built into the template library. When you create a new DB, use the template to create a new DB.)
for example
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. If you use MX function, you need to add it again.
Reference resources
PostgreSQL Sharing: Citus Series 1-Multiprocessor Deployment (including OLTP(TPC-B) Testing)
### CN MX: OLTP Read and Write Expansion
3. New 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. New function
Custom function 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. New operator
Custom operator
6. New Types
Custom Types
7. New plug-in
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 ]
These are commonly used DDL s that need to be executed on all nodes.
DDL that only needs to be executed on the CN node
1. Create a new table and call the citus function to create fragmentation.
Example
create table test(id int primary key, info text); select create_distributed_table('test','id');
2. New View
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 ]
Views only need to be operated on the CN node, because SQL will eventually parse into the contents of the view. for example
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)
special case
Views only need to be operated on CN s, but if you need to use this view on the worker node (or if you use the CITUS MX feature and need to use the worker node for other operations), you also need to create a view on the worker node.