Skip to main content

Configure and migrate to an external database

Learn how to store and migrate user information for Self-hosted Retool deployments.

In Docker-based self-hosted setups, user information and documents are stored in a PostgreSQL Docker container with a persistent volume. For production use cases, you should host this database on an external, managed database. Managed databases are more maintainable, scalable, and reliable than containerized PostgreSQL instances.

Refer to the Self-hosted Retool quickstart for info on the tables included in the external database.

Use the following steps to configure an external database for Docker-based deployments.

Prerequisites​

Before migrating, ensure that your architecture meets the following recommendations and requirements:

  • The minimum recommended version for the PostgreSQL database is version 13. Your PostgreSQL database must also enable the uuid-ossp module and use the Read Committed isolation level.
  • Retool recommends allocating at least 60GB of storage when you set up a new Retool instance. If you're migrating an existing instance, you might need more space.
  • The Retool PostgreSQL database user must have superuser privileges on the hammerhead_production database. This is necessary to perform essential tasks, such as installing updates.

1. Export data from Retool's Docker container​

To export data from Retool's PostgreSQL container, run the following command on the virtual machine hosting the Retool containers, in the Retool directory.

docker-compose exec postgres \
 pg_dump hammerhead_production --no-acl --no-owner --clean \
 -U  retool_internal_user -f retool_db_dump.sql

This dumps the data into a file named retool_db_dump.sql in the root of the volume used by the Retool PostgreSQL container.

To export data to your host machine, run the following command.

docker-compose exec postgres \
  pg_dump hammerhead_production --no-acl --no-owner --clean \
  -U  retool_internal_user > retool_db_dump.sql

2. Migrate the data to an external hosted database​

Retool recommends using PostgreSQL 13.7 for the external database.

To migrate the data to an external database, run the following command. Replace $DB_CONNECTION_URI with the PostgreSQL connection string that connects to your externally hosted database. Make sure to correctly format the URI.

docker-compose exec postgres \
 psql $DB_CONNECTION_URI -f retool_db_dump.sql

3. Create a user with superuser privileges​

Some databases grant superuser privileges to the first user added to the database, but you should check your database's documentation to learn more. Depending on your database, you might need to create a user. Some examples include the:

  • rds_superuser for Amazon RDS.
  • cloudsqlsuperuser for Google Cloud SQL.
  • azure_pg_admin Azure Database for PostgreSQL.

4. Configure Retool to use the external database​

The docker.env files defines several Retool options, including which database to connect to. The lines you need to edit follow this pattern:

POSTGRES_DB=hammerhead_production
POSTGRES_USER=retool_internal_user
POSTGRES_HOST=postgres
POSTGRES_PORT=5432
POSTGRES_PASSWORD={a random string}

Edit those variables to correspond with the database you created. For example:

POSTGRES_DB=retooldb
POSTGRES_USER=retool_user
POSTGRES_HOST=retool.xxxxxxxxxx.us-east-2.rds.amazonaws.com
POSTGRES_PORT=5432
POSTGRES_PASSWORD=xyzabc

Alternatively, you can delete the five environment variables and instead specify a database connection string:

DATABASE_URL=postgres://retool_user:xyzabc@retool.xxxxxxxxxx.us-east-2.rds.amazonaws.com:5432/retooldb

5. Restart the Retool server​

After updating the Retool configuration, run this command from the retool-onpremise directory to restart the server.

sudo docker-compose up -d