Happywzy

TimescaleDB

TimescaleDB 是基于postgres的时序数据库，安装TimescaleDB需要先安装postgres，然后将postgres转成TimescaleDB.

参考前文安装 postgres-15.

安装TimescaleDB

• 官方文档,通过yum安装.

1. 安装yum源

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. 安装TimescaleDB资源库

sudo 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

3. 更新

sudo yum update

4. YUM安装TimescaleDB

sudo yum install timescaledb-2-postgresql-15
# 同时安装postgres15
# sudo yum install timescaledb-2-postgresql-16 postgresql16

5. 初始化数据库（已经安装过了就跳过）

sudo /usr/pgsql-16/bin/postgresql-15-setup initdb

6. 将PostgreSQL调成TimescaleDB

# 如果修改过data目录可以通过 -conf-path 指定位置,否在找不到。
sudo timescaledb-tune --pg-config=/usr/pgsql-15/bin/pg_config -conf-path=/home/pgsql/data/postgresql.conf

7. 重启数据库

systemctl restart postgresql-15

8. 添加数据库拓展

# 登录
psql -U postgres -h 127.0.0.1 -p 5432

-- Add TimescaleDB to the database
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Check that TimescaleDB is installed
postgres=# \dx
                                               List of installed extensions
    Name     | Version |   Schema   |                                     Description                                      
-------------+---------+------------+--------------------------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.15.2  | public     | Enables scalable inserts and complex queries for time-series data (Apache 2 Edition)
(2 rows)

概述

Patroni + etcd + vipmanager 是cybertec推出的postgresql 高可用方案。其中， Etcd 用于存放集群状态信息。Patroni 负责为PostgreSQL 集群提供故障转移和高可用服务。vipmanager 根据etcd或Consul中保存的状态管理虚拟IP用于提供和管理虚拟ip，用于对外提供访问地址。

部署

1. vip-manager Github 文档,可以直接下载RPM包进行安装。

2. 下载

wget https://github.com/cybertec-postgresql/vip-manager/releases/download/v2.5.0/vip-manager_2.5.0_Linux_x86_64.rpm

3. 安装

rpm -ivh vip-manager_2.5.0_Linux_x86_64.rpm

rpm -qa查询所有RPM包; rpm -e 卸载 RPM 包

4. 修改默认配置: /etc/default/vip-manager.yml

# 检测间隔,单位毫秒
interval: 1000
# 和 patroni 配置保持一致：<namespace>/<scope>/leader
trigger-key: "/service/pg/leader"
# 和 patroni 配置保持一致：<name>
trigger-value: "data7"

ip: 172.16.20.99 # 虚拟ip
netmask: 24 # netmask for the virtual ip
interface: ens192 #网卡

# how the virtual ip should be managed. we currently support "ip addr add/remove" through shell commands or the Hetzner api
hosting-type: basic # possible values: basic, or hetzner.

dcs-type: etcd # etcd or consul
# etcd 地址
dcs-endpoints:
  - http://172.16.20.152:2379
  - http://172.16.20.153:2379
  - http://172.16.20.154:2379

# 本地的认证都取消了,没有配置
#etcd-user: "patroni"
#etcd-password: "Julian's secret password"
# when etcd-ca-file is specified, TLS connections to the etcd endpoints will be used.
#etcd-ca-file: "/path/to/etcd/trusted/ca/file"
# when etcd-cert-file and etcd-key-file are specified, we will authenticate at the etcd endpoints using this certificate and key.
#etcd-cert-file: "/path/to/etcd/client/cert/file"
#etcd-key-file: "/path/to/etcd/client/key/file"

# don't worry about parameter with a prefix that doesn't match the endpoint_type. You can write anything there, I won't even look at it.
#consul-token: "Julian's secret token"

# how often things should be retried and how long to wait between retries. (currently only affects arpClient)
retry-num: 2
retry-after: 250  #in milliseconds

# verbose logs (currently only supported for hetzner)
verbose: false

5. 启动/停止/自启

systemctl start vip-manager
systemctl stop vip-manager
systemctl enable vip-manager

说明

postgres数据库 yum 安装 的方式最高支持 15 版本, 后续版本官方不再提供.

