Use PostgreSQL To Store Terraform State

It's probably a good idea to use a backend to store your Terraform state. This way you can work on your infrastructure as part of a team without others needing access to your local files. It also means that you don't have to worry about losing access to your computer in one way or another.

In this tutorial, we will configure Terraform to store the state in a shared PostgreSQL database that everyone in your team should have access to.

Related Posts

• Use S3 To Store Terraform State

Steps

Create The PostgreSQL Database And User

First we need to create the database with a user that can connect to it for Terraform. If you have already done this, skip this part.

SSH into your PostgreSQL server and log into the database as the root user:

sudo -u postgres psql

Create the database for your project's Terraform state.

CREATE DATABASE my_terraform_database;

Now we need to create a user for terraform to use and connect to the database on.

CREATE USER myusername WITH PASSWORD 'thisIsMyPassword';

To help prevent possible issues, I recommend setting a long alphanumeric password. E.g. don't use special characters like : or $.

Now we need to grant that user access to the database we just set up for Terraform:

GRANT ALL ON database my_terraform_database TO myusername;

Now quit out.

\quit

Configure Terraform to Use PostgreSQL

Add the following block to one of your Terraform configuration files:

terraform {
  backend "pg" {}
}

Now initialize your Terraform project with:

terraform init \
  -backend-config="conn_str=postgres://myusername:thisIsMyPassword@postgresql.mydomain.com/my_terraform_database"

Be sure to swap out myusername with the username you specified, thisIsMyPassword with your password, postgresql.mydomain.com with your PostgreSQL IP address or hostname, and my_terraform_database with the name of the database.

That's it. Terraform will now use the PostgreSQL database for checking and managing state.

Testing It Worked

I wanted to check that it worked, so I connected to the PostgreSQL database:

psql \
  --user myusername \
  --password \
  -d my_terraform_database \
  --host postgresql.mydomain.com

Initially, I was confused because listing tables didn't show anything:

my_terraform_database=# \dt                         
Did not find any relations.

I had thought that I had messed up, but actually, Terraform creates a new schema to put the table within, instead of using the default schema named public.

Thus, to see the tables, you need to use:

\dt terraform_remote_state.*

... and you will get output similar to below:

my_terraform_database=# \dt terraform_remote_state.*
                  List of relations
         Schema         |  Name  | Type  |   Owner    
------------------------+--------+-------+------------
 terraform_remote_state | states | table | myusername
(1 row)

You can view the table structure with:

\d terraform_remote_state.states

my_terraform_database=# \d terraform_remote_state.states
                       Table "terraform_remote_state.states"
 Column |  Type  | Collation | Nullable |                  Default                  
--------+--------+-----------+----------+-------------------------------------------
 id     | bigint |           | not null | nextval('global_states_id_seq'::regclass)
 name   | text   |           |          | 
 data   | text   |           |          | 
Indexes:
    "states_pkey" PRIMARY KEY, btree (id)
    "states_by_name" UNIQUE, btree (name)

If you want to see the state data, just execute:

SELECT * FROM terraform_remote_state.states

References

• Stack Overflow - List tables in a PostgreSQL schema
• Terraform Docs - Backends - pg
• phillipsj.net - PostgreSQL Backend for Terraform