Exporting Database Dumps via the Upsun CLI

Overview

Exporting data from your project is a common task for creating local backups, debugging with production data, or migrating databases. While you can use standard database tools (like mysqldump or pg_dump), the Upsun CLI provides a streamlined, secure way to export your data without manually managing SSH tunnels or credentials.

This guide replaces the legacy community post and provides the modern, recommended approach for MySQL/MariaDB, PostgreSQL, and MongoDB.

1. Prerequisites

• The Upsun CLI installed and authenticated.

• Appropriate permissions to access the environment from which you are exporting data.

2. Exporting Data by Database Type

The Upsun CLI provides a dedicated db:dump command that automatically identifies your service credentials and pipes the output to a file on your local machine.

MySQL / MariaDB

To export a MySQL or MariaDB database, run the following command from your local project directory:

upsun db:dump -e <environment_name> --relationship <relationship_name> > dump.sql

• Tip: If you only have one database relationship defined, you can omit the --relationship flag.

• Compression: To save space and time, you can compress the dump on the fly:

  Bash

   

  upsun db:dump -e main --gzip > dump.sql.gz
  

PostgreSQL

For PostgreSQL, the process is identical. The CLI uses pg_dump internally:

upsun db:dump -e <environment_name> > dump.sql

• Schema only: If you only want the table structures without the data, use the --schema-only flag.

• Data only: Use --data-only to export just the row content.

MongoDB

Exporting MongoDB is slightly different as it produces BSON files rather than SQL text. The CLI facilitates this via the mongodb:export command:

upsun mongodb:export -e <environment_name> --archive=dump.archive

• Archive Format: The --archive flag is recommended as it bundles the collection data into a single file, making it easier to transport and restore.

3. Alternative Method: Using Standard Tools

If you prefer using your local database client (like MySQL Workbench, pgAdmin, or the command-line psql), you can use the CLI to open a secure tunnel to the remote service:

1. Open the tunnel:

   Bash

    

   upsun tunnel:open -e <environment_name>
   

2. Note the credentials: The CLI will output a local port (e.g., 127.0.0.1:30000).

3. Run your tool:

   Bash

    

   mysqldump -h 127.0.0.1 -P 30000 -u <username> <db_name> > local_dump.sql
   

4. Best Practices and Troubleshooting

Handle Large Databases

For very large databases, the db:dump command might time out due to network instability. In these cases:

• Use the --gzip flag to reduce the amount of data transferred.

• If timeouts persist, consider running the dump inside the application container and then downloading the file:

  Bash

   

  upsun ssh "mysqldump --all-databases | gzip > /tmp/dump.sql.gz"
  upsun scp app@default:/tmp/dump.sql.gz ./local_dump.sql.gz
  

Environment Specifics

Always verify which environment you are targeting. Exporting from main (production) may take longer and impact performance if the database is under heavy load. It is often safer to export from a recent Preview Environment (clone) if the data is fresh enough for your needs.

Security

The upsun db:dump command uses SSH under the hood. Your data is encrypted during transit. Never store your SQL dumps in public-facing directories or commit them to your Git repository.

Summary Table