Skip to main content

Deploy HA PostgreSQL Cluster

Pigsty​

We recommend using automation tools such as Pigsty to deploy a production PostgreSQL cluster with HA.

Pigsty Introduction : https://doc.pigsty.cc/

Download​

curl -L https://get.pigsty.cc/v2.5.1/pigsty-v2.5.1.tgz -o ~/pigsty.tgz 
curl -L https://get.pigsty.cc/v2.5.1/pigsty-pkg-v2.5.1.el7.x86_64.tgz -o /tmp/pkg.tgz

Install and Deploy​

1、set up password-less login for all nodes including self

ssh-keygen -t rsa
ssh-copy-id root@hosts

2、initialize in all nodes

bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"  
cd ~/pigsty
./bootstrap

3、modify ~/pigsty/pigsty.yml in the primary node as below

all:
  children:
    # infra cluster for proxy, monitor, alert, etc..
    infra: { hosts: { 192.168.17.91: { infra_seq: 1 } } }
    # etcd cluster for ha postgres
    etcd: { hosts: { 192.168.17.91: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
    # minio cluster, optional backup repo for pgbackrest
    #minio: { hosts: { 192.168.17.91: { minio_seq: 1 } }, vars: { minio_cluster: minio } }
    # postgres cluster 'pg-meta' with single primary instance
    pg-test:
      hosts:
        192.168.17.91: { pg_seq: 1, pg_role: primary }   # primary instance, leader of cluster
        192.168.25.215: { pg_seq: 2, pg_role: replica }   # replica instance, follower of leader
        192.168.28.137: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
      vars:
        pg_cluster: pg-test           # define pgsql cluster name
        pg_users:  [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
        pg_databases: [{ name: test }]
        pg_vip_enabled: true
        pg_vip_address: 192.168.17.92/24
        pg_vip_interface: eth0
        node_tune: tiny
        pg_conf: tiny.yml
        node_crontab:  # make a full backup on monday 1am, and an incremental backup during weekdays
          - '00 01 * * 1 postgres /pg/bin/pg-backup full'
          - '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'
  vars:                               # global parameters
    version: v2.5.1                   # pigsty version string
    admin_ip: 192.168.17.91             # admin node ip address
    region: china                     # upstream mirror region: default,china,europe
tip

pg_vip_address is needed

4、install

./install.yml

Check​

patronictl -c /etc/patroni/patroni.yml list
tip

Switch Command：patronictl -c /etc/patroni/patroni.yml switchover

Data Backup Command：/pg/bin/pg-backup full; Full and incremental backup tasks are configured in /etc/crontab

Monitor​

Grafana

http://192.168.17.91:3000
tip

use your ip address