Administration

• 1: Managing PostgreSQL Clusters
• 2: Managing PostgreSQL Users
• 3: Managing PostgreSQL Databases
• 4: Patroni HA Management
• 5: Pgbouncer Connection Pooling
• 6: Managing PostgreSQL Component Services
• 7: Manage PostgreSQL Cron Jobs
• 8: Managing PostgreSQL Extensions
• 9: Upgrading PostgreSQL Major/Minor Versions

1 - Managing PostgreSQL Clusters

Quick Reference

For other management tasks, see: HA Management, Manage Users, Manage Databases.

Create Cluster

To create a new PostgreSQL cluster, first define the cluster in the inventory, then add nodes and initialize:

• Script
• Playbook
• Example

bin/node-add  <cls>     # Add nodes in group <cls>

./node.yml  -l <cls>    # Use Ansible playbook to add nodes in group <cls>

bin/pgsql-add pg-test   # Add nodes in pg-test group, runs ./node.yml -l pg-test

On managed nodes, create the cluster with: (Execute pgsql.yml playbook on <cls> group)

• Script
• Playbook
• Example

bin/pgsql-add <cls>     # Create PostgreSQL cluster <cls>

./pgsql.yml -l <cls>    # Use Ansible playbook to create PostgreSQL cluster <cls>

bin/pgsql-add pg-test   # Create pg-test cluster

Example: Create 3-node PG cluster pg-test

Expand Cluster

To add a new replica to an existing PostgreSQL cluster, add the instance definition to inventory: all.children.<cls>.hosts.

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary } # existing member
    10.10.10.12: { pg_seq: 2, pg_role: replica } # existing member
    10.10.10.13: { pg_seq: 3, pg_role: replica } # <--- new member
  vars: { pg_cluster: pg-test }

Scaling out is similar to creating a cluster. First add the new node to Pigsty: Add Node:

• Script
• Playbook
• Example

bin/node-add <ip>       # Add node with IP <ip>

./node.yml -l <ip>      # Use Ansible playbook to add node <ip>

bin/node-add 10.10.10.13    # Add node 10.10.10.13, runs ./node.yml -l 10.10.10.13

Then run the following on the new node to scale out (Install PGSQL module on new node with same pg_cluster):

• Script
• Playbook
• Example

bin/pgsql-add <cls> <ip>  # Add node <ip> to cluster

./pgsql.yml -l <ip>       # Core: Use Ansible playbook to install PGSQL module on <ip>

bin/pgsql-add pg-test 10.10.10.13   # Scale out pg-test with node 10.10.10.13

After scaling, you should Reload Service to add the new member to load balancer.

Example: Add replica 10.10.10.13 to 2-node cluster pg-test

Shrink Cluster

To remove a replica from an existing PostgreSQL cluster, remove the instance definition from inventory all.children.<cls>.hosts.

First uninstall PGSQL module from target node (Execute pgsql-rm.yml on <ip>):

• Script
• Playbook
• Example

bin/pgsql-rm <cls> <ip>   # Remove PostgreSQL instance on <ip> from cluster <cls>

./pgsql-rm.yml -l <ip>    # Use Ansible playbook to remove PostgreSQL instance on <ip>

bin/pgsql-rm pg-test 10.10.10.13  # Remove 10.10.10.13 from pg-test cluster

After removing PGSQL module, optionally remove the node from Pigsty: Remove Node:

• Script
• Playbook
• Example

bin/node-rm <ip>          # Remove node <ip> from Pigsty management

./node-rm.yml -l <ip>     # Use Ansible playbook to remove node <ip>

bin/node-rm 10.10.10.13   # Remove node 10.10.10.13 from Pigsty

After scaling in, remove the instance from inventory, then Reload Service to remove it from load balancer.

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 } # <--- remove after execution
  vars: { pg_cluster: pg-test }

Example: Remove replica 10.10.10.13 from 3-node cluster pg-test

Remove Cluster

To destroy a cluster, uninstall PGSQL module from all nodes (Execute pgsql-rm.yml on <cls>):

• Script
• Playbook
• Example

bin/pgsql-rm <cls>        # Destroy entire PostgreSQL cluster <cls>

./pgsql-rm.yml -l <cls>   # Use Ansible playbook to destroy cluster <cls>

bin/pgsql-rm pg-test      # Destroy pg-test cluster

After destroying PGSQL, optionally remove all nodes from Pigsty: Remove Node (optional if other services exist):

• Script
• Playbook
• Example

bin/node-rm <cls>         # Remove all nodes in group <cls> from Pigsty

./node-rm.yml -l <cls>    # Use Ansible playbook to remove nodes in group <cls>

bin/node-rm pg-test       # Remove all pg-test nodes from Pigsty

After removal, delete the entire cluster definition from inventory.

pg-test: # remove this cluster definition group
  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 }

Example: Destroy 3-node PG cluster pg-test

Note: If pg_safeguard is configured (or globally true), pgsql-rm.yml will abort to prevent accidental removal. Override with playbook command line to force removal. By default, cluster backup repo is deleted with the cluster. To preserve backups (e.g., with centralized repo), set pg_rm_backup=false:

./pgsql-rm.yml -l pg-meta -e pg_safeguard=false    # force remove protected cluster pg-meta
./pgsql-rm.yml -l pg-meta -e pg_rm_backup=false    # preserve backup repo during removal

Reload Service

PostgreSQL clusters expose services via HAProxy on host nodes. When service definitions change, instance weights change, or cluster membership changes (e.g., scale out/scale in, switchover/failover), reload services to update load balancer config.

To reload service config on entire cluster or specific instances (Execute pg_service subtask of pgsql.yml on <cls> or <ip>):

• Script
• Playbook
• Example

bin/pgsql-svc <cls>           # Reload service config for entire cluster <cls>
bin/pgsql-svc <cls> <ip...>   # Reload service config for specific instances

./pgsql.yml -l <cls> -t pg_service -e pg_reload=true        # Reload entire cluster
./pgsql.yml -l <ip>  -t pg_service -e pg_reload=true        # Reload specific instance

bin/pgsql-svc pg-test                 # Reload pg-test cluster service config
bin/pgsql-svc pg-test 10.10.10.13     # Reload pg-test 10.10.10.13 instance service config

Note: If using dedicated load balancer cluster (pg_service_provider), only reloading cluster primary updates the LB config.

Example: Reload pg-test cluster service config

Reload HBA

When HBA configs change, reload HBA rules to apply. (pg_hba_rules / pgb_hba_rules) If you have role-specific HBA rules or IP ranges referencing cluster member aliases, reload HBA after switchover/scaling.

To reload PG and Pgbouncer HBA rules on entire cluster or specific instances (Execute HBA subtasks of pgsql.yml on <cls> or <ip>):

• Script
• Playbook
• Example

bin/pgsql-hba <cls>           # Reload HBA rules for entire cluster <cls>
bin/pgsql-hba <cls> <ip...>   # Reload HBA rules for specific instances

./pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true   # Reload entire cluster
./pgsql.yml -l <ip>  -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true   # Reload specific instance

bin/pgsql-hba pg-test                 # Reload pg-test cluster HBA rules
bin/pgsql-hba pg-test 10.10.10.13     # Reload pg-test 10.10.10.13 instance HBA rules

Example: Reload pg-test cluster HBA rules

Config Cluster

PostgreSQL config params are managed by Patroni. Initial params are specified by Patroni config template. After cluster init, config is stored in Etcd, dynamically managed and synced by Patroni. Most Patroni config params can be modified via patronictl. Other params (e.g., etcd DCS config, log/RestAPI config) can be updated via subtasks. For example, when etcd cluster membership changes, refresh Patroni config:

./pgsql.yml -l pg-test -t pg_conf                   # Update Patroni config file
ansible pg-test -b -a 'systemctl reload patroni'    # Reload Patroni service

You can override Patroni-managed defaults at different levels: specify params per instance, specify params per user, or specify params per database.

Clone Cluster

Two ways to clone a cluster: use Standby Cluster, or use Point-in-Time Recovery. The former is simple with no dependencies but only clones latest state; the latter requires centralized backup repository (e.g., MinIO) but can clone to any point within retention period.

Clone via Standby Cluster

Standby Cluster continuously syncs from upstream cluster via streaming replication - the simplest cloning method. Specify pg_upstream on the new cluster primary to auto-pull data from upstream.

# 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 standby cluster (clone) of pg-test
pg-test2:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary, pg_upstream: 10.10.10.11 }  # specify upstream
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-test2 }

Create standby cluster with:

• Script
• Playbook

bin/pgsql-add pg-test2    # Create standby cluster, auto-clone from upstream pg-test

./pgsql.yml -l pg-test2   # Use Ansible playbook to create standby cluster

Standby cluster follows upstream, keeping data in sync. Promote to independent cluster anytime:

$ 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 edit-config pg-test2

 standby_cluster:
   create_replica_methods:
   - basebackup
-  host: 10.10.10.11     # <--- old upstream
+  host: 10.10.10.14     # <--- new upstream
   port: 5432

