Managing PostgreSQL Clusters
Create/destroy PostgreSQL clusters, scale existing clusters, and clone clusters.
Categories: Task
Managing PostgreSQL Users
User management - create, modify, delete users, manage role membership, connection pool config
Managing PostgreSQL Databases
Database management - create, modify, delete, rebuild, and clone databases using templates
Patroni HA Management
Manage PostgreSQL cluster HA with Patroni, including config changes, status check, switchover, restart, and reinit replica.
Pgbouncer Connection Pooling
Manage Pgbouncer connection pool, including pause, resume, disable, enable, reconnect, kill, and reload operations.
Managing PostgreSQL Component Services
Use systemctl to manage PostgreSQL cluster component services - start, stop, restart, reload, and status check.
Managing PostgreSQL Extensions
Extension management - download, install, configure, enable, update, and remove extensions
Upgrading PostgreSQL Major/Minor Versions
Version upgrade - minor version rolling upgrade, major version migration, extension upgrade
Point-in-Time Recovery
Pigsty uses pgBackRest to implement PostgreSQL point-in-time recovery, allowing users to roll back to any point in time within the backup policy window.
ByteBase: PG Schema Migration
Self-hosting bytebase with PostgreSQL managed by Pigsty
PGAdmin4: PG Admin GUI Tool
Launch pgAdmin4 with docker, and load Pigsty server list into it
Administration
Standard Operating Procedures (SOP) for database administration tasks
Backup Policy
Design backup policies according to your needs
Backup Mechanism
Backup scripts, cron jobs, backup repository and infrastructure
Backup Repository
PostgreSQL backup storage repository configuration
Admin Commands
Managing backup repositories and backups
Restore Operations
Restore PostgreSQL from backups
Clone PG Cluster
How to use PITR to create a new PostgreSQL cluster and restore to a specified point in time?
Instance Recovery
Clone instances and perform point-in-time recovery on the same machine
Clone Database
How to clone an existing database within a PostgreSQL cluster using instant XFS cloning
Backup & Restore
Point-in-Time Recovery (PITR) Backup and Restore
Troubleshooting
Common failures and analysis troubleshooting approaches
Data Migration
How to migrate an existing PostgreSQL cluster to a new Pigsty-managed PostgreSQL cluster with minimal downtime?
Manual Recovery
Manually perform PITR following prompt scripts in sandbox environment
Enabling HugePage for PostgreSQL
Enabling HugePage for PostgreSQL to reduce memory fragmentation and improve performance.
Accidental Deletion
Handling accidental data deletion, table deletion, and database deletion
Playbook
How to manage PostgreSQL clusters with Ansible playbooks
HA Drill: Handling 2-of-3 Node Failure
HA scenario response plan: When two of three nodes fail and auto-failover doesn’t work, how to recover from the emergency state?
Bind a L2 VIP to PostgreSQL Primary with VIP-Manager
Deploy HA Citus Cluster
How to deploy a Citus high-availability distributed cluster?
Playbook
How to use built-in Ansible playbooks to manage the INFRA module, with a quick reference for common commands.
Administration
Infrastructure components and INFRA cluster administration SOP: create, destroy, scale out, scale in, certificates, repositories…
Ansible
Using Ansible to run administration commands
Playbooks
Built-in Ansible playbooks in Pigsty
Nginx Management
Nginx management, web portal configuration, web server, upstream services
Software Repository
Managing local APT/YUM software repositories
Domain Management
Configure local or public domain names to access Pigsty services.
Module Management
INFRA module management SOP: define, create, destroy, scale out, scale in
CA and Certificates
Using self-signed CA or real HTTPS certificates
Playbook
How to use built-in Ansible playbooks to manage NODE clusters, with a quick reference for common commands.
Administration
Node cluster management SOP - create, destroy, expand, shrink, and handle node/disk failures
Administration
etcd cluster management SOP: create, destroy, scale, config, and RBAC.
Playbook
Manage etcd clusters with Ansible playbooks and quick command reference.
Playbook
Manage MinIO clusters with Ansible playbooks and quick command reference.
Administration
MinIO cluster management SOP: create, destroy, expand, shrink, and handle node and disk failures.
Playbook
Manage Redis clusters with Ansible playbooks and quick command reference.
Administration
Redis cluster management SOPs for creating, destroying, scaling, and configuring high availability
Grafana High Availability: Using PostgreSQL Backend
Use PostgreSQL instead of SQLite as Grafana’s remote storage backend for better performance and availability.
Administration
Create, remove, expand, shrink, and upgrade FerretDB clusters
Playbook
Ansible playbooks available for the FERRET module
Playbooks
How to use the built-in Ansible playbook to manage Docker and quick reference for common management commands.
Installation
How to download and install the pig package manager