Unattended upgrades

On Ubuntu, it’s recommended to disable unattended-upgrades to avoid unexpected database restarts.

PostgreSQL instance parameter tuning

You may increase pg_max_conn as needed, but no more than 5000 unless you are prepared to bump HAProxy limits as well and face issues that Azure decided not to deal with in their Azure Database for PostgreSQL product. If possible, add more read replicas and optimize your queries instead of that.

You may also want to adjust WAL size settings in https://github.com/pgsty/pigsty/blob/v3.7.0/roles/pgsql/templates/olap.yml#L317-L319. The defaults could be wasteful.

Single-instance PgBouncer bottleneck

See https://github.com/pgsty/pigsty/issues/500.

Node DNS and `/etc/hosts` modifications

To prevent such modifications, set

node_write_etc_hosts: false
node_dns_method: none

in global vars section of pigsty.yml.

Bash history

roles/node/files/node.sh sets

export HISTSIZE=65535
export HISTFILESIZE=$HISTSIZE
export HISTCONTROL=ignoredups
export HISTIGNORE="l:ls:cd:cd -:pwd:exit:date:* --help"

which means ls, cd and xxx --help commands will not be recorded in bash history.

Default user for psql

Pigsty creates ~/.pigsty on the admin node with this content:

export PGUSER=dbuser_dba
export PGDATABASE=postgres

(from roles/infra/templates/env/pigsty.j2)

This file is sourced in ~/.bashrc:

[ -f ~/.pigsty ] && . ~/.pigsty

(configured in roles/infra/tasks/env.yml)

Password is stored in ~/.pgpass with entries for dbuser_dba, so no password prompt is needed.

Therefore, if you are running psql as the ansible_user during Pigsty setup, you will log in as dbuser_dba automatically.

If you use sudo -u postgres psql instead, you would log in as postgres via local peer authentication. Both are superuser, but the default parameters could be different.

Customize psqlrc

.psqlrc under the postgres user’s home directory defines

\set PROMPT1 '%[%033[0;31;31m%]%n@%`hostname`:%>/%/=%#%[%033[0m%] '
\set PROMPT2 '%[%033[0;31;31m%]%/%R%#%[%033[0m%] '

and a set of utility meta-commands that could be invoked via SQL Interpolation:

-- Check database sizes
:dbsize

-- View active connections
:conninfo

-- Find slow queries
:slowquery

-- Check for lock conflicts
:locks

-- Find bloated tables
:tablebloat

Backup multi-processing (fixed in v4.0)

How can I configure options independently for each command? https://pgbackrest.org/faq.html#optimize-config

pgBackRest has the ability to set options independently in the configuration file for each command. Configure Cluster Stanza details this feature as well as option precedence.

For example, the process-max option can be optimized for each command: …

As shown in commit 16fe7ab5c7149c1fbb7497faadd840c8a91bde8d, you could configure [global:backup] for parallel backups.

diff --git a/roles/pgsql/templates/pgbackrest.conf b/roles/pgsql/templates/pgbackrest.conf
index 4d9b5cb51..5fd571bfa 100644
--- a/roles/pgsql/templates/pgbackrest.conf
+++ b/roles/pgsql/templates/pgbackrest.conf
@@ -26,6 +26,7 @@ pg-version-force={{ pg_version|default(18) }}
 #--------------------------------------------------------------#
 [global]
 archive-async=y
+archive-push-queue-max=4GiB
 
 #--------------------------------------------------------------#
 # 3. backup
@@ -62,7 +63,10 @@ repo1-{{ k|replace('_', '-') }}={{ v|replace('${pg_cluster}', pg_cluster) }}
 #--------------------------------------------------------------#
 # 8. restore
 #--------------------------------------------------------------#
-archive-mode=off
+# archive-mode is intentionally NOT set here
+# For disaster recovery (new primary): use default (archive enabled)
+# For test/dev restore: add --archive-mode=off on command line
+# For exploratory PITR: add --archive-mode=off until correct point found
 delta=y
 
 #--------------------------------------------------------------#
@@ -77,11 +81,18 @@ delta=y
 #--------------------------------------------------------------#
 # 10. adhoc (parallel)
 #--------------------------------------------------------------#
+[global:backup]
+# 1/4, least 2, max 4
+process-max={{ ([ ([ (node_cpu | int / 4) | round(0, 'ceil'), 2 ]) | max, 4 ]) | min | int }}
+
 [global:restore]
