PGSQL Primary 主库部署
本文介绍PostgreSQL单例主库的部署方式,以及如何在主库是那个部署
在单个PostgreSQL数据库集群中的几种不同角色: primary
, replica
, offline
, 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集群共同提供服务。
本文介绍PostgreSQL单例主库的部署方式,以及如何在主库是那个部署
本文介绍如何为PostgreSQL集群添加只读从库副本,配置L2 VIP接入,以及流量分发。
离线从库 —— 一种不承载线上业务流量,用于ETL,只读分析查询的专用实例。
同步从库 —— 与主库保持同步提交,零复制延迟的热备从库,启用额外的服务,以及法定人数同步提交。
同步集群 —— 整个集群作为另一个集群的物理备份集群,可用于灾备Switchover
本文介绍PostgreSQL延时从库集群 —— 专门用于应对删库删表问题的特殊从库
Pigsty可用于部署加装分布式数据库扩展插件Citus的分布式PostgreSQL集群
Pigsty可用于部署与监控MatrixDB(等于Greenplum 7+时序数据库)
PostgreSQL所使用的目录层次结构
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.