Happywzy

概述

因为 postgres 16 只能通过源码的方式在 centos 上安装，所以 timescaledb 也只能通过源码的方式安装.

安装

• 官方文档
  前提条件：
• 已经安装 postgresql
• CMake 版本大于 3.11 (Cmake安装参考前文)
• gcc

# 下载源码
git clone https://github.com/timescale/timescaledb
cd timescaledb
git checkout 2.15.3

# 配置，如果不使用openssl，请添加参数： ./bootstrap -DUSE_OPENSSL=0
./bootstrap

cd build && make

make install

安装完成后配置

• 如果是单机 postgrsql

sudo timescaledb-tune --pg-config=/usr/local/pgsql/bin/pg_config -conf-path=/home/pgsql/data/postgresql.conf

systemctl restart postgresql-16

• 如果是patroni，直接修改postgresql.conf会被patroni重置。需要修改patroni.yml

# 将 timescaledb-tune 修改的配置文件通过 patroni 写到 postgres
postgresql:
  parameters:
    shared_preload_libraries: 'timescaledb'
    shared_buffers: 1955MB
    effective_cache_size: 5865MB
    maintenance_work_mem: 1001095kB
    work_mem: 5005kB
    timescaledb.max_background_workers: 16
    max_parallel_workers_per_gather: 2
    max_parallel_workers: 4
    wal_buffers: 16MB
    min_wal_size: 512MB
    max_wal_size: 1GB
    default_statistics_target: 100
    random_page_cost: 1.1
    checkpoint_completion_target: 0.9
    autovacuum_max_workers: 10
    autovacuum_naptime: 10
    effective_io_concurrency: 256

cmake安装

# 到官网 https://cmake.org/download/ 下载最新的包
wget https://github.com/Kitware/CMake/releases/download/v3.30.0/cmake-3.30.0-linux-x86_64.tar.gz

# 解压
tar -zxvf cmake-3.30.0-linux-x86_64.tar.gz

# 改个名字并移动到/usr/local目录下
mv cmake-3.30.0-linux-x86_64 cmake
mv cmake /usr/local/

# 添加bin到环境变量
export PATH=/usr/local/cmake/bin:$PATH

# 测试
cmake --version

安装

• 官方文档

# Add the TimescaleDB package
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
# Install the TimescaleDB GPG key
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
# Update your local repository list
sudo apt update
# Install TimescaleDB
sudo apt install timescaledb-2-postgresql-16 postgresql-client-16

转换

# Tune your PostgreSQL instance for TimescaleDB
sudo timescaledb-tune
# Restart PostgreSQL
sudo systemctl restart postgresql

添加拓展

重启pg后，登录到控制台：

-- 添加
CREATE EXTENSION IF NOT EXISTS timescaledb;
-- \dx 命令检查安装的拓展
postgres=# \dx
                                                List of installed extensions
    Name     | Version |   Schema   |                                      Description                                      
-------------+---------+------------+---------------------------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.15.3  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)

修改的配置详细过程

postgres@deri:~$ sudo timescaledb-tune
Using postgresql.conf at this path:
/etc/postgresql/16/main/postgresql.conf

Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup202407030214

shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
Recommendations based on 7.76 GB of available memory and 4 CPUs for PostgreSQL 16

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 1985MB
effective_cache_size = 5956MB
maintenance_work_mem = 1016498kB
work_mem = 5082kB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: memory settings will be updated

Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8
Recommended:
timescaledb.max_background_workers = 16
max_worker_processes = 23
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated

WAL settings recommendations
Current:
#wal_buffers = -1
min_wal_size = 80MB
Recommended:
wal_buffers = 16MB
min_wal_size = 512MB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated

Background writer settings recommendations
Current:
Recommended:
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: background writer settings will be updated

Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.9
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 100
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 128
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 256
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /etc/postgresql/16/main/postgresql.conf

安装

• 官方文档

# 安装 16 版本
sudo apt -y install postgresql-16
# 启动
systemctl start postgresql

默认目录

切换到 postgres 用户： sudo su postgres.

• 配置文件目录： /etc/postgresql/16/main/
• 数据目录：/var/lib/postgresql/16/main/
• 可执行文件：/usr/lib/postgresql/16/bin/
• 日志目录：/var/log/postgresql/

可以通过 pg_config 命令查看 PostgreSQL 的具体安装路径和配置信息.

网络配置

• sudo vim /etc/netplan/xxx.yaml

network:
    version: 2
    ethernets:
        ens160: # 网卡
            dhcp4: no
            addresses:
              - 172.16.0.176/24
            routes: # 配置网关
              - to: default
                via: 172.16.0.1
            nameservers:
              addresses:
                - 223.5.5.5
                - 223.6.6.6

• 应用：sudo netplan apply

关闭防火墙

# 检查防火墙状态
sudo ufw status
# 关闭防火墙
sudo ufw disable
# 开通防火墙
sudo ufw allow 5432/tcp

