# Database > In this context, Database refers to the object created by SQL `CREATE DATABASE`. --- A PostgreSQL server can serve multiple databases simultaneously. You can manage them with Pigsty. ------ ## Define Database Business databases are defined by [`pg_databases`](/docs/pgsql/param#pg_databases), which is a cluster-level parameter. For example, the default `meta` database is defined in the `pg-meta` cluster: ```yaml pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_databases: - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]} - { name: grafana ,owner: dbuser_grafana ,revokeconn: true ,comment: grafana primary database } - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database } - { name: kong ,owner: dbuser_kong ,revokeconn: true ,comment: kong the api gateway database } - { name: gitea ,owner: dbuser_gitea ,revokeconn: true ,comment: gitea meta database } - { name: wiki ,owner: dbuser_wiki ,revokeconn: true ,comment: wiki meta database } - { name: noco ,owner: dbuser_noco ,revokeconn: true ,comment: nocodb database } ``` Each database definition is a dict with the following fields: ```yaml - 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 `{name[,schema]}` - { name: postgis , schema: public } - { name: timescaledb } 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 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) 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. (leave connect with grant option to owner) register_datasource: true # optional, register this database to grafana datasources? true by default connlimit: -1 # optional, database connection limit, default -1 disable limit pool_auth_user: dbuser_meta # optional, all connection to this pgbouncer database will be authenticated by this user pool_mode: transaction # optional, pgbouncer pool mode at database level, default transaction pool_size: 64 # optional, pgbouncer pool size at database level, default 64 pool_size_reserve: 32 # optional, pgbouncer pool size reserve at database level, default 32 pool_size_min: 0 # optional, pgbouncer pool size min at database level, default 0 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`](/docs/pgsql/param#pg_provision) during cluster bootstrap. Check [ACL: Database Privilege](/docs/pgsql/acl#database-privilege) for details about database-level privilege. ------ ## Create Database Databases [defined](#define-database) in [`pg_databases`](/docs/pgsql/param#pg_databases) will be automatically created during module installation. If you wish to [create database](/docs/pgsql/admin#create-database) on an existing cluster, the `bin/pgsql-db` util can be used. Add new database definition to `all.children..pg_databases`, and create that database with: ```bash tab="utils" bin/pgsql-db # the bin util script bin/pgsql-db pg-meta meta # example: create meta database in pg-meta cluster ``` ```bash tab="playbook" ./pgsql-db.yml -l -e dbname= # the actual playbook ./pgsql-db.yml -l pg-meta -e dbname=meta # example: create meta database in pg-meta cluster ``` This playbook is usually idempotent and can be re-run to flush the database definition. But if you have non-trivial `baseline` schema (like drop stuff), you should **NOT** re-run this on existing databases. **Create postgres database with pigsty** Pigsty will manage [pgbouncer database list](#pgbouncer-database), so please create business databases with the Pigsty playbook/utils. Check [create database](/docs/pgsql/admin#create-database) SOP for details. If you are not using pgbouncer or able to maintain it by yourself, you can create databases in any way you like. **Create owner before create database** If your database has a non-trivial `owner` (dbsu `postgres` by default), make sure the owner user exists before creating the database. In short, always [create](/docs/pgsql/admin#create-user) the [users](/docs/pgsql/user) before creating databases. ------ ## Pgbouncer Database Pgbouncer is enabled by default and serves as connection pool middleware. Pigsty will add all databases in [`pg_databases`](/docs/pgsql/param#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](#define-database). The Pgbouncer database list will be updated when [create database](#create-database) with Pigsty util & playbook. Databases are listed in `/etc/pgbouncer/database.txt`, with extra database-level parameters: ```ini {title="/etc/pgbouncer/database.txt"} meta = host=/var/run/postgresql mode=session grafana = host=/var/run/postgresql mode=transaction bytebase = host=/var/run/postgresql auth_user=dbuser_meta kong = host=/var/run/postgresql pool_size=32 reserve_pool=64 gitea = host=/var/run/postgresql min_pool_size=10 wiki = host=/var/run/postgresql noco = host=/var/run/postgresql mongo = host=/var/run/postgresql ``` When you [create databases](/docs/pgsql/db#create-database), the Pgbouncer database list definition file will be refreshed and take effect through online configuration reload, without affecting existing connections. To access pgbouncer admin functionality, you can use the `pgb` alias as dbsu (`postgres`). Check [pgbouncer usage](https://www.pgbouncer.org/usage.html) for available commands: ```bash {title="postgres"} sudo su - postgres # switch to the postgres dbsu pgb # access the pgbouncer admin virtual database ``` There’s a util function defined in `/etc/profile.d/pg-alias.sh`, allowing you to reroute pgbouncer database traffic to a new host quickly, which can be used during zero-downtime migration. ```bash {title="/etc/profile.d/pg-alias.sh"} # route pgbouncer traffic to another cluster member 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 } ```