Customize database
可以通过 pg_databases 定制集群特定的业务数据库。
样例
一个完整的数据库定义由一个JSON/YAML对象构成，如下所示：
- name: meta # name is the only required field for a database
owner: postgres # optional, database owner
template: template1 # optional, template1 by default
encoding: UTF8 # optional, UTF8 by default , must same as template database, leave blank to set to db default
locale: C # optional, C by default , must same as template database, leave blank to set to db default
lc_collate: C # optional, C by default , must same as template database, leave blank to set to db default
lc_ctype: C # optional, C by default , must same as template database, leave blank to set to db default
allowconn: true # optional, true by default, false disable connect at all
revokeconn: false # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
tablespace: pg_default # optional, 'pg_default' is the default tablespace
connlimit: -1 # optional, connection limit, -1 or none disable limit (default)
schemas: [public,monitor] # create additional schema
extensions: # optional, extension name and where to create
- {name: postgis, schema: public}
parameters: # optional, extra parameters with ALTER DATABASE
enable_partitionwise_join: true
pgbouncer: true # optional, add this database to pgbouncer list? true by default
comment: pigsty meta database # optional, comment string for database
说明
一个数据库对象由以下键值构成，只有数据库名是必选项，其他参数均为可选，不添加相应键则会使用默认值。
-
name(string)： 数据库名称，必选项 -
owner(string)：数据库的属主，必须为已存在的用户（用户先于数据库创建）。 -
template(string)：创建数据库时所使用的模板，默认为template1。 -
encoding(enum)：数据库使用的字符集编码，默认为UTF8，必须与实例和模板数据库保持一致。 -
locale(enum)：数据库使用的本地化规则，默认与实例和模板数据库保持一致，建议不要修改。 -
lc_collate(enum)：数据库使用的本地化字符串排序规则，默认为与实例和模板数据库保持一致，建议不要修改。 -
lc_ctype(enum)：数据库使用的本地化规则，默认与实例和模板数据库保持一致，建议不要修改。 -
allowconn(bool)：是否允许连接至数据库，默认允许。 -
revokeconn(bool)：是否回收PUBLIC默认连接至数据库的权限？默认不回收，建议在多DB实例上开启。 -
tablespace(string)：数据库的默认表空间，默认为pg_default。 -
connlimit(number)： 是否限制数据库的连接数量？留空或-1不限，默认不限 -
schemas(string[])：需要在该数据库中额外创建的模式（默认会创建monitor模式） -
extensions(extension[])：数据库中额外安装的扩展，每个扩展包括name与schema两个字段。例如
{name: postgis, schema: public}指示Pigsty在该数据库的public模式下安装PostGIS扩展 -
pgbouncer(bool)： 是否将数据库加入连接池DB列表中？默认加入 -
parameters(dict)： 针对数据库额外修改配置参数，k-v结构 -
comment(string)： 数据库备注说明信息
实现
pg_databases 是数据库定义对象构成的数组，会依次渲染为主库上的SQL文件：
/pg/tmp/pg-db-{{ database.name }}.sql
并依次执行。一个实际渲染的例子如下所示：
----------------------------------------------------------------------
-- File : pg-db-meta.sql
-- Path : /pg/tmp/pg-db-meta.sql
-- Time : 2021-03-22 22:52
-- Note : managed by ansible, DO NOT CHANGE
-- Desc : creation sql script for database meta
----------------------------------------------------------------------
--==================================================================--
-- EXECUTION --
--==================================================================--
-- run as dbsu (postgres by default)
-- createdb -w -p 5432 'meta';
-- psql meta -p 5432 -AXtwqf /pg/tmp/pg-db-meta.sql
--==================================================================--
-- CREATE DATABASE --
--==================================================================--
-- create database with following commands
-- CREATE DATABASE "meta" ;
-- following commands are executed within database "meta"
--==================================================================--
-- ALTER DATABASE --
--==================================================================--
-- owner
-- tablespace
-- allow connection
ALTER DATABASE "meta" ALLOW_CONNECTIONS True;
-- connection limit
ALTER DATABASE "meta" CONNECTION LIMIT -1;
-- parameters
ALTER DATABASE "meta" SET enable_partitionwise_join = True;
-- comment
COMMENT ON DATABASE "meta" IS 'pigsty meta database';
--==================================================================--
-- REVOKE/GRANT CONNECT --
--==================================================================--
--==================================================================--
-- REVOKE/GRANT CREATE --
--==================================================================--
-- revoke create (schema) privilege from public
REVOKE CREATE ON DATABASE "meta" FROM PUBLIC;
-- only admin role have create privilege
GRANT CREATE ON DATABASE "meta" TO "dbrole_admin";
-- revoke public schema creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- admin can create objects in public schema
GRANT CREATE ON SCHEMA public TO "dbrole_admin";
--==================================================================--
-- CREATE SCHEMAS --
--==================================================================--
-- create schemas
--==================================================================--
-- CREATE EXTENSIONS --
--==================================================================--
-- create extensions
CREATE EXTENSION IF NOT EXISTS "postgis" WITH SCHEMA "public";
--==================================================================--
-- PGBOUNCER DATABASE --
--==================================================================--
-- database will be added to pgbouncer database list by default,
-- unless pgbouncer is explicitly set to 'false', means hidden database
-- Database 'meta' will be added to /etc/pgbouncer/database.txt via
-- /pg/bin/pgbouncer-create-db 'meta'
--==================================================================--
连接池
Pgbouncer有自己的数据库定义文件，通常是PG数据库的一个子集。
在Pigsty中，Pgbouncer的数据库定义文件位于：/etc/pgbouncer/database.txt
$ cat database.txt
meta = host=/var/run/postgresql
只有在该文件中出现的数据库，才可以通过PGbouncer访问。pgbouncer选项显式配置为false的数据库不会被添加至连接池DB列表中。修改该配置文件需要reload Pgbouncer方可生效。
导出
以下SQL查询可以以JSON格式导出当前数据库的定义（需少量修正）
psql -AXtw <<-EOF
SELECT jsonb_pretty(row_to_json(final)::JSONB)
FROM (SELECT datname AS name,
datdba::RegRole::Text AS owner,
encoding,
datcollate AS lc_collate,
datctype AS lc_ctype,
datallowconn AS allowconn,
datconnlimit AS connlimit,
(SELECT json_agg(nspname) AS schemas FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname NOT IN ('information_schema', 'monitor', 'repack')),
(SELECT json_agg(row_to_json(ex)) AS extensions FROM (SELECT extname, extnamespace::RegNamespace AS schema FROM pg_extension WHERE extnamespace::RegNamespace::TEXT NOT IN ('information_schema', 'monitor', 'repack', 'pg_catalog')) ex),
(SELECT json_object_agg(substring(cfg, 0 , strpos(cfg, '=')), substring(cfg, strpos(cfg, '=')+1)) AS value FROM
(SELECT unnest(setconfig) AS cfg FROM pg_db_role_setting s JOIN pg_database d ON d.oid = s.setdatabase WHERE d.datname = current_database()) cf
)
FROM pg_database WHERE datname = current_database()
) final;
EOF
创建
请尽可能通过声明的方式创建业务数据库，而不是在数据库中手工创建。因为业务用户与业务数据库需要同时在数据库与连接池中进行变更。
在运行中的数据库集群中创建新的业务数据库，首先应当在集群级配置中添加新数据库的定义，例如在pg-test.vars.pg_databases加入新的数据库对象。然后可以使用pgsql-createdb剧本创建数据库：
例如，在pg-test 集群中创建或修改名为test的数据库，可以执行以下命令。
./pgsql-createdb.yml -l <pg_cluster> -e pg_database=test
如果数据库test的定义不存在，则会在检查阶段报错。