Happywzy

安装

• 官方文档

# 安装 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

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/