基于Docker的PostgreSQL高可用部署（支持离线部署、读写分离）

 

部署架构

 

一、物料准备

1、Docker安装包：https://download.docker.com/linux/static/stable/x86_64/docker-29.7.2.tgz

2、Docker Compose可执行文件：https://github.com/docker/compose/releases/download/v5.4.0/docker-compose-linux-x86_64

3、etcd镜像：gcr.io/etcd-development/etcd:v3.7.1

4、Patroni+PostgreSQL镜像：ghcr.io/zalando/spilo-18:4.1-p2

更多PostgreSQL版本：https://github.com/orgs/zalando/packages?repo_name=spilo

5、HAProxy镜像：haproxy:3.4.3

6、Keepalived镜像：osixia/keepalived:2.3.4

 

二、设置环境

注意修改下方标红部分配置。

hostnamectl set-hostname postgres_ha_104

echo '192.168.162.104 postgres_ha_104' >> /etc/hosts

echo '192.168.162.105 postgres_ha_105' >> /etc/hosts

echo '192.168.162.106 postgres_ha_106' >> /etc/hosts

 

三、安装Docker、Docker Compose

1、Docker：

tar xf docker-29.7.2.tgz

mv docker/* /usr/bin/

rm -rf docker docker-29.7.2.tgz

cat > /etc/systemd/system/docker.service << EOF

[Unit]

Description=Docker Application Container Engine

Documentation=https://docs.docker.com

After=network-online.target firewalld.service containerd.service time-set.target

Wants=network-online.target containerd.service

 

[Service]

Type=notify

# the default is not to use systemd for cgroups because the delegate issues still

# exists and systemd currently does not support the cgroup feature set required

# for containers run by docker

ExecStart=/usr/bin/dockerd

ExecReload=/bin/kill -s HUP $MAINPID

TimeoutStartSec=0

RestartSec=2

Restart=always

 

# Note that StartLimit* options were moved from "Service" to "Unit" in systemd 229.

# Both the old, and new location are accepted by systemd 229 and up, so using the old location

# to make them work for either version of systemd.

StartLimitBurst=3

 

# Note that StartLimitInterval was renamed to StartLimitIntervalSec in systemd 230.

# Both the old, and new name are accepted by systemd 230 and up, so using the old name to make

# this option work for either version of systemd.

StartLimitInterval=60s

 

# Having non-zero Limit*s causes performance problems due to accounting overhead

# in the kernel. We recommend using cgroups to do container-local accounting.

LimitNPROC=infinity

LimitCORE=infinity

# Older systemd versions default to a LimitNOFILE of 1024:1024, which is insufficient for many

# applications including dockerd itself and will be inherited. Raise the hard limit, while

# preserving the soft limit for select(2).

LimitNOFILE=1024:524288

 

# Comment TasksMax if your systemd version does not support it.

# Only systemd 226 and above support this option.

TasksMax=infinity

 

# set delegate yes so that systemd does not reset the cgroups of docker containers

Delegate=yes

 

# kill only the docker process, not all processes in the cgroup

KillMode=process

OOMScoreAdjust=-500

 

[Install]

WantedBy=multi-user.target

 

EOF

设置docker存储位置

cat > /etc/docker/daemon.json << EOF

{

  "data-root": "/data/docker",

  "log-driver": "json-file",

  "log-opts": {

    "max-size": "100m",

    "max-file": "5"

  }

}

EOF

systemctl daemon-reload

systemctl start docker

systemctl enable docker

2、Docker Compose：

mv docker-compose-linux-x86_64 /usr/bin/docker-compose

chmod +x /usr/bin/docker-compose

 

四、部署etcd集群

注意修改下方标红部分配置。

mkdir -p /data/etcd

cat > /data/etcd/docker-compose.yml << EOF

services:

  etcd:

    image: gcr.io/etcd-development/etcd:v3.7.1

    container_name: etcd

    restart: unless-stopped

    network_mode: host

    environment:

      ETCD_NAME: etcd1

      ETCD_DATA_DIR: /etcd-data

      ETCD_LISTEN_CLIENT_URLS: http://0.0.0.0:2379

      ETCD_ADVERTISE_CLIENT_URLS: http://192.168.162.104:2379

      ETCD_LISTEN_PEER_URLS: http://0.0.0.0:2380

      ETCD_INITIAL_ADVERTISE_PEER_URLS: http://192.168.162.104:2380

      ETCD_INITIAL_CLUSTER: etcd1=http://192.168.162.104:2380,etcd2=http://192.168.162.105:2380,etcd3=http://192.168.162.106:2380

      ETCD_INITIAL_CLUSTER_TOKEN: etcd-cluster

      ETCD_INITIAL_CLUSTER_STATE: new

      ETCD_LOG_LEVEL: info

      ETCD_LOGGER: zap

      ETCD_LOG_OUTPUTS: stderr

    volumes:

      - /data/etcd/etcd_data:/etcd-data

 

EOF

cd /data/etcd && docker-compose up -d

 

五、部署Patroni管理的PostgreSQL集群

注意修改下方标红部分配置。

mkdir -p /data/postgres

cat > /data/postgres/docker-compose.yml << EOF

services:

  patroni:

    image: ghcr.io/zalando/spilo-18:4.1-p2

    container_name: patroni

    restart: unless-stopped

    shm_size: 2gb

    network_mode: host

    environment:

      SPILO_PROVIDER: local

      SCOPE: postgres-ha

      ETCD3_HOSTS: 192.168.162.104:2379,192.168.162.105:2379,192.168.162.106:2379

      RESTAPI_CONNECT_ADDRESS: 192.168.162.104:8008

      SPILO_CONFIGURATION: |

        postgresql:

          listen: 0.0.0.0:15432

        connect_address: 192.168.162.104:15432

        parameters:
          shared_buffers: 512MB

      PGUSER_SUPERUSER: postgres

      PGPASSWORD_SUPERUSER: postgres_pass

      PGUSER_ADMIN: admin

      PGPASSWORD_ADMIN: admin_pass

      PGUSER_STANDBY: standby

      PGPASSWORD_STANDBY: standby_pass

    volumes:

      - /data/postgres/pgdata:/home/postgres/pgdata

 

EOF

cd /data/postgres && docker-compose up -d

 

六、部署HAProxy集群

注意修改下方标红部分配置。

mkdir -p /data/haproxy

cat > /data/haproxy/haproxy.cfg << EOF

global

    log stdout format raw local0

    maxconn 10000

 

defaults

    log global

    mode tcp

    option tcplog

    timeout connect 5s

    timeout client 1h

    timeout server 1h

 

frontend postgres_write

    bind *:5432

    mode tcp

    default_backend postgres_primary

 

backend postgres_primary

    mode tcp

    option httpchk GET /primary

    http-check expect status 200

 

    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

 

    server pg1 192.168.162.104:15432 check port 8008

    server pg2 192.168.162.105:15432 check port 8008

    server pg3 192.168.162.106:15432 check port 8008

 

frontend postgres_read

    bind *:5433

    mode tcp

    default_backend postgres_replicas

 

backend postgres_replicas

    mode tcp

    option httpchk GET /replica

    http-check expect status 200

    balance roundrobin

 

    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

 

    server pg1 192.168.162.104:15432 check port 8008

    server pg2 192.168.162.105:15432 check port 8008

    server pg3 192.168.162.106:15432 check port 8008

 

EOF

cat > /data/haproxy/docker-compose.yml << EOF

services:

  haproxy:

    image: haproxy:3.4.3

    container_name: haproxy

    restart: unless-stopped

    network_mode: host

    volumes:

      - /data/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro

 

EOF

cd /data/haproxy && docker-compose up -d

 

七、部署Keepalived集群

注意修改下方标红部分配置，注意备机的keepalived.conf中state MASTER修改为state BACKUP，priority 150修改为priority 140。

mkdir -p /data/keepalived

cat > /data/keepalived/check_haproxy.sh << EOF

#!/bin/sh

 

nc -z -w 2 127.0.0.1 5432

 

EOF

# 查看网卡名称

ifconfig

cat > /data/keepalived/keepalived.conf << EOF

global_defs {

    router_id PG_HA_104

}

 

vrrp_script chk_haproxy {

    script "/usr/local/bin/check_haproxy.sh"

    interval 3

    timeout 5

    fall 3

    rise 2

    weight -50

}

 

vrrp_instance VI_PG {

    state MASTER

    interface eth0

    virtual_router_id 51

    priority 150

    advert_int 1

    unicast_src_ip 192.168.162.104

    unicast_peer {

        192.168.162.105

        192.168.162.106

}

 

    authentication {

        auth_type PASS

        auth_pass postgres

    }

    virtual_ipaddress {

        192.168.162.107/24

    }

    track_script {

        chk_haproxy

    }

}

 

EOF

cat > /data/keepalived/docker-compose.yml << EOF

services:

  keepalived:

    image: osixia/keepalived:2.3.4

    container_name: keepalived

    restart: unless-stopped

    network_mode: host

    cap_add:

      - NET_ADMIN

      - NET_BROADCAST

      - NET_RAW

    volumes:

      - /data/keepalived/keepalived.conf:/etc/keepalived/keepalived.conf:ro

      - /data/keepalived/check_haproxy.sh:/usr/local/bin/check_haproxy.sh:ro

 

EOF

cd /data/keepalived && docker-compose up -d