1 所有节点配置
#------服务安装 服务
yum update -y
#------扩展依赖安装
yum install -y epel-release && yum update -y
#------Add Citus repository for package manager
curl https://install.citusdata.com/community/rpm.sh | sudo bash
# install PostgreSQL with Citus extension
sudo yum install -y citus_95
sudo yum install -y postgis2_95.x86_64
# initialize system database (using RHEL 6 vs 7 method as necessary)
sudo service postgresql-9.5 initdb || sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
# preload citus extension
echo "shared_preload_libraries = 'citus'" | sudo tee -a /var/lib/pgsql/9.5/data/postgresql.conf
#------配置
sudo echo "listen_addresses = '*'" | sudo tee -a /var/lib/pgsql/9.5/data/postgresql.conf
sudo echo "host all all 0.0.0.0/0 trust" | sudo tee -a /var/lib/pgsql/9.5/data/pg_hba.conf
#-------------------------启动服务及开机自启--------------
# start the db server
sudo service postgresql-9.5 restart
# and make it start automatically when computer does
sudo chkconfig postgresql-9.5 on
#------------------------扩展---------------
sudo -i -u postgres psql -c "CREATE EXTENSION citus;"
sudo -i -u postgres psql -c "CREATE EXTENSION postgis;"
2 主节点配置
#-----配置work节点
sudo echo "worker-101 5432" | sudo -u postgres tee -a /var/lib/pgsql/9.5/data/pg_worker_list.conf
#-----重新加载配置
sudo service postgresql-9.5 reload
#-----查看work节点
sudo -i -u postgres psql -c "SELECT * FROM master_get_active_worker_nodes();"
3 表测试
#-----创建表
CREATE TABLE github_events
(
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb,
actor jsonb,
org jsonb,
created_at timestamp
);
#-----表分片
SELECT master_create_distributed_table('github_events', 'repo_id', 'hash');
#-----设定分片个数(16)及每个分片副本数(3)
SELECT master_create_worker_shards('github_events', 16, 3);
#----插入数据
INSERT INTO github_events VALUES (2489373118,'PublicEvent','t',24509048,'{}','{"id": 24509048, "url": "https://api.github.com/repos/SabinaS/csee6868", "name": "SabinaS/csee6868"}','{"id": 2955009, "url": "https://api.github.com/users/SabinaS", "login": "SabinaS", "avatar_url": "https://avatars.githubusercontent.com/u/2955009?", "gravatar_id": ""}',NULL,'2015-01-01 00:09:13');
#---删除或修改
SELECT master_modify_multiple_shards(
'DELETE FROM github_events);
4 集群管理
#-----查看work节点
SELECT * from master_get_active_worker_nodes();
#-----元数据查看
SELECT * from master_get_table_metadata('github_events');
#-----分区查看
SELECT * from pg_dist_partition;
#-----分片查看
SELECT * from pg_dist_shard;
#-----分片分布查看
SELECT * from pg_dist_shard_placement;
#-----移动分片
SELECT master_copy_shard_placement(shardid, 'good_host', 5432, 'bad_host', 5432);
5 加节点及移动分片 v1->v4
5.1 加节点
a 修改pg_worker_list.conf
b 重新载入配置 select pg_reload_conf(); 或 sudo service postgresql-9.5 reload
c 查看work节点 SELECT * from master_get_active_worker_nodes();
5.2 移动分片
a 查看分片元数据
SELECT * from master_get_table_metadata('github_events');
SELECT * from pg_dist_shard;
SELECT * from pg_dist_shard_placement;
shardid | shardstate | shardlength | nodename | nodeport
---------+------------+-------------+----------+----------
102013 | 1 | 0 | v1 | 5432
shardstate 1(正常) ， 3（待修复） ， 4（将删除）
b 元数据中加入新分片
INSERT INTO pg_dist_shard_placement(shardid , shardstate , shardlength , nodename , nodeport) VALUES (102021,3,0,v4,5432)
c 移动数据到新分片
SELECT master_copy_shard_placement(102021, 'v1', 5432, 'v4', 5432);
d 标记删除分片，改数据分片此时已经脱离集群，待删除
UPDATE pg_dist_shard_placement SET shardstate = 4 WHERE shardid = 102021 AND nodename = 'v1' AND nodeport = 5432
e 删除原分片数据及元数据
DROP TABLE github_events_shard_id (在对应节点上进行操作)
DELETE FROM pg_dist_shard_placement shardstate = 4
6 主节点的HA
配置参看http://www.cnblogs.com/zhangeamon/p/5465501.html
问题： 应用程序连接从库对数据分片移动后不生效
总结，程序只能连接主数据库，可做读写分离