Citus Deployment

Distributive extension citus deployment

Citus is a distributed extension plugin for PostgreSQL. By default, Pigsty installs Citus but does not enable it. pigsty-citus.yml provides a config file case for deploying a Citus cluster. To allow Citus to, you need to modify the following parameters.

  • max_prepared_transaction: Modify to a value greater than max_connections, e.g. 800.
  • pg_libs: Must contain citus and be placed in the top position.
  • You need to include the citus extension plugin in the business database (but you can also manually install it via CREATE EXTENSION).

Citus cluster sample config

  1. #----------------------------------#
  2. # cluster: citus coordinator
  3. #----------------------------------#
  4. pg-meta:
  5.   hosts:
  6.     10.10.10.10: { pg_seq: 1, pg_role: primary , pg_offline_query: true }
  7.   vars:
  8.     pg_cluster: pg-meta
  9.     vip_address: 10.10.10.2
  10.     pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  11.     pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]
  13. #----------------------------------#
  14. # cluster: citus data nodes
  15. #----------------------------------#
  16. pg-node1:
  17.   hosts:
  18.     10.10.10.11: { pg_seq: 1, pg_role: primary }
  19.   vars:
  20.     pg_cluster: pg-node1
  21.     vip_address: 10.10.10.3
  22.     pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  23.     pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]
  25. pg-node2:
  26.   hosts:
  27.     10.10.10.12: { pg_seq: 1, pg_role: primary  , pg_offline_query: true }
  28.   vars:
  29.     pg_cluster: pg-node2
  30.     vip_address: 10.10.10.4
  31.     pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  32.     pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]
  34. pg-node3:
  35.   hosts:
  36.     10.10.10.13: { pg_seq: 1, pg_role: primary  , pg_offline_query: true }
  37.   vars:
  38.     pg_cluster: pg-node3
  39.     vip_address: 10.10.10.5
  40.     pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  41.     pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]

Next, you need to refer to the Citus Multi-Node Deployment Guide, and on the Coordinator node, execute the following command to add a data node.

  1. sudo su - postgres; psql meta 
  2. SELECT * from citus_add_node('10.10.10.11', 5432);
  3. SELECT * from citus_add_node('10.10.10.12', 5432);
  4. SELECT * from citus_add_node('10.10.10.13', 5432);
  1. SELECT * FROM citus_get_active_worker_nodes();
  2.   node_name  | node_port
  3. -------------+-----------
  4.  10.10.10.11 |      5432
  5.  10.10.10.13 |      5432
  6.  10.10.10.12 |      5432
  7. (3 rows)

After successfully adding data nodes, you can use the following command to create sample data tables on the coordinator and distribute them to each data node.

  1. -- Declare a distributed table
  2. CREATE TABLE github_events
  3. (
  4.     event_id     bigint,
  5.     event_type   text,
  6.     event_public boolean,
  7.     repo_id      bigint,
  8.     payload      jsonb,
  9.     repo         jsonb,
  10.     actor        jsonb,
  11.     org          jsonb,
  12.     created_at   timestamp
  13. ) PARTITION BY RANGE (created_at);
  14. -- Creating Distributed Tables
  15. SELECT create_distributed_table('github_events', 'repo_id');

For more information about Citus, please refer to the Citus official doc.

Last modified 2022-06-04: fii en docs batch 2 (61bf601)