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