Postgres Backend

The Postgres (PostgreSQL) backend stores state in a PostgreSQL database table with automatic schema management and state locking.

Implementation

Location: /internal/backend/remote-state/pg/backend.go

Use Cases

Existing PostgreSQL infrastructure
Teams already using PostgreSQL
Strong ACID compliance requirements
Integration with PostgreSQL-based applications
On-premises deployments with PostgreSQL

Basic Configuration

terraform {
  backend "pg" {
    conn_str = "postgres://user:password@localhost/terraform_backend"
  }
}

Required Configuration

conn_str

Type: String
Optional: Yes (if environment variable is set)
Environment Variable: PG_CONN_STR
Description: PostgreSQL connection string

Format: postgres://username:password@host:port/database?sslmode=require

terraform {
  backend "pg" {
    conn_str = "postgres://terraform:[email protected]:5432/tfstate?sslmode=require"
  }
}

Optional Configuration

schema_name

Type: String
Optional: Yes
Default: "terraform_remote_state"
Environment Variable: PG_SCHEMA_NAME
Description: Name of the PostgreSQL schema to use

Terraform automatically creates this schema unless skip_schema_creation is enabled.

terraform {
  backend "pg" {
    conn_str    = "postgres://user:password@localhost/terraform_backend"
    schema_name = "production_state"
  }
}

skip_schema_creation

Type: Boolean
Optional: Yes
Default: false
Environment Variable: PG_SKIP_SCHEMA_CREATION
Description: Skip automatic schema creation

Set to true if the user doesn’t have CREATE SCHEMA privilege.

terraform {
  backend "pg" {
    conn_str             = "postgres://user:password@localhost/terraform_backend"
    schema_name          = "existing_schema"
    skip_schema_creation = true
  }
}

skip_table_creation

Type: Boolean
Optional: Yes
Default: false
Environment Variable: PG_SKIP_TABLE_CREATION
Description: Skip automatic table creation

Set to true if you want to create the table manually.

terraform {
  backend "pg" {
    conn_str            = "postgres://user:password@localhost/terraform_backend"
    skip_table_creation = true
  }
}

skip_index_creation

Type: Boolean
Optional: Yes
Default: false
Environment Variable: PG_SKIP_INDEX_CREATION
Description: Skip automatic index creation

terraform {
  backend "pg" {
    conn_str            = "postgres://user:password@localhost/terraform_backend"
    skip_index_creation = true
  }
}

Database Schema

The backend automatically creates:

Schema

CREATE SCHEMA IF NOT EXISTS terraform_remote_state;

Sequence

CREATE SEQUENCE IF NOT EXISTS public.global_states_id_seq AS bigint;

Table

CREATE TABLE IF NOT EXISTS terraform_remote_state.states (
    id bigint NOT NULL DEFAULT nextval('public.global_states_id_seq') PRIMARY KEY,
    name text UNIQUE,
    data text
);

Index

CREATE UNIQUE INDEX IF NOT EXISTS states_by_name 
    ON terraform_remote_state.states (name);

Connection String Format

The connection string supports various formats:

Basic Format

postgres://username:password@host:port/database

With SSL

postgres://username:password@host:port/database?sslmode=require

SSL Modes

disable - No SSL
require - Require SSL (default)
verify-ca - Verify CA certificate
verify-full - Verify CA and hostname

terraform {
  backend "pg" {
    conn_str = "postgres://user:pass@host/db?sslmode=verify-full"
  }
}

Unix Socket

postgres://user:password@/database?host=/var/run/postgresql

With Additional Parameters

postgres://user:pass@host/db?sslmode=require&connect_timeout=10&application_name=terraform

Workspaces

The Postgres backend supports workspaces. Each workspace is stored as a separate row:

name	data
`default`	`{state JSON}`
`development`	`{state JSON}`
`production`	`{state JSON}`

State Locking

The Postgres backend uses PostgreSQL’s advisory locks for state locking:

Automatic locking during state-modifying operations
Database-level locking ensures consistency
Locks are automatically released on connection close

Configuration Options Summary

Option	Type	Required	Default	Description
`conn_str`	string	Yes*	-	PostgreSQL connection string
`schema_name`	string	No	`terraform_remote_state`	Schema name
`skip_schema_creation`	bool	No	`false`	Skip schema creation
`skip_table_creation`	bool	No	`false`	Skip table creation
`skip_index_creation`	bool	No	`false`	Skip index creation

*Required unless PG_CONN_STR environment variable is set.

Example: Development Setup

