Migrating PostgreSQL Database Using pg_dump

Introduction

It may be necessary at some point in the lifecycle of a Terraform Enterprise deployment that the PostgreSQL database will need to be migrated or copied to another PostgreSQL databse. Migrating the PostgreSQL database can be done a couple ways, but this document will discuss how to do so using pg_dump and pg_restore.

Use Case

When Terraform Enterprise is deployed in External Services mode, users may need to migrate the PostgreSQL database to stand up a test instance of Terraform Enterprise using the same data from another instance or to migrate off of an unsupported release of PostgreSQL. Rather than relying on a snapshot or backup that could contain stale data, it may be desirable to migrate the the PostgreSQL database using pg_dump and pg_restore.

Procedure

Note: For installations using Terraform Enterprise v202205-01 through v202308-1, all container names now follow the naming convention of "tfe-<service>" 

Example: 

ptfe_atlas > tfe-atlas
ptfe_archivist > tfe-archivist

Note - older version can have "ptfe" prefix 

More information can be found in the release notes  with a change here.

This process will require knowing the DATABASE_URL used for the Terraform Enterprise application. The best way to get this information would be to run the following command:

$ docker exec -it ptfe_atlas env | grep DATABASE_URL

The output from the command will appear as follows:

DATABASE_URL=postgres://<USERNAME>:<REDACTED_PASSWORD>@<PG_HOSTNAME>:5432/<DATABASE_NAME>?sslmode=require&options=-c%20search%5Fpath%3Drails

Only a portion of this output will be needed to perform the pg_dump, so copy the following portion of the command output to keep for later use:

postgres://<USERNAME>:<REDACTED_PASSWORD>@<PG_HOSTNAME>:5432/<DATABASE_NAME>?sslmode=require

Depending on the activity on the Terraform Enterprise instance it may be best to stop the application prior to running pg_dump against the PostgreSQL database. Primarily because if the Terraform Enterprise application is running, the data in the PostgreSQL database could change after running the pg_dump command.

To stop the Terraform Enterprise application, ssh into the Terraform Enterprise instance and run the following command:

$ replicatedctl app stop

Confirm the application has stopped with the following command:

$ replicatedctl app status

Once the Terraform Enterprise application has been stopped, or if stopping the application is not required, then the data can be dumped from the database. This can be done from any workstation or virtual machine instance that has connectivity to the database and pg_dump installed.

If needed pg_dump and pg_restore can both be installed on the Terraform Enterprise instance itself.

$ apt-get install -y postgresql-client

Using the database url the pg_dump can then be peformed and the output saved to a file.

$ pg_dump postgres://<USERNAME>:<REDACTED_PASSWORD>@<PG_HOSTNAME>:5432/<DATABASE_NAME>?sslmode=require > tfe_pgdump.backup

Now that the dump has been generated, it can be restored into the new PostgreSQL instance using pg_restore. This will require knowing the hostname, username, and password that is associated with the new instance.

$ pg_restore -h <NEW_INSTANCE_HOSTNAME> -U <USERNAME> tfe_pgdump.backup

After the pg_restore has completed, the existing Terraform Enterprise instance can be pointed at the new PostgreSQL by navigating to the console at https://<TFE_FQDN>:8800/settings. Then changing the settings in the PostgreSQL Configuration section to use the new database instance. The Terraform Enterprise application can then be restarted either from the console at https://<TFE_FQDN>:8800 or by using the replicatedctl cli on the Terraform Enterprise instance

$ replicatedctl app start

If the new PostgreSQL database instance is to be used with a new instance of Terraform Enterprise, installation of the new Terraform Enterprise would take place after the data has been restored into the new database.