Apply these changes? [y/N]: y

Clone via PITR

Point-in-Time Recovery (PITR) allows recovery to any point within backup retention. Requires centralized backup repository (MinIO/S3), but more powerful.

To clone via PITR, add pg_pitr param specifying recovery target:

# Clone new cluster pg-meta2 from pg-meta backup
pg-meta2:
  hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr:
      cluster: pg-meta                    # Recover from pg-meta backup
      time: '2025-01-10 10:00:00+00'      # Recover to specific time

Execute clone with pgsql-pitr.yml playbook:

• Playbook
• CLI

./pgsql-pitr.yml -l pg-meta2    # Clone pg-meta2 from pg-meta backup

# Specify PITR options via command line
./pgsql-pitr.yml -l pg-meta2 -e '{"pg_pitr": {"cluster": "pg-meta", "time": "2025-01-10 10:00:00+00"}}'

PITR supports multiple recovery target types:

psql -c 'ALTER SYSTEM RESET archive_mode; SELECT pg_reload_conf();'
pg-backup full    # Execute new full backup

For detailed PITR usage, see Restore Operations documentation.

2 - Managing PostgreSQL Users

Quick Start

Pigsty uses declarative management: first define users in the inventory, then use bin/pgsql-user <cls> <username> to create or modify.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users: [{ name: dbuser_app, password: 'DBUser.App', pgbouncer: true }]  # <--- Define user list here!

• Script
• Playbook
• Example

bin/pgsql-user <cls> <username>    # Create/modify <username> user on <cls> cluster

./pgsql-user.yml -l pg-meta -e username=dbuser_app    # Use playbook to create/modify user

bin/pgsql-user pg-meta dbuser_app    # Create/modify dbuser_app user on pg-meta cluster

For complete user definition reference, see User Configuration. For access permissions, see ACL: Role Privileges.

Note: User name cannot be modified after creation. To rename, delete the old user and create new one.

Create User

Users defined in pg_users are auto-created during PostgreSQL cluster creation in the pg_user task.

To create a new user on an existing cluster, add user definition to all.children.<cls>.pg_users, then execute:

• Script
• Playbook
• Example

bin/pgsql-user <cls> <username>   # Create user <username>

./pgsql-user.yml -l <cls> -e username=<username>   # Use Ansible playbook

bin/pgsql-user pg-meta dbuser_app    # Create dbuser_app user in pg-meta cluster

Example: Create business user dbuser_app

#all.children.pg-meta.vars.pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true
    roles: [dbrole_readwrite]
    comment: application user for myapp

Result: Creates dbuser_app user on primary, sets password, grants dbrole_readwrite role, adds to Pgbouncer pool, reloads Pgbouncer config on all instances.

Modify User

Same command as create - playbook is idempotent. When target user exists, Pigsty modifies properties to match config.

• Script
• Playbook
• Example

bin/pgsql-user <cls> <user>   # Modify user <user> properties

./pgsql-user.yml -l <cls> -e username=<user>   # Idempotent, can repeat

bin/pgsql-user pg-meta dbuser_app    # Modify dbuser_app to match config

Immutable properties: User name can’t be modified after creation - requires delete and recreate.

All other properties can be modified. Common examples:

Modify password: Update password field. Logging is temporarily disabled during password change to prevent leakage.

- name: dbuser_app
  password: NewSecretPassword     # New password

Modify privilege attributes: Configure boolean flags for user privileges.

- name: dbuser_app
  superuser: false           # Superuser (use carefully!)
  createdb: true             # Allow CREATE DATABASE
  createrole: false          # Allow CREATE ROLE
  inherit: true              # Auto-inherit role privileges
  replication: false         # Allow streaming replication
  bypassrls: false           # Bypass row-level security
  connlimit: 50              # Connection limit, -1 unlimited

Modify expiration: Use expire_in for relative expiry (N days), or expire_at for absolute date. expire_in takes priority and recalculates on each playbook run - good for temp users needing periodic renewal.

- name: temp_user
  expire_in: 30                   # Expires in 30 days (relative)

- name: contractor_user
  expire_at: '2024-12-31'         # Expires on date (absolute)

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

Modify role membership: Use roles array with simple or extended format. Role membership is additive - won’t remove undeclared existing roles. Use state: absent to explicitly revoke.

- name: dbuser_app
  roles:
    - dbrole_readwrite                      # Simple form: grant role
    - { name: dbrole_admin, admin: true }   # With ADMIN OPTION
    - { name: pg_monitor, set: false }      # PG16+: disallow SET ROLE
    - { name: old_role, state: absent }     # Revoke role membership

Manage user parameters: Use parameters dict for user-level params, generates ALTER USER ... SET. Use DEFAULT to reset.

- name: dbuser_analyst
  parameters:
    work_mem: '256MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'
    log_statement: DEFAULT        # Reset to default

Connection pool config: Set pgbouncer: true to add user to pool. Optional pool_mode and pool_connlimit.

- name: dbuser_app
  pgbouncer: true                 # Add to pool
  pool_mode: transaction          # Pool mode
  pool_connlimit: 50              # Max user connections

Delete User

To delete a user, set state to absent and execute:

• Script
• Playbook
• Example

bin/pgsql-user <cls> <user>   # Delete <user> (config must have state: absent)

./pgsql-user.yml -l <cls> -e username=<user>   # Use Ansible playbook

bin/pgsql-user pg-meta dbuser_old    # Delete dbuser_old (config has state: absent)

Config example:

pg_users:
  - name: dbuser_old
    state: absent

Deletion process: Uses pg-drop-role script for safe deletion; auto-disables login and terminates connections; transfers database/tablespace ownership to postgres; handles object ownership in all databases; revokes all role memberships; creates audit log; removes from Pgbouncer and reloads config.

Protection: These system users cannot be deleted and are auto-skipped: postgres (superuser), replicator (or pg_replication_username), dbuser_dba (or pg_admin_username), dbuser_monitor (or pg_monitor_username).

Manual Deletion

For manual user deletion, use pg-drop-role script directly:

# Check dependencies (read-only)
pg-drop-role dbuser_old --check

# Preview deletion (don't execute)
pg-drop-role dbuser_old --dry-run -v

# Delete user, transfer objects to postgres
pg-drop-role dbuser_old

# Force delete (terminate connections)
pg-drop-role dbuser_old --force

# Delete user, transfer to specific user
pg-drop-role dbuser_old dbuser_new

Common Use Cases

Common user configuration examples:

Basic business user

- name: dbuser_app
  password: DBUser.App
  pgbouncer: true
  roles: [dbrole_readwrite]
  comment: application user

Read-only user

- name: dbuser_readonly
  password: DBUser.Readonly
  pgbouncer: true
  roles: [dbrole_readonly]

Admin user (can execute DDL)

- name: dbuser_admin
  password: DBUser.Admin
  pgbouncer: true
  pool_mode: session
  roles: [dbrole_admin]
  parameters:
    log_statement: 'all'

Temp user (expires in 30 days)

- name: temp_contractor
  password: TempPassword
  expire_in: 30
  roles: [dbrole_readonly]

Role (no login, for permission grouping)

- name: custom_role
  login: false
  comment: custom role for special permissions

User with advanced role options (PG16+)

- name: dbuser_special
  password: DBUser.Special
  pgbouncer: true
  roles:
    - dbrole_readwrite
    - { name: dbrole_admin, admin: true }
    - { name: pg_monitor, set: false }
    - { name: pg_execute_server_program, inherit: false }

Query Users

Common SQL queries for user info:

List all users

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb,
       rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil
FROM pg_roles WHERE rolname NOT LIKE 'pg_%' ORDER BY rolname;

View user role membership

SELECT r.rolname AS member, g.rolname AS role, m.admin_option, m.set_option, m.inherit_option
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.member
JOIN pg_roles g ON g.oid = m.roleid
WHERE r.rolname = 'dbuser_app';

View user-level parameters

SELECT rolname, setconfig FROM pg_db_role_setting s
JOIN pg_roles r ON r.oid = s.setrole WHERE s.setdatabase = 0;

View expiring users

SELECT rolname, rolvaliduntil, rolvaliduntil - CURRENT_TIMESTAMP AS time_remaining
FROM pg_roles WHERE rolvaliduntil IS NOT NULL
  AND rolvaliduntil < CURRENT_TIMESTAMP + INTERVAL '30 days'
ORDER BY rolvaliduntil;

Connection Pool Management

Connection pool params in user definitions are applied to Pgbouncer when creating/modifying users.

Users with pgbouncer: true are added to /etc/pgbouncer/userlist.txt. User-level pool params (pool_mode, pool_connlimit) are configured via /etc/pgbouncer/useropts.txt.

Use postgres OS user with pgb alias to access Pgbouncer admin database. For more pool management, see Pgbouncer Management.

3 - Managing PostgreSQL Databases

Quick Start

