os: ubuntu 16.04
postgresql: 9.6.8
citus: postgresql-9.6-citus 8.0.0
What is Citus?
Citus is basically worry-free Postgres that is built to scale out. It’s an extension to Postgres that distributes data and queries in a cluster of multiple machines. As an extension (rather than a fork), Citus supports new PostgreSQL releases, allowing users to benefit from new features while maintaining compatibility with existing PostgreSQL tools.
Citus horizontally scales PostgreSQL across multiple machines using sharding and replication. Its query engine parallelizes incoming SQL queries across these servers to enable human real-time (less than a second) responses on large datasets.
Available in Three Ways:
As open source to add to existing Postgres servers
On-premise with additional enterprise grade security and cluster management tools
As a fully-managed database as a service, called Citus Cloud
citus 是 postgresql 一种分库分表解决方案。是作为 extension使用的。
数据库的实例分为两类:
coordinator 节点: 存储数据库分表的元数据,不存储实际的数据。
worker 节点: 真实存储数据,是shard节点。
用户只连接 coordinator 节点,由 coordinator 解析sql,发给 worker节点执行,执行结果再返回给 coordinator 节点。
ip规划如下:
192.168.0.92 pgsql1 --coordinator 节点
192.168.0.90 pgsql2 --worker 节点
192.168.0.88 pgsql3 --worker 节点
生产环境一定要创建各个节点的salve节点,必须创建。
安装好 postgresql 9.6
剩下的参考其它blog
# vi /etc/hosts
192.168.0.92 pgsql1
192.168.0.90 pgsql2
192.168.0.88 pgsql3
下载安装 citus
所有节点都需要操作
使用 pgdg 源
# vi /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# apt-get update
# apt -y install postgresql-9.6-citus
或者使用 citus 源
# curl https://install.citusdata.com/community/deb.sh | sudo bash
# apt update
# apt -y install postgresql-9.6-citus-8.0
列出一些相关的文件及及文件
# dpkg -L postgresql-9.6-citus
/usr/include/postgresql/9.6/server
/usr/include/postgresql/9.6/server/citus_version.h
/usr/include/postgresql/9.6/server/distributed/*
/usr/share/postgresql/9.6
/usr/share/postgresql/9.6/extension
/usr/share/postgresql/9.6/extension/citus*.sql
/usr/share/postgresql/9.6/extension/citus.control
/usr/lib/postgresql/9.6/lib/citus.so
查看 postgres 用户环境变量
所有节点都需要操作
# su - postgres
$ cat ~/.profile
调整参数
所有节点都需要操作
# vi /etc/postgresql/9.6/main/postgresql.conf
shared_preload_libraries = 'citus,pg_stat_statements'
# /etc/init.d/postgresql restart
配置 citus
所有节点都需要操作
# su - postgres
postgres@pgsql1:~$ psql
psql (9.6.8)
Type "help" for help.
postgres=# select * from pg_available_extensions where name like '%citus%';
name | default_version | installed_version | comment
-------+-----------------+-------------------+----------------------------
citus | 8.0-8 | | Citus distributed database
(1 row)
由于 extension 是针对 database 级别的,所以需要先创建指定的业务数据库和 extension。
所有节点都需要操作
$ psql
psql (9.6.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# create user cituser with superuser;
postgres=# alter user cituser with password 'citusercituser';
postgres=# create database citusdb with owner = cituser;
postgres=# \q
$ psql -h 127.0.0.1 -U cituser citusdb -c "create extension citus;"
citusdb=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
citus | 8.0-8 | pg_catalog | Citus distributed database
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
citusdb=# \dn
List of schemas
Name | Owner
--------+----------
citus | postgres
public | postgres
(2 rows)
配置 pg_hba.conf
所有节点都需要操作
$ vi /etc/postgresql/9.6/main/pg_hba.conf
host all cituser 192.168.0.92/32 trust
host all cituser 192.168.0.90/32 trust
host all cituser 192.168.0.88/32 trust
$ psql -c "select pg_reload_conf();"
注意,pgsql2、pgsql3 是限定用户访问的,用户只能连接 pgsql1 。
plsql1节点上添加 work节点
$ psql -h 192.168.0.92 -U cituser citusdb
Password for user cituser:
psql (9.6.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
citusdb=# select * from master_add_node('192.168.0.90',5432);
citusdb=# select * from master_add_node('192.168.0.88',5432);
citusdb=# select * from master_get_active_worker_nodes();
node_name | node_port
--------------+-----------
192.168.0.88 | 5432
192.168.0.90 | 5432
(2 rows)
安装结束,下一篇blog介绍下如何创建表。
参考:
https://www.citusdata.com/
https://docs.citusdata.com/en/v8.0/
https://docs.citusdata.com/en/stable/index.html