Database

In this context, Database refers to the logical object created using the SQL command CREATE DATABASE within a database cluster.

A PostgreSQL server can serve multiple databases at the same time. In Pigsty, define the required databases in the cluster configuration.

Pigsty customizes the default template database, template1. It creates default schemas, installs default extensions, and configures default privileges. New databases inherit these settings from template1 by default.

By default, Pigsty adds each business database to the Pgbouncer connection pool with a 1:1 mapping. pg_exporter automatically discovers all business databases and monitors their objects.

Define Database

Define business databases in the cluster parameter pg_databases. This parameter is an array of database definition objects. Pigsty creates databases in definition order. Thus, a later database can use an earlier database as its template.

Below is the database definition for the default pg-meta cluster in the Pigsty demo environment:

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

Each database definition is an object. The following meta definition shows the available fields:

- name: meta                      # REQUIRED, `name` is the only mandatory field of a database definition
  baseline: cmdb.sql              # optional, database sql baseline path (relative path among ansible search path, e.g. files/)
  pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
  schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
  extensions:                     # optional, additional extensions to be installed: array of extension objects
    - { name: postgis , schema: public }  # can specify which schema to install the extension in, or leave it unspecified (will install in the first schema of search_path)
    - { name: timescaledb }               # for example, some extensions create and use fixed schemas, so no schema specification is needed.
  comment: pigsty meta database   # optional, comment string for this database
  owner: postgres                 # optional, database owner, postgres by default
  template: template1             # optional, which template to use, template1 by default, target must be a template database
  encoding: UTF8                  # optional, database encoding, UTF8 by default (MUST same as template database)
  locale: C                       # optional, database locale, C by default (MUST same as template database)
  lc_collate: C                   # optional, database collate, C by default (MUST same as template database), no reason not to recommend changing.
  lc_ctype: C                     # optional, database ctype, C by default (MUST same as template database)
  tablespace: pg_default          # optional, default tablespace, 'pg_default' by default
  allowconn: true                 # optional, allow connection, true by default. false will disable connect at all
  revokeconn: false               # optional, revoke public connection privilege. false by default, when set to true, CONNECT privilege will be revoked from users other than owner and admin
  register_datasource: true       # optional, register this database to grafana datasources? true by default, explicitly set to false to skip registration
  connlimit: -1                   # optional, database connection limit, default -1 disable limit, set to positive integer will limit connections
  pool_auth_user: dbuser_meta     # optional, all connections to this pgbouncer database will be authenticated using this user (only useful when pgbouncer_auth_query is enabled)
  pool_mode: transaction          # optional, pgbouncer pool mode at database level, default transaction
  pool_size: 50                   # optional, pgbouncer pool size at database level, default 50
  pool_reserve: 30                # optional, pgbouncer reserve pool at database level, default 30
  pool_size_min: 0                # optional, pgbouncer pool size min at database level, default 0
  pool_connlimit: 100             # optional, max database connections at database level, default 100

The only required field is name. It should be a valid database name that is unique within the PostgreSQL cluster. Other parameters have defaults.