Pigsty uses declarative management: first define databases in the inventory, then use bin/pgsql-db <cls> <dbname> to create or modify.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases: [{ name: some_db }]  # <--- Define database list here!

• Script
• Playbook
• Example

bin/pgsql-db <cls> <dbname>    # Create/modify <dbname> database on <cls> cluster

./pgsql-db.yml -l pg-meta -e dbname=some_db    # Use playbook to create/modify database

bin/pgsql-db pg-meta some_db    # Create/modify some_db database on pg-meta cluster

For complete database definition reference, see Database Configuration. For access permissions, see ACL: Database Privileges.

Note: Some parameters can only be specified at creation time. Modifying these requires recreating the database (use state: recreate).

Create Database

Databases defined in pg_databases are auto-created during PostgreSQL cluster creation in the pg_db task.

To create a new database on an existing cluster, add database definition to all.children.<cls>.pg_databases, then execute:

• Script
• Playbook
• Example

bin/pgsql-db <cls> <dbname>   # Create database <dbname>

./pgsql-db.yml -l <cls> -e dbname=<dbname>   # Use Ansible playbook

bin/pgsql-db pg-meta myapp    # Create myapp database in pg-meta cluster

Example: Create business database myapp

#all.children.pg-meta.vars.pg_databases:
  - name: myapp
    owner: dbuser_myapp
    schemas: [app]
    extensions:
      - { name: pg_trgm }
      - { name: btree_gin }
    comment: my application database

Result: Creates myapp database on primary, sets owner to dbuser_myapp, creates app schema, enables pg_trgm and btree_gin extensions. Database is auto-added to Pgbouncer pool and registered as Grafana datasource.

Modify Database

Same command as create - playbook is idempotent when no baseline SQL is defined.

When target database exists, Pigsty modifies properties to match config. However, some properties can only be set at creation.

• Script
• Playbook
• Example

bin/pgsql-db <cls> <db>   # Modify database <db> properties

./pgsql-db.yml -l <cls> -e dbname=<db>   # Idempotent, can repeat

bin/pgsql-db pg-meta myapp    # Modify myapp database to match config

Immutable properties: These can’t be modified after creation, require state: recreate:

• name (database name), template, strategy (clone strategy)
• encoding, locale/lc_collate/lc_ctype, locale_provider/icu_locale/icu_rules/builtin_locale

All other properties can be modified. Common examples:

Modify owner: Update owner field, executes ALTER DATABASE ... OWNER TO and grants permissions.

- name: myapp
  owner: dbuser_new_owner     # New owner

Modify connection limit: Use connlimit to limit max connections.

- name: myapp
  connlimit: 100              # Max 100 connections

Revoke public connect: Setting revokeconn: true revokes PUBLIC CONNECT privilege, allowing only owner, DBA, monitor, and replication users.

- name: myapp
  owner: dbuser_myapp
  revokeconn: true            # Revoke PUBLIC CONNECT

Manage parameters: Use parameters dict for database-level params, generates ALTER DATABASE ... SET. Use special value DEFAULT to reset.

- name: myapp
  parameters:
    work_mem: '256MB'
    maintenance_work_mem: '512MB'
    statement_timeout: '30s'
    search_path: DEFAULT      # Reset to default

Manage schemas: Use schemas array with simple or extended format. Use state: absent to drop (CASCADE).

- name: myapp
  schemas:
    - app                                   # Simple form
    - { name: core, owner: dbuser_myapp }   # Specify owner
    - { name: deprecated, state: absent }   # Drop schema

Manage extensions: Use extensions array with simple or extended format. Use state: absent to uninstall (CASCADE).

- name: myapp
  extensions:
    - postgis                                 # Simple form
    - { name: vector, schema: public }        # Specify schema
    - { name: pg_trgm, state: absent }        # Uninstall extension

Connection pool config: By default all databases are added to Pgbouncer. Configure pgbouncer, pool_mode, pool_size, pool_reserve, pool_size_min, pool_connlimit, and pool_auth_user.

- name: myapp
  pgbouncer: true              # Add to pool (default true)
  pool_mode: transaction       # Pool mode: transaction/session/statement
  pool_size: 64                # Default pool size
  pool_reserve: 32             # Reserve pool size
  pool_size_min: 0             # Minimum pool size
  pool_connlimit: 100          # Max database connections
  pool_auth_user: dbuser_meta  # Auth query user (with pgbouncer_auth_query)

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.

Delete Database

To delete a database, set state to absent and execute:

• Script
• Playbook
• Example

bin/pgsql-db <cls> <db>   # Delete <db> (config must have state: absent)

./pgsql-db.yml -l <cls> -e dbname=<db>   # Use Ansible playbook

bin/pgsql-db pg-meta olddb    # Delete olddb (config has state: absent)

Config example:

pg_databases:
  - name: olddb
    state: absent

Deletion process: If is_template: true, first executes ALTER DATABASE ... IS_TEMPLATE false; uses DROP DATABASE ... WITH (FORCE) (PG13+) to force drop and terminate all connections; removes from Pgbouncer pool; unregisters from Grafana datasource.

Protection: System databases postgres, template0, template1 cannot be deleted. Deletion only runs on primary - streaming replication syncs to replicas.

Rebuild Database

recreate state rebuilds database (drop then create):

• Script
• Playbook
• Example

bin/pgsql-db <cls> <db>   # Rebuild <db> (config must have state: recreate)

./pgsql-db.yml -l <cls> -e dbname=<db>   # Use Ansible playbook

bin/pgsql-db pg-meta testdb    # Rebuild testdb (config has state: recreate)

Config example:

pg_databases:
  - name: testdb
    state: recreate
    owner: dbuser_test
    baseline: test_init.sql    # Execute after rebuild

Use cases: Test environment reset, clear dev database, modify immutable properties (encoding, locale), restore to initial state.

Difference from manual DROP + CREATE: Single command; auto-preserves Pgbouncer and Grafana config; auto-loads baseline init script.

Clone Database

Clone PostgreSQL databases using PG template mechanism. During cloning, no active connections to template database are allowed.

• Script
• Playbook
• Example

bin/pgsql-db <cls> <db>   # Clone <db> (config must specify template)

./pgsql-db.yml -l <cls> -e dbname=<db>   # Use Ansible playbook

bin/pgsql-db pg-meta meta_dev    # Clone meta_dev (config has template: meta)

Config example:

pg_databases:
  - name: meta                   # Source database

  - name: meta_dev
    template: meta               # Use meta as template
    strategy: FILE_COPY          # PG15+ clone strategy, instant on PG18

Instant Clone (PG18+): If using PostgreSQL 18+, Pigsty defaults file_copy_method. With strategy: FILE_COPY, database clone completes in ~200ms without copying data files. E.g., cloning 30GB database: normal takes 18s, instant takes 200ms.

Manual clone: Ensure all connections to template are terminated:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'meta';
CREATE DATABASE meta_dev TEMPLATE meta STRATEGY FILE_COPY;

Limitations: Instant clone only available on supported filesystems (xfs, brtfs, zfs, apfs); don’t use postgres database as template; in high-concurrency environments, all template connections must be cleared within clone window (~200ms).

Connection Pool Management

Connection pool params in database definitions are applied to Pgbouncer when creating/modifying databases.

By default all databases are added to Pgbouncer pool (pgbouncer: true). Databases are added to /etc/pgbouncer/database.txt. Database-level pool params (pool_auth_user, pool_mode, pool_size, pool_reserve, pool_size_min, pool_connlimit) are configured via this file.

Use postgres OS user with pgb alias to access Pgbouncer admin database. For more pool management, see Pgbouncer Management.

4 - Patroni HA Management

Overview

Pigsty uses Patroni to manage PostgreSQL clusters. It handles config changes, status checks, switchover, restart, reinit replicas, and more.

To use Patroni for management, you need one of the following identities:

• From INFRA node as admin user, managing all clusters in the environment.
• From PGSQL node as pg_dbsu (default postgres), managing the current cluster only.

Patroni provides patronictl CLI for management. Pigsty provides a wrapper alias pg to simplify operations.

pg ()
{
    local patroni_conf="/infra/conf/patronictl.yml";
    if [ ! -r ${patroni_conf} ]; then
        patroni_conf="/etc/patroni/patroni.yml";
        if [ ! -r ${patroni_conf} ]; then
            echo "error: patronictl config not found";
            return 1;
        fi;
    fi;
    patronictl -c ${patroni_conf} "$@"
}

Available Commands

Edit Config

Use edit-config to interactively edit cluster Patroni and PostgreSQL config. This opens an editor to modify config stored in DCS, automatically applying changes to all members. You can change Patroni params (ttl, loop_wait, synchronous_mode, etc.) and PostgreSQL params in postgresql.parameters.

pg edit-config <cls>                  # Interactive edit cluster config
pg edit-config <cls> --force          # Skip confirmation and apply directly
pg edit-config <cls> -p <k>=<v>       # Modify PostgreSQL param (--pg shorthand)
pg edit-config <cls> -s <k>=<v>       # Modify Patroni param (--set shorthand)

