pgspider Citus节点数据移动操作

前边有简单的说明过通过副本数以及修改元数据信息,移除节点保证citus 的可用性,以下是一个简单的
节点添加以及数据移动的说明

环境准备

节点需要开启逻辑复制,所以以前的基础镜像稍有调整,同时因为开启了安全认证同时使用了.pgpass

  • docker 基础镜像
 
FROM dalongrong/pgspider:citus-9.1
RUN echo "wal_level = logical" >> /usr/local/pgspider/share/postgresql/postgresql.conf.sample
  • docker-compose 文件
version: "3"
services: 
  pg-citus-master:
    container_name: pg-citus-master
    image: dalongrong/pgspider:citus-9.1-wal
    volumes: 
    - "./csvfiles:/opt/csv"
    - "./sql:/docker-entrypoint-initdb.d/"
    - "./.pgpass:/var/lib/postgresql/.pgpass"
    ports: 
    - "5432:5432"
    environment: 
    - "POSTGRES_PASSWORD=dalong"
  pg-citus-worker:
    container_name: pg-citus-worker
    image: dalongrong/pgspider:citus-9.1-wal
    volumes: 
    - "./csvfiles:/opt/csv"
    - "./sql:/docker-entrypoint-initdb.d/"
    - "./.pgpass:/var/lib/postgresql/.pgpass"
    environment: 
    - "POSTGRES_PASSWORD=dalong"
    ports: 
    - "5433:5432"
  pg-citus-worker2:
    container_name: pg-citus-worker2
    image: dalongrong/pgspider:citus-9.1-wal
    volumes: 
    - "./csvfiles:/opt/csv"
    - "./sql:/docker-entrypoint-initdb.d/"
    - "./.pgpass:/var/lib/postgresql/.pgpass"
    environment: 
    - "POSTGRES_PASSWORD=dalong"
    ports: 
    - "5434:5432"
  pg-citus-worker3:
    container_name: pg-citus-worker3
    image: dalongrong/pgspider:citus-9.1-wal
    volumes: 
    - "./csvfiles:/opt/csv"
    - "./sql:/docker-entrypoint-initdb.d/"
    - "./.pgpass:/var/lib/postgresql/.pgpass"
    environment: 
    - "POSTGRES_PASSWORD=dalong"
    ports: 
    - "5435:5432"
  • 操作说明
    默认节点全部开启,但是citus 添加的节点只要1,2 ,3 为添加的节点,同时会有数据移动操作
  • init sql
    主要是扩展的创建
-- wrap in transaction to ensure Docker flag always visible
BEGIN;
CREATE EXTENSION citus;
COMMIT;
  • 需要的数据
    都是来自官方文档
 
curl https://examples.citusdata.com/tutorial/companies.csv > csvfiles/scompanies.csv
curl https://examples.citusdata.com/tutorial/campaigns.csv > csvfiles/campaigns.csv
curl https://examples.citusdata.com/tutorial/ads.csv > csvfiles/ads.csv
  • 启动
docker-compose up -d
  • 创建表以及添加数据
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
);
  • 添加主键
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);
  • 导入数据

    容器内部执行命令

\copy companies from ‘companies.csv‘ with csv
\copy campaigns from ‘campaigns.csv‘ with csv
\copy ads from ‘ads.csv‘ with csv

使用

  • 配置citus 分片数
set citus.shard_count =4;
  • 创建分布式表
SELECT create_distributed_table(‘companies‘, ‘id‘);
SELECT create_distributed_table(‘campaigns‘, ‘company_id‘);
SELECT create_distributed_table(‘ads‘, ‘company_id‘);
  • 添加新节点
SELECT
    master_add_node (‘pg-citus-worker3‘,
        ‘5432‘);
  • 查询分片元数据信息

    测试使用ads 表

select * from pg_dist_placement join pg_dist_node on pg_dist_placement.groupid=pg_dist_node.groupid where shardid in (select shardid from pg_dist_shard where logicalrelid=‘ads‘::regclass);

信息
pgspider Citus节点数据移动操作

 

 

  • 操作数据迁移
    我们需要的是将102017 以及102019 分片数据移动到worker3 ,操作是通过流式复制
    worker3 创建表结构
 
 CREATE TABLE ads_102017 (
    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
    );
   CREATE TABLE ads_102019 (
    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
    );

worker2创建发布:

CREATE PUBLICATION pub_shard FOR TABLE ads_102017;
CREATE PUBLICATION pub_shard2 FOR TABLE ads_102019;

worrker3 订阅:

CREATE SUBSCRIPTION sub_shard
CONNECTION ‘host=pg-citus-worker2‘
PUBLICATION pub_shard;
 
CREATE SUBSCRIPTION sub_shard2
CONNECTION ‘host=pg-citus-worker2‘
PUBLICATION pub_shard2;

master节点修改元数据
实际为了数据的一直,肯能还需要进行锁表操作 lock table ads IN EXCLUSIVE MODE;

 
等待数据同步完备修改元数据:
update pg_dist_placement set groupid=3 where shardid in (102017,102019) and groupid=2;

删除worker2 的对应分片的数据,以及删除发布

DROP PUBLICATION pub_shard;
DROP PUBLICATION pub_shard2;
drop table ads_102017;
drop table ads_102019;

删除worker3的订阅

DROP SUBSCRIPTION sub_shard;
DROP SUBSCRIPTION sub_shard2;
  • 数据查询
select * from ads

如果不出问题,数据查询一样是没有问题的

说明

Citus 对于数据处理同时包含了亲缘性,需要关联的分片一起移动,比如上边的操作我们只移动了ads ,但是ads 与
campaigns 几个分片也是需要移动的,不移动关联查询的效果
pgspider Citus节点数据移动操作

 

 

查询亲缘表信息,注意每个分片的信息都需要调整的,具体操作方法一样

 
select * from pg_dist_shard where logicalrelid in(select logicalrelid from pg_dist_partition where colocationid=(select colocationid from pg_dist_partition where partmethod=‘h‘ and logicalrelid=‘ads‘::regclass)) and (shardminvalue,shardmaxvalue)=(select shardminvalue,shardmaxvalue from pg_dist_shard where shardid=102017);

信息如下:
pgspider Citus节点数据移动操作

参考资料

https://yq.aliyun.com/articles/647369
https://github.com/rongfengliang/pgspider-docker
https://www.cnblogs.com/rongfengliang/p/12446941.html
http://docs.citusdata.com/en/v9.2/develop/reference_ddl.html#colocation-groups
http://docs.citusdata.com/en/v9.2/sharding/data_modeling.html#colocation

pgspider Citus节点数据移动操作

上一篇:微信小程序传值取值的几种方法


下一篇:使用phonegap + appframework2.0框架