terraform {
  backend "pg" {
    conn_str = "postgres://terraform:dev_password@localhost:5432/terraform_dev"
    schema_name = "dev_state"
  }
}

Example: Production with SSL

terraform {
  backend "pg" {
    conn_str = "postgres://terraform:[email protected]:5432/terraform_prod?sslmode=verify-full&sslcert=/etc/terraform/client.crt&sslkey=/etc/terraform/client.key&sslrootcert=/etc/terraform/ca.crt"
    schema_name = "production_state"
  }
}

Example: Using Environment Variable

export PG_CONN_STR="postgres://terraform:password@localhost/terraform_backend"
export PG_SCHEMA_NAME="my_team_state"

terraform {
  backend "pg" {
    # Configuration from environment variables
  }
}

Example: Manual Schema Management

If you don’t have CREATE SCHEMA privileges: 1. DBA creates schema and grants permissions:

CREATE SCHEMA terraform_state;
GRANT ALL ON SCHEMA terraform_state TO terraform_user;
GRANT ALL ON ALL TABLES IN SCHEMA terraform_state TO terraform_user;
GRANT USAGE ON SEQUENCE public.global_states_id_seq TO terraform_user;

2. Terraform configuration:

terraform {
  backend "pg" {
    conn_str             = "postgres://terraform_user:password@host/db"
    schema_name          = "terraform_state"
    skip_schema_creation = true
  }
}

Required Database Permissions

Minimum Permissions (Auto-create)

-- For automatic schema/table creation
GRANT CREATE ON DATABASE terraform_backend TO terraform_user;

-- Or grant schema creation privilege
GRANT CREATE ON SCHEMA public TO terraform_user;

Minimum Permissions (Manual setup)

-- After schema and table are created
GRANT USAGE ON SCHEMA terraform_remote_state TO terraform_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON terraform_remote_state.states TO terraform_user;
GRANT USAGE ON SEQUENCE public.global_states_id_seq TO terraform_user;

Database Setup Best Practices

1. Create Dedicated Database

CREATE DATABASE terraform_backend;

2. Create Dedicated User

CREATE USER terraform WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE terraform_backend TO terraform;

3. Enable SSL

# In postgresql.conf
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/ca.crt'

4. Connection Pooling

For high-traffic environments, use connection pooling with PgBouncer:

postgres://terraform:password@pgbouncer:6432/terraform_backend

Advantages

ACID Compliance - Strong consistency guarantees
Native Locking - Advisory locks prevent concurrent modifications
Familiar Technology - PostgreSQL is widely known and used
Integrated - Works with existing PostgreSQL infrastructure
Transactional - State operations are atomic
Auditing - Database logs track all changes

Limitations

Database Dependency - Requires PostgreSQL server
Size Limits - Large states may impact database performance
No Versioning - No built-in state history (use database backups)
Network Latency - Remote database connections may be slower

Monitoring and Maintenance

Check State Size

SELECT 
    name,
    pg_size_pretty(length(data)) AS state_size
FROM terraform_remote_state.states
ORDER BY length(data) DESC;

View All Workspaces

SELECT name, created_at 
FROM terraform_remote_state.states 
ORDER BY name;

Check for Locks

SELECT * FROM pg_locks WHERE locktype = 'advisory';

Best Practices

Use SSL for all connections
Dedicated database for Terraform state
Regular backups of the state database
Connection pooling for multiple users
Monitor database size and performance
Separate schemas for different teams
Rotate credentials regularly
Enable query logging for audit trails

Overview

Remote State Backends

Local Backend

​Postgres Backend

​Implementation

​Use Cases

​Basic Configuration

​Required Configuration

​conn_str

​Optional Configuration

​schema_name

​skip_schema_creation

​skip_table_creation

​skip_index_creation

​Database Schema

​Schema

​Sequence

​Table

​Index

​Connection String Format

​Basic Format

​With SSL

​SSL Modes

​Unix Socket

​With Additional Parameters

​Workspaces

​State Locking

​Configuration Options Summary

​Example: Development Setup

​Example: Production with SSL

​Example: Using Environment Variable

​Example: Manual Schema Management

​Required Database Permissions

​Minimum Permissions (Auto-create)

​Minimum Permissions (Manual setup)

​Database Setup Best Practices

​1. Create Dedicated Database

​2. Create Dedicated User

​3. Enable SSL

​4. Connection Pooling

​Advantages

​Limitations

​Monitoring and Maintenance

​Check State Size

​View All Workspaces

​Check for Locks

​Best Practices

Build docs developers (and LLMs) love