Operations SOP Index

Getting Started Path

Task Index

Preparation And Deployment

For production deployment, start with Planning and Preparation. These two pages cover node count, disks, filesystems, networks, VIPs, domains, and software sources.

After the machines are ready, read Administration Model. It covers admin users, passwordless SSH, sudo, reachability, and firewall configuration. Check system versions and architectures in Linux Compatibility.

Use Quick Start for the first installation. Use Production Deployment for multi-node production environments. If there is no internet access, read Offline Installation and Repository Management.

Select an initial template for your requirements:

• Use meta for a default single-node deployment.
• Use ha/trio for three-node HA.
• Use ha/full for more complete HA.
• Use ha/safe when consistency matters.
• Use ha/dual or ha/simu when resources are limited.

Naming And Configuration

First distinguish three names: cluster name, database name, and service name.

pg_cluster is the top-level name Pigsty uses to manage a PostgreSQL cluster. It affects instance names, service names, backup stanzas, monitoring labels, and many file paths. Do not treat it as a display name that you can change freely. See PGSQL Cluster Model for naming rules. See Cluster Instance Config for instance roles. See Service/Access for service names and connection entrypoints.

Database names and user names are logical objects inside PostgreSQL. See Database Config and Database Management for databases. See User/Role Config and User Management for users and roles. See Access Control and ACL Config for the privilege model.

In practice, use lowercase letters, digits, and hyphens for cluster names, such as pg-meta, pg-test, and pg-user-prod. Use snake_case for database object names. Avoid non-ASCII names, spaces, mixed case, and SQL keywords. For more background, read Database Cluster Management Concepts and Entity Naming Rules and PostgreSQL Convention (2024 Edition).

For configuration changes, edit pigsty.yml first. Then run the corresponding playbook.

See Declarative Configuration and Configuration Guide for configuration structure. See Configuration Parameters and Parameter List for parameter meanings. See Playbooks and Playbook List for playbook entrypoints.

Daily Operations

The main entrypoint for database operations is PGSQL Administration.

For background reading, see Routine PostgreSQL Maintenance.

High Availability Drills

To understand HA, start with PostgreSQL HA. Also read RPO and RTO. RPO is the maximum acceptable data loss. RTO is the time to restore service. Evaluate these measures as well as automatic failover.

For access-layer behavior, see HA Service Access and Service/Access. For component relationships, see PGSQL Architecture. For Etcd’s role, see ETCD Config.

For planned switchover drills, see Patroni Management. For service status, see Component Management. For extreme failures, see 3-of-2 Failure Drill. If you need VIP, read Configure PG VIP.

Background article: How Should PostgreSQL High Availability Be Done?.

Backup And Recovery

For PITR, read Point-in-Time Recovery first, then Mechanism, Architecture, Tradeoffs, and Scenarios.

For configuration and maintenance, see Backup & Restore, Backup Policy, Backup Mechanism, Backup Repository, and Backup Admin Commands.

When actually restoring, use Restore Operations for the automatic path and Manual Recovery for drills. For dropped data, tables, or databases, see Drop Recovery. If you do not want to touch the original cluster directly, start with Clone Database Cluster or Fork Instance.

Before recovery, confirm at least these four conditions:

• The target timestamp or restore point is clear.
• The backup and WAL are continuous.
• Business writes have stopped.
• You have selected recovery in place or initial recovery to a new validation cluster.

Background articles: Overview of Backup and Recovery Methods and PgBackRest2 Documentation.

Monitoring And Troubleshooting

For monitoring overview, see Monitoring System. For entrypoints and domains, see Web UI. For database metrics, logs, and alerts, see PGSQL Monitoring and PGSQL Dashboards.

Monitoring for non-database modules is documented separately: INFRA Monitoring, NODE Monitoring, ETCD Monitoring, and MINIO Monitoring.

Start troubleshooting with PGSQL FAQ, then Troubleshooting. For connection authentication problems, see HBA Management. For HA status problems, see Patroni Management. For process state problems, see Component Management.

General PostgreSQL troubleshooting articles: Routine PG Server Logging Configuration, Macro Query Optimization with pg_stat_statements, Incident File: PostgreSQL Transaction ID Wraparound, Finding Fake Indexes, and Table Bloat Cleanup.

Scaling, Upgrades, And Migration

For capacity and topology design, see Planning, Preparation, and PGSQL Cluster Model.

For module scaling, use the corresponding guide:

• PGSQL: Cluster Management
• NODE: NODE Management
• ETCD: ETCD Management
• MINIO: MINIO Management
• INFRA: INFRA Administration
• REDIS: REDIS Management

For PostgreSQL upgrades, see Version Upgrade and Kernel Versions. For extensions, see Extensions, Extension Management, Extension Repository, and Package Aliases.

For migrating existing PostgreSQL databases, see Data Migration and PGSQL Migration Playbook. For low-downtime migration ideas, see Migration without Downtime.

If you need horizontal scaling, then read Citus Cluster Deployment and Citus Kernel Branch.

Security And Entrypoints

For deployment security, start with Security Considerations. For the security model, see Security and Compliance. For PostgreSQL privileges, see Access Control and ACL Config. For authentication rules, see Authentication, HBA Config, and HBA Management.

For certificates, see CA & Certificates. For domains, Nginx, and web entrypoints, see Domain Management and Nginx Management.

For production, change default passwords, restrict HBA rules, and clearly separate business users from admin users. Confirm backup repository retention, encryption, and access permissions. These are minimum requirements.

Application Access

Before applications connect to databases, read Service/Access and PostgreSQL Quick Start. For connection pool behavior, see Pgbouncer Management.

When using Pigsty-managed databases and deploying stateless applications, see Application Templates and Docker Module.

Common Mistakes

Further Reading