-
Notifications
You must be signed in to change notification settings - Fork 256
PGSQL DB
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.
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:
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 }
Each database definition is a dict with the following 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 `{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
during cluster bootstrap.
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:
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 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:
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 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.
# 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
}