Configure and migrate to an external database
Learn how to store and migrate user information for Self-hosted Retool deployments.
For non-production and development purposes, the default docker-compose configuration for Self-hosted Retool includes a PostgreSQL container and does not set up SSL. This is not suitable for production use cases and you must host the Retool storage database on an external, managed PostgreSQL database. Managed databases are more maintainable, scalable, and reliable than containerized PostgreSQL instances.
If you originally deployed an instance using the default PostgreSQL container, you can migrate its data to an external PostgreSQL database before using the instance in production.
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_productiondatabase. This is necessary to perform essential tasks, such as installing updates.
Self-hosted Retool requires the use of an externalized PostgreSQL database (version 13 or later) to manage deployment functions and support stateless deployments. When configuring an external database, you must:
- Enable the uuid-ossp module.
- Use the Read Committed isolation level.
Retool creates a number of tables in the database, such as:
| Table | Description |
|---|---|
app_observability_provider_configs | Observability setup and streaming details. |
audit_trail_events | Events that feed audit logs on user instances with the /audit endpoint. |
grid_managed_cluster_resources | Retool Database when linked to an external PostgreSQL database. |
grid_managed_clusters | Retool Database when linked to an external PostgreSQL database. |
organizations | Spaces within the organization. |
page_saves | Changes made to specific apps since they were created. |
resources | Resources, including full setup and configuration information. |
users | All enabled and disabled users. |
user_task_instance | Workflows that include user task blocks. |
Do not directly modify Retool table data. Doing so can damage the instance.
All storage database tables
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_superuserfor Amazon RDS.cloudsqlsuperuserfor Google Cloud SQL.azure_pg_adminAzure 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