Add per database config for pgbouncer

[Vonng]

Propose

Set a per-database parameters for pgbouncer [database]

Add pgbouncer_poolmode and pgbouncer_max_db_conn to database definition.

pg_databases:                       # define business databases on this cluster, array of database definition
  
  # define the default `meta` database
  - 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/)
    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)
    comment: pigsty meta database   # optional, comment string for this database
    connlimit: -1                   # optional, database connection limit, default -1 disable limit
    schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
    extensions:                     # optional, additional extensions to be installed: array of schema definition `{name,schema}`
      - {name: adminpack, schema: pg_catalog}    # install adminpack to pg_catalog and install postgis to public
      - {name: postgis, schema: public}          # if schema is omitted, extension will be installed according to search_path.

    pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
    pgbouncer_poolmode: transaction # optional, per-database config which override global `pgbouncer_poolmode` 
    pgbouncer_max_db_conn: 100      # optional, per-database config which override global `pgbouncer_max_db_conn` 

to achieve this, we have to :

1. add extra parameter poolmode, connlimit for script pgbouncer-create-db
2. change roles/postgres/pgbouncer and roles/postgres/createdb to supply that parameter if exists in database definition.

files to be changed:

roles/postgres/pgbouncer.yml
roles/postgres/createdb.yml
roles/postgres/files/pg/pgbouncer-create-db

to upgrade this, one has to

1. update pigsty source code with : git pull
2. update /pg/bin/ content with: ./pgsql.yml -t pg_scripts

benefit

poolmode

For a multi-database cluster. different database may have different requirement.

For internet applications. Transaction Pooling is extremely useful to reduce connection overhead.

for some traditional business application. Lack of session ability (e.g prepared statements) is unacceptable.

Now you designate different pool_mode for different database rather than set it globally.

max_db_connection

This allows the user to control connection threshold in pgbouncer

if pgbouncer.max_db_connections > min(postgres.max_connections, database.connlimit)

certain errors may occur when pgbouncer trying to connect to postgres during high-load.

It's reasonable to set a pgbouncer_max_db_conn if database.connlimit is explicitly set.