test

PostgreSQL Testing Environment

This repo provides a testing environment of PostgreSQL cluster via vagrant, providing a minimal HA Unit consists of 3 nodes: Primary, Standby, Offline , and a battery included Monitor system with a simple CMDB.

This repo is also about illustrating some best practice on running PostgreSQL in production.

Quick start

Install

Make sure you already have Vagrant installed with a proper VM provider (VirtualBox), Then perform following steps in command line (Assume you are using Mac, or Linux environment with proper settings)

# Step 1: install dependencies: vagrant & VirtualBox
brew install vagrant
open https://www.virtualbox.org/wiki/Downloads

# Step 2: clone this repo and enter
git clone https://github.com/Vonng/pg && cd pg/test

# Step 3: setup DNS for your localhost
sudo make dns

Run

# create and launch all four nodes
make

Then you can view

• Grafana dashboard: http://monitor:3000
• Prometheus Console: http://monitor:9090

Tips

# This will download package to pkg/ , accelerating init process
# It may takes 5~20 min to finish you first time init without cache.
make download

# This will give you a whold new cluster without reinstall all things
make init

# These will shutdown and brings up your four nodes
make up
make halp

Content

Infrastructures

• DNS
• ssh
• System Utils / NTP
• consul
• node_exporter
• postgresql
• walarchiver
• postgres_exporter
• pgbouncer
• pgbouncer_exporter
• primary
• standby
• offline
• CMDB
• chronosync
• pacemaker
• keepalived
• Prometheus
• Grafana
• Alertmanager
• DNS
• HA Proxy

Moniotring

These are local links: http://monitor:3000/xxxx

• DB Overview
• DB Module
• DB Instance
• DB Instance Stats
• PG Logs
• PG Activities
• PG Settings
• PG Tables
• Grafana Provision
• Swtich to node id in PG Metrics
• Merge postgres_exporter and pgbouncer_exporter into one.

DBA/OPS Scritps

(on-going)

• deploy
• vacuum
• repack
• analyze
• report
• fencing
• failover
• pgbouncer_ctl
• rewind
• ……..

Architecture

Overview

This cluster is consist of three Database nodes: Primary, Standby, Offline

• Primary: PostgreSQL, Pgbouncer, Consul, NodeExporter, PostgresExporter
• Standby: PostgreSQL, Pgbouncer, Consul, NodeExporter, PostgresExporter
• Offline: PostgreSQL, walarchiver, Consul, NodeExporter, PostgreSQL
• Monitor: PostgreSQL, pgbouncer, Consul, NodeExporter, Grafana, Prometheus

File structure

pkg                 -> /opt/pkg   # softwares/binarys/scripts
bin                 -> /opt/bin   # init scripts
node/<node_id>      -> /opt/conf  # configuration
bin/pg              -> /pg/bin    # DBA scripts

Each node have a directory in dba/node named with their hostname (e.g primary instance have a corresponding dba/node/primary directory), That directory will maps to $HOSTNAME:/opt/conf when vagrant launch. Self defined configuration files (such as postgresql.conf, pg_bouncer.ini, postgres_exporter.yaml,etc…) will overwrite default settings.

About

Author：Vonng (fengruohang@outlook.com)