Timescaledb集群部署

EMQX 企业版技术支持发表于:2022年03月04日 10:58:49更新于:2022年03月04日 10:59:21

一、集群组件

Patroni + Etcd + PostgreSQL+ Timescale

说明:采用一主2备

备注:暂未编写主节点IP漂移部分,如果需要主节点自动漂移可以采用keepalive

二、环境准备

1. 主要软件

i. 操作系统:centOS 7.8

ii. Postgresql: 12

iii. Python3: 3.6.8

iv. Patroni: 1.6.5

v. Etcd:3.4.13

vi. Timescale:2.5.1

2. 主机规划

IP

software

10.206.0.32

PostgreSQL,Patroni,Etcd,timescale

10.206.0.53

PostgreSQL,Patroni,Etcd,timescale

10.206.0.80

PostgreSQL,Patroni,Etcd,timescale

三、部署

1. 环境基础准备

2. Etcd部署

i. 下载二进制文件

curl -L https://github.com/coreos/etcd/releases/download/v3.4.13/etcd-v3.4.13-linux-amd64.tar.gz -o etcd-v3.4.13-linux-amd64.tar.gz

image.png

ii.解压缩后得到etcd和etcdctl文件,将它们复制到/usr/bin目录下

image.png

iii.将其部署为一个systemd的服务,创建systemd服务配置文件/usr/lib/systemd/system/etcd.service

[Unit]
Description=Etcd Server
After=network.target
After=network-online.target
Wants=network-online.target 
[Service]
Type=notify
EnvironmentFile=/etc/etcd/etcd.conf
ExecStart=/usr/bin/etcd
Restart=on-failure
LimitNOFILE=65536 
[Install]
WantedBy=multi-user.target

image.png

iv.配置/etc/etcd/etcd.conf (每台有细节差别)

第一台:

ETCD_NAME=etcd1
ETCD_DATA_DIR="/var/lib/etcd/etcd1"
ETCD_LISTEN_PEER_URLS="http://10.206.0.32:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.206.0.32:2379,http://127.0.0.1:2379,http://10.206.0.32:4001"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.206.0.32:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://10.206.0.32:2380,etcd2=http://10.206.0.53:2380,etcd3=http://10.206.0.80:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://10.206.0.32:2379,http://10.206.0.32:4001"

image.png

第二台:

ETCD_NAME=etcd2
ETCD_DATA_DIR="/var/lib/etcd/etcd2"
ETCD_LISTEN_PEER_URLS=" 
ETCD_LISTEN_CLIENT_URLS=" 
ETCD_INITIAL_ADVERTISE_PEER_URLS=" 
ETCD_INITIAL_CLUSTER="etcd1= 
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://10.206.0.53:2379,http://10.206.0.53:4001"

image.png

第三台:

ETCD_NAME=etcd3
ETCD_DATA_DIR="/var/lib/etcd/etcd3"
ETCD_LISTEN_PEER_URLS=" 
ETCD_LISTEN_CLIENT_URLS=" 
ETCD_INITIAL_ADVERTISE_PEER_URLS=" 
ETCD_INITIAL_CLUSTER="etcd1= 
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://10.206.0.80:2379,http://10.206.0.80:4001"

image.png

v.启动etcd 服务

systemctl daemon-reload

systemctl enable etcd

systemctl restart etcd

vi. 查看每台服务是否正常

systemctl status etcd.service

image.png

vii.检查集群状态

etcdctl --endpoints="http://10.206.0.32:2379,http://10.206.0.53:2379,http://10.206.0.80:2379" endpoint status --write-out=table

image.png

viii.查看集群健康状态

etcdctl --endpoints="http://10.206.0.32:2379,http://10.206.0.53:2379,http://10.206.0.80:2379" endpoint health

image.png

3.PostgreSQL部署(每台pgsql上都执行)

先安装下载源:

yum install -y 

https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

有互联网的时候直接命令安装:yum install -y postgresql12 postgresql12-server postsedgresql12-libs postgresql12-contrib

i. 下载安装包

将安装包下载到/root/package/pgsql下:

yum install -y postgresql12-server --downloadonly --downloaddir=/root/package/pgsql/

yum install -y postgresql12-contrib --downloadonly --downloaddir=/root/package/pgsql/

image.png

ii. 安装命令

在/root/package/pgsql/下执行:

rpm -ivh libicu-50.2-4.el7_7.x86_64.rpm 

postgresql12-libs-12.9-1PGDG.rhel7.x86_64.rpm 

postgresql12-12.9-1PGDG.rhel7.x86_64.rpm 

postgresql12-server-12.9-1PGDG.rhel7.x86_64.rpm 

libicu-50.2-4.el7_7.x86_64.rpm 

