PG Pilot Documentation

Everything you need to monitor and optimize your PostgreSQL databases.

Installation

Download and set up the PG Pilot collector for your platform.

Features

Learn about query monitoring, index health, vacuum tracking, and more.

Issues Reference

Detailed documentation for every issue PG Pilot detects and how to fix them.

Inviting Collaborators

Learn how to create teams and invite team members to collaborate.

Installation

Download the PG Pilot collector for your platform. The collector runs on your database server and sends metrics to your PG Pilot dashboard.

macOS

Apple Silicon (M1/M2/M3)

Download

Linux

x86_64 (Intel/AMD)

Download

Linux

ARM64 (AWS Graviton, etc.)

Download

Quick Start

1. Make the binary executable:

chmod +x pgpilot-*

2. Run the collector:

./pgpilot --name "My Database" --user postgres --password YOUR_PASSWORD --host localhost --database mydb --token YOUR_API_TOKEN --ssl-mode require

Configuration Options

Option Description Default
--name, -n Display name for this database Required
--user, -u PostgreSQL username Required
--password, -p PostgreSQL password Required
--host PostgreSQL host address Required
--database, -d PostgreSQL database name Required
--token, -t API token from PG Pilot Required
--port PostgreSQL port 5432
--api-url PG Pilot API endpoint https://pgpilot.ai/api/v1/metric
--enable-auto-explain Enable auto_explain log reading for query plans false
--log-line-prefix PostgreSQL log_line_prefix format (for auto_explain) %t [%p]: ...
--ssl-mode SSL mode for PostgreSQL connection (disable, prefer, require) prefer

Database User Setup

The collector requires a PostgreSQL user with permissions to read system statistics. Create a dedicated monitoring user: 

-- Create a monitoring user
CREATE USER pgpilot_monitor WITH PASSWORD 'secure_password';

-- Grant necessary permissions (recommended)
GRANT pg_monitor TO pgpilot_monitor;

-- Or for more granular control:
GRANT SELECT ON pg_stat_statements TO pgpilot_monitor;

Running as a Systemd Service

For production deployments, run the collector as a systemd service to ensure it automatically restarts and starts on boot.

1. Create the service file at /etc/systemd/system/pgpilot.service:

[Unit]
Description=PG Pilot Collector
After=network.target

[Service]
Type=simple
ExecStart=/usr/local/bin/pgpilot \
  --name "My Database" \
  --user monitoring_user \
  --password "your_password" \
  --host localhost \
  --database mydb \
  --token YOUR_API_TOKEN \
  --ssl-mode require
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

2. Enable and start the service:

sudo systemctl daemon-reload
sudo systemctl enable pgpilot
sudo systemctl start pgpilot
sudo systemctl status pgpilot

Troubleshooting

  • Connection refused: Verify your database host, port, and firewall rules allow connections from the collector.
  • Authentication failed: Double-check username and password. Ensure the user exists and has proper permissions.
  • SSL/TLS errors: If using --ssl-mode require and connection fails, verify your database server has SSL enabled. Use --ssl-mode prefer to try TLS with fallback to plaintext, or --ssl-mode disable for local/trusted networks only.
  • No data appearing: Verify the API token is correct and the collector can reach pgpilot.ai.
  • pg_stat_statements missing: The collector will attempt to create the extension automatically, but you may need superuser privileges.

Inviting Collaborators

PG Pilot supports team collaboration. You can create team accounts and invite colleagues to share access to your monitored databases.

Personal vs Team Accounts

Personal Account

Created automatically when you sign up. Only you have access. Ideal for individual use or personal projects.

Team Account

Create additional accounts to collaborate with your team. Invite members and assign roles (Admin or Member).

How to Invite Team Members

1

Create a Team Account

Go to Accounts and click New Account. Give your team a name.

2

View the Team Account

Click on the team account name to open the team management page.

3

Invite Members

Click the Invite button. Enter their name, email, and select a role (Admin or Member).

4

Wait for Acceptance

The invited person receives an email with a link to join. They'll appear in your team once they accept.

Team Roles

Role Permissions
Admin Full access. Can invite/remove members, manage account settings, and view all data.
Member View access. Can view monitored databases and metrics but cannot manage team settings.

Features

Query Monitoring

Real-time query performance monitoring using pg_stat_statements. Track execution times, call counts, and buffer usage to identify slow queries.

Index Health

Comprehensive index monitoring to find unused, duplicate, and bloated indexes. Optimize storage and write performance with actionable insights.

Table Monitoring

Comprehensive table health monitoring including size tracking, write activity, and bloat detection.

Vacuum Management

Monitor vacuum operations and prevent transaction ID wraparound. Track autovacuum progress and identify tables needing maintenance.

Replication Monitoring

Monitor replication slots and streaming replication health. Track lag, WAL retention, and slot status to ensure replica reliability.

Connection Management

Real-time connection monitoring with state tracking, blocking detection, and connection pool usage analysis.

Configuration Management

Monitor PostgreSQL configuration settings and detect dangerous or suboptimal configurations with severity-based issue tracking.

Schema Monitoring

Schema-level aggregation and monitoring for multi-tenant or complex database architectures.

Dashboard

At-a-glance view of database health with real-time metrics, performance charts, and quick access to issues.

Issues Reference

PG Pilot detects 42 different issues across your PostgreSQL databases. Each issue page includes detailed explanations, impact analysis, and step-by-step remediation guides.