-process-max={{ node_cpu | int }}
+# use full cpu, max 8
+process-max={{ ([ node_cpu | int, 8 ]) | min | int }}
 
 [global:archive-get]
-process-max={{ ([( node_cpu | int / 4) | round(0, 'ceil'), 2 ]) | min | int }}
+# 1/4, least 2, max 4
+process-max={{ ([ ([ (node_cpu | int / 4) | round(0, 'ceil'), 2 ]) | max, 4 ]) | min | int }}
 
 [global:archive-push]
-process-max={{ ([ (node_cpu | int / 2) | round(0, 'ceil'), 4 ]) | min | int }}
+# async push with 1 / 4
+process-max={{ ([ ([ (node_cpu | int / 4) | round(0, 'ceil'), 2 ]) | max, 4 ]) | min | int }}

Note that archive-push-queue-max may need to be tuned higher if you have a slow repository, e.g. an S3 bucket at a distant region.

Offline single-node install

curl -fLo /tmp/pkg.tgz 'https://github.com/pgsty/pigsty/releases/download/v3.7.0/pigsty-pkg-v3.7.0.u24.aarch64.tgz'
./bootstrap -k
./configure

Then modify pigsty.yml to use local repo because the default is to use the infra node at http://${admin_ip}/pigsty:

node_repo_modules: local # use pre-made local repo rather than install from upstream
repo_upstream:
  - { name: pigsty-local ,description: 'Pigsty Local' ,module: local ,releases: [24] ,arch: [aarch64] ,baseurl: { default: 'file:/www/pigsty/ ./' }}

Note that only the latest PostgreSQL packages are included in the offline package downloaded from GitHub releases, so you are on your own if you need an older version.

Useful dashboards

PGCAT Instance (session breakdown)
PGCAT Database
PGSQL Tables (tuples changed)
PGRDS Cluster
PGRDS Instance
PGSQL Activity (Postgres load measured by total Exec Time Spent across databases)
PGSQL Persist
PGSQL PITR (backups)

Other notes:

PGCAT data are not bound by the time range selected on a dashboard. Reset the statistics with:

SELECT monitor.pg_stat_statements_reset();

The PGCAT Schema dashboard may default to a random schema with no data, and treemap panels like those in the Bloat row will say “Configure your query” as if it’s not configured. This is by design and cannot be fixed.
The Column Stat table in PGCAT Table will not display for tables dbuser_monitor doesn’t have SELECT privilege on. Because the dbuser_monitor user doesn’t have the BYPASSRLS attribute, this also includes any table with RLS enabled.

Sync Grafana dashboards from source

# dashboard_sync copies dashboards to /infra/dashboards/ for dashboard_init to read
./infra.yml -t dashboard_sync,dashboard_init

Installing extensions

NOTE

pgsql-feat, etc. already includes a lot of extensions. Check for duplicates before adding more to the pg_extensions list.

For example, install hypopg on the monitor schema:

CREATE EXTENSION "hypopg" WITH SCHEMA "monitor";

etcd authentication

etcdctl user get pg-meta # show user
etcdctl role get pg-meta # show permissions

Bytebase demo

--- pigsty.yml.old	2026-02-02 00:10:24.639422176 +0000
+++ pigsty.yml	2026-02-02 00:12:48.349472796 +0000
@@ -23,6 +23,25 @@
     etcd:  { hosts: { 127.0.0.1: { etcd_seq: 1  }} ,vars: { etcd_cluster: etcd  }}
     pgsql: { hosts: { 127.0.0.1: { pg_seq: 1, pg_role: primary  }} ,vars: { pg_cluster: pgsql }}
     #minio: { hosts: { 127.0.0.1: { minio_seq: 1 }} ,vars: { minio_cluster: minio }}
