Module: PGSQL

• 1: Core Concepts
• 2: Configuration
• 2.1: Cluster & Instances
• 2.2: Kernel Version
• 2.3: Package Alias
• 2.4: User/Role
• 2.5: Database
• 2.6: HBA Rules
• 2.7: Access Control
• 3: Service/Access
• 4: Access Control
• 5: Administration
• 6: Administration
• 6.1: Troubleshooting
• 6.2: Database Management
• 6.3: HBA Management
• 6.4: SOP
• 6.5: Cluster Management
• 6.6: User Management
• 6.7: Parameter Tuning
• 6.8: Accidental Deletion
• 6.9: Clone Replicas
• 6.10: Maintenance
• 6.11: Version Upgrade
• 7: Backup & Restore
• 7.1: Backup Policy
• 7.2: Backup Mechanism
• 7.3: Backup Repository
• 7.4: Admin Commands
• 7.5: Restore Operations
• 7.6: Clone Database Cluster
• 7.7: Instance Recovery
• 7.8: Clone Database
• 7.9: Manual Recovery
• 8: Data Migration
• 9: Tutorials
• 9.1: HA Drill: Handling 2-of-3 Node Failure
• 9.2: Bind a L2 VIP to PostgreSQL Primary with VIP-Manager
• 9.3: Citus: Deploy HA Citus Cluster
• 10: Reference
• 11: Monitoring
• 11.1: Dashboards
• 11.2: Metrics List
• 12: Parameters
• 13: Playbook
• 14: Extensions
• 14.1: Quick Start
• 14.2: Introduction
• 14.3: Packages
• 14.4: Download
• 14.5: Install
• 14.6: Config
• 14.7: Create
• 14.8: Update
• 14.9: Remove
• 14.10: Default Extensions
• 14.11: Repository
• 15: PG Kernels
• 15.1: PostgreSQL
• 15.2: Supabase
• 15.3: Percona
• 15.4: OpenHalo
• 15.5: OrioleDB
• 15.6: Citus
• 15.7: Babelfish
• 15.8: IvorySQL
• 15.9: PolarDB PG
• 15.10: PolarDB Oracle
• 15.11: PostgresML
• 15.12: Greenplum
• 15.13: Cloudberry
• 15.14: Neon
• 16: FAQ
• 17: Misc
• 17.1: Service / Access
• 17.2: User / Role
• 17.3: Database
• 17.4: Authentication / HBA
• 17.5: Access Control

1 - Core Concepts

Core concepts and architecture design

2 - Configuration