libxslt-1.1.28-6.el7.x86_64.rpm 

postgresql12-contrib-12.9-1PGDG.rhel7.x86_64.rpm

image.png

iii. 修改postgres 账号密码

rpm 方式安装pgsql时会自动创建postgres账号,但没有密码,设置密码命令(密码为引号中的值: postgres):

echo "postgres"| passwd --stdin postgres

image.png

iv. 创建pgsql的数据目录

mkdir -p /pgsql/data

chown postgres:postgres -R /pgsql

chmod -R 700 /pgsql/data

image.png

v. 配置sudo权限

visudo

## Allow root to run any commands anywhere

postgres        ALL=(root)        NOPASSWD: ALL

image.png

4. Python3 安装及依赖(可以有网的环境直接执行rpm安装:yum install -y python3 python-psycopg2 python3-devel)

i. 下载安装包

下载python3:

yum install -y python3 --downloadonly --downloaddir=/root/package/python/

image.png

下载python-psycopg2

yum install -y python-psycopg2 --downloadonly --downloaddir=/root/package/python/

image.png

下载python3-devel

yum install -y python3-devel --downloadonly --downloaddir=/root/package/python/

image.png

ii. 安装软件(每个节点)

安装python3

rpm -ivh python3-libs-3.6.8-18.el7.x86_64.rpm 

python3-pip-9.0.3-8.el7.noarch.rpm 

python3-setuptools-39.2.0-10.el7.noarch.rpm 

python3-3.6.8-18.el7.x86_64.rpm

image.png

安装python-psycopg2

rpm -ivh postgresql11-libs-11.14-1PGDG.rhel7.x86_64.rpm python-psycopg2-2.7.5-1.rhel7.1.x86_64.rpm

image.png

安装python3-devel

rpm -ivh python3-devel-3.6.8-18.el7.x86_64.rpm 

redhat-rpm-config-9.1.0-88.el7.centos.noarch.rpm 

dwz-0.11-3.el7.x86_64.rpm 

python3-rpm-macros-3-34.el7.noarch.rpm 

python3-rpm-generators-6-2.el7.noarch.rpm 

python-srpm-macros-3-34.el7.noarch.rpm 

python-rpm-macros-3-34.el7.noarch.rpm 

perl-srpm-macros-1-8.el7.noarch.rpm

image.png

5.安装patroni

i. 安装(每个节点)

python3 -m pip install --upgrade pip

pip3 install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/

pip3 install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/

image.png

image.png

ii. 验证patroi(每个节点)

which patroni

patronictl --help

image.png

iii.创建patroi服务(每个节点都执行)

