{"id":16318,"date":"2021-05-07T07:29:11","date_gmt":"2021-05-07T05:29:11","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/"},"modified":"2021-05-07T07:29:11","modified_gmt":"2021-05-07T05:29:11","slug":"getting-started-with-citus-setting-up-a-four-node-cluster","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/","title":{"rendered":"Getting started with Citus – Setting up a four node cluster"},"content":{"rendered":"

One issue with traditional database systems like PostgreSQL is, that you cannot easily scale vertically. Of course you could add read replicas and offload read operations, but that either requires changes in the application, or putting something in front that understands the PostgreSQL dialect and automatically routes writes to the primary and spreads reads across one or more replicas (e.g. pgpool-II<\/a>). But even if you have something in place, you need to deal with replications lags or you need to go for synchronous replication (which comes with it’s own downsides). Another answer to vertically scaling is Citus<\/a>. By using Citus you can have sharding in PostgreSQL by simple installing an extension. <\/p>\n

<\/p>\n

The official Citus documentation<\/a> is really good, so I will not repeat the concepts here. Basically you need one coordinator: This is the node receiving all the traffic from the application. All other nodes are so called worker nodes which perform the actual work, send the results back to the coordinator which finally accumulates the results.<\/p>\n

We’ll start simple with one node, and will end up with one coordinator node and three worker nodes at the end of this post:
\n\"\"<\/a><\/p>\n

Important right from the beginning: Citus is not a fork of PostgreSQL. Citus comes as an extension<\/a> and you can use it with plain community PostgreSQL. Nothing else is required. Of course you need to install the extension and there are pre-build packages for that, either for flavors of Debian<\/a> of flavors of Red Hat\/Fedora<\/a>. In this post will walk through installing the Citus extension from source code, as I’ve installed PostgreSQL 13.2 from source code as well (this is already done, search this blog for “install postgres source code”<\/a>, if you want to know how to do that).<\/p>\n

The first thing to do is to check your environment. pg_config<\/a> needs to be in your $PATH so the Citus extension can find it when it configures\/compiles:<\/p>\n

\npostgres@ip-10-0-1-23:\/home\/postgres\/ [pg132] which pg_config \n\/u01\/app\/postgres\/product\/13\/db_2\/bin\/pg_config\n<\/pre>\n
Once that is ready the procedure for getting the Citus extension installed is quite simple. Either clone the git repository<\/a> (which will give you the latest development snapshot) or download the latest release<\/a>. For the scope of this post we’ll clone the repository:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [pg132] git clone https:\/\/github.com\/citusdata\/citus.git\nCloning into 'citus'...\nremote: Enumerating objects: 66167, done.\nremote: Counting objects: 100% (1447\/1447), done.\nremote: Compressing objects: 100% (638\/638), done.\nremote: Total 66167 (delta 942), reused 1166 (delta 804), pack-reused 64720\nReceiving objects: 100% (66167\/66167), 33.13 MiB | 25.28 MiB\/s, done.\nResolving deltas: 100% (47834\/47834), done.\n<\/pre>\n
Having sources in place, configure, compile and install (I am installing the packages for Debian<\/a> here, you need to adjust this for other Linux distributions):<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [pg132] cd citus\/\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] sudo apt install libghc-curl-dev\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] sudo apt install libzstd-dev\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] .\/configure \nchecking for a sed that does not truncate output... \/usr\/bin\/sed\nchecking for gawk... no\nchecking for mawk... mawk\n...\nconfig.status: creating src\/include\/citus_config.h\nconfig.status: creating src\/include\/citus_version.h\n<\/pre>\n
Ready to compile …<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] make\nMakefile:51: warning: overriding recipe for target 'check'\n\/u01\/app\/postgres\/product\/13\/db_2\/lib\/pgxs\/src\/makefiles\/pgxs.mk:433: warning: ignoring old recipe for target 'check'\nmake -C src\/backend\/distributed\/ all\n...\n -D_GNU_SOURCE -I\/usr\/include\/libxml2  -I\/u01\/app\/postgres\/product\/13\/db_2\/include -I\/home\/postgres\/citus\/vendor\/safestringlib\/include -flto=thin -emit-llvm -c -o ..\/columnar\/write_state_management.bc ..\/columnar\/write_state_management.c\nmake[1]: Leaving directory '\/home\/postgres\/citus\/src\/backend\/distributed'\n<\/pre>\n
.. and install:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] make install\nMakefile:51: warning: overriding recipe for target 'check'\n\/u01\/app\/postgres\/product\/13\/db_2\/lib\/pgxs\/src\/makefiles\/pgxs.mk:433: warning: ignoring old recipe for target 'check'\nmake -C src\/backend\/distributed\/ all\nmake[1]: Entering directory '\/home\/postgres\/citus\/src\/backend\/distributed'\n...\n\/usr\/bin\/install -c -m 644 .\/src\/include\/citus_version.h '\/u01\/app\/postgres\/product\/13\/db_2\/include\/server\/'\n\/usr\/bin\/install -c -m 644 \/home\/postgres\/citus\/.\/src\/include\/distributed\/*.h '\/u01\/app\/postgres\/product\/13\/db_2\/include\/server\/distributed\/'\n<\/pre>\n
From now on the Citus extension will be available for every PostgreSQL cluster which is initialized with these binaries. As usual, we’ll need to initialize a new cluster:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] mkdir -p \/u02\/pgdata\/13\/citus\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] initdb -D \/u02\/pgdata\/13\/citus\nThe files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale \"C.UTF-8\".\nThe default database encoding has accordingly been set to \"UTF8\".\nThe default text search configuration will be set to \"english\".\n\nData page checksums are disabled.\n\nfixing permissions on existing directory \/u02\/pgdata\/13\/citus ... ok\ncreating subdirectories ... ok\nselecting dynamic shared memory implementation ... posix\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting default time zone ... Etc\/UTC\ncreating configuration files ... ok\nrunning bootstrap script ... ok\nperforming post-bootstrap initialization ... ok\nsyncing data to disk ... ok\n\ninitdb: warning: enabling \"trust\" authentication for local connections\nYou can change this by editing pg_hba.conf or using the option -A, or\n--auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    pg_ctl -D \/u02\/pgdata\/13\/citus -l logfile start\n<\/pre>\n
Start the cluster and install the Citus extension:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] echo \"shared_preload_libraries = 'citus,pg_stat_statements'\" >> \/u02\/pgdata\/13\/citus\/postgresql.auto.conf \npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] pg_ctl -D \/u02\/pgdata\/13\/citus\/ start\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] psql -c \"select * from pg_available_extensions where name like '%citus%'\" postgres\n name  | default_version | installed_version |          comment           \n-------+-----------------+-------------------+----------------------------\n citus | 10.1-1          |                   | Citus distributed database\n(1 row)\n<\/pre>\n
Installing the extension into a database works like any other extension (you’ll notice that the extension creates a self signed certificate by default):<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] psql -c \"create database citus\" postgres\nCREATE DATABASE\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] psql -c \"create extension citus\" citus\n2021-05-05 17:44:02.467 UTC [7306] LOG:  citus extension created on postgres without ssl enabled, turning it on during creation of the extension\n2021-05-05 17:44:02.467 UTC [7306] CONTEXT:  SQL statement \"SELECT citus_setup_ssl()\"\n        PL\/pgSQL function inline_code_block line 5 at PERFORM\n2021-05-05 17:44:02.467 UTC [7306] STATEMENT:  create extension citus\n2021-05-05 17:44:02.477 UTC [7306] LOG:  no certificate present, generating self signed certificate\n2021-05-05 17:44:02.477 UTC [7306] CONTEXT:  SQL statement \"SELECT citus_setup_ssl()\"\n        PL\/pgSQL function inline_code_block line 5 at PERFORM\n2021-05-05 17:44:02.477 UTC [7306] STATEMENT:  create extension citus\n2021-05-05 17:44:02.586 UTC [7289] LOG:  received SIGHUP, reloading configuration files\n2021-05-05 17:44:02.587 UTC [7289] LOG:  parameter \"ssl\" changed to \"on\"\n2021-05-05 17:44:02.587 UTC [7289] LOG:  parameter \"ssl_ciphers\" changed to \"ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384\"\nCREATE EXTENSION\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] ls -latr \/u02\/pgdata\/13\/citus\/server*\n-rw------- 1 postgres postgres 1708 May  5 17:44 \/u02\/pgdata\/13\/citus\/server.key\n-rw------- 1 postgres postgres  981 May  5 17:44 \/u02\/pgdata\/13\/citus\/server.crt\n<\/pre>\n
The Citus extension is ready:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] psql -c \"select citus_version();\" citus\n2021-05-05 17:51:13.927 UTC [7343] LOG:  starting maintenance daemon on database 16384 user 10\n2021-05-05 17:51:13.927 UTC [7343] CONTEXT:  Citus maintenance daemon for database 16384 user 10\n                                                     citus_version                                                     \n-----------------------------------------------------------------------------------------------------------------------\n Citus 10.1devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit gitref: master(sha: d0ba12206)\n(1 row)\n<\/pre>\n
Cheking the processes, there is new background worker:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/citus\/ [pg132] ps ax | grep Citus\n 7343 ?        Ss     0:00 postgres: Citus Maintenance Daemon: 16384\/10 \n 7351 pts\/0    S+     0:00 grep Citus\n<\/pre>\n
Time to create some tables and data using pgbench<\/a>:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] pgbench -i -s 10 citus\ndropping old tables...\nNOTICE:  table \"pgbench_accounts\" does not exist, skipping\nNOTICE:  table \"pgbench_branches\" does not exist, skipping\nNOTICE:  table \"pgbench_history\" does not exist, skipping\nNOTICE:  table \"pgbench_tellers\" does not exist, skipping\ncreating tables...\ngenerating data (client-side)...\n1000000 of 1000000 tuples (100%) done (elapsed 4.18 s, remaining 0.00 s)\nvacuuming...\ncreating primary keys...\ndone in 6.35 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 4.43 s, vacuum 0.32 s, primary keys 1.56 s).\n<\/pre>\n
As of now these are standard tables and we can turn them into distributed tables by using the “create_distributed_table” function and provide the column we we want to shard on:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT create_distributed_table('pgbench_accounts', 'aid')\" citus\nNOTICE:  Copying data from local table...\nNOTICE:  copying the data has completed\nDETAIL:  The local data in the table is no longer visible, but is still on disk.\nHINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.pgbench_accounts$$)\n create_distributed_table \n--------------------------\n \n(1 row)\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT truncate_local_data_after_distributing_table(\\$\\$public.pgbench_accounts\\$\\$)\" citus\n truncate_local_data_after_distributing_table \n----------------------------------------------\n \n(1 row)\n<\/pre>\n
This created a distributed table and you can query citus_tables to get more information:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"select * from citus_tables\" citus\n    table_name    | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method \n------------------+------------------+---------------------+---------------+------------+-------------+-------------+---------------\n pgbench_accounts | distributed      | aid                 |             1 | 151 MB     |          32 | postgres    | heap\n(1 row)\n<\/pre>\n
Running a simple query against that table, you’ll notice that the execution plan shows other plan nodes than usually:<\/p>\n
\ncitus=# explain (analyze) select * from pgbench_accounts where aid=100;\n                                                                                     QUERY PLAN                                                                                     \n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=0 width=0) (actual time=12.633..12.635 rows=1 loops=1)\n   Task Count: 1\n   Tuple data received from nodes: 89 bytes\n   Tasks Shown: All\n   ->  Task\n         Tuple data received from node: 89 bytes\n         Node: host=localhost port=5432 dbname=citus\n         ->  Index Scan using pgbench_accounts_pkey_102024 on pgbench_accounts_102024 pgbench_accounts  (cost=0.29..8.30 rows=1 width=97) (actual time=0.019..0.021 rows=1 loops=1)\n               Index Cond: (aid = 100)\n             Planning Time: 0.407 ms\n             Execution Time: 0.046 ms\n Planning Time: 0.481 ms\n Execution Time: 12.658 ms\n(13 rows)\n<\/pre>\n
As we currently only have one node, all data is coming from this node. Time to add more nodes to the cluster. Before doing this, repeat the Citus installation on the remaining nodes, initiliaze a new PostgreSQL cluster in the same way as above, and make sure that authentication is configured so the nodes can talk to each other (pg_hba.conf). Before we can add worker nodes we need to specify which of the nodes is the coordinator node, and we’ll use the current one for this<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT citus_set_coordinator_host('10.0.1.23') \" citus\n citus_set_coordinator_host \n----------------------------\n \n(1 row)\n<\/pre>\n
Now we can add the workers:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT * from citus_add_node('10.0.1.16', 5432)\" citus\nNOTICE:  shards are still on the coordinator after adding the new node\nHINT:  Use SELECT rebalance_table_shards(); to balance shards data between workers and coordinator or SELECT citus_drain_node('10.0.1.23',5432); to permanently move shards away from the coordinator.\n citus_add_node \n----------------\n              2\n(1 row)\n\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT * from citus_add_node('10.0.1.220', 5432)\" citus\n citus_add_node \n----------------\n              3\n(1 row)\n\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT * from citus_add_node('10.0.1.27', 5432)\" citus\n citus_add_node \n----------------\n              4\n(1 row)\n<\/pre>\n
Distributing the data across the nodes is something you need to kick of manually:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT rebalance_table_shards()\" citus\nNOTICE:  Moving shard 102008 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102009 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102010 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102011 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102012 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102013 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102014 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102015 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102016 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102017 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102018 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102019 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102020 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102021 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102022 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102023 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102024 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102025 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102026 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102027 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102028 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102029 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102030 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102031 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\n rebalance_table_shards \n------------------------\n \n(1 row)\n<\/pre>\n
Finally remove the data from the coordinator node by draining the node:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"SELECT citus_drain_node('10.0.1.23',5432)\" citus\nNOTICE:  Moving shard 102032 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102033 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102034 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102035 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102036 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\nNOTICE:  Moving shard 102037 from 10.0.1.23:5432 to 10.0.1.27:5432 ...\nNOTICE:  Moving shard 102038 from 10.0.1.23:5432 to 10.0.1.16:5432 ...\nNOTICE:  Moving shard 102039 from 10.0.1.23:5432 to 10.0.1.220:5432 ...\n citus_drain_node \n------------------\n \n(1 row)\n<\/pre>\n
Doing the same query again, the execution plan shows that the data is not coming anymore from the local node:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql citus\npsql (13.2)\nType \"help\" for help.\n\ncitus=# explain (analyze) select * from pgbench_accounts where aid=100;\n                                                                                     QUERY PLAN                                           \n------------------------------------------------------------------------------------------------------------------------------------------\n Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=0 width=0) (actual time=20.801..20.803 rows=1 loops=1)\n   Task Count: 1\n   Tuple data received from nodes: 89 bytes\n   Tasks Shown: All\n   ->  Task\n         Tuple data received from node: 89 bytes\n         Node: host=10.0.1.220 port=5432 dbname=citus\n         ->  Index Scan using pgbench_accounts_pkey_102024 on pgbench_accounts_102024 pgbench_accounts  (cost=0.29..8.30 rows=1 width=97) \n               Index Cond: (aid = 100)\n             Planning Time: 1.794 ms\n             Execution Time: 1.124 ms\n Planning Time: 2.193 ms\n Execution Time: 20.841 ms\n(13 rows)\n<\/pre>\n
All the shards are evenly distributed across the nodes:<\/p>\n
\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"\\d+\" -h 10.0.1.16 citus\n                                     List of relations\n Schema |          Name           | Type  |  Owner   | Persistence |  Size   | Description \n--------+-------------------------+-------+----------+-------------+---------+-------------\n public | citus_tables            | view  | postgres | permanent   | 0 bytes | \n public | pgbench_accounts_102008 | table | postgres | permanent   | 4176 kB | \n public | pgbench_accounts_102011 | table | postgres | permanent   | 4128 kB | \n public | pgbench_accounts_102014 | table | postgres | permanent   | 4072 kB | \n public | pgbench_accounts_102017 | table | postgres | permanent   | 4120 kB | \n public | pgbench_accounts_102020 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102023 | table | postgres | permanent   | 4144 kB | \n public | pgbench_accounts_102026 | table | postgres | permanent   | 4152 kB | \n public | pgbench_accounts_102029 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102032 | table | postgres | permanent   | 4104 kB | \n public | pgbench_accounts_102035 | table | postgres | permanent   | 4104 kB | \n public | pgbench_accounts_102038 | table | postgres | permanent   | 4136 kB | \n(12 rows)\n\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"\\d+\" -h 10.0.1.220 citus\n                                     List of relations\n Schema |          Name           | Type  |  Owner   | Persistence |  Size   | Description \n--------+-------------------------+-------+----------+-------------+---------+-------------\n public | citus_tables            | view  | postgres | permanent   | 0 bytes | \n public | pgbench_accounts_102009 | table | postgres | permanent   | 4152 kB | \n public | pgbench_accounts_102012 | table | postgres | permanent   | 4144 kB | \n public | pgbench_accounts_102015 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102018 | table | postgres | permanent   | 4128 kB | \n public | pgbench_accounts_102021 | table | postgres | permanent   | 4128 kB | \n public | pgbench_accounts_102024 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102027 | table | postgres | permanent   | 4128 kB | \n public | pgbench_accounts_102030 | table | postgres | permanent   | 4144 kB | \n public | pgbench_accounts_102033 | table | postgres | permanent   | 4160 kB | \n public | pgbench_accounts_102036 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102039 | table | postgres | permanent   | 4152 kB | \n(12 rows)\n\npostgres@ip-10-0-1-23:\/home\/postgres\/ [citus] psql -c \"\\d+\" -h 10.0.1.27 citus\n                                     List of relations\n Schema |          Name           | Type  |  Owner   | Persistence |  Size   | Description \n--------+-------------------------+-------+----------+-------------+---------+-------------\n public | citus_tables            | view  | postgres | permanent   | 0 bytes | \n public | pgbench_accounts_102010 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102013 | table | postgres | permanent   | 4152 kB | \n public | pgbench_accounts_102016 | table | postgres | permanent   | 4128 kB | \n public | pgbench_accounts_102019 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102022 | table | postgres | permanent   | 4128 kB | \n public | pgbench_accounts_102025 | table | postgres | permanent   | 4144 kB | \n public | pgbench_accounts_102028 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102031 | table | postgres | permanent   | 4136 kB | \n public | pgbench_accounts_102034 | table | postgres | permanent   | 4128 kB | \n public | pgbench_accounts_102037 | table | postgres | permanent   | 4112 kB | \n(11 rows)\n<\/pre>\n
That’s it for the initial setup. In a future post we’ll dive into the system in more detail.<\/p>\n","protected":false},"excerpt":{"rendered":"
One issue with traditional database systems like PostgreSQL is, that you cannot easily scale vertically. Of course you could add read replicas and offload read operations, but that either requires changes in the application, or putting something in front that understands the PostgreSQL dialect and automatically routes writes to the primary and spreads reads across […]<\/p>\n","protected":false},"author":29,"featured_media":16319,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1865,229],"tags":[133,2334,77],"type_dbi":[],"class_list":["post-16318","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-aws","category-database-administration-monitoring","tag-aws","tag-citus","tag-postgresql"],"acf":[],"yoast_head":"\nGetting started with Citus - Setting up a four node cluster - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting started with Citus - Setting up a four node cluster\" \/>\n<meta property=\"og:description\" content=\"One issue with traditional database systems like PostgreSQL is, that you cannot easily scale vertically. Of course you could add read replicas and offload read operations, but that either requires changes in the application, or putting something in front that understands the PostgreSQL dialect and automatically routes writes to the primary and spreads reads across […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-05-07T05:29:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png\" \/>\n\t<meta property=\"og:image:width\" content=\"603\" \/>\n\t<meta property=\"og:image:height\" content=\"596\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Getting started with Citus – Setting up a four node cluster\",\"datePublished\":\"2021-05-07T05:29:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/\"},\"wordCount\":728,\"commentCount\":2,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png\",\"keywords\":[\"AWS\",\"Citus\",\"PostgreSQL\"],\"articleSection\":[\"AWS\",\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/\",\"name\":\"Getting started with Citus - Setting up a four node cluster - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png\",\"datePublished\":\"2021-05-07T05:29:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png\",\"width\":603,\"height\":596},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting started with Citus – Setting up a four node cluster\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Getting started with Citus - Setting up a four node cluster - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/","og_locale":"en_US","og_type":"article","og_title":"Getting started with Citus - Setting up a four node cluster","og_description":"One issue with traditional database systems like PostgreSQL is, that you cannot easily scale vertically. Of course you could add read replicas and offload read operations, but that either requires changes in the application, or putting something in front that understands the PostgreSQL dialect and automatically routes writes to the primary and spreads reads across […]","og_url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/","og_site_name":"dbi Blog","article_published_time":"2021-05-07T05:29:11+00:00","og_image":[{"width":603,"height":596,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png","type":"image\/png"}],"author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Getting started with Citus – Setting up a four node cluster","datePublished":"2021-05-07T05:29:11+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/"},"wordCount":728,"commentCount":2,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png","keywords":["AWS","Citus","PostgreSQL"],"articleSection":["AWS","Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/","url":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/","name":"Getting started with Citus - Setting up a four node cluster - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png","datePublished":"2021-05-07T05:29:11+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/citus_overview.png","width":603,"height":596},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/getting-started-with-citus-setting-up-a-four-node-cluster\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Getting started with Citus – Setting up a four node cluster"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16318"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16318\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/16319"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16318"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}