PostgreSQL

Overview

PIG uses PostgreSQL for trace metadata, ingestion progress, analysis runs, and synchronization state. Before you install the analyzer, use this guide to prepare a dedicated database for each deployment. It covers connectivity, TLS verification, schema and data-access roles, and coordinated backups. Keep the database across application upgrades.

Check the database requirements

The database must meet these requirements:

PostgreSQL 15, 16, 17, or 18. The release’s preflight check blocks a database outside this range.
A role that can run the PIG schema migrations: ownership of the PIG tables and CREATE on the schema. The serving analyzer needs data access on those tables: SELECT, INSERT, UPDATE, DELETE, and temporary tables. One role can hold both, or you can separate the migration credential to keep schema ownership apart from data access. Neither role needs cloud administrator permissions.
Private network access from the analyzer and migration Jobs, normally on TCP 5432.
TLS, with the database certificate validated against your provider’s CA bundle. Every analyzer and migration connection requires sslmode=verify-full.
Automated backups, recovery retention, availability, and deletion protection, configured through your infrastructure workflow. The default configuration retains all trace data.

The cloud guides use Amazon RDS for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL. A workload identity for the trace bucket doesn’t also authenticate the analyzer to the database.

Deliver the connection string

Use your secret manager to deliver the connection string as key postgres-dsn in Secret pig-credentials, in namespace pig. URL-encode reserved characters in the username and password. For a database with a hostname-verifiable certificate:
```
postgresql://pig_analyzer:REPLACE_PASSWORD@REPLACE_DATABASE_HOST:5432/pig?sslmode=verify-full&sslrootcert=/etc/pig/postgres-ca/ca.pem
```
The analyzer rejects any sslmode other than verify-full, so fix certificate errors at the certificate. Cloud SQL CA modes and server certificates differ; follow the Cloud SQL certificate guidance for your instance.
Create ConfigMap pig-postgres-ca with key ca.pem from your provider’s CA bundle.

Reference the Secret and ConfigMap in PIGDeployment:

storage:
  postgres:
    dsnSecretRef:
      name: pig-credentials
      key: postgres-dsn
    caConfigMapRef:
      name: pig-postgres-ca
      key: ca.pem

The CA mounts at /etc/pig/postgres-ca/ca.pem in both the analyzer and migration containers. Omit caConfigMapRef only when the container’s trust store already validates the certificate and the DSN doesn’t reference this file.

Plan migrations and connection capacity

PIG applies schema migrations as a distinct step of each application release, before the analyzer serves traffic. At startup, the analyzer checks the schema revision and serves only when it matches. The analyzer never changes the schema itself. Keep migration access available during upgrades.

Each analyzer replica opens a connection pool of up to eight connections, and the migration step opens its own connections. Reserve database connections for the analyzer replicas, the migration step, and your own operational access.

Terraform owns the database instance’s version, size, networking, and backups. PIG owns its application schema. Don’t manage PIG tables or run competing schema migrations from Terraform.

Separate the migration credential

Schema migrations and normal analyzer traffic can use one database role or two. With two roles, the serving analyzer can’t alter the schema:

The migration role owns the PIG tables and holds CREATE on the schema. The migration step uses it to apply schema changes.
The application role holds data access on those tables: SELECT, INSERT, UPDATE, DELETE, and temporary tables. The serving analyzer uses it.

When you supply only the application credential, that single role runs migrations and serves traffic.

To set up two roles for a new database named pig:

As a database administrator, connect to the pig database and create the roles and their grants:
```
CREATE ROLE pig_migrator LOGIN;
CREATE ROLE pig_app LOGIN;
GRANT CONNECT ON DATABASE pig TO pig_migrator;
GRANT CONNECT, TEMPORARY ON DATABASE pig TO pig_app;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO pig_migrator;
GRANT USAGE ON SCHEMA public TO pig_app;
```
Migrations grant the application role data access to the PIG tables. They don’t create roles or transfer schema ownership. Keep the migration role out of the application role’s memberships, and keep the application role’s TEMPORARY permission.
Set both roles’ login passwords through your secret-management process.
Deliver the two connection strings: pig-credentials/postgres-dsn for pig_app and pig-migration/postgres-dsn for pig_migrator. Both use sslmode=verify-full and the same host and database. PIG confirms that both reach the same database before it migrates.
Pass the migration credential the way your installation path expects:
- For a manual installation, set the secrets.migrationPostgresDsnKey chart value.
- For a supervisor-managed installation, set spec.storage.postgres.migrationDsnSecretRef.

The migration credential reaches only the preflight and migration Jobs. The serving analyzer never receives it.

Back up and restore

Set your recovery objectives before you choose backup retention. Record a recovery point that keeps database object references consistent with their trace objects. Set object lifecycle rules to retain the data your database backups need.

Before a destructive migration, provide recovery confirmation for the exact target release. Your backup workflow creates the recovery points and supplies their identifiers; PIG doesn’t create cloud backups.

Rehearse restoration with an isolated database and trace location. Never run two active deployments against one database during recovery. Before you restore production or change application versions, follow Manage updates and recovery.