PIGSTY: Postgres Installation Good Standard Template.Yaml 😂

This project provides a PostgreSQL testing environment based on vagrant.

It is a template for setup, deploying, monitoring, managing a high available PostgreSQL cluster.

Minimal PG HA cluster consist of 3 DB nodes managed by patroni.

A battery included control node including a series of ansible playbooks and a powerful monitoring system.

1. Install vagrant and virtualbox
2. git clone https://github.com/vonng/pigsty && cd pigsty
3. make dns (one-time job, write DNS records to /etc/hosts)
4. ./build.sh (pull up vm cluster, setup control node and init postgres cluster)
5. Open http:https://pigsty to explore (pigsty is a local dns record created in step 3 )

Then you will have a running PostgreSQL cluster with a battery included monitoring/managing system.

Tips: bootstrap pigsty may takes a long time to download yum packages. Once you have a functional pigsty instance. Run make cache to copy rpm packages to pigsty/control/yum, so next time it would be much faster (15min to 1min)

6. Do whatever you like to explore Postgres

# for example, pgbench default database
pgbench postgres:https://dbuser_test:[email protected]:5432/testdb -i -s 10

# bench primary service
pgbench postgres:https://dbuser_test:[email protected]:6432/testdb -n -v -T 100

# bench standby service
pgbench postgres:https://dbuser_test:[email protected]:6432/testdb --select-only -n -v -T 100

This cluster contains 4 nodes running following services:

Control Node Services

• Nginx (10.10.10.10:80)：Local yum server, and proxy for grafana, prometheus, alertmanager, consul, etc...

• Yum (10.10.10.10:80): http:https://yum.pigsty/pigsty/

• Consul (10.10.10.10:8500): http:https://c.pigsty/

• Consul DNS Service (10.10.10.10:8600)

• Grafana (10.10.10.10:3000): http:https://g.pigsty/

• Prometheus (10.10.10.10:9090): http:https://p.pigsty/

• AlertManager (10.10.10.10:9093): http:https://am.pigsty/

• DNSMASQ (10.10.10.10:53)

DB Node Services

• consul (:8500) agent mode
• postgres (:5432) major service
• pgbouncer (:6432) connection pool
• patroni (:8008) HA agent
• node_exporter (:9100) prometheus machine metrics collector
• pg_exporter (:9630) prometheus postgres metrics collector
• pgbouncer_exporter (:9630) prometheus pgbouncer metrics collector

Domain Names

Default database cluster is named 'testdb'

testdb is a virtual service registered to consul, which have optional tags: primary, standby ,1,2,3. You can discover these services via common domain names.

• TestDB primary service domain name: primary.testdb.service.consul
• TestDB standby service domain name: standby.testdb.service.consul

DSN

Default database name: testdb, default business user is dbuser_test and password is dbuser_test too.

You can use DNS service discovery to connect to real server:

# connect to primary
psql postgres:https://dbuser_test:[email protected]:5432/testdb

# conntect to standby (random choose one)
psql postgres:https://dbuser_test:[email protected]:5432/testdb

Or just go through L4 proxy haproxy on control node via VIP testdb(10.10.10.2-> 10.10.10.10)

# haproxy primary service (vip=testdb/10.10.10.2 port=5556)
postgres:https://dbuser_test:dbuser_test@testdb:5555/testdb
pgbench -nv -P1 -c4 -T1000 postgres:https://dbuser_test:dbuser_test@testdb:5555/testdb

# haproxy standby service (vip=testdb/10.10.10.2 port=5556)
postgres:https://dbuser_test:dbuser_test@testdb:5556/testdb

# init testdb pgbench
pgbench -is10 postgres:https://dbuser_test:dbuser_test@testdb:5555/testdb

# load on primary
while true; do pgbench -nv -P1 -c2 -T10 postgres:https://dbuser_test:dbuser_test@testdb:5555/testdb; done

# load on standby
while true; do pgbench -nv -P1 -c4 -T10 --select-only postgres:https://dbuser_test:dbuser_test@testdb:5556/testdb; done

• Explore the monitoring system
• How service discovery works
• Add some load to cluster
• Managing postgres cluster with ansible
• Patroni HA Drill

Grafana Default Credential

• username : admin
• password : admin

PgAdmin4 Require manaul bootstrap before launching

# do this on control node to setup pgadmin
sudo /usr/bin/python2 /usr/lib/python2.7/site-packages/pgadmin4-web/pgAdmin4.py
sudo systemctl restart pgadmin4

Author：Vonng ([email protected])

LICENSE: CC BY-NC 4.0