安装

1. 安装yum源

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

2. 卸载历史版本

[root@data8 data]# yum list installed | grep postgre
postgresql-libs.x86_64                 9.2.24-9.el7_9                  @updates
# 卸载所有相关的
yum remove -y postgresql-libs.x86_64

3. 安装 15 版本

yum install -y postgresql15-server

4. 设置数据目录

# 新建目录
 mkdir -p /home/pgsql/data
# 分配权限
sudo chown postgres:postgres /home/pgsql/data/
# 设置数据目录
vi /usr/lib/systemd/system/postgresql-15.service
# Location of database directory
Environment=PGDATA=/home/pgsql/data/

5. 初始化数据库

postgresql-15-setup initdb

初始化完成后数据目录内就有配置文件等内容了,可以按照需要修改配置.

6. 修改pg_hba.conf

# 这里先将本地的改为trust,方便登录进去修改postgres用户的密码
host    all             all             127.0.0.1/32            trust
host    all             all             172.16.20.0/24          scram-sha-256

6. 启动/停止/自启

systemctl start postgresql-15
systemctl status postgresql-15
systemctl enable postgresql-15

7. 登录

[root@localhost ~]# psql -h 127.0.0.1 -U postgres  -p 5432
Password for user postgres: 
psql (15.7)
Type "help" for help.
# 修改密码,修改完后将pg_hba.conf改回来,提高安全
postgres=# alter user postgres password '123456';

8. 默认路径

• 默认的安装路径：/usr/pgsql-15
• 默认的数据路径：/var/lib/pgsql/15/

patroni

patroni 依赖第三方分布式存储, 可以托管 postgres 的配置管理和启停, 以及主备切换.

安装

1. 安装epel源，安装pyhton3

yum -y install epel-release
yum -y install python3 python3-devel

2. pip安装patroni

• 官方文档

yum install -y python3-psycopg2
pip3 install patroni[etcd3,psycopg2]
# 执行patroni命令无报错证明安装完成
[root@data8 data]# patroni
Config is empty.

pip3 卸载命令: pip3 uninstall xxxx

3. 生成patroni配置文件

patroni --generate-sample-config /etc/patroni/patroni.yml

• 官方配置文件详细说明

4. patroni 配置文件样例

scope: 'pg' # 集群名称,保持一致
namespace: '/service' # 保持默认
name: data7 # 不同节点设置不同的name

log:
  format: '%(asctime)s %(levelname)s: %(message)s'
  level: INFO
  max_queue_size: 1000
  traceback_level: ERROR
  type: plain

restapi:
  connect_address: 172.16.20.152:8008
  listen: 172.16.20.152:8008

# 这里使用的是etcd3
etcd3:
  hosts: 172.16.20.152:2379,172.16.20.153:2379,172.16.20.154:2379

# The bootstrap configuration. Works only when the cluster is not yet initialized.
# If the cluster is already initialized, all changes in the `bootstrap` section are ignored!
bootstrap:
  # This section will be written into <dcs>:/<namespace>/<scope>/config after initializing
  # new cluster and all other cluster members will use it as a `global configuration`.
  # WARNING! If you want to change any of the parameters that were set up
  # via `bootstrap.dcs` section, please use `patronictl edit-config`!
  dcs:
    loop_wait: 10
    retry_timeout: 10
    ttl: 30
    postgresql:
      parameters:
        hot_standby: 'on'
        max_connections: 200
        max_locks_per_transaction: 64
        max_prepared_transactions: 0
        max_replication_slots: 10
        max_wal_senders: 10
        max_worker_processes: 8
        track_commit_timestamp: 'off'
        wal_keep_size: 128MB
        wal_level: replica
        wal_log_hints: 'on'
      use_pg_rewind: true
      use_slots: true
  initdb:
  - data-checksums
  - encoding: UTF8

