Module: PGSQL

Grafana uses SQLite for its metadata by default. Pigsty v2.7 can provision a PostgreSQL user and database for Grafana, after which Grafana can be pointed at that database manually. v2.7 scope The old grafana_database and grafana_pgurl Pigsty …

ABORT due to postgres exists Set pg_clean = true and pg_safeguard = false to force clean postgres data during pgsql.yml This happens when you run pgsql.yml on a node with postgres running, and pg_clean is set to false. If pg_clean is true (and the …

Pigsty has a battery-included access control model based on Role System and Privileges. Role System Pigsty has a default role system consist of four default roles and four default users： Role name Attributes Member of Description dbrole_readonly …

PGSQL module has 638 available metrics Metric Name Type Labels Description ALERTS Unknown category, job, level, ins, severity, ip, alertname, alertstate, instance, cls N/A ALERTS_FOR_STATE Unknown category, job, level, ins, severity, ip, alertname, …

Personal User Service is meaningless to personal users. You can access the database with raw IP address or whatever method you like. psql postgres://dbuser_dba:[email protected]/meta # dbsu direct connect psql …

Grafana Dashboards for PostgreSQL clusters: Demo & Gallery. There are 26 default grafana dashboards about PostgreSQL and categorized into 4 levels. and categorized into PGSQL, PGCAT & PGLOG by datasource. Overview Cluster Instance Database PGSQL …

Overview You can roll back your cluster to any point in time, avoiding data loss caused by software defects and human errors. Pigsty’s PostgreSQL clusters come with an automatically configured Point in Time Recovery (PITR) solution, based on the …

Overview Pigsty uses the modern observability stack for PostgreSQL monitoring: Grafana for metrics visualization and PostgreSQL datasource. Prometheus for PostgreSQL / Pgbouncer / Patroni / HAProxy / Node metrics Loki for PostgreSQL / Pgbouncer / …

Pigsty has a built-in playbook pgsql-migration.yml to perform online database migration based on logical replication. With proper automation, the downtime could be minimized to several seconds. But beware that logical replication requires PostgreSQL …

Pigsty uses pgBackRest for PITR backup & restore. In the case of a hardware failure, a physical replica failover could be the best choice. Whereas for data corruption scenarios (whether machine or human errors), Point-in-Time Recovery (PITR) is …

Pigsty has a battery-included access control model based on Role System and Privileges. Role System Pigsty has a default role system consist of four default roles and four default users： Role name Attributes Member of Description dbrole_readonly …

You can define an OPTIONAL L2 VIP on a PostgreSQL cluster, provided that all nodes in the cluster are in the same L2 network. This VIP works on Master-Backup mode and always points to the node where the primary instance of the database cluster is …

Overview Primary Failure RTO ≈ 30s, RPO < 1MB, Replica Failure RTO≈0 (reset current conn) Pigsty’s PostgreSQL cluster has battery-included high-availability powered by Patroni, Etcd, and HAProxy When your have two or more instances in the PostgreSQL …

How to maintain an existing PostgreSQL cluster with Pigsty? Here are some SOP for common pgsql admin tasks Case 1: Create Cluster Case 2: Create User Case 3: Create Database Case 4: Reload Service Case 5: Reload HBARule Case 6: Config Cluster Case …

Pigsty has a series of playbooks for PostgreSQL: 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 …

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 …

Pigsty has rich support for PostgreSQL extensions, including 230 RPM extensions and 189 DEB extensions. There are 255 unique extensions in total (rpm + deb + contrib), including 73 common contrib extensions and 91 extensions both available to rpm/deb …

Pigsty v2.7 normally reads its Ansible inventory from pigsty.yml. The release also ships three scripts that can load that inventory into the pigsty schema of the meta database and switch Ansible to a generated dynamic inventory. The implementation in …

You can define different types of instances & clusters. 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 …

Host-Based Authentication in Pigsty PostgreSQL has various authentication methods. You can use all of them, while pigsty’s battery-include ACL system focuses on HBA, password, and SSL authentication. Client Authentication To connect to a PostgreSQL …

Extensions are the soul of PostgreSQL, and Pigsty deeply integrates the core extension plugins of the PostgreSQL ecosystem, providing you with battery-included distributed temporal, geospatial text, graph, and vector database capabilities! Check …

Split read & write, route traffic to the right place, and achieve stable & reliable access to the PostgreSQL cluster. Service is an abstraction to seal the details of the underlying cluster, especially during cluster failover/switchover. Personal …

In this context, Database refers to the object created by SQL CREATE DATABASE. A PostgreSQL server can serve multiple databases simultaneously. And you can customize each database with Pigsty API. Define Database Business databases are defined by …

In this context, the User refers to objects created by SQL CREATE USER/ROLE. Define User There are two parameters related to users: pg_users : Define business users & roles at cluster level pg_default_roles : Define system-wide roles & global users …

Component Overview Here is how PostgreSQL module components and their interactions. From top to bottom: Cluster DNS is resolved by DNSMASQ on infra nodes Cluster VIP is manged by vip-manager, which will bind to cluster primary. vip-manager will …