🪴 Zero's Garden

Pigsty notes

4 min read

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

  1. Pigsty creates ~/.pigsty on the admin node with this content:
export PGUSER=dbuser_dba
export PGDATABASE=postgres

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

  1. This file is sourced in ~/.bashrc:
[ -f ~/.pigsty ] && . ~/.pigsty

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

  1. 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: …

You could configure [global:backup] for parallel backups. For example, min(node_cpu / 2, 4):

diff --git a/roles/pgsql/templates/pgbackrest.conf b/roles/pgsql/templates/pgbackrest.conf
index d714d2c..0b17551 100644
--- a/roles/pgsql/templates/pgbackrest.conf
+++ b/roles/pgsql/templates/pgbackrest.conf
@@ -94,6 +94,9 @@ archive-mode=off
 #--------------------------------------------------------------#
 # 10. adhoc (parallel)                                         #
 #--------------------------------------------------------------#
+[global:backup]
+process-max={{ ([(node_cpu|int / 2)|round(0,'ceil'), 4])|min|int }}
+
 [global:restore]
 process-max={{ node_cpu|int }}

Optimistically, this should reduce full backup time significantly, but be careful scaling up further and monitor CPU, disk and network limits and exhaustion.

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

  1. PGCAT Database
  2. PGSQL Tables
  3. PGRDS Cluster
  4. PGRDS Instance
  5. PGSQL Persist

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

SELECT monitor.pg_stat_statements_reset();

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";

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';

References

Graph View