CitusDB Multi-node Install and Test
Multi-node setup on CentOS
参考官网：https://docs.citusdata.com/en/v6.2/installation/production_rhel.html
Test Environments
CentOS Linux release 7.2.1511 (Core)
Three nodes（citus1,citus2,citus3）
Steps to be executed on all nodes
1. Add repository
curl https://install.citusdata.com/community/rpm.sh | sudo bash
2. Install PostgreSQL + Citus and initialize a database
sudo yum install -y citus62_96 sudo service postgresql-9.6 initdb || sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb echo "shared_preload_libraries = 'citus'" | sudo tee -a /var/lib/pgsql/9.6/data/postgresql.conf
3. Configure connection and authentication
sudo vi /var/lib/pgsql/9.6/data/postgresql.conf
sudo vi /var/lib/pgsql/9.6/data/pg_hba.conf
Note:Your DNS settings may differ. Also these settings are too permissive for some environments.The configuration is shown below:
https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html
4. Start database servers, create Citus extension
sudo service postgresql-9.6 restart sudo chkconfig postgresql-9.6 on
You must add the Citus extension to every database you would like to use in a cluster. The following example adds the extension to the default database which is named postgres.
sudo -i -u postgres psql -c "CREATE EXTENSION citus;"
Steps to be executed only on the coordinator node
1. Add worker node
sudo -i -u postgres psql -c "SELECT * from master_add_node('citus2', 5432);" sudo -i -u postgres psql -c "SELECT * from master_add_node('citus3', 5432);"
2. Verify
sudo -i -u postgres psql -c "SELECT * FROM master_get_active_worker_nodes();"
Succeeded!
Notes:Remove worker node command(If you add a node wrong)
sudo -i -u postgres psql -c "SELECT * from master_remove_node('work', 5432);"
See the details below:
https://docs.citusdata.com/en/v6.2/reference/user_defined_functions.html
Test
Download sample data
curl https://examples.citusdata.com/tutorial/companies.csv > /var/lib/pgsql/companies.csv
curl https://examples.citusdata.com/tutorial/campaigns.csv > /var/lib/pgsql/campaigns.csv
curl https://examples.citusdata.com/tutorial/ads.csv > /var/lib/pgsql/ads.csv
Creating tables
To start, you can first connect to the Citus co-ordinator(citus1) using psql.
# sudo -i -u postgres psql
CREATE TABLE companies ( id bigint NOT NULL, name text NOT NULL, image_url text, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL ); CREATE TABLE campaigns ( id bigint NOT NULL, company_id bigint NOT NULL, name text NOT NULL, cost_model text NOT NULL, state text NOT NULL, monthly_budget bigint, blacklisted_site_urls text[], created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL ); CREATE TABLE ads ( id bigint NOT NULL, company_id bigint NOT NULL, campaign_id bigint NOT NULL, name text NOT NULL, image_url text, target_url text, impressions_count bigint DEFAULT 0, clicks_count bigint DEFAULT 0, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL );
Next, you can create primary key indexes on each of the tables just like you would do in PostgreSQL
ALTER TABLE companies ADD PRIMARY KEY (id); ALTER TABLE campaigns ADD PRIMARY KEY (id, company_id); ALTER TABLE ads ADD PRIMARY KEY (id, company_id);
Distributing tables and loading data
SELECT create_distributed_table('companies', 'id'); SELECT create_distributed_table('campaigns', 'company_id'); SELECT create_distributed_table('ads', 'company_id');
Load the data
postgres=# \copy companies from '/var/lib/pgsql/companies.csv' with csv; COPY 100 postgres=# \copy campaigns from '/var/lib/pgsql/campaigns.csv' with csv; COPY 978 postgres=# \copy ads from '/var/lib/pgsql/ads.csv' with csv; COPY 7364
Running queries
postgres=# INSERT INTO companies VALUES (5000, 'New Company', 'https://randomurl/image.png', now(), now()); INSERT 0 1 postgres=# UPDATE campaigns postgres-# SET monthly_budget = monthly_budget*2 postgres-# WHERE company_id = 5; UPDATE 12 postgres=# BEGIN; BEGIN postgres=# DELETE from campaigns where id = 46 AND company_id = 5; DELETE 1 postgres=# DELETE from ads where campaign_id = 46 AND company_id = 5; DELETE 7 postgres=# COMMIT; COMMIT postgres=# SELECT name, cost_model, state, monthly_budget postgres-# FROM campaigns postgres-# WHERE company_id = 5 postgres-# ORDER BY monthly_budget DESC postgres-# LIMIT 10; name | cost_model | state | monthly_budget -------------------------+---------------------+----------+---------------- Wondra | cost_per_impression | running | 16732 Quicksilver | cost_per_click | paused | 12664 Cyborg | cost_per_impression | running | 8198 Agent Hancock | cost_per_impression | running | 6996 Mr Cyclops I | cost_per_click | paused | 6122 Sasquatch Eyes | cost_per_impression | archived | 5828 Ultra Namor | cost_per_impression | paused | 5554 Carnage | cost_per_impression | paused | 4300 Cyborg Two-Face Machine | cost_per_click | archived | 4086 Binary | cost_per_impression | paused | 3620 (10 rows)
