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-archivistNote - 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.