Common config modification examples:

# Modify PostgreSQL param: slow query threshold (prompts for confirmation)
pg edit-config pg-test -p log_min_duration_statement=1000

# Modify PostgreSQL param, skip confirmation
pg edit-config pg-test -p log_min_duration_statement=1000 --force

# Modify multiple PostgreSQL params
pg edit-config pg-test -p work_mem=256MB -p maintenance_work_mem=1GB --force

# Modify Patroni params: increase failure detection window (increase RTO)
pg edit-config pg-test -s loop_wait=15 -s ttl=60 --force

# Modify Patroni param: enable synchronous replication mode
pg edit-config pg-test -s synchronous_mode=true --force

# Modify Patroni param: enable strict synchronous mode (require at least one sync replica for writes)
pg edit-config pg-test -s synchronous_mode_strict=true --force

# Modify restart-required params (need pg restart after)
pg edit-config pg-test -p shared_buffers=4GB --force
pg edit-config pg-test -p shared_preload_libraries='timescaledb, pg_stat_statements' --force
pg edit-config pg-test -p max_connections=200 --force

Some params require PostgreSQL restart to take effect. Use pg list to check - instances marked with * need restart. Then use pg restart to apply. You can also use curl or programs to call Patroni REST API:

# View current config
curl -s 10.10.10.11:8008/config | jq .

# Modify params via API (requires auth)
curl -u 'postgres:Patroni.API' \
     -d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
     -s -X PATCH http://10.10.10.11:8008/config | jq .

List Status

Use list to view cluster members and status. Output shows each instance’s name, host, role, state, timeline, and replication lag. This is the most commonly used command for checking cluster health.

pg list <cls>                         # List specified cluster status
pg list                               # List all clusters (on admin node)
pg list <cls> -e                      # Show extended info (--extended)
pg list <cls> -t                      # Show timestamp (--timestamp)
pg list <cls> -f json                 # Output as JSON (--format)
pg list <cls> -W 5                    # Refresh every 5 seconds (--watch)

Example output:

+ Cluster: pg-test (7322261897169354773) -----+----+--------------+
| Member    | Host        | Role    | State   | TL | Lag in MB    |
+-----------+-------------+---------+---------+----+--------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
| pg-test-3 | 10.10.10.13 | Replica | running |  1 |            0 |
+-----------+-------------+---------+---------+----+--------------+

Column descriptions: Member is instance name, composed of pg_cluster-pg_seq; Host is instance IP; Role is role type - Leader (primary), Replica, Sync Standby, Standby Leader (cascade primary); State is running state - running, streaming, in archive recovery, starting, stopped, etc.; TL is timeline number, incremented after each switchover; Lag in MB is replication lag in MB (not shown for primary).

Instances requiring restart show * after the name:

+ Cluster: pg-test (7322261897169354773) -------+----+--------------+
| Member      | Host        | Role    | State   | TL | Lag in MB    |
+-------------+-------------+---------+---------+----+--------------+
| pg-test-1 * | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 * | 10.10.10.12 | Replica | running |  1 |            0 |
+-------------+-------------+---------+---------+----+--------------+

Switchover

Use switchover for planned primary-replica switchover. Switchover is graceful: Patroni ensures replica is fully synced, demotes primary, then promotes target replica. Takes seconds with brief write unavailability. Use for primary host maintenance, upgrades, or migrating primary to better nodes.

pg switchover <cls>                   # Interactive switchover, prompts for target replica
pg switchover <cls> --leader <old>    # Specify current primary name
pg switchover <cls> --candidate <new> # Specify target replica name
pg switchover <cls> --scheduled <time> # Scheduled switchover, format: 2024-12-01T03:00
pg switchover <cls> --force           # Skip confirmation

Before switchover, ensure all replicas are healthy (running or streaming), replication lag is acceptable, and stakeholders are notified.

# Interactive switchover (recommended, shows topology and prompts for selection)
$ pg switchover pg-test
Current cluster topology
+ Cluster: pg-test (7322261897169354773) -----+----+--------------+
| Member    | Host        | Role    | State   | TL | Lag in MB    |
+-----------+-------------+---------+---------+----+--------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
| pg-test-3 | 10.10.10.13 | Replica | running |  1 |            0 |
+-----------+-------------+---------+---------+----+--------------+
Primary [pg-test-1]:
Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-2
When should the switchover take place (e.g. 2024-01-01T12:00) [now]:
Are you sure you want to switchover cluster pg-test, demoting current leader pg-test-1? [y/N]: y

# Non-interactive switchover (specify primary and candidate)
pg switchover pg-test --leader pg-test-1 --candidate pg-test-2 --force

# Scheduled switchover (at 3 AM, for maintenance window)
pg switchover pg-test --leader pg-test-1 --candidate pg-test-2 --scheduled "2024-12-01T03:00"

After switchover, use pg list to confirm new cluster topology.

Failover

Use failover for emergency failover. Unlike switchover, failover is for when primary is unavailable. It directly promotes a replica without waiting for original primary confirmation. Since replicas may not be fully synced, failover may cause minor data loss. Use switchover for non-emergency situations.

pg failover <cls>                     # Interactive failover
pg failover <cls> --leader <old>      # Specify original primary (for verification, optional)
pg failover <cls> --candidate <new>   # Specify replica to promote
pg failover <cls> --force             # Skip confirmation

Failover examples:

# Interactive failover
$ pg failover pg-test
Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-2
Are you sure you want to failover cluster pg-test? [y/N]: y
Successfully failed over to "pg-test-2"

# Non-interactive failover (for emergencies)
pg failover pg-test --candidate pg-test-2 --force

# Specify original primary for verification (errors if name mismatch)
pg failover pg-test --leader pg-test-1 --candidate pg-test-2 --force

Switchover vs Failover: Switchover is for planned maintenance, requires original primary online, ensures full sync before switching, no data loss; Failover is for emergency recovery, original primary can be offline, directly promotes replica, may lose unsynced data. Use Switchover for daily maintenance/upgrades; use Failover only when primary is completely down and unrecoverable.

Restart

Use restart to restart PostgreSQL instances, typically to apply restart-required param changes. Patroni coordinates restarts - for full cluster restart, it uses rolling restart: replicas first, then primary, minimizing downtime.

pg restart <cls>                      # Restart all instances in cluster
pg restart <cls> <member>             # Restart specific instance
pg restart <cls> --role leader        # Restart primary only
pg restart <cls> --role replica       # Restart all replicas
pg restart <cls> --pending            # Restart only instances marked for restart
pg restart <cls> --scheduled <time>   # Scheduled restart
pg restart <cls> --timeout <sec>      # Set restart timeout (seconds)
pg restart <cls> --force              # Skip confirmation

After modifying restart-required params (shared_buffers, shared_preload_libraries, max_connections, max_worker_processes, etc.), use this command.

# Check which instances need restart (marked with *)
$ pg list pg-test
+ Cluster: pg-test (7322261897169354773) -------+----+--------------+
| Member      | Host        | Role    | State   | TL | Lag in MB    |
+-------------+-------------+---------+---------+----+--------------+
| pg-test-1 * | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 * | 10.10.10.12 | Replica | running |  1 |            0 |
+-------------+-------------+---------+---------+----+--------------+

# Restart single replica
pg restart pg-test pg-test-2

# Restart entire cluster (rolling restart, replicas then primary)
pg restart pg-test --force

# Restart only pending instances
pg restart pg-test --pending --force

# Restart all replicas only
pg restart pg-test --role replica --force

# Scheduled restart (for maintenance window)
pg restart pg-test --scheduled "2024-12-01T03:00"

# Set restart timeout to 300 seconds
pg restart pg-test --timeout 300 --force

Reload

Use reload to reload Patroni config without restarting PostgreSQL. This re-reads config files and applies non-restart params via pg_reload_conf(). Lighter than restart - doesn’t interrupt connections or running queries.

pg reload <cls>                       # Reload entire cluster config
pg reload <cls> <member>              # Reload specific instance config
pg reload <cls> --role leader         # Reload primary only
pg reload <cls> --role replica        # Reload all replicas
pg reload <cls> --force               # Skip confirmation

Most PostgreSQL params work via reload. Only postmaster-context params (shared_buffers, max_connections, shared_preload_libraries, archive_mode, etc.) require restart.

# Reload entire cluster
pg reload pg-test

# Reload single instance
pg reload pg-test pg-test-1

# Force reload, skip confirmation
pg reload pg-test --force

Reinit Replica

Use reinit to reinitialize a replica. This deletes all data on the replica and performs fresh pg_basebackup from primary. Use when replica data is corrupted, replica is too far behind (WAL already purged), or replica config needs reset.

pg reinit <cls> <member>              # Reinitialize specified replica
pg reinit <cls> <member> --force      # Skip confirmation
pg reinit <cls> <member> --wait       # Wait for rebuild to complete

Warning: This operation deletes all data on target instance! Can only be run on replicas, not primary.