Pigsty is a “configuration-driven” PostgreSQL platform: all behaviors come from the combination of inventory files in ~/pigsty/conf/*.yml and PGSQL parameters. Once you’ve written the configuration, you can replicate a customized cluster with instances, users, databases, access control, extensions, and tuning policies in just a few minutes.

Configuration Entry

1. Prepare Inventory: Copy a pigsty/conf/*.yml template or write an Ansible Inventory from scratch, placing cluster groups (all.children.<cls>.hosts) and global variables (all.vars) in the same file.
2. Define Parameters: Override the required PGSQL parameters in the vars block. The override order from global → cluster → host determines the final value.
3. Apply Configuration: Run ./configure -c <conf> or bin/pgsql-add <cls> and other playbooks to apply the configuration. Pigsty will generate the configuration files needed for Patroni/pgbouncer/pgbackrest based on the parameters.

Pigsty’s default demo inventory conf/pgsql.yml is a minimal example: one pg-meta cluster, global pg_version: 18, and a few business user and database definitions. You can expand with more clusters from this base.

Focus Areas & Documentation Index

Pigsty’s PostgreSQL configuration can be organized from the following dimensions. Subsequent documentation will explain “how to configure” each:

• 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.

After understanding these parameters, you can write declarative inventory manifests as “configuration as infrastructure” for any business requirement. Pigsty will handle execution and ensure idempotency.

A Typical Example

The following snippet shows how to control instance topology, kernel version, extensions, users, and databases in the same 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: 17
    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: 17 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.

Modify and apply this inventory to get a customized PostgreSQL cluster without manual configuration.

2.1 - Cluster & Instances

Choose the appropriate instance and cluster types based on your requirements to configure PostgreSQL database clusters that meet your needs.

You can define different types of instances and clusters. Here are several common PostgreSQL instance/cluster types in Pigsty:

• 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

We start with the simplest case: a single instance cluster consisting of one primary:

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

This configuration is concise and self-describing, consisting only of identity parameters. Note that the Ansible Group name should match pg_cluster.

Use the following command to create this cluster:

bin/pgsql-add pg-test

For demos, development testing, hosting temporary requirements, or performing non-critical analytical tasks, a single database instance may not be a big problem. However, such a single-node cluster has no high availability. When hardware failures occur, you’ll need to use PITR or other recovery methods to ensure the cluster’s RTO/RPO. For this reason, you may consider adding several read-only replicas to the cluster.

Replica

To add a read-only replica instance, you can add a new 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 entire cluster doesn’t exist, you can directly create the complete cluster. If the cluster primary has already been initialized, you can 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

When the cluster primary fails, the read-only instance (Replica) can take over the primary’s work with the help of the high availability system. Additionally, read-only instances can be used to execute read-only queries: many businesses have far more read requests than write requests, and most read-only query loads can be handled by replica instances.

Offline

Offline instances are dedicated read-only replicas specifically for serving slow queries, ETL, OLAP traffic, and interactive queries. Slow queries/long transactions have adverse effects on the performance and stability of online business, so it’s best to isolate them from online business.

To add an offline instance, assign it 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 work similarly to common replica instances, but they serve as backup servers in the pg-test-replica service. That is, only when all replica instances are down will the offline and primary instances provide this read-only service.

In many cases, database resources are limited, and using a separate server as an offline instance is not economical. As a compromise, you can select an existing replica instance and mark it with the pg_offline_query flag to indicate it can handle “offline queries”. In this case, this read-only replica will handle both online read-only requests and offline queries. You can use pg_default_hba_rules and pg_hba_rules for additional access control on offline instances.

Sync Standby

When Sync Standby is enabled, PostgreSQL will select one replica as the sync standby, with all other replicas as candidates. The primary database will wait for the standby instance to flush to disk before confirming commits. The standby instance always has the latest data with no replication lag, and primary-standby switchover to the sync standby will have no data loss.

PostgreSQL uses asynchronous streaming replication by default, which may have small replication lag (on the order of 10KB/10ms). When the primary fails, there may be a small data loss window (which can be controlled using pg_rpo), but this is acceptable for most scenarios.

However, in some critical scenarios (e.g., financial transactions), data loss is completely unacceptable, or read replication lag is unacceptable. In such cases, you can use synchronous commit to solve this problem. To enable sync standby mode, you can simply use the crit.yml template in 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

In this case, the PostgreSQL configuration parameter synchronous_standby_names is automatically managed by Patroni. One replica will be elected as the sync standby, and its application_name will be written to the PostgreSQL primary configuration file and applied.

Quorum Commit

Quorum Commit provides more powerful control than sync standby: especially when you have multiple replicas, you can set criteria for successful commits, achieving higher/lower consistency levels (and trade-offs with availability).

If you want at least two replicas to confirm commits, you can adjust the synchronous_node_count parameter through Patroni cluster configuration and apply it:

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

If you want to use more sync replicas, modify the synchronous_node_count value. When the cluster size changes, you should ensure this configuration is still 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 |
+-----------+-------------+--------------+---------+----+-----------+-----------------+

Another scenario is using any n replicas to confirm commits. In this case, the configuration is slightly different. For example, if we only need any one replica to confirm commits:

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.

Under normal circumstances, the standby cluster will follow the upstream cluster and keep content synchronized. You can promote the standby cluster to become a truly independent cluster.

The standby cluster definition is basically the same as a normal cluster definition, except that the pg_upstream parameter is additionally defined on the primary. The primary of the standby cluster is called the Standby Leader.

For example, below defines a pg-test cluster and its standby cluster pg-test2. The configuration inventory might look like this:

# 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 }

The primary node pg-test2-1 of the pg-test2 cluster will be a downstream replica of pg-test and serve as the Standby Leader in the pg-test2 cluster.

Just ensure the pg_upstream parameter is configured on the standby cluster’s primary node to automatically pull backups from the original upstream.

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, if you want a cluster named pg-testdelay whose data content is the same as the pg-test cluster from one hour ago:

# 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

When some tuples and tables are accidentally deleted, you can modify this parameter to advance this delayed cluster to an appropriate point in time, read data from it, and quickly fix the original cluster.

Delayed clusters require additional resources, but are much faster than PITR and have much less impact on the system. For very critical clusters, consider setting up delayed clusters.

Citus Cluster

Pigsty natively supports Citus. You can refer to files/pigsty/citus.yml and prod.yml as examples.

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.
• If you want to use pg_dbsu postgres instead of the default pg_admin_username to execute admin commands, then pg_dbsu_password must be set to a non-empty plaintext password

Additionally, extra hba rules are needed to allow SSL access from localhost and other data nodes. As shown below:

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 node, you can create distributed tables and reference tables and query them from any data node. Starting from 11.2, any Citus database node can act as a 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$$);

2.2 - Kernel Version

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

Pigsty supports PostgreSQL from version 10 onwards. The current version packages core software for versions 13-18 by default and provides a complete extension set for 17/18. The following content shows how to make these choices through configuration files.

Major Version and Packages

• pg_version: Specify the PostgreSQL major version (default 18). Pigsty will automatically map to the correct package name prefix based on the version.
• pg_packages: Define the core package set to install, supports using package aliases (default pgsql-main pgsql-common, includes kernel + patroni/pgbouncer/pgbackrest and other common tools).
• pg_extensions: List of additional extension packages to install, also supports aliases; defaults to empty meaning only core dependencies are installed.

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

Effect: Ansible will pull packages corresponding to pg_version=17 during installation, pre-install extensions to the system, and database initialization scripts can then directly CREATE EXTENSION.

Extension support varies across versions in Pigsty’s offline repository: 12/13 only provide core and tier-1 extensions, while 15/17/18 cover all extensions. If an extension is not pre-packaged, it can be added via repo_packages_extra.

Kernel Mode (pg_mode)

pg_mode controls the kernel “flavor” to deploy. Default pgsql indicates standard PostgreSQL. Pigsty currently supports the following modes:

After selecting a mode, Pigsty will automatically load corresponding templates, dependency packages, and Patroni configurations. For example, deploying Citus:

all:
  children:
    pg-citus0:
      hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus0, pg_group: 0 }
    pg-citus1:
      hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus1, pg_group: 1 }
  vars:
    pg_mode: citus
    pg_shard: pg-citus
    patroni_citus_db: meta

Effect: All members will install Citus-related packages, Patroni writes to etcd in shard mode, and automatically CREATE EXTENSION citus in the meta database.

Extensions and Pre-installed Objects

Besides system packages, you can control components automatically loaded after database startup through the following parameters:

• 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: Append ALTER SYSTEM SET for all instances (written to postgresql.auto.conf).

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

pg-analytics:
  vars:
    pg_cluster: pg-analytics
    pg_libs: 'timescaledb, pg_stat_statements, pgml'
    pg_default_extensions:
      - { name: timescaledb }
      - { name: pgvector }
    pg_parameters:
      timescaledb.max_background_workers: 8
      shared_preload_libraries: "'timescaledb,pg_stat_statements,pgml'"

Effect: During initialization, template1 creates extensions, Patroni’s postgresql.conf injects corresponding parameters, and all business databases inherit these settings.

Tuning Template (pg_conf)

pg_conf points to Patroni templates in roles/pgsql/templates/*.yml. Pigsty includes four built-in general templates:

You can directly replace the template or customize a YAML file in templates/, then specify it in 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'

Effect: Copy crit.yml as Patroni configuration, overlay pg_parameters written to postgresql.auto.conf, making instances run immediately in synchronous commit mode.

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, pgvector, pgml'
    pg_parameters:
      max_parallel_workers: 8
      shared_buffers: '32GB'

• First primary + one replica, using olap.yml tuning.
• Install PG18 + RAG common extensions, automatically load pgvector/pgml at system level.
• Patroni/pgbouncer/pgbackrest generated by Pigsty, no manual intervention needed.

Replace the above parameters according to business needs to complete all kernel-level customization.

2.3 - Package Alias

PostgreSQL package naming conventions vary significantly across different operating systems:

• EL systems (RHEL/Rocky/Alma/…) use formats like pgvector_17, postgis36_17*
• Debian/Ubuntu systems use formats like postgresql-17-pgvector, postgresql-17-postgis-3

This difference adds cognitive burden to users: you need to remember different package name rules for different systems, and handle the embedding of PostgreSQL version numbers.

Package Alias

Pigsty solves this problem through the Package Alias mechanism: you only need to use unified aliases, and Pigsty will handle all the details:

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

# Equivalent to actual package names on EL9 + PG17
pg_extensions: [ postgis36_17*, pgvector_17*, timescaledb-tsl_17* ]

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

Alias Translation

Aliases can also group a set of packages as a whole. For example, Pigsty’s default installed packages - the default value of pg_packages is:

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

Pigsty will query the current operating system alias list (assuming el10.x86_64) and translate it to PGSQL kernel, extensions, and toolkits:

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"

Next, Pigsty further translates pgsql-main using the currently specified PG major version (assuming 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*"

Through this approach, Pigsty shields the complexity of packages, allowing users to simply specify the functional components they want.

Which Variables Can Use Aliases?

You can use package aliases in the following four parameters, and the aliases will be automatically converted to actual package names according to the translation 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_packages_extra - Extension installation parameter: additional packages to download to local repository

Alias List

You can find the alias mapping files for each operating system and architecture in the roles/node_id/vars/ directory of the Pigsty project source code:

• el10.x86_64
• el10.aarch64
• el9.x86_64
• el9.aarch64
• el8.x86_64
• el8.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_17*
  postgis          u24.x86_64         postgresql-$v-postgis-3         postgresql-17-postgis-3

Version Placeholder

Pigsty’s alias system uses $v as a placeholder for the PostgreSQL version number. When you specify a PostgreSQL version using pg_version, all $v in aliases will be replaced with the actual version number.

For example, when pg_version: 17:

Wildcard Matching

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

• postgis36_17* will match postgis36_17, postgis36_17-client, postgis36_17-utils, etc.
• postgresql17* will match postgresql17, postgresql17-server, postgresql17-libs, postgresql17-contrib, etc.

This design ensures you don’t need to list each subpackage individually - one alias can install the complete extension.

2.4 - User/Role

In this context, user refers to logical objects created by the SQL command CREATE USER/ROLE within a database cluster.

In PostgreSQL, users belong directly to the database cluster rather than a specific database. Therefore, when creating business databases and business users, the principle of “users first, databases later” should be followed.

Define Users

Pigsty defines roles and users in database clusters through two config parameters:

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

The former defines roles and users shared across the entire env, while the latter defines business roles and users specific to a single cluster. Both have the same format as arrays of user definition objects.

You can define multiple users/roles. They will be created sequentially: first global, then cluster, and finally by array order. So later users can belong to roles defined earlier.

Here is the business user definition in the default pg-meta cluster in the Pigsty demo env:

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/role definition is an object that may include the following fields, using dbuser_meta user as an example:

- name: dbuser_meta               # Required, `name` is the only mandatory field
  state: create                   # Optional, user state: create (default), absent (delete)
  password: DBUser.Meta           # Optional, password, can be scram-sha-256 hash or plaintext
  login: true                     # Optional, can login by default
  superuser: false                # Optional, default false, is it a superuser?
  createdb: false                 # Optional, default false, can create databases?
  createrole: false               # Optional, default false, can create roles?
  inherit: true                   # Optional, can this role use inherited privileges by default?
  replication: false              # Optional, default false, can this role perform replication?
  bypassrls: false                # Optional, default false, can bypass row-level security?
  pgbouncer: true                 # Optional, default false, add to pgbouncer user list? (prod users should set to true)
  connlimit: -1                   # Optional, user connection limit, default -1 disables limit
  expire_in: 3650                 # Optional, expire after n days from creation (higher priority than expire_at)
  expire_at: '2030-12-31'         # Optional, expiration date in YYYY-MM-DD format (lower priority than expire_in)
  comment: pigsty admin user      # Optional, description and comment string
  roles: [dbrole_admin]           # Optional, default roles: dbrole_{admin,readonly,readwrite,offline}
  parameters:                     # Optional, role-level params via `ALTER ROLE SET`
    search_path: public           # e.g., set default search_path
  pool_mode: transaction          # Optional, pgbouncer pool mode, default transaction
  pool_connlimit: -1              # Optional, user-level max pool connections, -1 disables limit

• The only required field is name, which should be a valid and unique username in the PostgreSQL cluster.
• Username must match regex ^[a-z_][a-z0-9_]{0,62}$ (lowercase letters, digits, underscores, starts with letter or underscore, max 63 chars).
• Roles don’t need password, but for login-able business users, a password is usually needed.
• password can be plaintext or scram-sha-256 / md5 hash string. Please avoid using plaintext passwords.
• Users/roles are created sequentially in array order, so ensure role/group definitions come before their members.
• login, superuser, createdb, createrole, inherit, replication, bypassrls are boolean flags.
• pgbouncer is disabled by default: to add business users to the pgbouncer user list, you should explicitly set it to true.

Parameter Overview

Mutability Notes

Basic Parameters

name

• Type: string
• Mutability: Required
• Description: Username, unique identifier within cluster

Username must be a valid PostgreSQL identifier matching regex ^[a-z_][a-z0-9_]{0,62}$:

• Starts with lowercase letter or underscore
• Contains only lowercase letters, digits, underscores
• Max 63 characters

- name: dbuser_app         # standard naming
- name: app_readonly       # underscore separated
- name: _internal          # underscore prefix (for internal roles)

state

• Type: enum
• Mutability: Optional
• Default: create
• Values: create, absent
• Description: Target user state

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

Note: These system users cannot be deleted via state: absent:

• postgres (superuser)
• replicator (or pg_replication_username configured user)
• dbuser_dba (or pg_admin_username configured user)
• dbuser_monitor (or pg_monitor_username configured user)

password

• Type: string
• Mutability: Mutable
• Default: None
• Description: User password

Password can be one of:

• Plaintext: DBUser.Meta (not recommended for prod)
• SCRAM-SHA-256 hash: SCRAM-SHA-256$4096:... (recommended)
• MD5 hash: md5... (legacy compatibility)

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

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

comment

• Type: string
• Mutability: Mutable
• Default: business user {name}
• Description: User comment/description

Executes COMMENT ON ROLE statement. Supports special chars (single quotes auto-escaped).

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

Privilege Parameters

login

• Type: bool
• Mutability: Mutable
• Default: true
• Description: Can login

Set to false creates a Role rather than User, typically used for permission grouping.

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

# Create role (no login)
- name: dbrole_custom
  login: false

superuser

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Is superuser

createdb

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Can create databases

createrole

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Can create roles

inherit

• Type: bool
• Mutability: Mutable
• Default: true
• Description: Auto-inherit privileges from member roles

Set to false requires explicit SET ROLE to use inherited privileges.

replication

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Can initiate streaming replication

Usually only replication users (like replicator) need this privilege.

bypassrls

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Can bypass row-level security (RLS) policies

connlimit

• Type: int
• Mutability: Mutable
• Default: -1 (no limit)
• Description: Max concurrent connections for user

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

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

Validity Parameters

expire_in

• Type: int
• Mutability: Mutable
• Description: Expire N days from current date

This param has higher priority than expire_at. Expiration time recalculated on each playbook run.

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

- name: long_term_user
  expire_in: 3650          # expire in ~10 years

expire_at

• Type: string
• Mutability: Mutable
• Description: Specify expiration date

Format YYYY-MM-DD or special value infinity (never expires).

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

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

Note: expire_in has higher priority than expire_at. If both specified, only expire_in takes effect.

Role Membership Parameter

roles

• Type: array
• Mutability: Incremental
• Description: Roles this user belongs to

roles array supports two formats:

Simple Format (String)

- name: dbuser_app
  roles:
    - dbrole_readwrite
    - pg_read_all_data

Generated SQL:

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

Extended Format (Object)

Object format supports finer-grained role membership control:

- name: dbuser_app
  roles:
    - dbrole_readwrite                              # simple string: GRANT role
    - { name: dbrole_admin, admin: true }           # GRANT WITH ADMIN OPTION
    - { name: pg_monitor, set: false }              # PG16+: REVOKE SET OPTION
    - { name: pg_signal_backend, inherit: false }   # PG16+: REVOKE INHERIT OPTION
    - { 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 (can only inherit privileges)
    - { name: pg_monitor, set: false }

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

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

Note: set and inherit options only work in PostgreSQL 16+. On earlier versions they’re ignored with warning comments.

Role-Level Parameters

parameters

• Type: object
• Mutability: Mutable
• Description: Role-level config parameters

Set via ALTER ROLE ... SET, params apply to all sessions for this user.

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

Generated SQL:

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';

Reset Parameter to Default

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

- name: dbuser_app
  parameters:
    work_mem: DEFAULT         # reset to PostgreSQL default
    statement_timeout: '30s'  # set new value

Common Role-Level Parameters

Connection Pool Parameters

These params control user behavior in Pgbouncer connection pool.

pgbouncer

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Add user to Pgbouncer user list

# 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

pool_mode

• Type: enum
• Mutability: Mutable
• Values: transaction, session, statement
• Default: transaction
• Description: User-level pool mode

# 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

pool_connlimit

• Type: int
• Mutability: Mutable
• Default: -1 (no limit)
• Description: User-level max pool connections

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

ACL System

Pigsty has a built-in, out-of-the-box access control / ACL system. You only need to assign these four default roles to business users:

• dbrole_readwrite: Global read-write access role (primary business prod accounts should have this)
• dbrole_readonly: Global read-only access role (for other businesses needing read-only access)
• dbrole_admin: DDL privileges role (business admins, scenarios requiring table creation in apps)
• dbrole_offline: Restricted read-only role (can only access offline instances, typically for individual users)

If you want to redesign your own ACL system, consider customizing:

• pg_default_roles: System-wide roles and global users
• pg_default_privileges: Default privileges for newly created objects
• roles/pgsql/templates/pg-init-role.sql: Role creation SQL template
• roles/pgsql/templates/pg-init-template.sql: Privilege SQL template

Pgbouncer Users

Pgbouncer is enabled by default as connection pool middleware, with users managed automatically.

Pigsty adds all users in pg_users with explicit pgbouncer: true flag to the pgbouncer user list.

Users in Pgbouncer 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 connection 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, Pgbouncer’s user list definition file will be refreshed and take effect via online config reload, without affecting existing connections.

Pgbouncer runs with the same dbsu as PostgreSQL, defaulting to the postgres OS user. You can use the pgb alias to access pgbouncer admin functions using dbsu.

Note that the pgbouncer_auth_query param allows dynamic query for connection pool user auth—a compromise when you’re lazy about managing pool users.

For user management operations, see User Management.

For user access privileges, see ACL: Role Privileges.

2.5 - Database

In this context, database refers to logical objects created by the SQL command CREATE DATABASE within a database cluster.

A PostgreSQL server can serve multiple databases simultaneously. In Pigsty, you can define the required databases in the cluster config.

Pigsty modifies and customizes the default template database template1, creating default schemas, installing default extensions, and configuring default privileges. Newly created databases will inherit these settings from template1 by default.

By default, all business databases are added 1:1 to the Pgbouncer connection pool; pg_exporter will automatically discover all business databases through an auto-discovery mechanism and monitor objects within them.

Define Database

Business databases are defined in the cluster parameter pg_databases, which is an array of database definition objects. Databases in the array are created sequentially in definition order, so databases defined later can use previously defined databases as templates.

Here is the database definition in the default pg-meta cluster in the Pigsty demo env:

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 is an object that may include the following fields, using the meta database as an example:

- name: meta                      # Required, `name` is the only mandatory field
  state: create                   # Optional, database state: create (default), absent (delete), recreate (rebuild)
  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, schemas to create, array of schema names
  extensions:                     # Optional, extensions to install: array of extension objects
    - { name: postgis , schema: public }  # can specify schema for extension
    - { name: timescaledb }               # some extensions create their own fixed schemas
  comment: pigsty meta database   # Optional, database comment
  owner: postgres                 # Optional, database owner, default postgres
  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 rule (PG15+)
  icu_rules: ''                   # Optional, ICU collation rule (PG16+)
  builtin_locale: C.UTF-8         # Optional, builtin locale provider rule (PG17+)
  tablespace: pg_default          # Optional, default tablespace, default 'pg_default'
  is_template: false              # Optional, mark as template database, allows any user with CREATEDB to clone
  allowconn: true                 # Optional, allow connections, default true. Explicit false prohibits all connections
  revokeconn: false               # Optional, revoke public connect privilege. Default false, true revokes CONNECT from non-owner/admin
  register_datasource: true       # Optional, register as grafana datasource? Default true, false skips registration
  connlimit: -1                   # Optional, connection limit, default -1 (no limit)
  parameters:                     # Optional, database-level params via ALTER DATABASE SET
    work_mem: '64MB'
    statement_timeout: '30s'
  pool_auth_user: dbuser_meta     # Optional, auth user for this pgbouncer database (requires pgbouncer_auth_query)
  pool_mode: transaction          # Optional, database-level pgbouncer pool mode, default transaction
  pool_size: 64                   # Optional, database-level pool size, default 64
  pool_reserve: 32                # Optional, database-level pool reserve, default 32
  pool_size_min: 0                # Optional, database-level min pool size, default 0
  pool_connlimit: 100             # Optional, database-level max connections, default 100

The only required field is name, which should be a valid and unique database name in the current PostgreSQL cluster.

Parameter Overview

Mutability Notes

Basic Parameters

name

• Type: string
• Mutability: Required
• Description: Database name, unique identifier within cluster

Database name should be a valid PostgreSQL identifier. Recommend lowercase letters, digits, and underscores.

- name: myapp              # simple naming
- name: my_application     # underscore separated
- name: app_v2             # with version number

state

• Type: enum
• Mutability: Optional
• Default: create
• Values: create, absent, recreate
• Description: Target database state

- name: myapp                # state defaults to create
- name: olddb
  state: absent              # delete database
- name: testdb
  state: recreate            # rebuild database

owner

• Type: string
• Mutability: Mutable
• Default: postgres (current user)
• Description: Database owner

Specified user must already exist. Changing owner executes:

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

comment

• Type: string
• Mutability: Mutable
• Default: business database {name}
• Description: Database comment

Executes COMMENT ON DATABASE statement. Supports special chars (single quotes auto-escaped).

Template & Clone Parameters

template

• Type: string
• Mutability: Immutable
• Default: template1
• Description: Template database for creation

Common templates:

Important: When using icu or builtin locale provider, you must specify template: template0.

- name: myapp_icu
  template: template0        # required when using ICU
  locale_provider: icu
  icu_locale: en-US

strategy

• Type: enum
• Mutability: Immutable
• Version: PostgreSQL 15+
• Values: FILE_COPY, WAL_LOG
• Description: Strategy for cloning from template

Ignored on PostgreSQL 14 and earlier versions.

Encoding & Locale Parameters

encoding

• Type: string
• Mutability: Immutable
• Default: Inherits from template (usually UTF8)
• Description: Database character encoding

Common encodings: UTF8, LATIN1, SQL_ASCII

locale

• Type: string
• Mutability: Immutable
• Default: Inherits from template (usually C)
• Description: Database locale rule, sets both lc_collate and lc_ctype

lc_collate

• Type: string
• Mutability: Immutable
• Default: Inherits from template (usually C)
• Description: String collation rule

Common values: C, C.UTF-8, en_US.UTF-8, zh_CN.UTF-8

lc_ctype

• Type: string
• Mutability: Immutable
• Default: Inherits from template (usually C)
• Description: Character classification rule (upper/lower case, digits, etc.)

locale_provider

• Type: enum
• Mutability: Immutable
• Version: PostgreSQL 15+
• Values: libc, icu, builtin
• Default: libc
• Description: Locale implementation provider

Note: When using icu or builtin, you must specify template: template0.

icu_locale

• Type: string
• Mutability: Immutable
• Version: PostgreSQL 15+
• Description: ICU locale identifier

Common values:

- name: chinese_db
  template: template0
  locale_provider: icu
  icu_locale: zh-Hans
  encoding: UTF8

icu_rules

• Type: string
• Mutability: Immutable
• Version: PostgreSQL 16+
• Description: ICU collation custom rule

Used to customize sorting behavior with ICU rule syntax.

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

builtin_locale

• Type: string
• Mutability: Immutable
• Version: PostgreSQL 17+
• Values: C, C.UTF-8
• Description: Builtin locale provider rule

builtin provider is faster than libc, especially suitable when only C or C.UTF-8 collation is needed.

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

Storage & Privilege Parameters

tablespace

• Type: string
• Mutability: Mutable
• Default: pg_default
• Description: Database default tablespace

Changing tablespace triggers physical data migration, may take long time for large databases.

- name: archive_db
  tablespace: slow_hdd       # use slow storage for archive data

is_template

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Mark as template database

When set to true, any user with CREATEDB privilege can use this database as template to clone new databases.

- name: app_template
  is_template: true          # allow normal users to clone
  schemas: [core, api]
  extensions: [postgis]

Note: Databases marked is_template: true will first execute ALTER DATABASE ... IS_TEMPLATE false when deleted.

allowconn

• Type: bool
• Mutability: Mutable
• Default: true
• Description: Allow connections to this database

Setting to false completely prohibits any user from connecting (including superusers).

- name: archive_db
  allowconn: false           # prohibit connections

revokeconn

• Type: bool
• Mutability: Mutable
• Default: false
• Description: Revoke PUBLIC CONNECT privilege

When set to true:

• Revokes PUBLIC CONNECT privilege
• Grants replicator, monitor connection privilege
• Grants admin, owner connection privilege (WITH GRANT OPTION)

When set to false:

• Restores PUBLIC CONNECT privilege

- name: secure_db
  owner: dbuser_secure
  revokeconn: true           # only specified users can connect

connlimit

• Type: int
• Mutability: Mutable
• Default: -1 (no limit)
• Description: Database max connection limit

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

Initialization Parameters

baseline

• Type: string
• Mutability: One-time
• Description: SQL baseline file path

Specifies SQL file to execute after database creation for initializing table structure, data, etc.

• Path is relative to Ansible search path (usually files/ directory)
• Only executes on first database creation
• Re-executes when using state: recreate

- name: myapp
  baseline: myapp_init.sql   # will search files/myapp_init.sql

schemas

• Type: (string | object)[]
• Mutability: Incremental
• Description: Schema definitions to create

Supports two formats:

schemas:
  # Simple format: schema name only
  - app
  - api

  # Full format: object definition
  - name: core               # schema name (required)
    owner: dbuser_app        # schema owner (optional)
  - name: old_schema
    state: absent            # delete schema

Schema owner: Use owner to specify schema owner, generates AUTHORIZATION clause:

- name: myapp
  owner: dbuser_myapp
  schemas:
    - name: app
      owner: dbuser_myapp    # schema owner same as database owner
    - name: audit
      owner: dbuser_audit    # schema owner is different user

Generated SQL:

CREATE SCHEMA IF NOT EXISTS "app" AUTHORIZATION "dbuser_myapp";
CREATE SCHEMA IF NOT EXISTS "audit" AUTHORIZATION "dbuser_audit";

Delete schema: Use state: absent to delete schema:

schemas:
  - { name: deprecated_schema, state: absent }

Generated SQL:

DROP SCHEMA IF EXISTS "deprecated_schema" CASCADE;

Note:

• Create operations are incremental, uses IF NOT EXISTS
• Delete operations use CASCADE, deletes all objects in schema

extensions

• Type: object[]
• Mutability: Incremental
• Description: Extension definitions to install

Supports two formats:

extensions:
  # Simple format: extension name only
  - postgis
  - pg_trgm

  # Full format: object definition
  - name: vector             # extension name (required)
    schema: public           # install to specified schema (optional)
    version: '0.5.1'         # specify version (optional)
    state: absent            # set absent to uninstall extension (optional)