| --- |
| page_title: 'Backend Type: pg' |
| description: Terraform can store state remotely in a Postgres database with locking. |
| --- |
| |
| # pg |
| |
| Stores the state in a [Postgres database](https://www.postgresql.org) version 10 or newer. |
| |
| This backend supports [state locking](/terraform/language/state/locking). |
| |
| ## Example Configuration |
| |
| ```hcl |
| terraform { |
| backend "pg" { |
| conn_str = "postgres://user:pass@db.example.com/terraform_backend" |
| } |
| } |
| ``` |
| |
| Before initializing the backend with `terraform init`, the database must already exist: |
| |
| ``` |
| createdb terraform_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/terraform_backend |
| $ terraform init |
| ``` |
| |
| or just the sensitive parameters: |
| |
| ```hcl |
| terraform { |
| backend "pg" { |
| conn_str = "postgres://db.example.com/terraform_backend" |
| } |
| } |
| ``` |
| |
| ```shellsession |
| $ export PGUSER=user |
| $ read -s PGPASSWORD |
| $ export PGPASSWORD |
| $ terraform init |
| ``` |
| |
| ## Data Source Configuration |
| |
| To make use of the pg remote state in another configuration, use the [`terraform_remote_state` data source](/terraform/language/state/remote-state-data). |
| |
| ```hcl |
| data "terraform_remote_state" "network" { |
| backend = "pg" |
| config = { |
| conn_str = "postgres://localhost/terraform_backend" |
| } |
| } |
| ``` |
| |
| ## Configuration Variables |
| |
| !> **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, Terraform will include these values in both the `.terraform` subdirectory and in plan files. Refer to [Credentials and Sensitive Data](/terraform/language/settings/backends/configuration#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. Terraform won't try to create the schema, this is useful when it has already been created by a database administrator. |
| - `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. Terraform won't try to create the table, this is useful when it has already been created by a database administrator. |
| - `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. Terraform won't try to create the index, this is useful when it has already been created by a database administrator. |
| |
| ## Technical Design |
| |
| This backend creates one table **states** in the automatically-managed Postgres schema configured by the `schema_name` variable. |
| |
| The table is keyed by the [workspace](/terraform/language/state/workspaces) 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`](/terraform/cli/commands/force-unlock) 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). |
| |
| The **states** table contains: |
| |
| - a serial integer `id`, used as the key for advisory locks |
| - the workspace `name` key as _text_ with a unique index |
| - the Terraform state `data` as _text_ |