# Reinitialize replica (prompts for confirmation)
$ pg reinit pg-test pg-test-2
Are you sure you want to reinitialize members pg-test-2? [y/N]: y
Success: reinitialize for member pg-test-2

# Force reinitialize, skip confirmation
pg reinit pg-test pg-test-2 --force

# Reinitialize and wait for completion
pg reinit pg-test pg-test-2 --force --wait

During rebuild, use pg list to check progress. Replica state shows creating replica:

+ Cluster: pg-test (7322261897169354773) --------------+----+------+
| Member    | Host        | Role    | State            | TL | Lag  |
+-----------+-------------+---------+------------------+----+------+
| pg-test-1 | 10.10.10.11 | Leader  | running          |  2 |      |
| pg-test-2 | 10.10.10.12 | Replica | creating replica |    |    ? |
+-----------+-------------+---------+------------------+----+------+

Pause

Use pause to pause Patroni automatic failover. When paused, Patroni won’t auto-promote replicas even if primary fails. Use for planned maintenance windows (prevent accidental triggers), debugging (prevent cluster state changes), or manual switchover timing control.

pg pause <cls>                        # Pause automatic failover
pg pause <cls> --wait                 # Pause and wait for all members to confirm

Warning: During pause, cluster won’t auto-recover if primary fails! Remember to resume after maintenance.

# Pause automatic failover
$ pg pause pg-test
Success: cluster management is paused

# Check cluster status (shows Maintenance mode: on)
$ pg list pg-test
+ Cluster: pg-test (7322261897169354773) -----+----+--------------+
| Member    | Host        | Role    | State   | TL | Lag in MB    |
+-----------+-------------+---------+---------+----+--------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
+-----------+-------------+---------+---------+----+--------------+
 Maintenance mode: on

Resume

Use resume to resume Patroni automatic failover. Execute immediately after maintenance to ensure cluster auto-recovers on primary failure.

pg resume <cls>                       # Resume automatic failover
pg resume <cls> --wait                # Resume and wait for all members to confirm

# Resume automatic failover
$ pg resume pg-test
Success: cluster management is resumed

# Confirm resumed (Maintenance mode prompt disappears)
$ pg list pg-test

History

Use history to view cluster failover history. Each switchover (auto or manual) creates a new timeline record.

pg history <cls>                      # Show failover history
pg history <cls> -f json              # Output as JSON
pg history <cls> -f yaml              # Output as YAML

$ pg history pg-test
+----+-----------+------------------------------+---------------------------+
| TL |       LSN | Reason                       | Timestamp                 |
+----+-----------+------------------------------+---------------------------+
|  1 | 0/5000060 | no recovery target specified | 2024-01-15T10:30:00+08:00 |
|  2 | 0/6000000 | switchover to pg-test-2      | 2024-01-20T14:00:00+08:00 |
|  3 | 0/7000028 | failover to pg-test-1        | 2024-01-25T09:15:00+08:00 |
+----+-----------+------------------------------+---------------------------+

Column descriptions: TL is timeline number, incremented after each switchover, distinguishes primary histories; LSN is Log Sequence Number at switchover, marks WAL position; Reason is switchover reason - switchover to xxx (manual), failover to xxx (failure), or no recovery target specified (init); Timestamp is when switchover occurred.

Show Config

Use show-config to view current cluster config stored in DCS. This is read-only; use edit-config to modify.

pg show-config <cls>                  # Show cluster config

$ pg show-config pg-test
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    archive_command: pgbackrest --stanza=pg-test archive-push %p
    max_connections: 100
    shared_buffers: 256MB
    log_min_duration_statement: 1000
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
ttl: 30
synchronous_mode: false

Query

Use query to quickly execute SQL on cluster members. Convenient for debugging - for complex production queries, use psql or applications.

pg query <cls> -c "<sql>"             # Execute on primary
pg query <cls> -c "<sql>" -m <member> # Execute on specific instance (--member)
pg query <cls> -c "<sql>" -r leader   # Execute on primary (--role)
pg query <cls> -c "<sql>" -r replica  # Execute on all replicas
pg query <cls> -f <file>              # Execute SQL from file
pg query <cls> -c "<sql>" -U <user>   # Specify username (--username)
pg query <cls> -c "<sql>" -d <db>     # Specify database (--dbname)
pg query <cls> -c "<sql>" --format json  # Output as JSON

# Check primary connection count
pg query pg-test -c "SELECT count(*) FROM pg_stat_activity"

# Check PostgreSQL version
pg query pg-test -c "SELECT version()"

# Check replication status on all replicas
pg query pg-test -c "SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn()" -r replica

# Execute on specific instance
pg query pg-test -c "SELECT pg_is_in_recovery()" -m pg-test-2

# Use specific user and database
pg query pg-test -c "SELECT current_user, current_database()" -U postgres -d postgres

# Output as JSON
pg query pg-test -c "SELECT * FROM pg_stat_replication" --format json

Topology

Use topology to view cluster replication topology as a tree. More intuitive than list for showing primary-replica relationships, especially for cascading replication.

pg topology <cls>                     # Show replication topology

$ pg topology pg-test
+ Cluster: pg-test (7322261897169354773) -------+----+--------------+
| Member      | Host        | Role    | State   | TL | Lag in MB    |
+-------------+-------------+---------+---------+----+--------------+
| pg-test-1   | 10.10.10.11 | Leader  | running |  1 |              |
| + pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
| + pg-test-3 | 10.10.10.13 | Replica | running |  1 |            0 |
+-------------+-------------+---------+---------+----+--------------+

In cascading replication, topology clearly shows replication hierarchy - e.g., pg-test-3 replicates from pg-test-2, which replicates from primary pg-test-1.

Version

Use version to view patronictl version.

pg version                            # Show patronictl version

$ pg version
patronictl version 4.1.0

Remove

Use remove to remove cluster or member metadata from DCS. This is dangerous - only removes DCS metadata, doesn’t stop PostgreSQL or delete data files. Misuse may cause cluster state inconsistency.

pg remove <cls>                       # Remove entire cluster metadata from DCS

Normally you don’t need this command. To properly remove clusters/instances, use Pigsty’s bin/pgsql-rm script or pgsql-rm.yml playbook. Only consider remove for: orphaned DCS metadata (node physically removed but metadata remains), or cluster destroyed via other means requiring metadata cleanup.

# Remove entire cluster metadata (requires multiple confirmations)
$ pg remove pg-test
Please confirm the cluster name to remove: pg-test
You are about to remove all information in DCS for pg-test, please type: "Yes I am aware": Yes I am aware

5 - Pgbouncer Connection Pooling

Overview

Pigsty uses Pgbouncer as PostgreSQL connection pooling middleware, listening on port 6432 by default, proxying access to local PostgreSQL on port 5432.

This is an optional component. If you don’t have massive connections or need transaction pooling and query metrics, you can disable it, connect directly to the database, or keep it unused.

User & Database Management

Pgbouncer users and databases are auto-managed by Pigsty, applying database config and user config when creating databases and creating users.

Database Management: Databases defined in pg_databases are auto-added to Pgbouncer by default. Set pgbouncer: false to exclude specific databases.

pg_databases:
  - name: mydb                # Added to connection pool by default
    pool_auth_user: dbuser_meta # Optional, auth query user (with pgbouncer_auth_query)
    pool_mode: transaction    # Database-level pool mode
    pool_size: 64             # Default pool size
    pool_reserve: 32          # Reserve pool size
    pool_size_min: 0          # Minimum pool size
    pool_connlimit: 100       # Max database connections
  - name: internal
    pgbouncer: false          # Excluded from connection pool

User Management: Users defined in pg_users need explicit pgbouncer: true to be added to connection pool user list.

pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true           # Add to connection pool user list
    pool_mode: transaction    # User-level pool mode
    pool_connlimit: 50        # User-level max 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.

Service Management

In Pigsty, PostgreSQL cluster Primary Service and Replica Service default to Pgbouncer port 6432. To bypass connection pool and access PostgreSQL directly, customize pg_services, or set pg_default_service_dest to postgres.

Config Management

Pgbouncer config files are in /etc/pgbouncer/, generated and managed by Pigsty:

Pigsty auto-manages database.txt and userlist.txt, updating them when creating databases or creating users.

You can manually edit config then RELOAD to apply:

# Edit config
$ vim /etc/pgbouncer/pgbouncer.ini

# Reload via systemctl
$ sudo systemctl reload pgbouncer

# Reload as pg_dbsu / postgres user
$ pgb -c "RELOAD;"

Pool Management

Pgbouncer runs as the same dbsu as PostgreSQL, default postgres OS user. Pigsty provides pgb alias for easy management:

alias pgb="psql -p 6432 -d pgbouncer -U postgres"

Use pgb on database nodes to connect to Pgbouncer admin console for management commands and monitoring queries.

$ pgb
pgbouncer=# SHOW POOLS;
pgbouncer=# SHOW CLIENTS;
pgbouncer=# SHOW SERVERS;

PAUSE

