Happywzy

安装

• 官方文档

# 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
    timeout connect 4s
    timeout server 30m
    timeout check 5s
    #option clitcpka # haproxy与client端的keepalive配置
    #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 3 rise 2 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 3 rise 2 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

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