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 simultaneously. In Pigsty, you can define required databases in cluster configuration.

Pigsty customizes the template1 template database - creating default schemas, installing default extensions, configuring default privileges. Newly created databases inherit these settings from template1. You can also specify other template databases via template for instant database cloning.

By default, all business databases are 1:1 added to Pgbouncer connection pool; pg_exporter auto-discovers all business databases for in-database object monitoring. All databases are also registered as PostgreSQL datasources in Grafana on all INFRA nodes for PGCAT dashboards.

Define Database

Business databases are defined in cluster param pg_databases, an array of database definition objects. During cluster initialization, databases are created in definition order, so later databases can use earlier ones as templates.

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 is a complex object with 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, revoke public CONNECT privilege, default false
  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: 64                   # Optional, database-level pgbouncer default pool size
  pool_reserve: 32                # 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

Since Pigsty v4.1.0, database pool fields are unified as pool_reserve and pool_connlimit; legacy aliases pool_size_reserve / pool_max_db_conn are converged.

Parameter Overview

The only required field is name - a valid, unique database name within the cluster. All other params have sensible defaults. Parameters marked “Immutable” only take effect at creation; changing them requires database recreation.

Parameter Details

name

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

Must be a valid PostgreSQL identifier: max 63 chars, no SQL keywords, starts with letter or underscore, followed by letters, digits, or underscores. Must match: ^[A-Za-z_][A-Za-z0-9_$]{0,62}$

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

Target user must exist. Changing owner executes (old owner retains existing privileges):

Database owner has full control including creating schemas, tables, extensions - useful for multi-tenant scenarios.

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

Set via COMMENT ON DATABASE, supports Chinese and special chars (Pigsty auto-escapes quotes). Stored in pg_database.datacl, viewable via \l+.

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.

PostgreSQL’s CREATE DATABASE clones the template - new database inherits all objects, extensions, schemas, permissions. Pigsty customizes template1 during cluster init, so new databases inherit these settings.

When using icu or builtin locale provider, must specify template: template0 since template1 locale settings can’t be overridden.

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

Using template0 skips monitoring extensions/schemas and default privileges - allowing fully custom database.

strategy

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

WAL_LOG doesn’t block template connections during clone but less efficient for large templates. Ignored on PG14 and earlier.

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

encoding

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

Strongly recommend UTF8 unless special requirements. Cannot be changed 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).

Determines string sort order and character classification. Use C or POSIX for best performance and cross-platform consistency; use language-specific locales (e.g., zh_CN.UTF-8) for proper language sorting.

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

Determines ORDER BY and comparison results. Common values: C (byte order, fastest), C.UTF-8, en_US.UTF-8, zh_CN.UTF-8. Cannot be changed 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).

Affects upper(), lower(), regex \w, etc. Cannot be changed after creation.

locale_provider

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

Using icu or builtin requires template: template0 with 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+.

Allows fine-tuning default sort behavior using ICU Collation Customization.

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

builtin provider is PG17’s new builtin implementation - faster than libc with consistent cross-platform behavior. Suitable for C/C.UTF-8 collation only.

- 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 tablespace triggers physical data migration - PostgreSQL moves all objects to new tablespace. Can take long time for large databases, use cautiously.

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

When true, any user with CREATEDB privilege can use this database as template for cloning. Template databases typically pre-install standard schemas, extensions, and data.

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

Deleting is_template: true databases: Pigsty first executes ALTER DATABASE ... IS_TEMPLATE false then drops.

allowconn

Boolean, mutable. Allow connections, default true.

Setting false completely disables connections at database level - no user (including superuser) can connect. Used for maintenance or archival purposes.

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

Positive integer limits max simultaneous sessions. Doesn’t affect superusers.

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

ALTER DATABASE "limited_db" CONNECTION LIMIT 50;

baseline

String, one-time. SQL baseline file path executed after database creation.

Baseline files typically contain schema definitions, initial data, stored procedures. Path is relative to Ansible search path, usually in files/.

Baseline runs only on first creation; skipped if database exists. state: recreate re-runs baseline.

- 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)

Create uses IF NOT EXISTS; drop uses CASCADE (deletes all objects in 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)