Use PAUSE to pause database connections. Pgbouncer waits for active txn/session to complete based on pool mode, then disconnects server connections. New client requests are blocked until RESUME.

PAUSE [db];           -- Pause specified database, or all if not specified

Typical use cases:

• Online backend database switch (e.g., update connection target after switchover)
• Maintenance operations requiring all connections disconnected
• Combined with SUSPEND for Pgbouncer online restart

$ pgb -c "PAUSE mydb;"        # Pause mydb database
$ pgb -c "PAUSE;"             # Pause all databases

After pause, SHOW DATABASES shows paused status:

pgbouncer=# SHOW DATABASES;
   name   |   host    | port | database | ... | paused | disabled
----------+-----------+------+----------+-----+--------+----------
 mydb     | /var/run  | 5432 | mydb     | ... |      1 |        0

RESUME

Use RESUME to restore databases paused by PAUSE, KILL, or SUSPEND, allowing new connections and resuming normal service.

RESUME [db];          -- Resume specified database, or all if not specified

$ pgb -c "RESUME mydb;"       # Resume mydb database
$ pgb -c "RESUME;"            # Resume all databases

DISABLE

Use DISABLE to disable a database, rejecting all new client connection requests. Existing connections are unaffected.

DISABLE db;           -- Disable specified database (database name required)

Typical use cases:

• Temporarily offline a database for maintenance
• Block new connections for safe database migration
• Gradually decommission a database being removed

$ pgb -c "DISABLE mydb;"      # Disable mydb, new connections rejected

ENABLE

Use ENABLE to enable a database previously disabled by DISABLE, accepting new client connections again.

ENABLE db;            -- Enable specified database (database name required)

$ pgb -c "ENABLE mydb;"       # Enable mydb, allow new connections

RECONNECT

Use RECONNECT to gracefully rebuild server connections. Pgbouncer closes connections when released back to pool, creating new ones when needed.

RECONNECT [db];       -- Rebuild server connections for database, or all if not specified

Typical use cases:

• Refresh connections after backend database IP change
• Reroute traffic after switchover
• Rebuild connections after DNS update

$ pgb -c "RECONNECT mydb;"    # Rebuild mydb server connections
$ pgb -c "RECONNECT;"         # Rebuild all server connections

After RECONNECT, use WAIT_CLOSE to wait for old connections to fully release.

KILL

Use KILL to immediately disconnect all client and server connections for a database. Unlike PAUSE, KILL doesn’t wait for transaction completion - forces immediate disconnect.

KILL [db];            -- Kill all connections for database, or all (except admin) if not specified

$ pgb -c "KILL mydb;"         # Force disconnect all mydb connections
$ pgb -c "KILL;"              # Force disconnect all database connections (except admin)

After KILL, new connections are blocked until RESUME.

KILL_CLIENT

Use KILL_CLIENT to terminate a specific client connection. Client ID can be obtained from SHOW CLIENTS output.

KILL_CLIENT id;       -- Terminate client connection with specified ID

# View client connections
$ pgb -c "SHOW CLIENTS;"

# Terminate specific client (assuming ptr column shows ID 0x1234567890)
$ pgb -c "KILL_CLIENT 0x1234567890;"

SUSPEND

Use SUSPEND to suspend Pgbouncer. Flushes all socket buffers and stops listening until RESUME.

SUSPEND;              -- Suspend Pgbouncer

SUSPEND is mainly for Pgbouncer online restart (zero-downtime upgrade):

# 1. Suspend current Pgbouncer
$ pgb -c "SUSPEND;"

# 2. Start new Pgbouncer process (with -R option to take over sockets)
$ pgbouncer -R /etc/pgbouncer/pgbouncer.ini

# 3. New process takes over, old process exits automatically

SHUTDOWN

Use SHUTDOWN to shut down Pgbouncer process. Multiple shutdown modes supported:

SHUTDOWN;                      -- Immediate shutdown
SHUTDOWN WAIT_FOR_SERVERS;     -- Wait for server connections to release
SHUTDOWN WAIT_FOR_CLIENTS;     -- Wait for clients to disconnect (zero-downtime rolling restart)

$ pgb -c "SHUTDOWN WAIT_FOR_CLIENTS;"   # Graceful shutdown, wait for clients

RELOAD

Use RELOAD to reload Pgbouncer config files. Dynamically updates most config params without process restart.

RELOAD;               -- Reload config files

$ pgb -c "RELOAD;"              # Reload via admin console
$ systemctl reload pgbouncer    # Reload via systemd
$ kill -SIGHUP $(cat /var/run/pgbouncer/pgbouncer.pid)  # Reload via signal

Pigsty provides playbook task to reload Pgbouncer config:

./pgsql.yml -l <cls> -t pgbouncer_reload    # Reload cluster Pgbouncer config

WAIT_CLOSE

Use WAIT_CLOSE to wait for server connections to finish closing. Typically used after RECONNECT or RELOAD to ensure old connections are fully released.

WAIT_CLOSE [db];      -- Wait for server connections to close, or all if not specified

# Complete connection rebuild flow
$ pgb -c "RECONNECT mydb;"
$ pgb -c "WAIT_CLOSE mydb;"    # Wait for old connections to release

Monitoring

Pgbouncer provides rich SHOW commands for monitoring pool status:

Common monitoring examples:

# View pool status
$ pgb -c "SHOW POOLS;"

# View client connections
$ pgb -c "SHOW CLIENTS;"

# View server connections
$ pgb -c "SHOW SERVERS;"

# View statistics
$ pgb -c "SHOW STATS;"

# View database status
$ pgb -c "SHOW DATABASES;"

For more monitoring command details, see Pgbouncer official docs.

Unix Signals

Pgbouncer supports Unix signal control, useful when admin console is unavailable:

# Reload config via signal
$ kill -SIGHUP $(cat /var/run/pgbouncer/pgbouncer.pid)

# Graceful shutdown via signal
$ kill -SIGTERM $(cat /var/run/pgbouncer/pgbouncer.pid)

# Pause via signal
$ kill -SIGUSR1 $(cat /var/run/pgbouncer/pgbouncer.pid)

# Resume via signal
$ kill -SIGUSR2 $(cat /var/run/pgbouncer/pgbouncer.pid)

Traffic Switching

Pigsty provides pgb-route utility function to quickly switch Pgbouncer traffic to other nodes for zero-downtime migration:

# Definition (already in /etc/profile.d/pg-alias.sh)
function pgb-route(){
  local ip=${1-'\/var\/run\/postgresql'}
  sed -ie "s/host=[^[:space:]]\+/host=${ip}/g" /etc/pgbouncer/pgbouncer.ini
  cat /etc/pgbouncer/pgbouncer.ini
}

# Usage: Route traffic to 10.10.10.12
$ pgb-route 10.10.10.12
$ pgb -c "RECONNECT; WAIT_CLOSE;"

Complete zero-downtime switching flow:

# 1. Modify route target
$ pgb-route 10.10.10.12

# 2. Reload config
$ pgb -c "RELOAD;"

# 3. Rebuild connections and wait for old connections to release
$ pgb -c "RECONNECT;"
$ pgb -c "WAIT_CLOSE;"

6 - Managing PostgreSQL Component Services

Overview

Pigsty’s PGSQL module consists of multiple components, each running as a systemd service on nodes. (pgbackrest is an exception)

Understanding these components and their management is essential for maintaining production PostgreSQL clusters.

Quick Reference

Common service names: patroni, pgbouncer, haproxy, pg_exporter, pgbouncer_exporter, vip-manager

Patroni

Patroni is PostgreSQL’s HA manager, handling startup, shutdown, failure detection, and automatic failover. It’s the core PGSQL module component. PostgreSQL process is managed by Patroni - don’t use systemctl to manage postgres service directly.

Start Patroni

systemctl start patroni     # Start Patroni (also starts PostgreSQL)

After starting, Patroni auto-launches PostgreSQL. On first start, behavior depends on role:

• Primary: Initialize or recover data directory
• Replica: Clone data from primary and establish replication

Stop Patroni

systemctl stop patroni      # Stop Patroni (also stops PostgreSQL)

Stopping Patroni gracefully shuts down PostgreSQL. Note: If this is primary and auto-failover isn’t paused, may trigger failover.

Restart Patroni

systemctl restart patroni   # Restart Patroni (also restarts PostgreSQL)

Restart causes brief service interruption. For production, use pg restart for rolling restart.

Reload Patroni

systemctl reload patroni    # Reload Patroni config

Reload re-reads config file and applies hot-reloadable params to PostgreSQL.

View Status & Logs

systemctl status patroni    # View Patroni service status
journalctl -u patroni -f    # Real-time Patroni logs
journalctl -u patroni -n 100 --no-pager  # Last 100 lines

Config file: /etc/patroni/patroni.yml

Best Practice: Use patronictl instead of systemctl to manage PostgreSQL clusters.

Pgbouncer

Pgbouncer is a lightweight PostgreSQL connection pooling middleware. Business traffic typically goes through Pgbouncer (6432) rather than directly to PostgreSQL (5432) for connection reuse and database protection.

