<commit_before>---
layout: "backend-types"
page_title: "Backend Type: pg"
sidebar_current: "docs-backends-types-standard-pg"
description: |-
  Terraform can store state remotely in a Postgres database with locking.
---

# pg

**Kind: Standard with locking**

Stores the state in a [Postgres database](https://www.postgresql.org) using two tables, `states` and `locks`.

Supports Postgres version 10 and newer.

## Example Configuration

```hcl
terraform {
  backend "pg" {
    conn_str = "postgres://localhost/terraform_backend"
  }
}
```

This assumes we have a database created called `terraform_backend`. The
Terraform state is stored in a self-managed schema, by default 
`terraform_remote_backend`.

Note that for the access credentials we recommend using a
[partial configuration](/docs/backends/config.html).

## Using the pg remote state

To make use of the pg remote state we can use the
[`terraform_remote_state` data
source](/docs/providers/terraform/d/remote_state.html).

```hcl
data "terraform_remote_state" "network" {
  backend = "pg"
  config {
    conn_str = "postgres://localhost/terraform_backend"
  }
}
```

The `terraform_remote_state` data source will return all of the root module 
outputs defined in the referenced remote state (but not any outputs from 
nested modules unless they are explicitly output again in the root). An 
example output might look like:

```
data.terraform_remote_state.network:
  id = 2018-10-11 01:57:59.780010914 +0000 UTC
  backend = pg
  config.% = 1
  config.conn_str = postgres://localhost/terraform_backend
```

## Configuration variables

The following configuration options or environment variables are supported:

 * `conn_str` - (Required) Postgres connection string; a `postgres://` URL
 * `lock` - Use locks to synchronize state access, default `true`
 * `schema_name` - Name of the automatically managed Postgres schema to store locks & state.
 <commit_msg>📚 pg backend technical design & clarifications<commit_after>---
layout: "backend-types"
page_title: "Backend Type: pg"
sidebar_current: "docs-backends-types-standard-pg"
description: |-
  Terraform can store state remotely in a Postgres database with locking.
---

# pg

**Kind: Standard (with locking)**

Stores the state in a [Postgres database](https://www.postgresql.org) version 9.5 or newer.

This backend supports [state locking](/docs/state/locking.html).

## Example Configuration

```hcl
terraform {
  backend "pg" {
    conn_str = "postgres://localhost/terraform_backend"
  }
}
```

Before initializing the backend with `terraform init`, the database must already exist.

We recommend using a [partial configuration](/docs/backends/config.html#partial-configuration) for the `conn_str` variable, because it typically contains access credentials that should not be committed to source control.

## Example Referencing

To make use of the pg remote state we can use the [`terraform_remote_state` data source](/docs/providers/terraform/d/remote_state.html).

```hcl
data "terraform_remote_state" "network" {
  backend = "pg"
  config {
    conn_str = "postgres://localhost/terraform_backend"
  }
}
```

## Configuration Variables

The following configuration options or environment variables are supported:

 * `conn_str` - (Required) Postgres connection string; a `postgres://` URL
 * `lock` - Use locks to synchronize state access, default `true`
 * `schema_name` - Name of the automatically-managed Postgres schema to store locks & state, default `terraform_remote_backend`.
 
## Technical Design

Postgres version 9.5 or newer is required to support the "ON CONFLICT" upsert syntax and *jsonb* data type.

This backend creates two tables, **states** and **locks**, in the automatically-managed Postgres schema configured by the `schema_name` variable.

Both tables are keyed by the [workspace](/docs/state/workspaces.html) name. If workspaces are not in use, the name `default` is used.

The **states** table contains:

 * the workspace `name` key as *text* with a unique index
 * the Terraform state `data` JSON as *text*.

The **locks** table contains:

 * the workspace `name` key as *text* with a unique index
 * the lock's `info` JSON as *jsonb*.
{'fraction_non_alphanumeric': 0.10112359550561797, 'fraction_numerical': 0.008128137700215157, 'mean_word_length': 4.58611481975968, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4137981', 'n_tokens_mistral': 1249, 'n_tokens_neox': 1210, 'n_words': 463}