PostgreSQL

The most advanced open-source relational database in the world!

With battery-included observability, reliability, and maintainability powered by Pigsty

Concept

Overview of PostgreSQL in Pigsty

• Architecture
• Configuration
• Extensions
• Databases
• Users
• Services
• HBA Rules
• Access Control
• Administration
• Backup & PITR
• Monitor
• Migration

---

Configuration

Describe the cluster you want

• Identity: Parameters used for describing a PostgreSQL cluster
• Primary: Define a single instance cluster
• Replica: Define a basic HA cluster with one primary & one replica
• Offline: Define a dedicated instance for OLAP/ETL/Interactive queries.
• Sync Standby: Enable synchronous commit to ensure no data loss
• Quorum Commit: Use quorum sync commit for an even higher consistency level
• Standby Cluster: Clone an existing cluster and follow it
• Delayed Cluster: Clone an existing cluster for emergency data recovery
• Citus Cluster: Define a Citus distributed database cluster
• Major Version: Define a PostgreSQL cluster with specific major version

---

Administration

Admin your existing clusters

• Admin Cheatsheet
• Create Cluster
• Create User
• Create Database
• Reload Service
• Reload HBARule
• Config Cluster
• Append Replica
• Remove Replica
• Remove Cluster
• Switchover Cluster
• Backup Cluster
• Restore Cluster

---

Playbook

Materialize the cluster with idempotent playbooks

• pgsql.yml : Init HA PostgreSQL clusters or add new replicas.
• pgsql-rm.yml : Remove PostgreSQL cluster, or remove replicas
• pgsql-user.yml : Add new business user to existing PostgreSQL cluster
• pgsql-db.yml : Add new business database to existing PostgreSQL cluster
• pgsql-monitor.yml : Monitor remote PostgreSQL instance with local exporters
• pgsql-migration.yml : Generate Migration manual & scripts for existing PostgreSQL

---

Dashboard

There are 26 default grafana dashboards about PostgreSQL and categorized into 4 levels. Check Dashboards for details.

Tutorials

• Fork an existing PostgreSQL cluster.
• Create a standby cluster of an existing PostgreSQL cluster.
• Create a delayed cluster of another pgsql cluster?
• Monitoring an existing postgres instance?
• Migration from an external PostgreSQL with logical replication?
• Use MinIO as a central pgBackRest repo.
• Use dedicate etcd cluster for DCS?
• Use dedicated haproxy for exposing PostgreSQL service.
• Deploy a multi-node MinIO cluster?
• Use CMDB instead of Config as inventory.
• Use PostgreSQL as grafana backend storage ?
• Use PostgreSQL as prometheus backend storage ?