Factorizer/Cluster ERP WikiCluster ERP Türkçe
Home / Operations

Reference

PostgreSQL roles and tuning

The two database roles of deploy/postgres-roles.sql, the backup runner's role check, and the memory and planner settings that deploy/pg-tune.sh writes.

A Cluster ERP deployment needs two PostgreSQL roles on every database server: the application role and the backup role. This page describes them as deploy/postgres-roles.sql creates them, the check that the backup runner makes before each dump, and the settings that deploy/pg-tune.sh writes for a node. Passwords are never part of this page; the script carries placeholders that the operator replaces.

Scope and source

  • Defined in: deploy/postgres-roles.sql, deploy/pg-tune.sh, and src/cluster_erp/server/backup_tenants.py (assert_dump_role_can_read_everything).
  • Count: two roles, counted from the two CREATE ROLE statements; fourteen settings, counted from the lines of the generated file in pg-tune.sh.

Roles

Role Attributes Used by Source
application role LOGIN, CREATEDB, NOSUPERUSER, NOBYPASSRLS The server, which creates its control-plane and tenant databases itself. deploy/postgres-roles.sql:13-27
backup role LOGIN, BYPASSRLS, NOSUPERUSER, NOCREATEDB, member of pg_read_all_data Only the backup runner. deploy/postgres-roles.sql:29-42

Rules stated in the script:

  • Run it on every database server, including every data-residency region (deploy/postgres-roles.sql:2-3).
  • Replace both passwords before running. On a server that logs DDL statements, set the passwords afterwards with \password instead (deploy/postgres-roles.sql:5-8).
  • Re-running is safe: an existing role keeps its password and only the attributes are applied again (deploy/postgres-roles.sql:10-11).
  • The application role must never get BYPASSRLS, because that would switch off the row guard on the private tables (deploy/postgres-roles.sql:13-15).

The settings CLUSTER_ERP_BACKUP_DB_USER and CLUSTER_ERP_BACKUP_DB_PASSWORD (or CLUSTER_ERP_BACKUP_DB_PASSWORD_FILE) point the runner at the backup role (src/cluster_erp/server/backup_tenants.py:67-80). See configuration reference.

Backup role check

Before each dump, assert_dump_role_can_read_everything asks PostgreSQL whether the dump role is a superuser or has BYPASSRLS, and whether it is a superuser or a member of pg_read_all_data. When either answer is no, the runner stops with an error that names the role and the missing attribute (src/cluster_erp/server/backup_tenants.py:105-144). The final SELECT of postgres-roles.sql shows the same attributes for both roles (deploy/postgres-roles.sql:44-52).

Tuning settings

pg-tune.sh reads the node's memory and processor count and writes one file into the conf.d folder of PostgreSQL 16, then restarts PostgreSQL (deploy/pg-tune.sh:23-25, deploy/pg-tune.sh:33-69).

Setting Value written Source
shared_buffers a quarter of memory deploy/pg-tune.sh:37, deploy/pg-tune.sh:51
effective_cache_size 60 percent of memory deploy/pg-tune.sh:38, deploy/pg-tune.sh:52
maintenance_work_mem memory divided by 16, at most 2048 MB deploy/pg-tune.sh:39, deploy/pg-tune.sh:53
work_mem (memory minus shared buffers) divided by 4 and by 100, between 8 and 64 MB deploy/pg-tune.sh:40-42, deploy/pg-tune.sh:54
random_page_cost 1.1 deploy/pg-tune.sh:55
effective_io_concurrency 200 deploy/pg-tune.sh:56
wal_buffers 16MB deploy/pg-tune.sh:57
min_wal_size 1GB deploy/pg-tune.sh:58
max_wal_size a quarter of memory, between 1024 and 8192 MB deploy/pg-tune.sh:43-45, deploy/pg-tune.sh:59
checkpoint_completion_target 0.9 deploy/pg-tune.sh:60
max_worker_processes processor count deploy/pg-tune.sh:61
max_parallel_workers processor count deploy/pg-tune.sh:62
max_parallel_workers_per_gather half the processor count, at least 1 deploy/pg-tune.sh:46, deploy/pg-tune.sh:63
max_parallel_maintenance_workers half the processor count, at least 1 deploy/pg-tune.sh:46, deploy/pg-tune.sh:64

Constraints and validation

  • The script runs only as root, only when the PostgreSQL 16 conf.d folder exists, and only when postgresql.conf includes that folder (deploy/pg-tune.sh:28-31).
  • It refuses a node with less than 1 GB of memory (deploy/pg-tune.sh:35).
  • After the restart it prints seven of the applied settings and asks for a restart of the application (deploy/pg-tune.sh:70-72).
  • The server's start-up warns when workers times pool plus overflow exceeds max_connections (src/cluster_erp/server/app.py:610-631). pg-tune.sh does not change max_connections.

Discrepancies

Terms introduced

Term Code identifier Definition Turkish term Source
backup role CLUSTER_ERP_BACKUP_DB_USER The database role that reads past the row guard to take complete dumps; used only by the backup runner. yedek rolü deploy/postgres-roles.sql:29-42
row guard FORCE ROW LEVEL SECURITY Row-level security on private tables that also applies to the table owner. satır koruması src/cluster_erp/server/backup_tenants.py:105-113

Verified against commit 92f8051 on 2026-11-13.

Verified against commit 92f8051 · 2026-11-13