Managing PostgreSQL Component Services

Overview

Pigsty’s PGSQL module consists of multiple components, each running as a systemd service on nodes. (pgbackrest is an exception)

Understanding these components and their management is essential for maintaining production PostgreSQL clusters.

Quick Reference

Common service names: patroni, pgbouncer, haproxy, pg_exporter, pgbouncer_exporter, vip-manager

Patroni

Patroni is PostgreSQL’s HA manager, handling startup, shutdown, failure detection, and automatic failover. It’s the core PGSQL module component. PostgreSQL process is managed by Patroni - don’t use systemctl to manage postgres service directly.

Start Patroni

systemctl start patroni     # Start Patroni (also starts PostgreSQL)

After starting, Patroni auto-launches PostgreSQL. On first start, behavior depends on role:

• Primary: Initialize or recover data directory
• Replica: Clone data from primary and establish replication

Stop Patroni

systemctl stop patroni      # Stop Patroni (also stops PostgreSQL)

Stopping Patroni gracefully shuts down PostgreSQL. Note: If this is primary and auto-failover isn’t paused, may trigger failover.

Restart Patroni

systemctl restart patroni   # Restart Patroni (also restarts PostgreSQL)

Restart causes brief service interruption. For production, use pg restart for rolling restart.

Reload Patroni

systemctl reload patroni    # Reload Patroni config

Reload re-reads config file and applies hot-reloadable params to PostgreSQL.

View Status & Logs

systemctl status patroni    # View Patroni service status
journalctl -u patroni -f    # Real-time Patroni logs
journalctl -u patroni -n 100 --no-pager  # Last 100 lines

Config file: /etc/patroni/patroni.yml

Best Practice: Use patronictl instead of systemctl to manage PostgreSQL clusters.

Pgbouncer

Pgbouncer is a lightweight PostgreSQL connection pooling middleware. Business traffic typically goes through Pgbouncer (6432) rather than directly to PostgreSQL (5432) for connection reuse and database protection.

Start Pgbouncer

systemctl start pgbouncer

Stop Pgbouncer

systemctl stop pgbouncer

Note: Stopping Pgbouncer disconnects all pooled business connections.

Restart Pgbouncer

systemctl restart pgbouncer

Restart disconnects all existing connections. For config changes only, use reload.

Reload Pgbouncer

systemctl reload pgbouncer

Reload re-reads config files (user list, pool params, etc.) without disconnecting existing connections.

View Status & Logs

systemctl status pgbouncer
journalctl -u pgbouncer -f

Config files:

• Main config: /etc/pgbouncer/pgbouncer.ini
• HBA rules: /etc/pgbouncer/pgb_hba.conf
• User list: /etc/pgbouncer/userlist.txt
• Database list: /etc/pgbouncer/database.txt

Admin Console

psql -p 6432 -U postgres -d pgbouncer  # Connect to Pgbouncer admin console

Common admin commands:

SHOW POOLS;      -- View pool status
SHOW CLIENTS;    -- View client connections
SHOW SERVERS;    -- View backend server connections
SHOW STATS;      -- View statistics
RELOAD;          -- Reload config
PAUSE;           -- Pause all pools
RESUME;          -- Resume all pools

HAProxy

HAProxy is a high-performance load balancer that routes traffic to correct PostgreSQL instances. Pigsty uses HAProxy to expose services, routing traffic based on role (primary/replica) and health status.

Start HAProxy

systemctl start haproxy

Stop HAProxy

systemctl stop haproxy

Note: Stopping HAProxy disconnects all load-balanced connections.

Restart HAProxy

systemctl restart haproxy

Reload HAProxy

systemctl reload haproxy

HAProxy supports graceful reload without disconnecting existing connections. Use reload for config changes.

View Status & Logs

systemctl status haproxy
journalctl -u haproxy -f

Config file: /etc/haproxy/haproxy.cfg

Admin Interface

HAProxy provides a web admin interface, default port 9101:

http://<node_ip>:9101/haproxy

Default auth: username admin, password configured by haproxy_admin_password.

pg_exporter

pg_exporter is PostgreSQL’s Prometheus metrics exporter for collecting database performance metrics.

Start pg_exporter

systemctl start pg_exporter

Stop pg_exporter

systemctl stop pg_exporter

After stopping, Prometheus can’t collect PostgreSQL metrics from this instance.

Restart pg_exporter

systemctl restart pg_exporter

View Status & Logs

systemctl status pg_exporter
journalctl -u pg_exporter -f

Config file: /etc/pg_exporter.yml

Verify Metrics

curl -s localhost:9630/metrics | head -20

pgbouncer_exporter

pgbouncer_exporter is Pgbouncer’s Prometheus metrics exporter.

Start/Stop/Restart

systemctl start pgbouncer_exporter
systemctl stop pgbouncer_exporter
systemctl restart pgbouncer_exporter

View Status & Logs

systemctl status pgbouncer_exporter
journalctl -u pgbouncer_exporter -f

Verify Metrics

curl -s localhost:9631/metrics | head -20

vip-manager

vip-manager is an optional component for managing L2 VIP address floating. When pg_vip_enabled is enabled, vip-manager binds VIP to current primary node.

Start vip-manager

systemctl start vip-manager

Stop vip-manager

systemctl stop vip-manager

After stopping, VIP address is released from current node.

Restart vip-manager

systemctl restart vip-manager

View Status & Logs

systemctl status vip-manager
journalctl -u vip-manager -f

Config file: /etc/default/vip-manager

Verify VIP Binding

ip addr show           # Check network interfaces, verify VIP binding
pg list <cls>          # Confirm primary location

Startup Order & Dependencies

Recommended PGSQL module component startup order:

1. patroni          # Start Patroni first (auto-starts PostgreSQL)
2. pgbouncer        # Then start connection pool
3. haproxy          # Start load balancer
4. pg_exporter      # Start metrics exporters
5. pgbouncer_exporter
6. vip-manager      # Finally start VIP manager (if enabled)

Stop order should be reversed. Pigsty playbooks handle these dependencies automatically.

Batch Start All Services

systemctl start patroni pgbouncer haproxy pg_exporter pgbouncer_exporter

Batch Stop All Services

systemctl stop pgbouncer_exporter pg_exporter haproxy pgbouncer patroni

Common Troubleshooting

Service Startup Failure

systemctl status <service>        # View service status
journalctl -u <service> -n 50     # View recent logs
journalctl -u <service> --since "5 min ago"  # Last 5 minutes logs

Patroni Won’t Start

Pgbouncer Won’t Start

HAProxy Won’t Start

Related Documentation

• Patroni Management: Manage PostgreSQL HA with patronictl
• Cluster Management: Create, scale, destroy clusters
• Service Configuration: HAProxy service definition and config
• Monitoring System: PostgreSQL monitoring and alerting