Customize database

Configure business 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[]):数据库中额外安装的扩展,每个扩展包括nameschema两个字段。

    例如{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的定义不存在,则会在检查阶段报错。

Last modified 2021-03-28: update en docs (f994b54)