Start Pgbouncer

systemctl start pgbouncer

Stop Pgbouncer

systemctl stop pgbouncer

Note: Stopping Pgbouncer disconnects all pooled business connections.

Restart Pgbouncer

systemctl restart pgbouncer

Restart disconnects all existing connections. For config changes only, use reload.

Reload Pgbouncer

systemctl reload pgbouncer

Reload re-reads config files (user list, pool params, etc.) without disconnecting existing connections.

View Status & Logs

systemctl status pgbouncer
journalctl -u pgbouncer -f

Config files:

• Main config: /etc/pgbouncer/pgbouncer.ini
• HBA rules: /etc/pgbouncer/pgb_hba.conf
• User list: /etc/pgbouncer/userlist.txt
• Database list: /etc/pgbouncer/database.txt

Admin Console

psql -p 6432 -U postgres -d pgbouncer  # Connect to Pgbouncer admin console

Common admin commands:

SHOW POOLS;      -- View pool status
SHOW CLIENTS;    -- View client connections
SHOW SERVERS;    -- View backend server connections
SHOW STATS;      -- View statistics
RELOAD;          -- Reload config
PAUSE;           -- Pause all pools
RESUME;          -- Resume all pools

HAProxy

HAProxy is a high-performance load balancer that routes traffic to correct PostgreSQL instances. Pigsty uses HAProxy to expose services, routing traffic based on role (primary/replica) and health status.

Start HAProxy

systemctl start haproxy

Stop HAProxy

systemctl stop haproxy

Note: Stopping HAProxy disconnects all load-balanced connections.

Restart HAProxy

systemctl restart haproxy

Reload HAProxy

systemctl reload haproxy

HAProxy supports graceful reload without disconnecting existing connections. Use reload for config changes.

View Status & Logs

systemctl status haproxy
journalctl -u haproxy -f

Config file: /etc/haproxy/haproxy.cfg

Admin Interface

HAProxy provides a web admin interface, default port 9101:

http://<node_ip>:9101/haproxy

Default auth: username admin, password configured by haproxy_admin_password.

pg_exporter

pg_exporter is PostgreSQL’s Prometheus metrics exporter for collecting database performance metrics.

Start pg_exporter

systemctl start pg_exporter

Stop pg_exporter

systemctl stop pg_exporter

After stopping, Prometheus can’t collect PostgreSQL metrics from this instance.

Restart pg_exporter

systemctl restart pg_exporter

View Status & Logs

systemctl status pg_exporter
journalctl -u pg_exporter -f

Config file: /etc/pg_exporter.yml

Verify Metrics

curl -s localhost:9630/metrics | head -20

pgbouncer_exporter

pgbouncer_exporter is Pgbouncer’s Prometheus metrics exporter.

Start/Stop/Restart

systemctl start pgbouncer_exporter
systemctl stop pgbouncer_exporter
systemctl restart pgbouncer_exporter

View Status & Logs

systemctl status pgbouncer_exporter
journalctl -u pgbouncer_exporter -f

Verify Metrics

curl -s localhost:9631/metrics | head -20

vip-manager

vip-manager is an optional component for managing L2 VIP address floating. When pg_vip_enabled is enabled, vip-manager binds VIP to current primary node.

Start vip-manager

systemctl start vip-manager

Stop vip-manager

systemctl stop vip-manager

After stopping, VIP address is released from current node.

Restart vip-manager

systemctl restart vip-manager

View Status & Logs

systemctl status vip-manager
journalctl -u vip-manager -f

Config file: /etc/default/vip-manager

Verify VIP Binding

ip addr show           # Check network interfaces, verify VIP binding
pg list <cls>          # Confirm primary location

Startup Order & Dependencies

Recommended PGSQL module component startup order:

1. patroni          # Start Patroni first (auto-starts PostgreSQL)
2. pgbouncer        # Then start connection pool
3. haproxy          # Start load balancer
4. pg_exporter      # Start metrics exporters
5. pgbouncer_exporter
6. vip-manager      # Finally start VIP manager (if enabled)

Stop order should be reversed. Pigsty playbooks handle these dependencies automatically.

Batch Start All Services

systemctl start patroni pgbouncer haproxy pg_exporter pgbouncer_exporter

Batch Stop All Services

systemctl stop pgbouncer_exporter pg_exporter haproxy pgbouncer patroni

Common Troubleshooting

Service Startup Failure

systemctl status <service>        # View service status
journalctl -u <service> -n 50     # View recent logs
journalctl -u <service> --since "5 min ago"  # Last 5 minutes logs

Patroni Won’t Start

Pgbouncer Won’t Start

HAProxy Won’t Start

Related Documentation

• Patroni Management: Manage PostgreSQL HA with patronictl
• Cluster Management: Create, scale, destroy clusters
• Service Configuration: HAProxy service definition and config
• Monitoring System: PostgreSQL monitoring and alerting

7 - Manage PostgreSQL Cron Jobs

Pigsty uses crontab to manage scheduled tasks for routine backups, freezing aging transactions, and reorganizing bloated tables and indexes.

Quick Reference

For other management tasks, see: Backup Management, Monitoring System, HA Management.

Configure Cron Jobs

Use the pg_crontab parameter to configure cron jobs for the PostgreSQL database superuser (pg_dbsu, default postgres).

Example Configuration

The following pg-meta cluster configures a daily full backup at 1:00 AM, while pg-test configures weekly full backup on Monday with incremental backups on other days.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_crontab:
      - '00 01 * * * /pg/bin/pg-backup'
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-test
    pg_crontab:
      - '00 01 * * 1            /pg/bin/pg-backup full'
      - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup'

Recommended Maintenance Schedule

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'    # Daily full backup at 1:00 AM
  - '00 03 * * 0 /pg/bin/pg-vacuum'         # Weekly vacuum freeze on Sunday at 3:00 AM
  - '00 04 * * 1 /pg/bin/pg-repack'         # Weekly repack on Monday at 4:00 AM

Apply Cron Jobs

Cron jobs are automatically written to the default location for the corresponding OS distribution when the pgsql.yml playbook executes (the pg_crontab task):

• EL (RHEL/Rocky/Alma): /var/spool/cron/postgres
• Debian/Ubuntu: /var/spool/cron/crontabs/postgres

• Playbook
• Manual

./pgsql.yml -l pg-meta -t pg_crontab     # Apply pg_crontab config to specified cluster
./pgsql.yml -l 10.10.10.10 -t pg_crontab # Target specific host only

# Edit cron jobs as postgres user
sudo -u postgres crontab -e

# Or edit crontab file directly
sudo vi /var/spool/cron/postgres           # EL series
sudo vi /var/spool/cron/crontabs/postgres  # Debian/Ubuntu

Each playbook execution will fully overwrite the cron job configuration.

View Cron Jobs

Execute the following command as the pg_dbsu OS user to view cron jobs:

crontab -l

# Pigsty Managed Crontab for postgres
SHELL=/bin/bash
PATH=/usr/pgsql/bin:/pg/bin:/usr/local/bin:/usr/bin:/usr/sbin:/bin:/sbin
MAILTO=""
00 01 * * * /pg/bin/pg-backup

If you’re not familiar with crontab syntax, refer to Crontab Guru for explanations.

pg-backup

pg-backup is Pigsty’s physical backup script based on pgBackRest, supporting full, differential, and incremental backup modes.

Basic Usage

pg-backup                # Execute incremental backup (default), auto full if no existing full backup
pg-backup full           # Execute full backup
pg-backup diff           # Execute differential backup (based on most recent full backup)
pg-backup incr           # Execute incremental backup (based on most recent any backup)

Backup Types

Execution Requirements

• Script must run on primary as postgres user
• Script auto-detects current node role, exits (exit 1) when run on replica
• Auto-retrieves stanza name from /etc/pgbackrest/pgbackrest.conf

Common Cron Configurations

• Daily Full
• Weekly Full + Daily Incr
• Weekly Full + Daily Diff

pg_crontab:
  - '00 01 * * * /pg/bin/pg-backup full'    # Daily full backup at 1:00 AM

pg_crontab:
  - '00 01 * * 1            /pg/bin/pg-backup full'  # Monday full backup
  - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup'       # Other days incremental

pg_crontab:
  - '00 01 * * 1            /pg/bin/pg-backup full'  # Monday full backup
  - '00 01 * * 2,3,4,5,6,7  /pg/bin/pg-backup diff'  # Other days differential

For more backup and recovery operations, see the Backup Management section.

pg-vacuum

pg-vacuum is Pigsty’s transaction freeze script for executing VACUUM FREEZE operations to prevent database shutdown from transaction ID (XID) wraparound.

Basic Usage

• Basic
• Options
• Manual SQL

pg-vacuum                    # Freeze aging tables in all databases
pg-vacuum mydb               # Process specified database only
pg-vacuum mydb1 mydb2        # Process multiple databases

