(postgresql-backend)=

PostgreSQL Backend for Pulumi

The pkg/backend/diy/postgres package provides a PostgreSQL-based backend implementation for Pulumi state storage. It stores Pulumi state in a PostgreSQL database table, allowing you to use PostgreSQL as your state storage mechanism.

Features

Store Pulumi state in a PostgreSQL database
Use standard PostgreSQL authentication and connection options
Supports all Pulumi stack operations
Configurable table name

Connection String Format

The PostgreSQL backend connection string follows the standard PostgreSQL connection string format with a postgres:// prefix:

postgres://username:password@hostname:port/database?param1=value1&param2=value2

Configuration Options

The following query parameters are supported in the connection string:

table: The name of the table to use for state storage (default: pulumi_state)
All standard PostgreSQL connection parameters (sslmode, connect_timeout, etc.)

PostgreSQL Connection Parameters

The following PostgreSQL connection parameters can be included in the connection string query parameters:

SSL and Security Parameters

sslmode: SSL connection mode (disable, allow, prefer, require, verify-ca, verify-full)
sslcert: Client certificate file path
sslkey: Client private key file path
sslrootcert: Root certificate file path
sslcrl: Certificate revocation list file path
sslcompression: Enable SSL compression (0 or 1)
sslsni: Enable SSL Server Name Indication (0 or 1)

Connection and Timeout Parameters

connect_timeout: Maximum time to wait for connection (seconds)
keepalives_idle: Time before sending keepalive probe (seconds)
keepalives_interval: Interval between keepalive probes (seconds)
keepalives_count: Number of keepalive probes before giving up
tcp_user_timeout: Time for transmitted data to be acknowledged (milliseconds)

Application and Session Parameters

application_name: Application name for connection identification
client_encoding: Client character set encoding
options: Command-line options to send to server on connection startup
timezone: Session timezone setting

Authentication Parameters

passfile: Password file path (alternative to inline password)

Connection Pool and Behavior Parameters

target_session_attrs: Required session attributes (any, read-write, read-only, primary, standby, prefer-standby)
load_balance_hosts: Enable connection load balancing (disable, random)
hostaddr: Numeric IP address (can be used instead of or in addition to host)

Examples with Parameters

bash

# Basic SSL connection
postgres://user:pass@localhost:5432/mydb?sslmode=require

# Connection with custom table and SSL verification
postgres://user:[email protected]:5432/pulumi?table=my_state&sslmode=verify-full&sslrootcert=/path/to/ca.crt

# Connection with timeout and keepalive settings
postgres://user:pass@localhost:5432/mydb?connect_timeout=30&keepalives_idle=600&keepalives_interval=30

# Read-only replica connection with application name
postgres://user:[email protected]:5432/mydb?target_session_attrs=read-only&application_name=pulumi-state

# Connection with custom search path and timezone
postgres://user:pass@localhost:5432/mydb?search_path=pulumi,public&timezone=UTC

Usage

To use PostgreSQL as your Pulumi state backend:

bash

pulumi login postgres://username:password@hostname:port/database

Environment Setup

For most secure setups, use environment variables to store your PostgreSQL credentials. The following standard PostgreSQL environment variables are supported:

bash

export PGUSER=username        # Defaults to current system user
export PGPASSWORD=password    # Required - no default
export PGHOST=hostname        # Defaults to "localhost"
export PGPORT=5432           # Defaults to 5432
export PGDATABASE=database   # Defaults to the value of PGUSER
# Then login with minimal connection string
pulumi login postgres://

Note: Only PGPASSWORD is required when using environment variables. All others have sensible defaults as noted above.

Table Schema

The PostgreSQL backend will automatically create the necessary table for state storage. The table schema is defined in schema.sql.

Security Considerations

Always use SSL connections in production (sslmode=require or sslmode=verify-full)
Create a dedicated database user with limited permissions for Pulumi state storage
Ensure your database is properly secured with appropriate network access controls
Enable database backups to prevent data loss

Limitations

Signed URLs (used for state permalinks in the Pulumi CLI) are not supported with the PostgreSQL backend
Performance may be slower compared to cloud-specific object storage backends for very large states