PGSQL Customize

Customize postgres cluster content & database objects

Pigsty provides nearly 100 parameters on PGSQL describing the PostgreSQL cluster.

However, if you profoundly customize the database cluster created by Pigsty, you can see the Patroni template and Postgres template.

Patroni Templates

Pigsty uses Patroni to manage and initialize Postgres clusters. Suppose you wish to modify the default config params, specifications and tuning schemes, HA policies, DCS access, and control APIs of the PostgreSQL cluster. You can do so by modifying the Patroni template.

Pigsty uses Patroni to do the main provisioning work. Even if the user selects no Patroni mode, pulling up the database cluster will be taken care of by Patroni, and removing the Patroni component after the creation is completed.

Users can do most PostgreSQL cluster customization through the Patroni config file. Pleargv4se refer to Patroni’s official doc for the Patroni config file format.

Predefined Patroni templates

Pigsty provides several predefined initialization templates for initializing the cluster definition files, located by default in roles/postgres/templates/.

Specify the path to the template to be used via the pg_conf, or fill in the template name if using a predefined template. If a custom Patroni config template is used, the companion node optimization template should also be used for the machine nodes.

pg_conf:   tiny.yml      # Using tiny.yml to tune templates
node_tune: tiny          # Node Tuning Mode：oltp|olap|crit|tiny

During Configure, Pigsty detects the corresponding default specifications that are automatically selected based on the specifications of the current machine (management machine).

Custom Patroni templates

When customizing Patroni templates, you can use several existing templates as a baseline from which to make changes.

Place them in the templates/ dir, just name them in <mode>.yml format.

Please keep the template variables in Patroni, otherwise, the related parameters may not work properly. For example pg_libs.

Finally, in the pg_conf config file, specify the name of your newly created template, e.g., olap-32C128G-nvme.yml.

Postgres templates

The template template1 in the cluster can be customized using the PG template config entry.

This way ensures that any database newly created in that cluster comes with the same default config: schema, extensions, and default privileges.

Related docs

When customizing a template, the relevant parameters are first rendered as SQL scripts to be executed on the deployed cluster.

^---/pg/bin/pg-init
          |
          ^---(1)--- /pg/tmp/pg-init-roles.sql
          ^---(2)--- /pg/tmp/pg-init-template.sql
          ^---(3)--- <other customize logic in pg-init>
# Business users and DB are not created in the template customization
^-------------(4)--- /pg/tmp/pg-user-{{ user.name }}.sql
^-------------(5)--- /pg/tmp/pg-db-{{ db.name }}.sql

pg-init

pg-init is the path to a Shell script for customizing the initialization template that will be executed as a Postgres user, only on the primary, with the primary pulled up at execution. It can run any shell command or any SQL command via psql.

Pigsty will use the default pg-init shell script if this config entry is not specified.

#!/usr/bin/env bash
set -uo pipefail
#==================================================================#
#                          Default Roles                           #
#==================================================================#
psql postgres -qAXwtf /pg/tmp/pg-init-roles.sql
#==================================================================#
#                          System Template                         #
#==================================================================#
# system default template
psql template1 -qAXwtf /pg/tmp/pg-init-template.sql
# make postgres same as templated database (optional)
psql postgres  -qAXwtf /pg/tmp/pg-init-template.sql
#==================================================================#
#                          Customize Logic                         #
#==================================================================#
# add your template logic here

This script can be appended if the user needs to perform complex customization logic. Note pg-init is used to customize database clusters, usually achieved by modifying database templates. When this script is executed, the cluster has been started, but the business users and DB have not yet been created. Therefore the changes to the database templates are reflected in the business database defined by default.

Last modified 2022-06-04: fii en docs batch 2 (61bf601)