postgresql:
  # 这里的用户patroni会自动生成,直接写好用户密码即可.
  authentication:
    replication:
      password: 'replicator'
      username: replicator
    rewind:
      password: 'rewind_user'
      username: rewind_user
    superuser:
      password: '123456'
      username: postgres
  connect_address: 172.16.20.152:5432
  data_dir: '/home/pgsql/data/' # 指定data目录,需要设置好权限,可以是空目录表示一个新的pg集群,会自动初始化
  bin_dir: '/usr/local/pgsql/bin/' # 指定pg的安装的bin目录
  listen: 0.0.0.0:5432 # 监听0.0.0.0
  parameters:
    # centos7 直接启动时会报错：SCRAM authentication requires libpq version 10 or above。解决办法写在后面。
    password_encryption: scram-sha-256
  pg_hba:
  - host all all 0.0.0.0/0 md5
  - host replication replicator 172.16.20.0/24 md5
  #callbacks:
  #  on_reload: /etc/patroni/on_reload.sh
  #  on_restart: /etc/patroni/on_restart.sh
  #  on_role_change: /etc/patroni/on_role_change.sh
  #  on_start: /etc/patroni/on_start.sh
  #  on_stop: /etc/patroni/on_stop.sh

tags:
  clonefrom: true
  failover_priority: 1
  noloadbalance: false
  nostream: false
  nosync: false

脚本示例:

#!/bin/bash
curl -X GET http://172.16.20.35:8085/ping/152/on_start

注意#!/bin/bash解析器一定要写，不然patroni执行报错.

5. 注册 patroni service
   创建文件/usr/lib/systemd/system/patroni.service

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=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=process
TimeoutSec=30
Restart=no
 
[Install]
WantedBy=multi-user.target

6. 启动patroni

# 启动
systemctl start patroni
# 检查运行状态,并查看pg data目录是否有数据了.
systemctl status patroni

7. 查询patroni节点状态

[root@data7 data]# patronictl -c /etc/patroni/patroni.yml list
+ Cluster: pg (7384360501716690603) -----------+----+-----------+----------------------+
| Member | Host          | Role    | State     | TL | Lag in MB | Tags                 |
+--------+---------------+---------+-----------+----+-----------+----------------------+
| data7  | 172.16.20.152 | Leader  | running   |  1 |           | clonefrom: true      |
|        |               |         |           |    |           | failover_priority: 1 |
+--------+---------------+---------+-----------+----+-----------+----------------------+
| data8  | 172.16.20.153 | Replica | streaming |  1 |         0 | clonefrom: true      |
|        |               |         |           |    |           | failover_priority: 1 |
+--------+---------------+---------+-----------+----+-----------+----------------------+
| data9  | 172.16.20.154 | Replica | streaming |  1 |         0 | clonefrom: true      |
|        |               |         |           |    |           | failover_priority: 1 |
+--------+---------------+---------+-----------+----+-----------+----------------------+

错误

1. SCRAM authentication requires libpq version 10 or above
   这个错误原因是 centos7 默认安装的 postgresql-libs 版本太低,需要升级.

[root@data8 data]# yum list installed | grep postgre
postgresql-libs.x86_64                 9.2.24-9.el7_9                  @updates

• 卸载现有的lib

yum remove -y postgresql-libs.x86_64

• 安装pg yum 源

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

• 安装最新的,注意: centos最高只支持 15 版本

yum install postgresql15-libs -y

• 确认

[root@data8 data]# yum list installed | grep postgre
postgresql15-libs.x86_64               15.7-1PGDG.rhel7                @pgdg15 

• 注意,重新安装lib后patroni运行会报错

 FATAL: Patroni requires psycopg2>=2.5.4, psycopg2-binary, or psycopg>=3.0.0

需要 重新安装

yum install -y python3-psycopg2 

安装

1. 下载源码

wget https://ftp.postgresql.org/pub/source/v16.3/postgresql-16.3.tar.gz --no-check-certificate

2. 安装依赖

yum install gcc gcc-c++ readline-devel readline readline-dev zlib-devel

3. 解压编译

# 解压
tar -zxvf postgresql-16.3.tar.gz
# 配置,默认安装路径 --prefix=/usr/local/pgsql
./configure --without-icu
# 编译
make
make install

4. 添加数据目录

adduser postgres
mkdir -p /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
# 初始化
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
# 启动
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

5. 测试

# 连接
/usr/local/pgsql/bin/psql -h 127.0.0.1 -U postgres -p 5432