+    app:
+      hosts: { 127.0.0.1: {} }
+      vars:
+        docker_enabled: true                # enabled docker with ./docker.yml
+        #docker_registry_mirrors: ["https://docker.1panel.live","https://docker.1ms.run","https://docker.xuanyuan.me","https://registry-1.docker.io"]
+        app: bytebase                       # specify the default app name to be installed (in the apps)
+        apps:                               # define all applications, appname: definition
+
+          # Admin GUI for PostgreSQL, launch with: ./app.yml
+          pgadmin:                          # pgadmin app definition (app/pgadmin -> /opt/pgadmin)
+            conf:                           # override /opt/pgadmin/.env
+              PGADMIN_DEFAULT_EMAIL: admin@pigsty.cc   # default user name
+              PGADMIN_DEFAULT_PASSWORD: pigsty         # default password
+
+          # Schema Migration GUI for PostgreSQL, launch with: ./app.yml -e app=bytebase
+          bytebase:
+            conf:
+              BB_DOMAIN: http://ddl.pigsty  # replace it with your public domain name and postgres database url
+              BB_PGURL: "postgresql://dbuser_bytebase:<CHANGE ME>@127.0.0.1:5432/bytebase?sslmode=prefer"
 
   vars:
 
@@ -35,6 +54,7 @@
     dns_enabled: false                # disable dnsmasq service on single node
     infra_portal:
       home : { domain: i.pigsty }
+      bytebase  : { domain: ddl.pigsty ,endpoint: "${admin_ip}:8887" }
     proxy_env:                        # global proxy env when downloading packages
       no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.myqcloud.com,*.tsinghua.edu.cn"
       # http_proxy:  # set your proxy here: e.g http://user:pass@proxy.xxx.com
@@ -68,8 +88,10 @@
     pg_users:
       - { name: dbuser_meta ,password: <25-character random characters>   ,pgbouncer: true ,roles: [dbrole_admin   ] ,comment: pigsty admin user }
       - { name: dbuser_view ,password: <25-character random characters> ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer  }
+      - { name: dbuser_bytebase ,password: <CHANGE ME> ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database   }
     pg_databases:
       - { name: meta, baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [ postgis, timescaledb, vector ]}
+      - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
     pg_libs: 'timescaledb, pg_stat_statements, auto_explain, pg_wait_sampling'
     pg_hba_rules:
       - { user: all ,db: all ,addr: intra ,auth: pwd ,title: 'everyone intranet access with password' ,order: 800 }

General PostgreSQL notes

Vacuuming

You may increase autovacuum_freeze_max_age to avoid the emergency vacuum kicking in too often. Often 500 million or 1 billion is fine.
You can use the vacuumdb command instead of SQL commands. There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the server, but --jobs comes in handy.

vacuumdb --all --freeze --jobs=2 --echo --analyze

You can log into template1 to fix it if you want, but you won’t be able to log into template0. template0 can safely be ignored and let run to autovacuum_freeze_max_age since it’s extremely small and will finish nearly instantaneously. – Keith Fiske

Read-only role for AI analysis

CREATE ROLE xxx LOGIN BYPASSRLS ROLE dbrole_readonly PASSWORD 'your secret password';
 
ALTER ROLE xxx SET search_path TO "\$user", public, extensions;
ALTER ROLE xxx SET default_transaction_read_only = on;
ALTER ROLE xxx SET statement_timeout = '1min';

EXPLAIN in pgAdmin 4 query tool

Make sure you have selected “Costs” in the dropdown menu. It applies to explain too.
For explain analyze, also select “Timing”.

References

https://github.com/pgsty/pigsty/blob/v3.7.0/roles/node_id/vars/u24.aarch64.yml#L40

🪴 Zero's Garden

Explorer

Pigsty notes

Unattended upgrades

PostgreSQL instance parameter tuning

Single-instance PgBouncer bottleneck

Node DNS and `/etc/hosts` modifications

Bash history

Default user for psql

Customize psqlrc

Backup multi-processing (fixed in v4.0)

Offline single-node install

Useful dashboards

Sync Grafana dashboards from source

Installing extensions

etcd authentication

Bytebase demo

General PostgreSQL notes

Vacuuming

Read-only role for AI analysis

EXPLAIN in pgAdmin 4 query tool

References

Graph View

Table of Contents

🪴 Zero's Garden

Explorer

Pigsty notes

Unattended upgrades

PostgreSQL instance parameter tuning

Single-instance PgBouncer bottleneck

Node DNS and /etc/hosts modifications

Bash history

Default user for psql

Customize psqlrc

Backup multi-processing (fixed in v4.0)

Offline single-node install

Useful dashboards

Sync Grafana dashboards from source

Installing extensions

etcd authentication

Bytebase demo

General PostgreSQL notes

Vacuuming

Read-only role for AI analysis

EXPLAIN in pgAdmin 4 query tool

References

Graph View

Table of Contents

Node DNS and `/etc/hosts` modifications