部署

置备高可用PostgreSQL数据库集群!

单集群部署

在单个PostgreSQL数据库集群中的几种不同角色: primary, replica, offline, standby

  • primary:定义一个单实例的PostgreSQL集群主库,添加业务用户与数据库
  • replica:添加标准PostgreSQL物理复制从库,调整流量,并为集群添加2层VIP
  • offline:用于单独承载OLAP分析,ETL,交互式个人查询的专用离线实例,与服务接入
  • standby:定义同步复制的从库,配置法定人数提交的高一致性从库,添加额外的特殊服务
完整的三节点高可用集群
    #----------------------------------#
    # pgsql cluster: pg-test (3 nodes) #
    #----------------------------------#
    # pg-test --->  10.10.10.3 ---> 10.10.10.1{1,2,3}
    pg-test:                                # define the new 3-node cluster pg-test
      hosts:
        10.10.10.11: { pg_seq: 1, pg_role: primary }   # primary instance, leader of cluster
        10.10.10.12: { pg_seq: 2, pg_role: replica }   # replica instance, follower of leader
        10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
      vars:
        pg_cluster: pg-test           # define pgsql cluster name
        pg_version: 14                # test postgresql 13 with pg-test cluster
        pg_users:
          - name: test
            password: test
            pgbouncer: true
            roles: [ dbrole_admin ]
        pg_databases:                 # create a database and user named 'test'
          - name: test

        vip_mode: l2                  # enable/disable vip (require members in same LAN)
        vip_address: 10.10.10.3       # virtual ip address for this cluster
        vip_cidrmask: 8               # cidr network mask length
        vip_interface: eth1           # interface to add virtual ip

        pg_services_extra:            # extra services in addition to pg_services_default, array of service definition
          # standby service will route {ip|name}:5435 to sync replica's pgbouncer (5435->6432 standby)
          - name: standby             # required, service name, the actual svc name will be prefixed with `pg_cluster`, e.g: pg-meta-standby
            src_ip: "*"               # required, service bind ip address, `*` for all ip, `vip` for cluster `vip_address`
            src_port: 5435            # required, service exposed port (work as kubernetes service node port mode)
            dst_port: pgbouncer       # optional, destination port, postgres|pgbouncer|<port_number>   , pgbouncer(6432) by default
            check_method: http        # optional, health check method: http is the only available method for now
            check_port: patroni       # optional, health check port: patroni|pg_exporter|<port_number> , patroni(8008) by default
            check_url: /sync          # optional, health check url path, /read-only?lag=0 by default
            check_code: 200           # optional, health check expected http code, 200 by default
            selector: "[]"            # required, JMESPath to filter inventory ()
            selector_backup: "[? pg_role == `primary`]"  # primary used as backup server for standby service (will not work because /sync for )
            haproxy:                  # optional, adhoc parameters for haproxy service provider (vip_l4 is another service provider)
              maxconn: 3000           # optional, max allowed front-end connection
              balance: roundrobin     # optional, haproxy load balance algorithm (roundrobin by default, other: leastconn)
              default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

多集群部署

多个PostgreSQL集群共同提供服务。

  • 备份集群:制作现有集群的实时在线克隆,用于异地灾备。
  • 延迟集群:用于应对误删表删库等软件/人为故障,快速恢复数据。
  • Citus集群部署:部署Citus分布式数据库集群,1协调者与3数据节点
  • MatrixDB集群部署:部署Greenplum7/PostgreSQL12兼容的时序数据仓库。

PGSQL Primary 主库部署

本文介绍PostgreSQL单例主库的部署方式,以及如何在主库是那个部署

PGSQL Replica 从库部署

本文介绍如何为PostgreSQL集群添加只读从库副本,配置L2 VIP接入,以及流量分发。

PGSQL Offline 离线从库部署

离线从库 —— 一种不承载线上业务流量,用于ETL,只读分析查询的专用实例。

PGSQL Standby 同步从库部署

同步从库 —— 与主库保持同步提交,零复制延迟的热备从库,启用额外的服务,以及法定人数同步提交。

PGSQL Standby Cluster 同步集群

同步集群 —— 整个集群作为另一个集群的物理备份集群,可用于灾备Switchover

PGSQL Delayed Cluster 延时集群

本文介绍PostgreSQL延时从库集群 —— 专门用于应对删库删表问题的特殊从库

Citus集群部署

Pigsty可用于部署加装分布式数据库扩展插件Citus的分布式PostgreSQL集群

MatrixDB部署

Pigsty可用于部署与监控MatrixDB(等于Greenplum 7+时序数据库)

PGSQL目录结构

PostgreSQL所使用的目录层次结构


最后修改 2022-06-05: add pgsql/deploy document (34a3325)