opentf/website/docs/language/settings/backends/pg.mdx

---
sidebar_label: pg
description: OpenTofu can store state remotely in a Postgres database with locking.
---

# Backend Type: pg

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

This backend supports [state locking](../../../language/state/locking.mdx).

:::warning
OpenTofu 1.10 changed how workspace creation locking works. It is unsafe to
use `pg` backend with multiple OpenTofu versions (under 1.10) in parallel if those
are sharing the same underlying database.
:::

## Example Configuration

```hcl
terraform {
  backend "pg" {
    conn_str = "postgres://user:pass@db.example.com/tofu_backend"
  }
}
```

Before initializing the backend with `tofu init`, the database must already exist:

```
createdb tofu_backend
```

This `createdb` command is found in [Postgres client applications](https://www.postgresql.org/docs/10/reference-client.html) which are installed along with the database server.


### Using environment variables

We recommend using environment variables to configure the `pg` backend in order
not to have sensitive credentials written to  disk and committed to source
control.

The `pg` backend supports the standard [`libpq` environment variables](https://www.postgresql.org/docs/current/libpq-envars.html).

The backend can be configured either by giving the whole configuration as an
environment variable:

```hcl
terraform {
  backend "pg" {}
}
```

```shellsession
$ export PG_CONN_STR=postgres://user:pass@db.example.com/tofu_backend
$ tofu init
```

or just the sensitive parameters:

```hcl
terraform {
  backend "pg" {
    conn_str = "postgres://db.example.com/tofu_backend"
  }
}
```

```shellsession
$ export PGUSER=user
$ read -s PGPASSWORD
$ export PGPASSWORD
$ tofu init
```

## Data Source Configuration

To make use of the pg remote state in another configuration, use the [`terraform_remote_state` data source](../../../language/state/remote-state-data.mdx).

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

## Configuration Variables

:::danger Warning
We recommend using environment variables to supply credentials and other sensitive data. If you use `-backend-config` or hardcode these values directly in your configuration, OpenTofu will include these values in both the `.terraform` subdirectory and in plan files. Refer to [Credentials and Sensitive Data](../../../language/settings/backends/configuration.mdx#credentials-and-sensitive-data) for details.
:::

The following configuration options or environment variables are supported:

- `conn_str` - Postgres connection string; a `postgres://` URL. The `PG_CONN_STR` and [standard `libpq`](https://www.postgresql.org/docs/current/libpq-envars.html) environment variables can also be used to indicate how to connect to the PostgreSQL database.
- `schema_name` - Name of the automatically-managed Postgres schema, default to `terraform_remote_state`. Can also be set using the `PG_SCHEMA_NAME` environment variable.
- `skip_schema_creation` - If set to `true`, the Postgres schema must already exist. Can also be set using the `PG_SKIP_SCHEMA_CREATION` environment variable. OpenTofu won't try to create the schema, this is useful when it has already been created by a database administrator.
- `table_name` - Name of the automatically-managed Postgres table, default to `states`. Can also be set using the `PG_TABLE_NAME` environment variable.
- `skip_table_creation` - If set to `true`, the Postgres table must already exist. Can also be set using the `PG_SKIP_TABLE_CREATION` environment variable. OpenTofu won't try to create the table, this is useful when it has already been created by a database administrator.
- `index_name` - Name of the automatically-managed Postgres index, default to `states_by_name`. Can also be set using the `PG_INDEX_NAME` environment variable.
- `skip_index_creation` - If set to `true`, the Postgres index must already exist. Can also be set using the `PG_SKIP_INDEX_CREATION` environment variable. OpenTofu won't try to create the index, this is useful when it has already been created by a database administrator.

Please, keep in mind, that if `table_name` or `schema_name` is changed, you would need to manually migrate the existing state data.

## Technical Design

This backend creates a table with a name defined by `table_name` in the automatically-managed Postgres schema configured by the `schema_name` variable.

The table is keyed by the [workspace](../../../language/state/workspaces.mdx) name. If workspaces are not in use, the name `default` is used.

Locking is supported using [Postgres advisory locks](https://www.postgresql.org/docs/9.5/explicit-locking.html#ADVISORY-LOCKS). [`force-unlock`](../../../cli/commands/force-unlock.mdx) is not supported, because these database-native locks will automatically unlock when the session is aborted or the connection fails. To see outstanding locks in a Postgres server, use the [`pg_locks` system view](https://www.postgresql.org/docs/9.5/view-pg-locks.html).

Advisory locks are used for multiple scenarios: state updates and state creation. When the state is updated, advisory lock is acquired with state ID. Otherwise, on state (and workspace) creation, it is acquired with the hash of schema name. This way, multiple backend configurations doesn't affect each other, when the database is shared.

The table used for state contains:

- a serial integer `id`, used as the key for advisory locks
- the workspace `name` key as _text_ with a unique index
- the OpenTofu state `data` as _text_