Happywzy

说明

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

比如有一个请求,需要先解密,返回的内容需要先加密

可以用gin.HandlerFunc统一处理

// Data是加密的,需要统一解密传给后端业务处理
type Request struct {
    AggEntityID string `json:"AggEntityId"`
    Data        string `json:"Data"`
    TimeStamp   string `json:"TimeStamp"` //yyyyMMddHHmmss
    Seq         string `json:"Seq"`       //0001
    Sig         string `json:"Sig"`       // 参数签名
}
// Data是明文的，需要统一加密返回给请求端
type Response struct {
    Ret  int32  `json:"Ret"`
    Msg  string `json:"Msg"`
    Data string `json:"Data"`
    Sig  string `json:"Sig"`
}

统一处理

import (
    "bytes"
    "encoding/json"
    "gitee.com/njderi/public-plat/internal/vpp/front-gateway/model"
    "github.com/gin-gonic/gin"
    "net/http"
)

// EncryptResponse 返回Response时先加密Data字段
func EncryptResponse() gin.HandlerFunc {
    return func(c *gin.Context) {
        // 创建一个缓冲区来捕获响应
        w := &responseWriter{body: &bytes.Buffer{}, ResponseWriter: c.Writer}
        c.Writer = w

        c.Next()

        if c.Writer.Status() != http.StatusOK {
            return
        }
        // 获取response
        var res model.Response
        if err := json.Unmarshal(w.body.Bytes(), &res); err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to read response body"})
            return
        }
        // 修改response
        res.Ret = 1
        res.Msg = "Encrypted Response"
        encryptedBody, err := json.Marshal(res)
        if err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to marshal encrypted response"})
            return
        }
        // 修改response body
        c.Writer = w.ResponseWriter
        c.Writer.Header().Set("Content-Type", "application/json")
        c.Writer.WriteHeader(http.StatusOK)
        c.Writer.Write(encryptedBody)
    }
}

type responseWriter struct {
    gin.ResponseWriter
    body *bytes.Buffer
}

func (w *responseWriter) Write(b []byte) (int, error) {
    return w.body.Write(b)
}

import (
    "bytes"
    "encoding/json"
    "gitee.com/njderi/public-plat/internal/vpp/front-gateway/model"
    "github.com/gin-gonic/gin"
    "io"
    "io/ioutil"
    "net/http"
)

// DecryptRequest 接收到Request后解密Data字段
func DecryptRequest() gin.HandlerFunc {
    return func(c *gin.Context) {
        if c.Request.Method == http.MethodPost || c.Request.Method == http.MethodPut {
            body, err := io.ReadAll(c.Request.Body)
            if err != nil {
                c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid request body"})
                c.Abort()
                return
            }

            var req model.Request
            if err := json.Unmarshal(body, &req); err != nil {
                c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid request format"})
                c.Abort()
                return
            }

            req.Data = "decrypt(req.Data, key)"

            decryptedBody, err := json.Marshal(req)
            if err != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to marshal decrypted request"})
                c.Abort()
                return
            }

            c.Request.Body = ioutil.NopCloser(bytes.NewBuffer(decryptedBody))
        }
        c.Next()
    }
}

import (
    "github.com/gin-gonic/gin"
)

func SetupAuthGroup(c *gin.Engine) {
    authGroup := c.Group("/vpp/fg/v1")
    // 这个group下所有的接口都会先解密,返回先加密
    authGroup.Use(controller.DecryptRequest())
    authGroup.Use(controller.EncryptResponse())
    {
        authGroup.POST("/query_token", controller.GetToken)
    }
}

事务隔离级别

MySQL 8.4支持的事务隔离级别包括 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

临时修改

-- 查看当前隔离级别的命令
select @@global.transaction_isolation,@@transaction_isolation;
-- 修改全局隔离级别为读提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 修改会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

永久生效

# my.cnf
transaction-isolation = READ-COMMITTED