Migrating CARTO Self-Hosted installation to an external database

This documentation is for the CARTO Self-Hosted Legacy Version. Use only if you've installed this specific version. Explore our latest documentation for updated features.

This guide outlines the steps to migrate the internal database of a CARTO Self-Hosted installation to an external database. By following these steps, you can seamlessly transition your CARTO workspace to a new database environment.

Prerequisites

Before starting the migration process, ensure that you have the following:

• Access to the CARTO Self-Hosted installation

• Credentials for the external database (hostname, port, username, password)

How to perform the migration to an external database

Step 1: Dump the Container Database

Use the following command to create a dump of the internal database:

Single VM deployment

Run the following command from the installation folder:

docker-compose exec workspace-postgres pg_dump -U postgres -d workspace > dump.sql

Orchestrated container deployment

1. Obtain the PostgreSQL password generated by the CARTO installation from the secret created for postgresql:

kubectl get secret <postgresql_secret_name> \
 -n <namespace> \
 -ogo-template='{{ index .data "postgres-password" }}' | base64 -D

You can obtain the name of your PostgreSQL secret running the following command:

kubectl get secrets -n <namespace>

2. Obtain the name of your workspace database pod. The following command should return a pod named postgresql

kubectl get pods -n <namespace> | grep postgresql

3. Run the following command to perform a backup of the internal database located in your cluster:

kubectl exec <postgresql_pod_name> \
 -n mcalzado \
 -- pg_dump --dbname=postgresql://postgres:<postgres_password>@localhost:5432/workspace > dump.sql

This command exports the database structure and data to a SQL file named dump.sql

Step 2: Create Workspace Admin User in External Database

Run the following command to create the necessary user in the new external database:

CREATE USER workspace_admin with encrypted password '<password>';

Step 3: Create Database in External Database

Execute the following commands to create the database in the external database:

CREATE DATABASE workspace OWNER workspace_admin;

Step 4: Restore the Dump

Restore the dumped data into the new external database. If you have access to the external database using psql you can directly restore the database using the following command:

psql -h <external_db_host> -p <external_db_port> -U workspace_admin -d workspace -f dump.sql

You have successfully migrated your CARTO Self-Hosted installation to an external database! 🎉 Ensure that you update your CARTO configuration files to reflect the changes in the database connection details. You can find more information about how to connect your Self-Hosted installation to an external database in our quickstart guides.