包管理

# 更新软件包列表
sudo apt update
# 升级已安装的软件包
sudo apt upgrade
# 安装新软件包
sudo apt install <package_name>
# 删除软件包
sudo apt remove <package_name>
# 清理不再需要的包
sudo apt autoremove
# 查看包的信息
apt show <package_name>

apt-get用法与apt基本一致，apt 结合了 apt-get 和 apt-cache 的一些常用功能，使其更加简洁和高效，如果你在编写脚本，使用 apt-get 可能会更好.

# 列出已安装的软件包
apt list --installed

安装docker

sudo apt update
# 安装必要的包
sudo apt install apt-transport-https ca-certificates curl software-properties-common
# 添加 Docker 的官方 GPG 密钥
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
# 添加 Docker 仓库到 APT 源
echo "deb [arch=amd64 signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt update
# 安装 Docker
sudo apt install docker-ce
# 启动
sudo systemctl start docker
# 开机启动
sudo systemctl enable docker

# 将当前用户添加到 Docker 组,这样就不用加sudo了
sudo usermod -aG docker ${USER}
# 立即生效
newgrp docker
# 验证是否成功
groups $USER

• 设置docker数据目录及日志大小

{
"data-root": "/data/docker",
"registry-mirrors": [
    "https://7x3mic2t.mirror.aliyuncs.com"
  ],
"log-opts": {
    "max-size": "100m",
    "max-file":"1"
  }
}

sudo systemctl daemon-reload
sudo systemctl restart docker

普通用户添加sudo

# 将用户添加到 sudo 组
sudo usermod -aG sudo username
# 验证用户是否已被添加到 sudo 组
groups username
# 测试 sudo 权限
sudo apt update

概述

基于patroni搭建了三台服务器的postgres集群，本文通过haproxy实现postgres的动态负载。

3台机器都需要安装haproxy，三个haproxy上面可以由负载均衡器或者其他工具提供服务。

安装

1. haproxy的安装

# 基于CentOS7系统，通过yum只能安装haproxy-1.5或haproxy-1.8，这两个版本都已EOL
yum install haproxy # haproxy-1.5
vim /etc/haproxy/haproxy.cfg

yum install haproxy18 # haproxy-1.8，需要具有epel-release软件包
vim /etc/haproxy18/haproxy.cfg

# 源码安装方式：省略

2. /etc/haproxy18/haproxy.conf

defaults     
    mode tcp
    log global
    option tcplog
    option dontlognull
    retries 3 
    option redispatch
    timeout queue 1m
    timeout client 30m # 1h
    timeout connect 4s # 10s
    timeout server 30m # 1h
    timeout check 5s
    #option clitcpka # haproxy与client端的keepalive配置
    #option srvtcpka # haproxy与server端的keepalive配置
    option tcpka # haproxy与双方都建立长连接
    #clitcpka-cnt 3
    #clitcpka-idle 180
    #clitcpka-intvl 10

listen primary
    bind 0.0.0.0:15432
    mode tcp
    balance roundrobin
    option httpchk HEAD /primary
    http-check expect status 200
    default-server inter 3s fall 2 rise 1 on-marked-down shutdown-sessions
    server DB01 192.168.86.101:5432 maxconn 2000 check port 8008
    server DB02 192.168.86.102:5432 maxconn 2000 check port 8008
    server DB03 192.168.86.103:5432 maxconn 2000 check port 8008
    # acl is_lb_healthcheck src 192.168.0.10 # LB探活机制的源IP
    # tcp-request connection reject if is_lb_healthcheck # 阻止探活转发到后端PG，避免造成PG频繁fork

listen replicas
    bind 0.0.0.0:15433
    mode tcp
    balance roundrobin
    option httpchk HEAD /replica
    # option tcpka
    http-check expect status 200
    default-server inter 3s fall 2 rise 1 on-marked-down shutdown-sessions
    server DB01 192.168.86.101:5432 maxconn 2000 check port 8008
    server DB02 192.168.86.102:5432 maxconn 2000 check port 8008
    server DB03 192.168.86.103:5432 maxconn 2000 check port 8008
    # acl is_lb_healthcheck src 192.168.0.10 # LB探活机制的源IP
    # tcp-request connection reject if is_lb_healthcheck # 阻止探活转发到后端PG，避免造成PG频繁fork

listen monitor
    bind 0.0.0.0:8888
    mode http
    option httplog
    stats enable
    stats uri /haproxy_stats
    stats realm Haproxy\ Statistics
    # stats auth admin:123456 # haproxy web管理用户名密码，自行设置
    stats refresh 30s
    stats hide-version

通过15432始终与主节点建立连接，通过15433始终与备节点建立连接，主节点可读可写，备节点只能读，实现读写分离。检测8008端口由patroni提供。

3. 启动

systemctl start haproxy18.service