# Managing PostgreSQL Databases > Database management - create, modify, delete, rebuild, and clone databases using templates --- LLMS index: [llms.txt](/llms.txt) --- ## Quick Start Pigsty uses declarative management: first [**define databases**](/docs/pgsql/config/db) in the [**inventory**](/docs/concept/iac/inventory), then use `bin/pgsql-db ` to create or modify. ```yaml 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! ``` ```bash {tab="Script" group="script-playbook-example" value="script"} bin/pgsql-db # Create/modify database on cluster ``` ```bash {tab="Playbook" value="playbook"} ./pgsql-db.yml -l pg-meta -e dbname=some_db # Use playbook to create/modify database ``` ```bash {tab="Example" value="example"} bin/pgsql-db pg-meta some_db # Create/modify some_db database on pg-meta cluster ``` For the complete database definition reference, see [**Database Configuration**](/docs/pgsql/config/db). For database access permissions, see [**Access Control: Database Isolation**](/docs/concept/sec/ac#database-isolation). Note: Some parameters can only be specified at **creation time**. Modifying these requires recreating the database (use `state: recreate`). | Action | Command | Description | |:------------------------------------------|:--------------------------|:-----------------------------------------------| | [**Create Database**](#create-database) | `bin/pgsql-db ` | Create new business database | | [**Modify Database**](#modify-database) | `bin/pgsql-db ` | Modify existing database properties | | [**Delete Database**](#delete-database) | `bin/pgsql-db ` | Delete database (requires `state: absent`) | | [**Rebuild Database**](#rebuild-database) | `bin/pgsql-db ` | Drop and recreate (requires `state: recreate`) | | [**Clone Database**](#clone-database) | `bin/pgsql-db ` | Clone database using template | {.full-width}
demo/pgsql-db.cast
---------------- ## Create Database Databases defined in [**`pg_databases`**](/docs/pgsql/param#pg_databases) are auto-created during PostgreSQL [**cluster creation**](/docs/pgsql/admin/cluster#create-cluster) in the `pg_db` task. To create a new database on an existing cluster, add [**database definition**](/docs/pgsql/config/db) to `all.children..pg_databases`, then execute: ```bash {tab="Script" group="script-playbook-example" value="script"} bin/pgsql-db # Create database ``` ```bash {tab="Playbook" value="playbook"} ./pgsql-db.yml -l -e dbname= # Use Ansible playbook ``` ```bash {tab="Example" value="example"} bin/pgsql-db pg-meta myapp # Create myapp database in pg-meta cluster ``` **Example: Create business database `myapp`** ```yaml #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. > [!NOTE] Recommendation: Use playbook > For manual database creation, you must ensure Pgbouncer pool and Grafana datasource sync yourself. ---------------- ## 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. ```bash {tab="Script" group="script-playbook-example" value="script"} bin/pgsql-db # Modify database properties ``` ```bash {tab="Playbook" value="playbook"} ./pgsql-db.yml -l -e dbname= # Idempotent, can repeat ``` ```bash {tab="Example" value="example"} 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. ```yaml - name: myapp owner: dbuser_new_owner # New owner ``` **Modify connection limit**: Use `connlimit` to limit max connections. ```yaml - 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. ```yaml - 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. ```yaml - 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). ```yaml - 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). ```yaml - name: myapp extensions: - postgis # Simple form - { name: vector, schema: public } # Specify schema - { name: pg_trgm, state: absent } # Uninstall extension ``` > [!WARNING] CASCADE Warning > Dropping schemas or uninstalling extensions uses `CASCADE`, deleting all dependent objects. Understand impact before executing. **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`. ```yaml - name: myapp pgbouncer: true # Add to pool (default true) pool_mode: transaction # Pool mode: transaction/session/statement pool_size: 50 # Default pool size pool_reserve: 30 # 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: ```bash {tab="Script" group="script-playbook-example" value="script"} bin/pgsql-db # Delete (config must have state: absent) ``` ```bash {tab="Playbook" value="playbook"} ./pgsql-db.yml -l -e dbname= # Use Ansible playbook ``` ```bash {tab="Example" value="example"} bin/pgsql-db pg-meta olddb # Delete olddb (config has state: absent) ``` **Config example**: ```yaml 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. > [!CAUTION] Danger Warning > Database deletion is **irreversible** - permanently deletes all data. Before executing: ensure recent backup exists, confirm no business uses the database, notify stakeholders. > Pigsty is not responsible for any data loss from database deletion. Use at your own risk. ---------------- ## Rebuild Database `recreate` state rebuilds database (drop then create): ```bash {tab="Script" group="script-playbook-example" value="script"} bin/pgsql-db # Rebuild (config must have state: recreate) ``` ```bash {tab="Playbook" value="playbook"} ./pgsql-db.yml -l -e dbname= # Use Ansible playbook ``` ```bash {tab="Example" value="example"} bin/pgsql-db pg-meta testdb # Rebuild testdb (config has state: recreate) ``` **Config example**: ```yaml 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. ```bash {tab="Script" group="script-playbook-example" value="script"} bin/pgsql-db # Clone (config must specify template) ``` ```bash {tab="Playbook" value="playbook"} ./pgsql-db.yml -l -e dbname= # Use Ansible playbook ``` ```bash {tab="Example" value="example"} bin/pgsql-db pg-meta meta_dev # Clone meta_dev (config has template: meta) ``` **Config example**: ```yaml 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`**](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-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: ```sql 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, btrfs, 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**](/docs/pgsql/config/db#connection-pool) 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**](/docs/pgsql/admin/pgbouncer).