6. 配置环境变量

vim /etc/profile

#PG安装目录
export PGHOME=/usr/local/pgsql
export PATH=$PGHOME/bin:$PATH

source /etc/profile

配置

默认只能 localhost/127.0.0.1 才能连接数据库,需要修改以下配置,修改完重启生效.

1. /usr/local/pgsql/data/postgresql.conf

# 修改监听的地址,默认localhost
listen_addresses = '*'
# 修改端口,默认5432
port = 5432
# 修改最大连接数,默认100
max_connections = 200

2. /usr/local/pgsql/data/pg_hba.conf

# 通过本地登录完全信任,无需密码
host    all             all             127.0.0.1/32            trust
# 设置部分网段可以连接数据库
host    all             all             172.16.20.0/24          md5
# 设置所有IP都可以连接数据库
# host    all             all             0.0.0.0/0               md5

3. 修改默认用户的密码

psql -h 127.0.0.1 -U postgres -p 5432

 alter user postgres password '123456';

说明

在三台机器上搭建一个集群

部署

• 安装etcd

yum -y install etcd

• 修改默认配置: /etc/etcd/etcd.conf

ETCD_DATA_DIR="/home/etcd/node152.etcd"
ETCD_LISTEN_PEER_URLS="http://172.16.20.152:2380"
ETCD_LISTEN_CLIENT_URLS="http://172.16.20.152:2379,http://localhost:2379"
ETCD_NAME="node152"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.16.20.152:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://172.16.20.152:2379"
ETCD_INITIAL_CLUSTER="node152=http://172.16.20.152:2380,node153=http://172.16.20.153:2380,node154=http://172.16.20.154:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"

注意：数据目录etcd用户有权限创建,或者提前创建好并分配权限;
其他节点根据IP修改.

• 修改服务配置: /usr/lib/systemd/system/etcd.service

[Unit]
Description=Etcd Server
After=network.target
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
WorkingDirectory=/var/lib/etcd/
EnvironmentFile=-/etc/etcd/etcd.conf
User=etcd
# set GOMAXPROCS to number of processors
ExecStart=/bin/bash -c "GOMAXPROCS=$(nproc) /usr/bin/etcd --name=\"${ETCD_NAME}\" --data-dir=\"${ETCD_DATA_DIR}\" --listen-client-urls=\"${ETCD_LISTEN_CLIENT_URLS}\" \
--listen-peer-urls=\"${ETCD_LISTEN_PEER_URLS}\" \
--initial-advertise-peer-urls=\"${ETCD_INITIAL_ADVERTISE_PEER_URLS}\" \
--advertise-client-urls=\"${ETCD_ADVERTISE_CLIENT_URLS}\" \
--initial-cluster=\"${ETCD_INITIAL_CLUSTER}\"  \
--initial-cluster-token=\"${ETCD_INITIAL_CLUSTER_TOKEN}\" \
--initial-cluster-state=\"${ETCD_INITIAL_CLUSTER_STATE}\""

Restart=on-failure
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target

• 启动并设置开机启动

systemctl start etcd
systemctl enable etcd

• 检查集群状态

[root@data7 home]# etcdctl cluster-health
member 1031e29bb8572c51 is healthy: got healthy result from http://172.16.20.152:2379
member 6937e3968a3fec18 is healthy: got healthy result from http://172.16.20.154:2379
member 8b79f77c6775414b is healthy: got healthy result from http://172.16.20.153:2379
cluster is healthy

[root@data7 home]# etcdctl member list
1031e29bb8572c51: name=node152 peerURLs=http://172.16.20.152:2380 clientURLs=http://172.16.20.152:2379 isLeader=true
6937e3968a3fec18: name=node154 peerURLs=http://172.16.20.154:2380 clientURLs=http://172.16.20.154:2379 isLeader=false
8b79f77c6775414b: name=node153 peerURLs=http://172.16.20.153:2380 clientURLs=http://172.16.20.153:2379 isLeader=false

常见问题

1. 通过v3版本写入，etcdctl 命令查不到.
   ETCD v2 v3版本数据不互通,需要指定版本：export ETCDCTL_API=3