Install uses CASCADE to auto-install dependencies; uninstall uses CASCADE (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

ALTER DATABASE "myapp" SET "work_mem" = DEFAULT;
ALTER DATABASE "myapp" SET "statement_timeout" = '30s';

pgbouncer

Boolean, mutable. Add database to Pgbouncer pool list, default true.

Setting false excludes database from Pgbouncer - clients can’t access via connection pool. For internal management databases or direct-connect scenarios.

- name: internal_db
  pgbouncer: false           # No connection pool access

pool_mode

Enum, mutable. Pgbouncer pool mode: transaction, session, or statement. Default transaction.

- name: session_app
  pool_mode: session         # Session-level pooling

pool_size

Integer, mutable. Pgbouncer default pool size, default 64.

Pool size determines backend connections reserved for this database. Adjust based on workload.

- name: high_load_db
  pool_size: 128             # Larger pool for high load

pool_size_min

Integer, mutable. Pgbouncer minimum pool size, default 0.

Values > 0 pre-create specified backend connections for connection warming, reducing first-request latency.

- name: latency_sensitive
  pool_size_min: 10          # Pre-warm 10 connections

pool_reserve

Integer, mutable. Pgbouncer reserve pool size, default 32.

When default pool exhausted, Pgbouncer can allocate up to pool_reserve additional connections for burst traffic.

- name: bursty_db
  pool_size: 64
  pool_reserve: 64           # Allow burst to 128 connections

pool_connlimit

Integer, mutable. Max connections via Pgbouncer pool, default 100.

This is Pgbouncer-level limit, independent of database’s connlimit param.

- name: limited_pool_db
  pool_connlimit: 50         # Pool max 50 connections

pool_auth_user

String, mutable. User for Pgbouncer auth query.

Requires pgbouncer_auth_query enabled. When set, all Pgbouncer connections to this database use specified user for auth query password verification.

- name: myapp
  pool_auth_user: dbuser_monitor  # Use monitor user for auth query

register_datasource

Boolean, mutable. Register database to Grafana as PostgreSQL datasource, default true.

Set false to skip Grafana registration. For temp databases, test databases, or internal databases not needed in monitoring.

- name: temp_db
  register_datasource: false  # Don't register to Grafana

Template Inheritance

Many parameters inherit from template database if not explicitly specified. Default template is template1, whose encoding settings are determined by cluster init params:

New databases fork from template1, which is customized during PG_PROVISION with extensions, schemas, and default privileges. Unless you explicitly use another template.

Deep Customization

Pigsty provides rich customization params. To customize template database, refer to:

• pg_default_roles: Default predefined roles and system users
• pg_default_privileges: Default privileges for objects created by admin user
• pg_default_schemas: Default schemas to create
• pg_default_extensions: Default extensions to create
• pg_default_hba_rules: Default PostgreSQL HBA rules
• pgb_default_hba_rules: Default Pgbouncer HBA rules

If above configurations don’t meet your needs, use pg_init to specify custom cluster init scripts:

• pg-init: Cluster init script
• pg-init-template.sql: Template customization SQL
• pg-init-roles.sql: Default roles SQL

Locale Providers

PostgreSQL 15+ introduced locale_provider for different locale implementations. These are immutable after creation.

Pigsty’s configure wizard selects builtin C.UTF-8/C locale provider based on PG and OS versions. Databases inherit cluster locale by default. To specify different locale provider, you must use template0.

Using ICU provider (PG15+):

- name: myapp_icu
  template: template0        # ICU requires template0
  locale_provider: icu
  icu_locale: en-US          # ICU locale rules
  encoding: UTF8

Using builtin provider (PG17+):

- name: myapp_builtin
  template: template0
  locale_provider: builtin
  builtin_locale: C.UTF-8    # Builtin locale rules
  encoding: UTF8

Provider comparison: libc (traditional, OS-dependent), icu (PG15+, cross-platform, feature-rich), builtin (PG17+, most efficient C/C.UTF-8).

Connection Pool

Pgbouncer connection pool optimizes short-connection performance, reduces contention, prevents excessive connections from overwhelming database, and provides flexibility during migrations.

Pigsty configures 1:1 connection pool for each PostgreSQL instance, running as same pg_dbsu (default postgres OS user). Pool communicates with database via /var/run/postgresql Unix socket.

Pigsty adds all databases in pg_databases to pgbouncer by default. Set pgbouncer: false to exclude specific databases. Pgbouncer database list and config params are defined in /etc/pgbouncer/database.txt:

meta                        = host=/var/run/postgresql mode=session
grafana                     = host=/var/run/postgresql mode=transaction
bytebase                    = host=/var/run/postgresql auth_user=dbuser_meta
kong                        = host=/var/run/postgresql pool_size=32 reserve_pool=64
gitea                       = host=/var/run/postgresql min_pool_size=10
wiki                        = host=/var/run/postgresql
noco                        = host=/var/run/postgresql
mongo                       = host=/var/run/postgresql

When creating databases, Pgbouncer database list is refreshed via online reload - doesn’t affect existing connections.