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/.

Conf CPU Mem Disk Description
oltp 64 400GB 4TB Production OLTP template, default config, optimized latency and performance for production models.
olap 64 400GB 4TB Produce OLAP templates, improve parallelism, optimize for throughput, long queries.
crit 64 400GB 4TB Production core business templates, based on OLTP templates optimized for RPO, security, and data integrity, with synchronous replication and data checksum, enabled.
tiny 1 1GB 40GB Micro templates optimized for low-resource scenarios, such as demo clusters running in virtual machines.
mini 2 4GB 100GB 2C4G model OLTP template
small 4 8GB 200GB 4C8G model OLTP template
medium 8 16GB 500GB 8C16G model OLTP template
large 16 32GB 1TB 16C32G model OLTP template
xlarge 32 64GB 2TB 32C64G model OLTP template

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.

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

          ^---(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-{{ }}.sql
^-------------(5)--- /pg/tmp/pg-db-{{ }}.sql


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)