timescaledb replication 使用

replication 可以确保系统的ha 以及lb 数据的查询，timesacledb 使用pg 内置的stream replication 进行复制的支持

docker 运行参考 https://github.com/rongfengliang/streaming-replication-docker ( 修改基础镜像添加gis 支持）

运行复制集群

clone 复制集群docker 代码

git clone https://github.com/timescale/streaming-replication-docker.git

运行复制集群

  ./start_containers.sh

测试复制集群

注意此时账户postgres 密码是postgres repuser 是repuser 可以查看配置
测试数据下载地址 https://timescaledata.blob.core.windows.net/datasets/nyc_data.tar.gz

状态查看
导入schema

psql -U postgres -d nyc_data -h localhost < nyc_data.sql

导入数据

psql -U postgres -d nyc_data -h localhost -c "\COPY rides FROM nyc_data_rides.csv CSV"

数据查询

时序查询

SELECT time_bucket('5 minute', pickup_datetime) AS five_min, count(*) FROM rides WHERE pickup_datetime < '2016-01-01 02:00' GROUP BY five_min ORDER BY five_min;

结果

2016-01-01 00:00:00 703

2016-01-01 00:05:00 1482

2016-01-01 00:10:00 1959

2016-01-01 00:15:00 2200

2016-01-01 00:20:00 2285

2016-01-01 00:25:00 2291

2016-01-01 00:30:00 2349

2016-01-01 00:35:00 2328

2016-01-01 00:40:00 2440

2016-01-01 00:45:00 2372

2016-01-01 00:50:00 2388

2016-01-01 00:55:00 2473

2016-01-01 01:00:00 2395

2016-01-01 01:05:00 2510

2016-01-01 01:10:00 2412

2016-01-01 01:15:00 2482

2016-01-01 01:20:00 2428

2016-01-01 01:25:00 2433

2016-01-01 01:30:00 2337

2016-01-01 01:35:00 2366

2016-01-01 01:40:00 2325

gis 查询

让nyc_data 支持gis

CREATE EXTENSION postgis;

ALTER TABLE rides ADD COLUMN pickup_geom geometry(POINT,2163);

ALTER TABLE rides ADD COLUMN dropoff_geom geometry(POINT,2163);

生成geo 数据（有点慢，需要花点时间）

UPDATE rides SET pickup_geom = ST_Transform(ST_SetSRID(ST_MakePoint(pickup_longitude,pickup_latitude),4326),2163);

UPDATE rides SET dropoff_geom = ST_Transform(ST_SetSRID(ST_MakePoint(dropoff_longitude,dropoff_latitude),4326),2163);

gis 查询

SELECT time_bucket('30 minutes', pickup_datetime) AS thirty_min, COUNT(*) AS near_times_sq

  FROM rides

  WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400

    AND pickup_datetime < '2016-01-01 14:00'

  GROUP BY thirty_min ORDER BY thirty_min;

说明

详细配置说明，参考 https://docs.timescale.com/v0.9/tutorials/replication，里面有详细的说明

参考资料

https://docs.timescale.com/v0.9/tutorials/tutorial-hello-nyc
https://docs.timescale.com/v0.9/tutorials/replication
https://docs.timescale.com/v0.9/getting-started/installation/mac/installation-homebrew
https://github.com/timescale/streaming-replication-docker
https://github.com/rongfengliang/streaming-replication-docker

巴特西

timescaledb replication 使用

运行复制集群

测试复制集群

数据查询

gis 查询

说明

参考资料

最新文章

热门文章