Module: PGSQL

• 1: Configuration
  • 1.1: Cluster / Instance
  • 1.2: Kernel Version
  • 1.3: Package Alias
  • 1.4: User/Role
  • 1.5: Database
  • 1.6: HBA Rules
  • 1.7: Access Control
  • 1.8: Parameters
• 2: Service/Access
• 3: PostgreSQL Security
• 4: Administration
  • 4.1: Managing PostgreSQL Clusters
  • 4.2: Managing PostgreSQL Users
  • 4.3: Managing PostgreSQL Databases
  • 4.4: Patroni HA Management
  • 4.5: Managing PostgreSQL HBA Rules
  • 4.6: Pgbouncer Connection Pooling
  • 4.7: Managing PostgreSQL Component Services
  • 4.8: Manage PostgreSQL Cron Jobs
  • 4.9: Managing PostgreSQL Extensions
  • 4.10: Upgrading PostgreSQL Major/Minor Versions
• 5: Backup & Restore
  • 5.1: Backup Mechanism
  • 5.2: Backup Policy
  • 5.3: Backup Repository
  • 5.4: Admin Commands
  • 5.5: Restore Operations
  • 5.6: Clone a PG Cluster
• 6: Data Migration
• 7: Tutorials
  • 7.1: Troubleshooting
  • 7.2: Manual PITR Drill
  • 7.3: Enabling HugePage for PostgreSQL
  • 7.4: Clone and Side-Restore a PostgreSQL Instance
  • 7.5: Accidental Deletion
  • 7.6: HA Drill: Handling 2-of-3 Node Failure
  • 7.7: Bind a L2 VIP to PostgreSQL Primary with VIP-Manager
  • 7.8: Deploy HA Citus Cluster
• 8: Monitoring
• 9: Dashboard
  • 9.1: Overview
    • 9.1.1: PGSQL Overview
    • 9.1.2: PGSQL Alert
    • 9.1.3: PGSQL Shard
  • 9.2: Cluster
    • 9.2.1: PGSQL Cluster
    • 9.2.2: PGRDS Cluster
    • 9.2.3: PGSQL Activity
    • 9.2.4: PGSQL Replication
    • 9.2.5: PGSQL Service
    • 9.2.6: PGSQL Databases
    • 9.2.7: PGSQL Patroni
    • 9.2.8: PGSQL PITR
  • 9.3: Instance
    • 9.3.1: PGSQL Instance
    • 9.3.2: PGRDS Instance
    • 9.3.3: PGCAT Instance
    • 9.3.4: PGSQL Persist
    • 9.3.5: PGSQL Proxy
    • 9.3.6: PGSQL Pgbouncer
    • 9.3.7: PGSQL Session
    • 9.3.8: PGSQL Xacts
    • 9.3.9: PGSQL Exporter
  • 9.4: Database
    • 9.4.1: PGSQL Database
    • 9.4.2: PGCAT Database
    • 9.4.3: PGSQL Tables
    • 9.4.4: PGSQL Table
    • 9.4.5: PGCAT Table
    • 9.4.6: PGSQL Query
    • 9.4.7: PGCAT Query
    • 9.4.8: PGCAT Locks
    • 9.4.9: PGCAT Schema
• 10: Metrics
• 11: Parameters
• 12: Playbook
• 13: Extensions
  • 13.1: Quick Start
  • 13.2: Introduction
  • 13.3: Packages
  • 13.4: Download
  • 13.5: Install
  • 13.6: Config
  • 13.7: Create
  • 13.8: Update
  • 13.9: Remove
  • 13.10: Default Extensions
  • 13.11: Repository
• 14: Param Templates
  • 14.1: Parameter Optimization Policy
  • 14.2: OLTP Template
  • 14.3: OLAP Template
  • 14.4: CRIT Template
  • 14.5: TINY Template
• 15: PG Kernels
  • 15.1: PostgreSQL
  • 15.2: Supabase
  • 15.3: Babelfish
  • 15.4: Percona
  • 15.5: openHalo
  • 15.6: OrioleDB
  • 15.7: Cloudberry
  • 15.8: AgensGraph
  • 15.9: pgEdge
  • 15.10: DocumentDB
  • 15.11: Citus
  • 15.12: IvorySQL
  • 15.13: PolarDB PG
  • 15.14: PolarDB Oracle
  • 15.15: PostgresML
  • 15.16: Greenplum
  • 15.17: Neon
• 16: FAQ
• 17: Misc
  • 17.1: Service / Access
  • 17.2: Access Control
  • 17.3: User / Role
  • 17.4: Database
  • 17.5: Authentication / HBA

1 - Configuration