pg-vacuum -n mydb            # Dry run mode, display only without executing
pg-vacuum -a 80000000 mydb   # Use custom age threshold (default 100M)
pg-vacuum -r 50 mydb         # Use custom aging ratio threshold (default 40%)

-- Execute VACUUM FREEZE on entire database
VACUUM FREEZE;

-- Execute VACUUM FREEZE on specific table
VACUUM FREEZE schema.table_name;

Command Options

Logic

1. Check database datfrozenxid age, skip database if below threshold
2. Calculate aging page ratio (percentage of table pages exceeding age threshold of total pages)
3. If aging ratio > 40%, execute full database VACUUM FREEZE ANALYZE
4. Otherwise, only execute VACUUM FREEZE ANALYZE on tables exceeding age threshold

Script sets vacuum_cost_limit = 10000 and vacuum_cost_delay = 1ms to control I/O impact.

Execution Requirements

• Script must run on primary as postgres user
• Uses file lock /tmp/pg-vacuum.lock to prevent concurrent execution
• Auto-skips template0, template1, postgres system databases

Common Cron Configuration

pg_crontab:
  - '00 03 * * 0 /pg/bin/pg-vacuum'     # Weekly Sunday at 3:00 AM

pg-repack

pg-repack is Pigsty’s bloat maintenance script based on the pg_repack extension for online reorganization of bloated tables and indexes.

Basic Usage

• Basic
• Options
• Manual

pg-repack                    # Reorganize bloated tables and indexes in all databases
pg-repack mydb               # Reorganize specified database only
pg-repack mydb1 mydb2        # Reorganize multiple databases

pg-repack -n mydb            # Dry run mode, display only without executing
pg-repack -t mydb            # Reorganize tables only
pg-repack -i mydb            # Reorganize indexes only
pg-repack -T 30 -j 4 mydb    # Custom lock timeout (seconds) and parallelism

# Use pg_repack command directly to reorganize specific table
pg_repack dbname -t schema.table

# Use pg_repack command directly to reorganize specific index
pg_repack dbname -i schema.index

Command Options

Auto-Selection Thresholds

Script auto-selects objects to reorganize based on table/index size and bloat ratio:

Table Bloat Thresholds

Index Bloat Thresholds

Tables/indexes over 64GB are skipped with a warning and require manual handling.

Execution Requirements

• Script must run on primary as postgres user
• Requires pg_repack extension installed (installed by default in Pigsty)
• Requires pg_table_bloat and pg_index_bloat views in monitor schema
• Uses file lock /tmp/pg-repack.lock to prevent concurrent execution
• Auto-skips template0, template1, postgres system databases

Common Cron Configuration

pg_crontab:
  - '00 04 * * 1 /pg/bin/pg-repack'     # Weekly Monday at 4:00 AM

You can confirm database bloat through Pigsty’s PGCAT Database - Table Bloat panel and select high-bloat tables and indexes for reorganization.

For more details see: Managing Relation Bloat

Remove Cron Jobs

When using the pgsql-rm.yml playbook to remove a PostgreSQL cluster, it automatically deletes the postgres user’s crontab file.

./pgsql-rm.yml -l <cls> -t pg_crontab    # Remove cron jobs only
./pgsql-rm.yml -l <cls>                  # Remove entire cluster (including cron jobs)

Related Documentation

• Backup Management: PostgreSQL backup and recovery
• Monitoring System: PostgreSQL monitoring and alerting
• Cluster Management: Cluster creation, scaling, and teardown
• Patroni Management: HA cluster management

8 - Managing PostgreSQL Extensions

Quick Start

Pigsty provides 451 extensions. Using extensions involves four steps: Download, Install, Configure, Enable.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_extensions: [ postgis, timescaledb, pgvector ]           # <--- Install extension packages
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'    # <--- Configure preload extensions
    pg_databases:
      - name: meta
        extensions: [ postgis, timescaledb, vector ]            # <--- Enable in database

• Script
• Playbook
• Example

bin/pgsql-ext <cls>           # Install extensions defined in config on <cls> cluster
bin/pgsql-ext <cls> [ext...]  # Install extensions specified on command line

./pgsql.yml -l pg-meta -t pg_ext    # Use playbook to install extensions

bin/pgsql-ext pg-meta                         # Install defined extensions on pg-meta cluster
bin/pgsql-ext pg-meta pg_duckdb pg_mooncake   # Install specified extensions

For complete extension reference, see Extensions. For available extensions, see Extension Catalog.

Install Extensions

Extensions defined in pg_extensions are auto-installed during PostgreSQL cluster creation in the pg_extension task.

To install extensions on an existing cluster, add extensions to all.children.<cls>.pg_extensions, then execute:

• Script
• Playbook
• Example

bin/pgsql-ext <cls>   # Install extensions on <cls> cluster

./pgsql.yml -l <cls> -t pg_extension   # Use Ansible playbook

bin/pgsql-ext pg-meta    # Install extensions defined in config on pg-meta

Example: Install PostGIS, TimescaleDB and PGVector on cluster

#all.children.pg-meta.vars:
pg_extensions: [ postgis, timescaledb, pgvector ]

Result: Installs extension packages on all cluster nodes. Pigsty auto-translates package aliases to actual package names for OS and PG version.

Manual Install

If you don’t want to use Pigsty config to manage extensions, pass extension list directly on command line:

• Script
• Playbook

bin/pgsql-ext pg-meta pg_duckdb pg_mooncake   # Install specified extensions on pg-meta

./pgsql.yml -l pg-meta -t pg_ext -e '{"pg_extensions": ["pg_duckdb", "pg_mooncake"]}'

You can also use pig package manager CLI to install extensions on single node, with auto package alias resolution.

pig install postgis timescaledb       # Install multiple extensions
pig install pgvector -v 18            # Install for specific PG major version

ansible pg-test -b -a 'pig install pg_duckdb'   # Batch install on cluster with Ansible

You can also use OS package manager directly (apt/dnf), but you must know the exact RPM/DEB package name for your OS/PG:

# EL systems (RHEL, Rocky, Alma, Oracle Linux)
sudo yum install -y pgvector_18*

# Debian / Ubuntu
sudo apt install -y postgresql-18-pgvector

Download Extensions

To install extensions, ensure node’s extension repos contain the extension:

• Standalone install: No worries, upstream repos already added to node.
• Offline install: No worries, most extensions included in offline package, few require online install.
• Production multi-node deployment with local repo: depends - if extension was in repo_packages / repo_extra_packages when creating local repo, it’s already downloaded. Otherwise download first or configure upstream repos for online install.

Pigsty’s default config auto-downloads mainstream extensions during installation. For additional extensions, add to repo_extra_packages and rebuild repo:

repo_extra_packages: [ pgvector, postgis, timescaledb ]

• Script
• Playbook

make repo         # Shortcut = repo-build + node-repo
make repo-build   # Rebuild Infra repo (download packages and deps)
make node-repo    # Refresh node repo cache, update Infra repo reference

./deploy.yml -t repo_build,node_repo  # Execute both tasks at once
./infra.yml -t repo_build     # Re-download packages to local repo
./node.yml  -t node_repo      # Refresh node repo cache

Configure Repos

You can also let all nodes use upstream repos directly (not recommended for production), skipping download and installing from upstream extension repos:

./node.yml -t node_repo -e node_repo_modules=node,pgsql   # Add PGDG and Pigsty upstream repos

Configure Extensions

Some extensions require preloading to shared_preload_libraries, requiring database restart after modification.

Use pg_libs as its default value to configure preload extensions, but this only takes effect during cluster init - later modifications are ineffective.

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'   # Preload extensions
    pg_extensions: [ timescaledb, postgis, pgvector ]          # Install packages

For existing clusters, refer to Modify Config to modify shared_preload_libraries:

pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'
pg restart pg-meta   # Modify pg-meta params and restart to apply

Ensure extension packages are correctly installed before adding preload config. If extension in shared_preload_libraries doesn’t exist or fails to load, PostgreSQL won’t start. Also, manage cluster config changes through Patroni - avoid using ALTER SYSTEM or pg_parameters to modify instance config separately. If primary and replica configs differ, it may cause startup failure or replication interruption.

Enable Extensions

After installing packages, execute CREATE EXTENSION in database to use extension features.

Enable during cluster init

Declare extensions to enable in database definition via extensions array:

pg_databases:
  - name: meta
    extensions:
      - vector                             # Simple form
      - { name: postgis, schema: public }  # Specify schema

Manual enable

• SQL
• psql
• Playbook

CREATE EXTENSION vector;                      -- Create extension
CREATE EXTENSION postgis SCHEMA public;       -- Specify schema
CREATE EXTENSION IF NOT EXISTS vector;        -- Idempotent creation
CREATE EXTENSION postgis_topology CASCADE;    -- Auto-install dependencies

psql -d meta -c 'CREATE EXTENSION vector;'                  # Create extension in meta database
psql -d meta -c 'CREATE EXTENSION postgis SCHEMA public;'   # Specify schema