vi /usr/lib/systemd/system/patroni.service
[Unit]
Description=Runners to orchestrate a high-availability 
PostgreSQLAfter=syslog.target network.target  
[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=processTimeoutSec=30Restart=no  
[Install]
WantedBy=multi-user.target

image.png

iv. 配置 patroni 配置文件/etc/patroni/patroni.yml

vi /etc/patroni/patroni.yml

scope: pgsql
namespace: /service/ # Etcd中键值位置
name: pg3 # patroni名称,每个节点不一样  
restapi:  
listen: 0.0.0.0:8008 # 保持默认,监听所有的8008端口  
connect_address: 10.206.0.80:8008 # 本地连接通信  
etcd3: # 这里建议使用etcdv3,默认是etcdv2,默认写入到etcd中的键值都是不可见的(仅patroni如此)  
hosts: 10.206.0.32:2379,10.206.0.53:2379,10.206.0.80:2379 # Etcd地址,如果这里使用单节点的etcd,需要将hosts关键字替换为host  
bootstrap:  dcs:    
ttl: 30    
loop_wait: 10    
retry_timeout: 10    
maximum_lag_on_failover: 1048576 # 如果延迟超过1048576,不允许failover    
master_start_timeout: 300    
synchronous_mode: false # 异步复制    
postgresql: # 以下是pgsql服务的特性即参数配置,不详述      
use_pg_rewind: true      
use_slots: true      
parameters:        
listen_addresses: "0.0.0.0"        
port: 5432        
wal_level: logical        
hot_standby: "on"        
wal_keep_segments: 100        
max_wal_senders: 10        
max_replication_slots: 10        
wal_log_hints: "on"        
Shared_buffers: 12GB        
max_connections: 600    
initdb:  
- encoding: UTF8  
- locale: C  
- lc-ctype: zh_CN.UTF-8  
- data-checksums    
pg_hba: # 定义流复制用户和远程连接身份鉴别设置  
- host replication repl 10.206.0.0/24 md5  
- host all all 0.0.0.0/0 md5  
postgresql:  
listen: 0.0.0.0:5432  
connect_address: 10.206.0.80:5432 # 连接pgsql服务的配置,这里不能使用127.0.0.1,pg_basebackup需要远程连接主库进行在线复制  
data_dir: /pgsql/data  
bin_dir: /usr/pgsql-12/bin    
authentication:    
  replication:      
    username: repl      
    password: "postgres"    
  superuser:      
    username: postgres      
    password: "postgres"    
  rewind:      
    username: postgres      
    password: "postgres"     
  basebackup:    
    max-rate: 100M    
    checkpoint: fast  
  tags:    
       nofailover: false # 是否执行自动切换    
       noloadbalance: false # 是否开启负载均衡    
       clonefrom: false    
       nosync: false

备注:其他PG节点的patroni.yml需要相应修改下面3个

· name: 名字每个节点不重复就行

· restapi.connect_address:各自节点的IP端口

postgresql.connect_address:各自节点的IP端口

image.png

image.png

v. 启动patroni (每台执行)

systemctl start patroni

查看启动状态

systemctl status patroni

image.png

vi. 检查集群状态

patronictl -c /etc/patroni/patroni.yml list

image.png

vii. 设置开启自启

systemctl enable patroni

image.png

viii. 修改参数(选做)修改后pgsql相关配置也会自动更新

1. 修改shared_buffers

patronictl -c /etc/patroni/patroni.yml edit-config -p "shared_buffers='32GB'"

image.png

重启生效 :

patronictl -c /etc/patroni/patroni.yml restart pgsql

备注:pgsql的只是集群的Member的 Cluster值

image.png

修改max_connections

patronictl -c /etc/patroni/patroni.yml edit-config -p 'max_connections=600'

重启生效:

patronictl -c /etc/patroni/patroni.yml restart pgsql

四、TimescaleDB部署

1. 创建 timescaledb 库(每台执行)

tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL

image.png

2. 下载安装包(每台执行)

yum install -y timescaledb-2-postgresql-12 --downloadonly --downloaddir=/root/timescale/

image.png

3.安装(每台执行)

rpm -ivh timescaledb-tools-0.12.0-0.el7.x86_64.rpm 

timescaledb-2-loader-postgresql-12-2.5.1-0.el7.x86_64.rpm 

timescaledb-2-postgresql-12-2.5.1-0.el7.x86_64.rpm

image.png

4. 修改postgres.conf (任意一节点)

patronictl -c /etc/patroni/patroni.yml edit-config -p "shared_preload_libraries = 'timescaledb'"

image.png

5. 重启生效(任意一节点)

patronictl -c /etc/patroni/patroni.yml restart pgsql

image.png

6. 检查配置是否持久化

cat /pgsql/data/postgresql.conf

image.png

7. 验证安装

i. 进入到主节点。

su – postgres

psql

CREATE DATABASE timeseries;

image.png

ii. 使用扩展数据库

\c timeseries

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

image.png

iii. 查看timescaledb版本

\dx;

SELECT default_version, installed_version FROM pg_available_extensions WHERE name = 'timescaledb';

image.png

iv.查看时序相关的模式是否生成

image.png

五、测试(进行主节点操作,根据检查集群状态中的Role为Leader机器上操作)

1. 数据库创建

创建账号emqx 密码为:emqxa301

create user emqx with password 'emqxa301';

修改数据库,所timeseries属者为:emqx

alter database timeseries owner to emqx;

image.png

将数据库 赋所timeseries有权限 给到emqx用户

GRANT ALL PRIVILEGES ON DATABASE timeseries to emqx;

image.png

2.根据新建数据库客户端进行登录验证:

image.png

3. 通过emqx 进行验证

i. 创建资源

image.png

ii. 数据库创建表

CREATE TABLE conditions (

    time        TIMESTAMPTZ       NOT NULL,

    location    TEXT              NOT NULL,

    temperature DOUBLE PRECISION  NULL,

    humidity    DOUBLE PRECISION  NULL

);

SELECT create_hypertable('conditions', 'time');

image.png

iii.编写规则

image.png

iv.动作响应

image.png

v.MQTT客户端发送消息

image.png

vi.登录从节点验证数据

image.png

六、启停操作

都是通过systemctl 进行启动,pgsql会由patroni进行起停

1. Etcd

· 启动:systemctl start etcd

· 停止:systemctl stop etcd

· 查看状态:systemctl status etcd

2. Patroni

· 启动:systemctl start patroni

· 停止:systemctl stop patroni

查看状态:systemctl status patroni


七、参考

https://blog.csdn.net/qihoo_tech/article/details/120212996

https://www.cnblogs.com/VicLiu/p/14333778.html


    您需要登录后才可以回复