Migrating PostgreSQL Database Using pg_dump

Steps

Introduction

You may need to migrate or copy the PostgreSQL database for a Terraform Enterprise deployment. This guide details how to perform this migration using the pg_dump and pg_restore command-line utilities.

For more information on these tools, refer to the official PostgreSQL documentation for pg_dump and pg_restore.

Use Case

When Terraform Enterprise is deployed in External Services mode, you might need to migrate the PostgreSQL database to:

• Create a test instance of Terraform Enterprise using production data.
• Move from an unsupported version of PostgreSQL to a supported one.

Using pg_dump and pg_restore allows for a direct data migration, which can be preferable to restoring from a potentially outdated snapshot.

Prerequisites

• Administrative access to the Terraform Enterprise instance via SSH.
• Network connectivity from a workstation to both the source and destination PostgreSQL database instances.

• The pg_dump and pg_restore utilities installed on the workstation. If needed, you can install them on the Terraform Enterprise instance itself by running the following command.

  $ sudo apt-get update && sudo apt-get install -y postgresql-client

Procedure

Important Note on Container Naming: For Terraform Enterprise versions v202205-1 through v202308-1, container names follow the tfe-<service> convention (e.g., tfe-atlas). Older versions may use the ptfe_ prefix (e.g., ptfe_atlas). Adjust the commands in this guide to match your installation's naming convention. More information about this change is available in the v202205-1 release notes.

1. Retrieve the Database URL

First, retrieve the database connection string from the Terraform Enterprise application container. SSH into the instance and run the following command.

$ docker exec -it tfe-atlas env | grep DATABASE_URL

The command returns the full database URL.

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

Copy the main part of the URL, as you will need it for the pg_dump command.

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

2. (Recommended) Stop the Terraform Enterprise Application

To ensure data consistency, stop the Terraform Enterprise application before creating the database dump. This prevents any data changes from occurring during the export process.

Stop the application by running this command.

$ replicatedctl app stop

Confirm the application has stopped.

$ replicatedctl app status

3. Create the Database Dump

From your workstation, run pg_dump with the database URL you retrieved in step 1. This command creates a backup file named tfe_pgdump.backup.

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

4. Restore the Database to the New Instance

Next, use pg_restore to import the data into your new PostgreSQL instance. You will need the hostname, username, and database name for the new instance.

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

5. Update Terraform Enterprise Configuration

After the restore is complete, you must configure your Terraform Enterprise instance to connect to the new database.

1. Navigate to the Admin Console at https://<TFE_HOSTNAME>:8800/settings.
2. In the PostgreSQL Configuration section, update the settings to point to the new database instance.

3. Save the settings and restart the Terraform Enterprise application when prompted. You can also restart it from the command line.

   $ replicatedctl app start

If you are migrating the database for a new Terraform Enterprise installation, you would perform the installation after successfully restoring the data into the new database.