• name: Database name, required.
• baseline: SQL file path (Ansible search path, usually in files), used to initialize database content.
• owner: Database owner, default is postgres
• template: Template used when creating the database, default is template1
• encoding: Database default character encoding, default is UTF8, default is consistent with the instance. It is recommended not to configure and modify.
• locale: Database default locale, default is C, it is recommended not to configure, keep consistent with the instance.
• lc_collate: Database default locale string collation, default is same as instance setting, it is recommended not to modify, must be consistent with template database. It is strongly recommended not to configure, or configure to C.
• lc_ctype: Database default LOCALE, default is same as instance setting, it is recommended not to modify or set, must be consistent with template database. It is recommended to configure to C or en_US.UTF8.
• allowconn: Whether to allow connection to the database, default is true, not recommended to modify.
• revokeconn: Whether to revoke connection privilege to the database? Default is false. If true, PUBLIC CONNECT privilege on the database will be revoked. Only default users (dbsu|monitor|admin|replicator|owner) can connect. In addition, admin|owner will have GRANT OPTION, can grant connection privileges to other users.
• tablespace: Tablespace associated with the database, default is pg_default.
• connlimit: Database connection limit, default is -1, meaning no limit.
• extensions: Object array, each object defines an extension in the database, and the schema in which it is installed.
• parameters: KV object, each KV defines a parameter that needs to be modified for the database through ALTER DATABASE.
• pgbouncer: Boolean option, whether to add this database to Pgbouncer. All databases will be added to Pgbouncer list unless explicitly specified as pgbouncer: false.
• comment: Database comment information.
• pool_auth_user: When pgbouncer_auth_query is enabled, all connections to this pgbouncer database will use the user specified here to execute authentication queries. You need to use a user with access to the pg_shadow table.
• pool_mode: Database level pgbouncer pool mode, default is transaction, i.e., transaction pooling. If left empty, will use pgbouncer_poolmode parameter as default value.
• pool_size: Database-level Pgbouncer default pool size, default 50.
• pool_reserve: Database-level Pgbouncer reserve pool, default 30; when the regular pool is exhausted, at most this many burst connections can be added.
• pool_size_min: Database level pgbouncer pool size min, default is 0
• pool_connlimit: Database level pgbouncer connection pool max database connections, default is 100

By default, new databases use template1 as their template. During PG_PROVISION, Pigsty configures this template with extensions, schemas, and default privileges. New databases inherit this configuration unless you explicitly select another template.

For database access privileges, see Access Control: Database Isolation.

Create Database

Pigsty creates databases defined in pg_databases during cluster initialization. To create a database on an existing cluster, use bin/pgsql-db. Add the definition to all.children.<cls>.pg_databases, then run:

bin/pgsql-db <cls> <dbname>    # pgsql-db.yml -l <cls> -e dbname=<dbname>

Here are some considerations when creating a new database:

The database creation playbook is idempotent by default. However, a baseline script can change this behavior. Do not usually repeat the playbook on an existing database unless you know that its baseline SQL is also idempotent.

Manual database creation is not recommended, especially with the default Pgbouncer connection pool. If you create databases manually, you must maintain the Pgbouncer database list and keep it consistent with PostgreSQL. The pgsql-db tool and pgsql-db.yml playbook also add new databases to the Pgbouncer database list.

If the database owner differs from the default dbsu, postgres, verify that the owner user exists first. Always create users before you create databases.

Pgbouncer Database

By default, Pigsty enables one Pgbouncer connection pool for each PostgreSQL instance. The pool connects through the /var/run/postgresql Unix socket.

Connection pools can improve performance for short connections and reduce concurrency contention. They prevent excessive connections from overloading the database. They also provide more options during database migration.

By default, Pigsty adds all databases from pg_databases to the Pgbouncer database list. To exclude a database, set pgbouncer: false in its definition.

The Pgbouncer database list is in /etc/pgbouncer/database.txt. This file includes pool parameters from each database definition:

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 you create databases, Pigsty refreshes the Pgbouncer database list. An online configuration reload applies the changes, normally without affecting existing connections.

Pgbouncer runs as the same dbsu as PostgreSQL. The default is the postgres operating system user. As dbsu, use the pgb alias for Pgbouncer administration.

To route a managed Pgbouncer database to another node, edit /etc/pgbouncer/database.txt. Then reload the configuration and rebuild existing server connections:

# Change only mydb's backend target
sed -i -E '/^mydb[[:space:]]*=/ s#host=[^[:space:]]+#host=10.10.10.12#' /etc/pgbouncer/database.txt
pgb -c "RELOAD;"
pgb -c "RECONNECT mydb;"
pgb -c "WAIT_CLOSE mydb;"

The current pgb-route helper edits only /etc/pgbouncer/pgbouncer.ini. Pigsty stores managed database routes in the included database.txt file. Thus, this helper does not change managed routes.