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 machines are ready, read Administration Model: admin users, passwordless SSH, sudo, reachability, and firewall handling are covered there. 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.

Do not overthink template choice at the beginning: use meta for single-node default deployment; ha/trio for three-node HA; ha/full for more complete HA; ha/safe when consistency matters; and ha/dual or ha/simu when resources are tight.

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. It is not a display name that can be casually changed. See PGSQL Cluster Model for naming rules, Cluster Instance Config for instance roles, and 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; User/Role Config and User Management for users and roles; and Access Control plus 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).

Follow one habit for config changes: edit pigsty.yml first, then run the corresponding playbook. See Declarative Configuration and Configuration Guide for config structure; Configuration Parameters and Parameter List for parameter meanings; 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. Do not only ask “can it fail over automatically”; also read RPO and RTO: the former is the maximum acceptable data loss, and the latter is the time to restore service.

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

Drill entrypoints are concentrated in three places: Patroni Management for planned switchover; Component Management for service status; and 3-of-2 Failure Drill for extreme failures. 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 four things: the target timestamp or restore point is clear; backup and WAL are continuous; business writes have stopped; and you know whether you are restoring in place or first pulling up 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 issues, see HBA Management; for HA status issues, see Patroni Management; for process state issues, 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.

When scaling by module, see Cluster Management for PGSQL; NODE Management for NODE; ETCD Management for ETCD; MINIO Management for MINIO; INFRA Administration for INFRA; and REDIS Management for REDIS.

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, at minimum change default passwords, tighten HBA, clearly separate business users from admin users, and confirm backup repository retention, encryption, and access permissions.

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