Pigsty uses configuration to control PostgreSQL behavior. This configuration combines inventory files in ~/pigsty/conf/*.yml with PGSQL parameters. After you write the configuration, you can reproduce a customized cluster in a few minutes. The configuration includes instances, users, databases, access control, extensions, and tuning policies.

Configuration Entry

1. Prepare Inventory: Copy a pigsty/conf/*.yml template or write an Ansible inventory. Put cluster groups (all.children.<cls>.hosts) and global variables (all.vars) in the same file.
2. Define Parameters: Override the required PGSQL parameters in vars. The override order is global → cluster → host. The last applicable setting determines the value.
3. Apply Configuration: Use ./configure -c <conf>, bin/pgsql-add <cls>, and other playbooks to apply the configuration. Pigsty generates the required Patroni, PgBouncer, and pgBackRest files from these parameters.

The default demonstration inventory, conf/pgsql.yml, provides a minimal example. It defines one pg-meta cluster, global pg_version: 18, and a few application users and databases. You can add more clusters to this configuration.

Focus Areas & Documentation Index

Use the following pages to configure each part of a PostgreSQL deployment:

• Cluster & Instances: Define instance topology (standalone, primary-replica, standby cluster, delayed cluster, Citus, etc.) through pg_cluster / pg_role / pg_seq / pg_upstream.
• Kernel Version: Select the core version, flavor, and tuning templates using pg_version, pg_mode, pg_packages, pg_extensions, pg_conf, and other parameters.
• Users/Roles: Declare system roles, business accounts, password policies, and connection pool attributes in pg_default_roles and pg_users.
• Database Objects: Create databases as needed using pg_databases, baseline, schemas, extensions, pool_* fields and automatically integrate with pgbouncer/Grafana.
• Access Control (HBA): Maintain host-based authentication policies using pg_default_hba_rules and pg_hba_rules to ensure access boundaries for different roles/networks.
• Privilege Model (ACL): Converge object privileges through pg_default_privileges, pg_default_roles, pg_revoke_public parameters, providing an out-of-the-box layered role system.

Use these parameters to declare infrastructure in the inventory for your application requirements. Pigsty performs the operations and ensures idempotency.

A Typical Example

This example defines topology, kernel version, extensions, users, and databases in one configuration file:

all:
  children:
    pg-analytics:
      hosts:
        10.10.10.11: { pg_seq: 1, pg_role: primary }
        10.10.10.12: { pg_seq: 2, pg_role: replica, pg_offline_query: true }
      vars:
        pg_cluster: pg-analytics
        pg_conf: olap.yml
        pg_extensions: [ postgis, timescaledb, pgvector ]
        pg_databases:
          - { name: bi, owner: dbuser_bi, schemas: [mart], extensions: [timescaledb], pool_mode: session }
        pg_users:
          - { name: dbuser_bi, password: DBUser.BI, roles: [dbrole_admin], pgbouncer: true }
  vars:
    pg_version: 18
    pg_packages: [ pgsql-main, pgsql-common ]
    pg_hba_rules:
      - { user: dbuser_bi, db: bi, addr: intra, auth: ssl, title: 'BI only allows intranet SSL access' }

• The pg-analytics cluster contains one primary and one offline replica.
• Global settings specify pg_version: 18 with a set of extension examples and load olap.yml tuning.
• Declare business objects in pg_databases and pg_users, automatically generating schema/extension and connection pool entries.
• Additional pg_hba_rules restrict access sources and authentication methods.

Adjust and apply this inventory to create a customized PostgreSQL cluster without manual configuration.

1.1 - Cluster / Instance

Select instance and cluster types that meet your requirements.

Pigsty supports several PostgreSQL instance and cluster types:

• Primary: Define a single instance cluster.
• Replica: Define a basic HA cluster with one primary and one replica.
• Offline: Define an instance dedicated to OLAP/ETL/interactive queries
• Sync Standby: Enable synchronous commit to ensure no data loss.
• Quorum Commit: Use quorum sync commit for a 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

Primary

The simplest cluster contains one primary instance:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-test

This configuration contains only identity parameters. Matching the Ansible group name to pg_cluster makes selectors such as -l pg-test convenient. However, matching group names are not required for membership. Pigsty finds members from each host’s pg_cluster identity. One PostgreSQL cluster can therefore span multiple inventory groups.

Use the following command to create this cluster:

bin/pgsql-add pg-test

One instance can be suitable for demonstrations, development tests, temporary hosting, or non-critical analytical tasks. A single-node cluster has no high availability. After hardware failure, use PITR or another recovery method to meet RTO/RPO requirements. Consider adding read-only replicas for this reason.

Replica

To add a read-only replica, add a node to pg-test and set its pg_role to replica.

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }  # <--- newly added replica
  vars:
    pg_cluster: pg-test

If the cluster does not exist, create the complete cluster. If the primary is initialized, add a replica to the existing cluster:

bin/pgsql-add pg-test               # initialize the entire cluster at once
bin/pgsql-add pg-test 10.10.10.12   # add replica to existing cluster

If the primary fails, the high-availability system can promote a replica to take over. Replicas also process read-only queries. Many applications have more reads than writes, so replicas can handle most of their read-only workload.

Offline

Offline instances are read-only replicas for slow queries, ETL, OLAP, and interactive queries. Slow queries and long transactions can reduce online application performance and stability. We therefore recommend that you isolate them from online traffic.

To add an offline instance, add a new instance and set pg_role to offline.

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: offline }  # <--- newly added offline replica
  vars:
    pg_cluster: pg-test

Dedicated offline instances operate like ordinary replicas but act as backup servers for the pg-test-replica service. Offline and primary instances serve read-only traffic only when every replica instance is down.

A separate offline server may be too expensive when resources are limited. Instead, mark an existing replica with pg_offline_query. This replica handles both online read-only requests and offline queries. Use pg_default_hba_rules and pg_hba_rules for additional access control on offline instances.

Sync Standby

With Sync Standby enabled, PostgreSQL selects one replica as the sync standby. The other replicas are candidates. Before it confirms a commit, the primary waits for the sync standby to flush data to disk. The sync standby has the latest data without replication lag. A switchover to that standby has no data loss.

PostgreSQL uses asynchronous streaming replication by default. If the primary fails, WAL that has not replicated may be lost. pg_rpo is Patroni’s sampled lag threshold for failover candidates. It is not a hard limit on actual data loss. The loss window also depends on write rate, replication state, and Patroni sampling time.

Critical workloads, such as financial transactions, may permit no data loss or read replication lag. Use synchronous commit for these requirements. To enable sync standby mode, select crit.yml with pg_conf.

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica }
  vars:
    pg_cluster: pg-test
    pg_conf: crit.yml   # <--- use crit template

To enable sync standby on an existing cluster, configure the cluster and enable synchronous_mode:

$ pg edit-config pg-test    # run as admin user on admin node
+++
-synchronous_mode: false    # <--- old value
+synchronous_mode: true     # <--- new value
 synchronous_mode_strict: false

Apply these changes? [y/N]: y

Patroni manages synchronous_standby_names automatically. It selects one replica as the sync standby. It writes and applies that replica’s application_name in the primary configuration.

Quorum Commit

Quorum Commit lets you set commit acknowledgment criteria, particularly for clusters with multiple replicas. You can adjust the consistency level and the associated availability tradeoff.

To require confirmation from at least two replicas, set synchronous_node_count through Patroni cluster configuration. Apply this setting:

synchronous_mode: true          # ensure synchronous commit is enabled
synchronous_node_count: 2       # specify "at least" how many replicas must successfully commit

To require more synchronous replicas, increase synchronous_node_count. After cluster size changes, verify that this setting remains valid to avoid service unavailability.

In this case, the PostgreSQL configuration parameter synchronous_standby_names is automatically managed by Patroni.

synchronous_standby_names = '2 ("pg-test-3","pg-test-2")'

$ pg edit-config pg-test
---
+synchronous_node_count: 2

Apply these changes? [y/N]: y

+ Cluster: pg-test (7080814403632534854) +---------+----+-----------+-----------------+
| Member    | Host        | Role         | State   | TL | Lag in MB | Tags            |
+-----------+-------------+--------------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.10 | Leader       | running |  1 |           | clonefrom: true |
| pg-test-2 | 10.10.10.11 | Sync Standby | running |  1 |         0 | clonefrom: true |
| pg-test-3 | 10.10.10.12 | Sync Standby | running |  1 |         0 | clonefrom: true |
+-----------+-------------+--------------+---------+----+-----------+-----------------+

You can also require confirmation from any n replicas. For example, this configuration requires any one replica:

synchronous_mode: quorum        # use quorum commit
postgresql:
  parameters:                   # modify PostgreSQL's configuration parameter synchronous_standby_names, using `ANY n ()` syntax
    synchronous_standby_names: 'ANY 1 (*)'  # you can specify a specific replica list or use * to wildcard all replicas.

$ pg edit-config pg-test

+    synchronous_standby_names: 'ANY 1 (*)' # in ANY mode, this parameter is needed
- synchronous_node_count: 2  # in ANY mode, this parameter is not needed

Apply these changes? [y/N]: y

Standby Cluster

You can clone an existing cluster and create a standby cluster for data migration, horizontal splitting, multi-region deployment, or disaster recovery.

Normally, the standby cluster follows the upstream cluster and keeps its data synchronized. Promote it to make it an independent cluster.

Use a normal cluster definition, then add pg_upstream to its primary. This primary is called the Standby Leader.

This inventory defines pg-test and its standby cluster, pg-test2:

# pg-test is the original cluster
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars: { pg_cluster: pg-test }

# pg-test2 is the standby cluster of pg-test
pg-test2:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # <--- pg_upstream defined here
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-test2 }

pg-test2-1 replicates from pg-test and acts as the Standby Leader for pg-test2.

Set pg_upstream on the standby cluster primary to pull backups automatically from the upstream cluster.

bin/pgsql-add pg-test     # create original cluster
bin/pgsql-add pg-test2    # create standby cluster

$ pg edit-config pg-test2

 standby_cluster:
   create_replica_methods:
   - basebackup
-  host: 10.10.10.13     # <--- old upstream
+  host: 10.10.10.12     # <--- new upstream
   port: 5432

 Apply these changes? [y/N]: y

$ pg edit-config pg-test2
-standby_cluster:
-  create_replica_methods:
-  - basebackup
-  host: 10.10.10.11
-  port: 5432

Apply these changes? [y/N]: y

pg-test:
 hosts: # pg-test-1 ---> pg-test-2 ---> pg-test-3
   10.10.10.11: { pg_seq: 1, pg_role: primary }
   10.10.10.12: { pg_seq: 2, pg_role: replica } # <--- bridge instance
   10.10.10.13: { pg_seq: 3, pg_role: replica, pg_upstream: 10.10.10.12 }
   # ^--- replicate from pg-test-2 (bridge) instead of pg-test-1 (primary)
 vars: { pg_cluster: pg-test }

Delayed Cluster

A Delayed Cluster is a special type of standby cluster used to quickly recover “accidentally deleted” data.

For example, define pg-testdelay to retain the state of pg-test from one hour earlier:

# pg-test is the original cluster
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars: { pg_cluster: pg-test }

# pg-testdelay is the delayed cluster of pg-test
pg-testdelay:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11, pg_delay: 1d }
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-testdelay }

You can also configure a “replication delay” on an existing standby cluster.

$ pg edit-config pg-testdelay
 standby_cluster:
   create_replica_methods:
   - basebackup
   host: 10.10.10.11
   port: 5432
+  recovery_min_apply_delay: 1h    # <--- add delay duration here, e.g. 1 hour

Apply these changes? [y/N]: y

After accidental row or table deletion, adjust this delay to advance the delayed cluster to the required time. Read the data from that cluster to repair the original cluster.

Delayed clusters need additional resources. They recover data much faster than PITR and have less effect on the system. Consider them for critical clusters.

Citus Cluster

Pigsty natively supports Citus. See conf/ha/citus.yml for a complete example.

To define a Citus cluster, you need to specify the following parameters:

• pg_mode must be set to citus, not the default pgsql
• The shard name pg_shard and shard number pg_group must be defined on each shard cluster
• pg_primary_db must be defined to specify the database managed by Patroni.
• To run administration commands as pg_dbsu postgres instead of pg_admin_username, set pg_dbsu_password to a non-empty plaintext password.

Add HBA rules for SSL access from localhost and other data nodes. For example:

all:
  children:
    pg-citus0: # citus shard 0
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus0 , pg_group: 0 }
    pg-citus1: # citus shard 1
      hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus1 , pg_group: 1 }
    pg-citus2: # citus shard 2
      hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus2 , pg_group: 2 }
    pg-citus3: # citus shard 3
      hosts:
        10.10.10.13: { pg_seq: 1, pg_role: primary }
        10.10.10.14: { pg_seq: 2, pg_role: replica }
      vars: { pg_cluster: pg-citus3 , pg_group: 3 }
  vars:                               # global parameters for all Citus clusters
    pg_mode: citus                    # pgsql cluster mode must be set to: citus
    pg_shard: pg-citus                # citus horizontal shard name: pg-citus
    pg_primary_db: meta               # citus database name: meta
    pg_dbsu_password: DBUser.Postgres # if using dbsu, need to configure a password for it
    pg_users: [ { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] } ]
    pg_databases: [ { name: meta ,extensions: [ { name: citus }, { name: postgis }, { name: timescaledb } ] } ]
    pg_hba_rules:
      - { user: 'all' ,db: all  ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' }
      - { user: 'all' ,db: all  ,addr: intra        ,auth: ssl ,title: 'all user ssl access from intranet'  }

On the coordinator, create distributed tables and reference tables. You can query them from any data node. From Citus 11.2, any Citus database node can act as coordinator.

SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table($$public.pgbench_accounts$$);
SELECT create_reference_table('pgbench_branches')         ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_branches$$);
SELECT create_reference_table('pgbench_history')          ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_history$$);
SELECT create_reference_table('pgbench_tellers')          ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_tellers$$);

1.2 - Kernel Version

Choosing a “kernel” in Pigsty means determining the PostgreSQL major version, mode/distribution, packages to install, and tuning templates to load.

The Pigsty v4.5 source supports PostgreSQL 14-18 and uses 18 by default. Use the following configuration settings to select a kernel.

Major Version and Packages

• pg_version: Select the PostgreSQL major version. The default is 18. Pigsty maps the version to the correct package name prefix.
• pg_packages: Select core packages with package aliases. The default, pgsql-main pgsql-common, includes the kernel, Patroni, PgBouncer, pgBackRest, and other common tools.
• pg_extensions: Select additional extension packages. Aliases are supported. The default is empty, which installs only core dependencies.

all:
  vars:
    pg_version: 18
    pg_packages: [ pgsql-main, pgsql-common ]
    pg_extensions: [ postgis, timescaledb, pgvector, pgml ]

During installation, Ansible downloads packages for pg_version=18 and installs extension files on the system. Database initialization scripts can then run CREATE EXTENSION.

Extension availability differs between versions in Pigsty’s offline repository. PostgreSQL 14 has fewer extensions, while 17/18 have the widest coverage. If an extension is not included, add it through repo_extra_packages.

Kernel Mode (pg_mode)

pg_mode selects the kernel distribution. The default, pgsql, selects standard PostgreSQL. Pigsty supports these modes:

pg_mode selects binary paths, Patroni integration, and some kernel-specific behavior. It does not automatically add every required package, extension, and application database. For deployment, use the matching conf/*.yml template. Alternatively, set pg_packages, pg_extensions, pg_libs, and pg_databases explicitly. This is a minimal Citus example:

all:
  children:
    pg-citus1:
      hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus1, pg_group: 0 }
    pg-citus2:
      hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus2, pg_group: 1 }
  vars:
    pg_mode: citus
    pg_shard: pg-citus
    pg_primary_db: citus
    pg_extensions: [ citus ]
    pg_libs: 'citus, pg_stat_statements'
    pg_databases:
      - { name: citus, extensions: [ citus ] }

conf/ha/citus.yml provides the current complete example. The example above explicitly installs Citus packages and creates the extension in the citus database.

Extensions and Pre-installed Objects

Use these parameters to control components loaded automatically after database startup:

• pg_libs: List to write to shared_preload_libraries. For example: pg_libs: 'timescaledb, pg_stat_statements, auto_explain'.
• pg_default_extensions / pg_default_schemas: Control schemas and extensions pre-created in template1 and postgres by initialization scripts.
• pg_parameters: Rendered by Pigsty into postgresql.auto.conf during configuration. Do not also manage the same settings manually with ALTER SYSTEM.

Example: Enable TimescaleDB, pgvector and customize some system parameters.

pg-analytics:
  vars:
    pg_cluster: pg-analytics
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_default_extensions:
      - { name: timescaledb }
      - { name: vector }
    pg_parameters:
      timescaledb.max_background_workers: 8

During initialization, Pigsty creates default extensions in template1 and postgres. New databases based on template1 inherit these objects. Pigsty writes pg_parameters directly to postgresql.auto.conf.

Tuning Template (pg_conf)

pg_conf selects a Patroni template from roles/pgsql/templates/*.yml. Pigsty includes four general templates:

You can replace the template or customize a YAML file in templates/. Then select it in the cluster vars.

pg-ledger:
  hosts: { 10.10.10.21: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-ledger
    pg_conf: crit.yml
    pg_parameters:
      synchronous_commit: 'remote_apply'
      max_wal_senders: 16
      wal_keep_size: '2GB'

Pigsty uses crit.yml as the Patroni configuration and writes pg_parameters overrides to postgresql.auto.conf. The instances then use synchronous commit immediately.

Combined Instance: A Complete Example

pg-rag:
  hosts:
    10.10.10.31: { pg_seq: 1, pg_role: primary }
    10.10.10.32: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-rag
    pg_version: 18
    pg_mode: pgsql
    pg_conf: olap.yml
    pg_packages: [ pgsql-main, pgsql-common ]
    pg_extensions: [ pgvector, pgml, postgis ]
    pg_libs: 'pg_stat_statements, auto_explain'
    pg_parameters:
      max_parallel_workers: 8
      shared_buffers: '32GB'

• First primary + one replica, using olap.yml tuning.
• Install PG18 plus common RAG extensions; only libraries that actually require preloading belong in pg_libs.
• Patroni/pgbouncer/pgbackrest generated by Pigsty, no manual intervention needed.

Adjust these parameters for the application requirements to customize the kernel configuration.

1.3 - Package Alias

PostgreSQL package names differ between operating systems:

• EL systems (RHEL/Rocky/Alma/…) use formats like pgvector_18, postgis36_18*
• Debian/Ubuntu systems use formats like postgresql-18-pgvector, postgresql-18-postgis-3

Without aliases, you need different naming rules for each system. You must also include the correct PostgreSQL version in the names.

Package Alias

Use a Package Alias to specify the same component on different platforms. Pigsty converts it to the correct package names:

# Using aliases - simple, unified, cross-platform
pg_extensions: [ postgis, pgvector, timescaledb ]

# Equivalent to actual package names on EL9 + PG18
pg_extensions: [ postgis36_18*, pgvector_18*, timescaledb-tsl_18* ]

# Equivalent to actual package names on Ubuntu 24 + PG18
pg_extensions: [ postgresql-18-postgis-3, postgresql-18-pgvector, postgresql-18-timescaledb-tsl ]

Alias Translation

An alias can also identify a group of packages. For example, the default pg_packages value is:

pg_packages:                      # pg packages to be installed, alias can be used
  - pgsql-main pgsql-common

Pigsty reads the alias list for the current operating system. For el10.x86_64, it expands the aliases to PostgreSQL kernel, extension, and utility packages:

pgsql-main:    "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl postgresql$v-llvmjit pg_repack_$v* wal2json_$v* pgvector_$v*"
pgsql-common:  "patroni patroni-etcd pgbouncer pgbackrest pg_exporter pgbackrest_exporter vip-manager"

Then Pigsty substitutes the selected PostgreSQL major version into pgsql-main. For example, with pg_version = 18:

pg18-main:   "postgresql18 postgresql18-server postgresql18-libs postgresql18-contrib postgresql18-plperl postgresql18-plpython3 postgresql18-pltcl postgresql18-llvmjit pg_repack_18* wal2json_18* pgvector_18*"

This process lets you select functional components without specifying platform-specific package details.

Which Variables Can Use Aliases?

These four parameters accept package aliases. Pigsty converts the aliases to package names through the same process:

• pg_extensions - PG extension packages
• pg_packages - PG kernel/base utility packages
• repo_packages - Package download parameter: packages to download to local repository
• repo_extra_packages - Extension installation parameter: additional packages to download to local repository

Alias List

The Pigsty source directory roles/node_id/vars/ contains alias mappings for each operating system and architecture:

• el10.x86_64
• el10.aarch64
• el9.x86_64
• el9.aarch64
• el8.x86_64
• el8.aarch64
• u26.x86_64
• u26.aarch64
• u24.x86_64
• u24.aarch64
• u22.x86_64
• u22.aarch64
• d13.x86_64
• d13.aarch64
• d12.x86_64
• d12.aarch64

How It Works

Alias Translation Process

User config alias --> Detect OS -->  Find alias mapping table ---> Replace $v placeholder ---> Install actual packages
     ↓                 ↓                   ↓                                   ↓
  postgis          el9.x86_64         postgis36_$v*                   postgis36_18*
  postgis          u24.x86_64         postgresql-$v-postgis-3         postgresql-18-postgis-3

Version Placeholder

The alias system uses $v as a PostgreSQL version placeholder. Pigsty replaces each $v in an alias with the value of pg_version.

For example, when pg_version: 18:

Wildcard Matching

On EL systems, many aliases use the * wildcard to match related subpackages. For example:

• postgis36_18* will match postgis36_18, postgis36_18-client, postgis36_18-utils, etc.
• postgresql18* will match postgresql18, postgresql18-server, postgresql18-libs, postgresql18-contrib, etc.

One alias can install a complete extension. You do not need to list each subpackage.

1.4 - User/Role

In this document, “user” refers to a logical object within a database cluster created with CREATE USER/ROLE.

In PostgreSQL, users belong to the database cluster, rather than a specific database. Create business users before you create business databases.

Pigsty defines roles and users through two config parameters:

• pg_default_roles: Define globally shared roles and users
• pg_users: Define business users and roles at cluster level

The first parameter defines roles and users shared across the environment. The second defines business roles and users for one cluster. Both parameters use arrays of user definition objects. Pigsty creates users and roles in array order. Thus, a later user can belong to an earlier role.

By default, all users marked with pgbouncer: true are added to the Pgbouncer connection pool user list.

Define Users

Example from Pigsty demo pg-meta cluster:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - {name: dbuser_meta     ,password: DBUser.Meta     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: pigsty admin user }
      - {name: dbuser_view     ,password: DBUser.Viewer   ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
      - {name: dbuser_grafana  ,password: DBUser.Grafana  ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for grafana database    }
      - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for bytebase database   }
      - {name: dbuser_kong     ,password: DBUser.Kong     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for kong api gateway    }
      - {name: dbuser_gitea    ,password: DBUser.Gitea    ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for gitea service       }
      - {name: dbuser_wiki     ,password: DBUser.Wiki     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for wiki.js service     }
      - {name: dbuser_noco     ,password: DBUser.Noco     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for nocodb service      }
      - {name: dbuser_remove   ,state: absent }  # use state: absent to delete user

Each user or role definition is an object. Only name is required:

- name: dbuser_meta               # REQUIRED, `name` is the only mandatory field
  state: create                   # Optional, user state: create (default), absent
  password: DBUser.Meta           # Optional, password, can be scram-sha-256 hash or plaintext
  login: true                     # Optional, can login, default true
  superuser: false                # Optional, is superuser, default false
  createdb: false                 # Optional, can create databases, default false
  createrole: false               # Optional, can create roles, default false
  inherit: true                   # Optional, inherit role privileges, default true
  replication: false              # Optional, can replicate, default false
  bypassrls: false                # Optional, bypass row-level security, default false
  connlimit: -1                   # Optional, connection limit, default -1 (unlimited)
  expire_in: 3650                 # Optional, expire N days from creation (priority over expire_at)
  expire_at: '2030-12-31'         # Optional, expiration date in YYYY-MM-DD format
  comment: pigsty admin user      # Optional, user comment
  roles: [dbrole_admin]           # Optional, roles array
  parameters:                     # Optional, role-level config params
    search_path: public
  pgbouncer: true                 # Optional, add to connection pool user list, default false
  pool_mode: transaction          # Optional, pgbouncer pool mode, default transaction
  pool_connlimit: 100             # Optional, user-level max pool connections; omitted inherits global default 100

User-level pool quota is consistently defined by pool_connlimit (mapped to Pgbouncer max_user_connections).

Parameter Overview

The only required field is name. It must be a valid username that is unique within the cluster. All other parameters have defaults.

Parameter Details

name

This required string specifies the username. The username must be unique within the cluster.

Use a valid PostgreSQL identifier that matches ^[a-z_][a-z0-9_]{0,62}$. Start with a lowercase letter or underscore. Use only lowercase letters, digits, and underscores. The maximum length is 63 characters.

- name: dbuser_app         # Standard naming
- name: app_readonly       # Underscore separated
- name: _internal          # Underscore prefix (for internal roles)

state

Enum for user operation: create or absent. Default create.

- name: dbuser_app             # state defaults to create
- name: dbuser_old
  state: absent                # Delete user

Pigsty prevents deletion of these system users with state: absent to prevent cluster failure:

• postgres: Database superuser
• replicator: Replication user (or pg_replication_username)
• dbuser_dba: Admin user (or pg_admin_username)
• dbuser_monitor: Monitor user (or pg_monitor_username)

password

This mutable string specifies the user password. Users without a password cannot log in with password authentication.

Password can be:

# Plaintext (not recommended, logged to config)
- name: dbuser_app
  password: MySecretPassword

# SCRAM-SHA-256 hash (recommended)
- name: dbuser_app
  password: 'SCRAM-SHA-256$4096:xxx$yyy:zzz'

When Pigsty sets a password, it temporarily disables logging to prevent password disclosure:

SET log_statement TO 'none';
ALTER USER "dbuser_app" PASSWORD 'xxx';
SET log_statement TO DEFAULT;

To generate SCRAM-SHA-256 hash:

# Using PostgreSQL (requires pgcrypto extension)
psql -c "SELECT encode(digest('password' || 'username', 'sha256'), 'hex')"

comment

String, mutable. User comment, defaults to business user {name}.

Pigsty sets the comment with COMMENT ON ROLE. Special characters are supported, and Pigsty automatically escapes quotes.

- name: dbuser_app
  comment: 'Main business application account'

COMMENT ON ROLE "dbuser_app" IS 'Main business application account';

login

This mutable Boolean controls login access. The default is true.

Set false to create a Role rather than a user. Roles usually group permissions.

In PostgreSQL, CREATE USER equals CREATE ROLE ... LOGIN.

# Create login-able user
- name: dbuser_app
  login: true

# Create role (no login, for permission grouping)
- name: dbrole_custom
  login: false
  comment: custom permission role

CREATE USER "dbuser_app" LOGIN;
CREATE USER "dbrole_custom" NOLOGIN;

superuser

This mutable Boolean controls superuser privileges. The default is false.

Superusers have full database privileges. They bypass all permission checks.

- name: dbuser_admin
  superuser: true            # Dangerous: full privileges

ALTER USER "dbuser_admin" SUPERUSER;

Pigsty provides a default superuser through pg_admin_username (dbuser_dba). Do not create additional superusers unless necessary.

createdb

This mutable Boolean controls permission to create databases. The default is false.

- name: dbuser_dev
  createdb: true             # Allow create database

ALTER USER "dbuser_dev" CREATEDB;

Some applications, such as Gitea and Odoo, may require the CREATEDB privilege for their administration users.

createrole

This mutable Boolean controls permission to create other roles. The default is false.

Users with CREATEROLE can create, modify, and delete other roles that are not superusers.

- name: dbuser_admin
  createrole: true           # Allow manage other roles

ALTER USER "dbuser_admin" CREATEROLE;

inherit

This mutable Boolean controls automatic inheritance of privileges from roles to which the user belongs. The default is true.

If you set false, use an explicit SET ROLE command to use the role privileges.

# Auto-inherit role privileges (default)
- name: dbuser_app
  inherit: true
  roles: [dbrole_readwrite]

# Requires explicit SET ROLE
- name: dbuser_special
  inherit: false
  roles: [dbrole_admin]

ALTER USER "dbuser_special" NOINHERIT;
-- User must execute SET ROLE dbrole_admin to get privileges

replication

This mutable Boolean controls permission to start streaming replication. The default is false.

Usually, only replication users (replicator) need this permission. Regular users should not have it unless they need logical decoding subscriptions.

- name: replicator
  replication: true          # Allow streaming replication
  roles: [pg_monitor, dbrole_readonly]

ALTER USER "replicator" REPLICATION;

bypassrls

This mutable Boolean controls permission to bypass row-level security (RLS) policies. The default is false.

When enabled, the user can access all rows, even when RLS policies apply. Usually, only administrators need this permission.

- name: dbuser_myappadmin
  bypassrls: true            # Bypass RLS policies

ALTER USER "dbuser_myappadmin" BYPASSRLS;

connlimit

This mutable integer sets the maximum number of concurrent connections. The default is -1 (unlimited).

A positive integer limits the number of simultaneous sessions for this user. The limit does not apply to superusers.

- name: dbuser_app
  connlimit: 100             # Max 100 concurrent connections

- name: dbuser_batch
  connlimit: 10              # Limit batch user connections

ALTER USER "dbuser_app" CONNECTION LIMIT 100;

expire_in

This mutable integer sets expiration to N days from the current date.

This parameter takes priority over expire_at. Each playbook run recalculates the expiration date. Use this parameter for temporary users that need periodic renewal.

- name: temp_user
  expire_in: 30              # Expire in 30 days

- name: contractor_user
  expire_in: 90              # Expire in 90 days

Generates SQL:

-- expire_in: 30, assuming current date is 2025-01-01
ALTER USER "temp_user" VALID UNTIL '2025-01-31';

expire_at

String, mutable. Expiration date in YYYY-MM-DD format, or special value infinity.

This parameter has lower priority than expire_in. Use infinity for users that must not expire.

- name: contractor_user
  expire_at: '2024-12-31'    # Expire on specific date

- name: permanent_user
  expire_at: 'infinity'      # Never expires

ALTER USER "contractor_user" VALID UNTIL '2024-12-31';
ALTER USER "permanent_user" VALID UNTIL 'infinity';

roles

This additive array lists the roles to which the user belongs. Array elements can be strings or objects.

Simple format - strings for role names:

- name: dbuser_app
  roles:
    - dbrole_readwrite
    - pg_read_all_data

GRANT "dbrole_readwrite" TO "dbuser_app";
GRANT "pg_read_all_data" TO "dbuser_app";

Use objects for more detailed control:

- name: dbuser_app
  roles:
    - dbrole_readwrite                            # Simple string: GRANT role
    - { name: dbrole_admin, admin: true }         # WITH ADMIN OPTION
    - { name: pg_monitor, set: false }            # PG16+: disallow SET ROLE
    - { name: pg_signal_backend, inherit: false } # PG16+: don't auto-inherit
    - { name: old_role, state: absent }           # Revoke role membership

Object Format Parameters:

PostgreSQL 16+ New Features:

PostgreSQL 16 introduced finer-grained role membership control:

• ADMIN OPTION: Allow granting role to other users
• SET OPTION: Allow using SET ROLE to switch to this role
• INHERIT OPTION: Auto-inherit this role’s privileges

# PostgreSQL 16+ complete example
- name: dbuser_app
  roles:
    # Normal membership
    - dbrole_readwrite

    # Can grant dbrole_admin to other users
    - { name: dbrole_admin, admin: true }

    # Cannot SET ROLE to pg_monitor (only inherit privileges)
    - { name: pg_monitor, set: false }

    # Don't auto-inherit pg_execute_server_program (need explicit SET ROLE)
    - { name: pg_execute_server_program, inherit: false }

    # Revoke old_role membership
    - { name: old_role, state: absent }

The set and inherit options work only in PG16+. Earlier versions ignore them and add warning comments.

parameters

This mutable object sets role-level configuration parameters with ALTER ROLE ... SET. The parameters apply to all sessions for this user.

- name: dbuser_analyst
  parameters:
    work_mem: '256MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'
    log_statement: 'all'

ALTER USER "dbuser_analyst" SET "work_mem" = '256MB';
ALTER USER "dbuser_analyst" SET "statement_timeout" = '5min';
ALTER USER "dbuser_analyst" SET "search_path" = 'analytics,public';
ALTER USER "dbuser_analyst" SET "log_statement" = 'all';

Use the special value DEFAULT to reset a parameter to its PostgreSQL default. This value is case-insensitive:

- name: dbuser_app
  parameters:
    work_mem: DEFAULT          # Reset to default
    statement_timeout: '30s'   # Set new value

ALTER USER "dbuser_app" SET "work_mem" = DEFAULT;
ALTER USER "dbuser_app" SET "statement_timeout" = '30s';

Common role-level params:

Query user-level parameters through the pg_db_role_setting system view.

pgbouncer

This mutable Boolean adds the user to the Pgbouncer user list. The default is false.

For production users that need connection pool access, explicitly set pgbouncer: true. The default, false, prevents accidental pool access for internal users.

# Prod user: needs connection pool
- name: dbuser_app
  password: DBUser.App
  pgbouncer: true

# Internal user: no connection pool needed
- name: dbuser_internal
  password: DBUser.Internal
  pgbouncer: false           # Default, can be omitted

Users with pgbouncer: true are added to /etc/pgbouncer/userlist.txt.

pool_mode

Enum, mutable. User-level pool mode: transaction, session, or statement. Default transaction.

# DBA user: session mode (may need SET commands etc.)
- name: dbuser_dba
  pgbouncer: true
  pool_mode: session

# Normal business user: transaction mode
- name: dbuser_app
  pgbouncer: true
  pool_mode: transaction

User-level pool params are configured via /etc/pgbouncer/useropts.txt:

dbuser_dba      = pool_mode=session max_user_connections=16
dbuser_monitor  = pool_mode=session max_user_connections=8

pool_connlimit

This mutable integer sets the maximum pool connections for the user. If you omit it, Pigsty does not generate a user-level override. The global pgbouncer.ini default of 100 then applies. PgBouncer uses 0 for unlimited connections.

- name: dbuser_app
  pgbouncer: true
  pool_connlimit: 50         # Max 50 pool connections for this user

ACL System

Pigsty provides a built-in access control / ACL model. Assign these default business roles to users as required:

# Typical business user configuration
pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true
    roles: [dbrole_readwrite]    # Prod account, read-write

  - name: dbuser_readonly
    password: DBUser.Readonly
    pgbouncer: true
    roles: [dbrole_readonly]     # Read-only account

  - name: dbuser_admin
    password: DBUser.Admin
    pgbouncer: true
    roles: [dbrole_admin]        # Admin, can execute DDL

  - name: dbuser_etl
    password: DBUser.ETL
    roles: [dbrole_offline]      # Analytics account; restrict instance scope through HBA

dbrole_offline does not restrict a user to offline instances by itself. To apply this restriction, set role: offline on the corresponding HBA rule. See Offline Role and Instance Isolation.

To define your own ACL system, customize these parameters and templates:

• pg_default_roles: System-wide roles and global users
• pg_default_privileges: Default privileges for new objects
• pg-init-roles.sql: Role creation SQL template
• pg-init-template.sql: Privilege SQL template

Pgbouncer Users

Pigsty enables Pgbouncer as connection pool middleware by default. Pigsty adds users from pg_users to the Pgbouncer user list only when they have pgbouncer: true.

Users in connection pool are listed in /etc/pgbouncer/userlist.txt:

"postgres" ""
"dbuser_wiki" "SCRAM-SHA-256$4096:+77dyhrPeFDT/TptHs7/7Q==$KeatuohpKIYzHPCt/tqBu85vI11o9mar/by0hHYM2W8=:X9gig4JtjoS8Y/o1vQsIX/gY1Fns8ynTXkbWOjUfbRQ="
"dbuser_view" "SCRAM-SHA-256$4096:DFoZHU/DXsHL8MJ8regdEw==$gx9sUGgpVpdSM4o6A2R9PKAUkAsRPLhLoBDLBUYtKS0=:MujSgKe6rxcIUMv4GnyXJmV0YNbf39uFRZv724+X1FE="
"dbuser_monitor" "SCRAM-SHA-256$4096:fwU97ZMO/KR0ScHO5+UuBg==$CrNsmGrx1DkIGrtrD1Wjexb/aygzqQdirTO1oBZROPY=:L8+dJ+fqlMQh7y4PmVR/gbAOvYWOr+KINjeMZ8LlFww="
"dbuser_meta" "SCRAM-SHA-256$4096:leB2RQPcw1OIiRnPnOMUEg==$eyC+NIMKeoTxshJu314+BmbMFpCcspzI3UFZ1RYfNyU=:fJgXcykVPvOfro2MWNkl5q38oz21nSl1dTtM65uYR1Q="

User-level pool params are maintained in /etc/pgbouncer/useropts.txt:

dbuser_dba      = pool_mode=session max_user_connections=16
dbuser_monitor  = pool_mode=session max_user_connections=8

When you create users, Pigsty reloads the Pgbouncer user list online. The reload does not affect existing connections.

Pgbouncer runs as the same dbsu as PostgreSQL. The default is the postgres operating system user. Use the pgb alias to access Pgbouncer administration functions.

Use pgbouncer_auth_query to query authentication data dynamically for pool users. This parameter removes the need to manage pool users manually.

Related Resources

For user management operations, see User Management.

For user access privileges, see Access Control: Role System.

1.5 - Database

In this document, “database” refers to a logical object within a database cluster created with CREATE DATABASE.

A PostgreSQL cluster can serve multiple databases at the same time. Define the required databases in the Pigsty cluster configuration.

Pigsty customizes the template1 database with default schemas, extensions, and privileges. New databases inherit these settings from template1. Use template to select another template for instant database cloning.

By default, Pigsty adds each application database to the PgBouncer connection pool with a 1:1 mapping. pg_exporter discovers application databases automatically to monitor their objects. Pigsty also registers all databases as PostgreSQL data sources in Grafana on every INFRA node for PGCAT dashboards.

Define Database

Define application databases in pg_databases, an array of database definition objects. During cluster initialization, Pigsty creates databases in definition order. A later database can therefore use an earlier database as a template.

Example from Pigsty demo pg-meta cluster:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]}
      - { name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database }
      - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
      - { name: kong     ,owner: dbuser_kong     ,revokeconn: true ,comment: kong the api gateway database }
      - { name: gitea    ,owner: dbuser_gitea    ,revokeconn: true ,comment: gitea meta database }
      - { name: wiki     ,owner: dbuser_wiki     ,revokeconn: true ,comment: wiki meta database }
      - { name: noco     ,owner: dbuser_noco     ,revokeconn: true ,comment: nocodb database }

Each database definition has the fields below. Only name is required:

- name: meta                      # REQUIRED, `name` is the only mandatory field
  state: create                   # Optional, database state: create (default), absent, recreate
  baseline: cmdb.sql              # Optional, SQL baseline file path (relative to Ansible search path, e.g., files/)
  pgbouncer: true                 # Optional, add to pgbouncer database list? default true
  schemas: [pigsty]               # Optional, additional schemas to create, array of schema names
  extensions:                     # Optional, extensions to install: array of extension objects
    - { name: postgis , schema: public }  # Can specify schema, or omit (installs to first schema in search_path)
    - { name: timescaledb }               # Some extensions create and use fixed schemas
  comment: pigsty meta database   # Optional, database comment/description
  owner: postgres                 # Optional, database owner, defaults to current user
  template: template1             # Optional, template to use, default template1
  strategy: FILE_COPY             # Optional, clone strategy: FILE_COPY or WAL_LOG (PG15+)
  encoding: UTF8                  # Optional, inherits from template/cluster config (UTF8)
  locale: C                       # Optional, inherits from template/cluster config (C)
  lc_collate: C                   # Optional, inherits from template/cluster config (C)
  lc_ctype: C                     # Optional, inherits from template/cluster config (C)
  locale_provider: libc           # Optional, locale provider: libc, icu, builtin (PG15+)
  icu_locale: en-US               # Optional, ICU locale rules (PG15+)
  icu_rules: ''                   # Optional, ICU collation rules (PG16+)
  builtin_locale: C.UTF-8         # Optional, builtin locale provider rules (PG17+)
  tablespace: pg_default          # Optional, default tablespace
  is_template: false              # Optional, mark as template database
  allowconn: true                 # Optional, allow connections, default true
  revokeconn: false               # Optional; when true, retain CONNECT only for owner, admin, monitor, and replication users
  register_datasource: true       # Optional, register to grafana datasource? default true
  connlimit: -1                   # Optional, connection limit, -1 means unlimited
  parameters:                     # Optional, database-level params via ALTER DATABASE SET
    work_mem: '64MB'
    statement_timeout: '30s'
  pool_auth_user: dbuser_meta     # Optional, auth user for pgbouncer auth_query
  pool_mode: transaction          # Optional, database-level pgbouncer pool mode
  pool_size: 50                   # Optional, database-level pgbouncer default pool size
  pool_reserve: 30                # Optional, database-level pgbouncer reserve pool
  pool_size_min: 0                # Optional, database-level pgbouncer min pool size
  pool_connlimit: 100             # Optional, database-level max database connections

From Pigsty v4.1.0, use pool_reserve and pool_connlimit for database pools. These unify the legacy aliases pool_size_reserve / pool_max_db_conn.

Parameter Overview

Only name is required. It must be a valid database name that is unique within the cluster. All other parameters have defaults. Immutable parameters take effect only at creation. To change them, recreate the database.

Parameter Details

name

String, required. Database name - must be unique within the cluster.

The current role does not enforce this regular expression. It double-quotes SQL identifiers, but also uses the name in temporary paths and shell/SQL commands. For safe operation throughout the automation, keep the name within 63 bytes and follow ^[A-Za-z_][A-Za-z0-9_$]{0,62}$. Avoid spaces, quotes, slashes, and other special characters.

- name: myapp              # Simple naming
- name: my_application     # Underscore separated
- name: app_v2             # Version included

state

Enum for database operation: create, absent, or recreate. Default create.

- name: myapp                # state defaults to create
- name: olddb
  state: absent              # Delete database
- name: testdb
  state: recreate            # Rebuild database

owner

String. Database owner, defaults to pg_dbsu (postgres) if not specified.

The target user must exist. To change the owner, Pigsty runs the following commands. The old owner retains existing privileges.

The database owner has full control, including schema, table, and extension creation. This is useful for multi-tenant deployments.

ALTER DATABASE "myapp" OWNER TO "new_owner";
GRANT ALL PRIVILEGES ON DATABASE "myapp" TO "new_owner";

comment

String. Database comment, defaults to business database {name}.

Pigsty uses COMMENT ON DATABASE to set the comment. Comments support Chinese and special characters; Pigsty escapes quotes automatically. PostgreSQL stores them in pg_shdescription, the shared-object comment catalog. Use \l+ to show them.

COMMENT ON DATABASE "myapp" IS 'my main application database';

- name: myapp
  comment: my main application database

template

String, immutable. Template database for creation, default template1.

CREATE DATABASE clones the template, including objects, extensions, schemas, and permissions. Pigsty customizes template1 during cluster initialization. New databases inherit these settings.

For the icu or builtin locale provider, specify template: template0. You cannot override the locale settings inherited from template1.

- name: myapp_icu
  template: template0        # Required for ICU
  locale_provider: icu
  icu_locale: zh-Hans

template0 omits the monitoring extensions, schemas, and default privileges. Use it for a fully custom database.

strategy

Enum, immutable. Clone strategy: FILE_COPY or WAL_LOG. Available PG15+.

WAL_LOG permits template connections during cloning but is less efficient for large templates. PG14 and earlier ignore this setting.

- name: cloned_db
  template: source_db
  strategy: WAL_LOG          # WAL-based cloning

encoding

String, immutable. Character encoding, inherits from template if unspecified (usually UTF8).

We strongly recommend UTF8 unless you have special requirements. You cannot change the encoding after creation.

- name: legacy_db
  template: template0        # Use template0 for non-default encoding
  encoding: LATIN1

locale

String, immutable. Locale setting - sets both lc_collate and lc_ctype. Inherits from template (usually C).

This setting controls string sort order and character classification. Use C or POSIX for the best performance and cross-platform consistency. For language-specific sorting, use a locale such as zh_CN.UTF-8.

- name: chinese_db
  template: template0
  locale: zh_CN.UTF-8        # Chinese locale
  encoding: UTF8

lc_collate

String, immutable. String collation rule. Inherits from template (usually C).

This setting controls ORDER BY and comparison results. Common values are C (byte order, fastest), C.UTF-8, en_US.UTF-8, and zh_CN.UTF-8. You cannot change it after creation.

- name: myapp
  template: template0
  lc_collate: en_US.UTF-8    # English collation
  lc_ctype: en_US.UTF-8

lc_ctype

String, immutable. Character classification rule for upper/lower case, digits, letters. Inherits from template (usually C).

This setting affects upper(), lower(), regex \w, and similar operations. You cannot change it after creation.

locale_provider

Enum, immutable. Locale implementation provider: libc, icu, or builtin. Available PG15+, default libc.

For icu or builtin, use template: template0. Also set the corresponding icu_locale or builtin_locale.

- name: fast_db
  template: template0
  locale_provider: builtin   # Builtin provider, most efficient
  builtin_locale: C.UTF-8

icu_locale

String, immutable. ICU locale identifier. Available PG15+ when locale_provider: icu.

ICU identifiers follow BCP 47. Common values:

- name: chinese_app
  template: template0
  locale_provider: icu
  icu_locale: zh-Hans        # Simplified Chinese ICU collation
  encoding: UTF8

icu_rules

String, immutable. Custom ICU collation rules. Available PG16+.

Use ICU Collation Customization to adjust the default sort behavior.

- name: custom_sort_db
  template: template0
  locale_provider: icu
  icu_locale: en-US
  icu_rules: '&V << w <<< W'  # Custom V/W sort order

builtin_locale

String, immutable. Builtin locale provider rules. Available PG17+ when locale_provider: builtin. Values: C or C.UTF-8.

PG17 introduced the builtin provider. It is faster than libc and provides consistent behavior across platforms. It supports only C/C.UTF-8 collation.

- name: fast_db
  template: template0
  locale_provider: builtin
  builtin_locale: C.UTF-8    # Builtin UTF-8 support
  encoding: UTF8

tablespace

String, mutable. Default tablespace, default pg_default.

Changing the tablespace moves all database objects to the new tablespace. For large databases, this physical migration can take a long time. Use it carefully.

- name: archive_db
  tablespace: slow_hdd       # Archive data on slow storage

ALTER DATABASE "archive_db" SET TABLESPACE "slow_hdd";

is_template

Boolean, mutable. Mark database as template, default false.

If true, any user with CREATEDB can clone this database as a template. Template databases usually contain predefined schemas, extensions, and data.

- name: app_template
  is_template: true          # Mark as template, allow user cloning
  schemas: [core, api]
  extensions: [postgis, pg_trgm]

Before it deletes an is_template: true database, Pigsty runs ALTER DATABASE ... IS_TEMPLATE false.

allowconn

Boolean, mutable. Allow connections, default true.

If false, the database rejects all connections, including superuser connections. Use this setting for maintenance or archival.

- name: archive_db
  allowconn: false           # Disallow all connections

ALTER DATABASE "archive_db" ALLOW_CONNECTIONS false;

revokeconn

Boolean, mutable. Revoke PUBLIC CONNECT privilege, default false.

When true, Pigsty executes:

• Revoke PUBLIC CONNECT, regular users can’t connect
• Grant connect to replication user (replicator) and monitor user (dbuser_monitor)
• Grant connect to admin user (dbuser_dba) and owner with WITH GRANT OPTION

Setting false restores PUBLIC CONNECT privilege.

- name: secure_db
  owner: dbuser_secure
  revokeconn: true           # Revoke public connect, only specified users

connlimit

Integer, mutable. Max concurrent connections, default -1 (unlimited).

A positive integer limits simultaneous sessions. The limit does not apply to superusers.

- name: limited_db
  connlimit: 50              # Max 50 concurrent connections

ALTER DATABASE "limited_db" CONNECTION LIMIT 50;

baseline

String. SQL baseline file path executed while provisioning the database.

Baseline files usually contain schema definitions, initial data, and stored procedures. The path is relative to the Ansible search path, usually files/.

If baseline is defined, the role runs that file on every provisioning pass for the database. It runs even if the database already exists, and after state: recreate. Make the SQL idempotent, or do not rerun it against an existing database.

- name: myapp
  baseline: myapp_schema.sql  # Looks for files/myapp_schema.sql

schemas

Array, mutable (add/remove). Schema definitions to create or drop. Elements can be strings or objects.

Simple format - strings for schema names (create only):

schemas:
  - app
  - api
  - core

Full format - objects for owner and drop operations:

schemas:
  - name: app                # Schema name (required)
    owner: dbuser_app        # Schema owner (optional), generates AUTHORIZATION clause
  - name: deprecated
    state: absent            # Drop schema (CASCADE)

Creation uses IF NOT EXISTS. Deletion uses CASCADE, which deletes all objects in the schema.

CREATE SCHEMA IF NOT EXISTS "app" AUTHORIZATION "dbuser_app";
DROP SCHEMA IF EXISTS "deprecated" CASCADE;

extensions

Array, mutable (add/remove). Extension definitions to install or uninstall. Elements can be strings or objects.

Simple format - strings for extension names (install only):

extensions:
  - postgis
  - pg_trgm
  - vector

Full format - objects for schema, version, and uninstall:

extensions:
  - name: vector             # Extension name (required)
    schema: public           # Install to schema (optional)
    version: '0.5.1'         # Specific version (optional)
  - name: old_extension
    state: absent            # Uninstall extension (CASCADE)

Installation uses IF NOT EXISTS ... CASCADE. If an extension exists, PostgreSQL reports a NOTICE and skips it. When possible, PostgreSQL installs dependencies automatically. Uninstallation uses CASCADE and deletes dependent objects.

CREATE EXTENSION IF NOT EXISTS "vector" WITH SCHEMA "public" VERSION '0.5.1' CASCADE;
DROP EXTENSION IF EXISTS "old_extension" CASCADE;

parameters

Object, mutable. Database-level config params via ALTER DATABASE ... SET. Applies to all sessions connecting to this database.

- name: analytics
  parameters:
    work_mem: '256MB'
    maintenance_work_mem: '512MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'

Use special value DEFAULT (case-insensitive) to reset to PostgreSQL default:

parameters:
  work_mem: DEFAULT          # Reset to default
  statement_timeout: '30s'   # Set new value