Databases

Define business databases in PostgreSQL

In this context, Database refers to object created by SQL CREATE DATABASE.

A postgres server can serve multiple databases simultaneously. And you can customize each database with Pigsty API.

Define Database

Business databases are defined by pg_databases, which is a cluster-level parameter, for example, the default meta database is defined in the pg-meta cluster:

  1. pg-meta:
  2.   hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  3.   vars:
  4.     pg_cluster: pg-meta
  5.     pg_databases:
  6.       - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public},{name: timescaledb}]}
  7.       - { name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database }
  8.       - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
  9.       - { name: kong     ,owner: dbuser_kong     ,revokeconn: true ,comment: kong the api gateway database }
  10.       - { name: gitea    ,owner: dbuser_gitea    ,revokeconn: true ,comment: gitea meta database }
  11.       - { name: wiki     ,owner: dbuser_wiki     ,revokeconn: true ,comment: wiki meta database }

Each database definition is a dict with the following fields:

  1. - name: meta                      # REQUIRED, `name` is the only mandatory field of a database definition
  2.   baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
  3.   pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
  4.   schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
  5.   extensions:                     # optional, additional extensions to be installed: array of `{name[,schema]}`
  6.     - { name: postgis , schema: public }
  7.     - { name: timescaledb }
  8.   comment: pigsty meta database   # optional, comment string for this database
  9.   owner: postgres                 # optional, database owner, postgres by default
  10.   template: template1             # optional, which template to use, template1 by default
  11.   encoding: UTF8                  # optional, database encoding, UTF8 by default. (MUST same as template database)
  12.   locale: C                       # optional, database locale, C by default.  (MUST same as template database)
  13.   lc_collate: C                   # optional, database collate, C by default. (MUST same as template database)
  14.   lc_ctype: C                     # optional, database ctype, C by default.   (MUST same as template database)
  15.   tablespace: pg_default          # optional, default tablespace, 'pg_default' by default.
  16.   allowconn: true                 # optional, allow connection, true by default. false will disable connect at all
  17.   revokeconn: false               # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
  18.   register_datasource: true       # optional, register this database to grafana datasources? true by default
  19.   connlimit: -1                   # optional, database connection limit, default -1 disable limit
  20.   pool_auth_user: dbuser_meta     # optional, all connection to this pgbouncer database will be authenticated by this user
  21.   pool_mode: transaction          # optional, pgbouncer pool mode at database level, default transaction
  22.   pool_size: 64                   # optional, pgbouncer pool size at database level, default 64
  23.   pool_size_reserve: 32           # optional, pgbouncer pool size reserve at database level, default 32
  24.   pool_size_min: 0                # optional, pgbouncer pool size min at database level, default 0
  25.   pool_max_db_conn: 100           # optional, max database connections at database level, default 100

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

Newly created databases are forked from template1 database by default. which is customized by PG_PROVISION during cluster bootstrap.

Create Database

Databases defined in pg_databases will be automatically created during cluster bootstrap.

If you wish to create database on an existing cluster, the bin/pgsql-db util can be used.

Add new database definition to all.children.<cls>.pg_databases, and create that database with:

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

It’s usually not a good idea to execute this on the existing database again when a baseline script is used.

If you are using the default pgbouncer as the proxy middleware, YOU MUST create the new database with pgsql-db util or pgsql-db.yml playbook. Otherwise, the new database will not be added to the pgbouncer database list.

Remember, if your database definition has a non-trivial owner (dbsu postgres by default ), make sure the owner user exists. That is to say, always create the user before the database.

Pgbouncer Database

Pgbouncer is enabled by default and serves as a connection pool middleware.

Pigsty will add all databases in pg_databases to the pgbouncer database list by default. You can disable the pgbouncer proxy for a specific database by setting pgbouncer: false in the database definition.

The database is listed in /etc/pgbouncer/database.txt, with extra database-level parameters such as:

  1. pool_auth_user: dbuser_meta     # optional, all connection to this pgbouncer database will be authenticated by this user
  2. pool_mode: transaction          # optional, pgbouncer pool mode at database level, default transaction
  3. pool_size: 64                   # optional, pgbouncer pool size at database level, default 64
  4. pool_size_reserve: 32           # optional, pgbouncer pool size reserve at database level, default 32
  5. pool_size_min: 0                # optional, pgbouncer pool size min at database level, default 0
  6. pool_max_db_conn: 100           # optional, max database connections at database level, default 100

The Pgbouncer database list will be updated when create database with Pigsty util & playbook.

To access pgbouncer administration functionality, you can use the pgb alias as dbsu.

There’s a util function defined in /etc/profile.d/pg-alias.sh, that can allow you to reroute pgbouncer database traffic to a new host quickly, which can be used during zero-downtime migration.

  1. # route pgbouncer traffic to another cluster member
  2. function pgb-route(){
  3.   local ip=${1-'\/var\/run\/postgresql'}
  4.   sed -ie "s/host=[^[:space:]]\+/host=${ip}/g" /etc/pgbouncer/pgbouncer.ini
  5.   cat /etc/pgbouncer/pgbouncer.ini
  6. }

Last modified 2023-02-27: refresh en docs to v2.0 (e82b371)