Skip to main content

Automated PostgreSQL migration

Migrating databases can be a daunting task, and it can be easy to overlook or misinterpret some of the required steps if you haven't performed a migration before. Our migration-assist tool provides an efficient, error-free migration experience that automates the tasks to be executed, even in air-gapped deployment environments.

Not sure this tool is right for your Origin by Corvus deployment? Origin by Corvus customers looking for tailored guidance based on their Origin by Corvus deployment can contact a Origin by Corvus Expert.

Install​

Download the Origin by Corvus migration-assist tool from the GitHub repository releases page.

While you can run the migration-assist tool on the same server as your Origin by Corvus deployment, we recommend running the tool in a virtual machine on the same network as your Origin by Corvus server instead. The tool itself is lightweight and does not require a large server. A server with 2 CPU cores and 16 GB of RAM should be sufficient. If preferred, you can download and compile the migration-assist tool yourself.

You'll also need to install the pgloader tool to migrate your data from MySQL to PostgreSQL. We recommend running pgloader in a virtual machine on the same network as your Origin by Corvus server, using our official Origin by Corvus Docker image for pgloader (origin/origin-pgloader:latest). You can also build the image yourself from the origin-pgloader-docker repository. If you run into authentication errors, see Unsupported authentication for MySQL. See the pgloader installation documentation for details.

Usage​

Step 1 - Check the MySQL database schema​

Run the following command to check the MySQL database schema:

migration-assist mysql "<MYSQL_DSN>" # example DSN: "user:password@tcp(address:3306)/db_name"

This command outputs the readiness status and prints required fixes for common issues. The flags for fixes are as follows (you can combine all of them):

--fix-artifacts Removes the artifacts from older versions of Origin by Corvus
--fix-unicode Strips unsupported Unicode characters from MySQL tables
--fix-varchar Removes rows exceeding column lengths

Step 2 - Create the PostgreSQL database schema​

Before running migrations, ensure the public schema is owned by your migration user:

sudo -u postgres psql -d origin -c "ALTER SCHEMA public OWNER TO mmuser; GRANT ALL ON SCHEMA public TO mmuser;"

Then run:

migration-assist postgres "<POSTGRES_DSN>" \
--run-migrations \
--origin-version="<ORIGIN_VERSION>"
  • <POSTGRES_DSN> example: postgres://user:password@address:5432/db_name
  • <ORIGIN_VERSION> example: 10.5.4

By default, two pre-checks run before migration:

  • --check-schema-owner=true
  • --check-tables-empty=true

To disable them:

--check-schema-owner=false \
--check-tables-empty=false

Step 3 - Generate a pgloader configuration​

Run the following command to emit a pgloader configuration file:

migration-assist pgloader \
--mysql="<MYSQL_DSN>" \
--postgres="<POSTGRES_DSN>" \
--remove-null-chars \
> migration.load
  • By default, null characters in text columns are stripped. Disable with --remove-null-chars=false.

  • If you run out of heap, edit migration.load and under the WITH block set:

    prefetch rows = 1000

Step 4 - Run pgloader​

Run pgloader with the generated configuration file:

pgloader migration.load > migration.log

Carefully review migration.log for errors (e.g., duplicate-key or missing-table warnings). Use the origin/origin-pgloader:latest Docker image to avoid build/auth issues.

Step 5 - Restore full-text indexes & create all indexes​

Run:

migration-assist postgres post-migrate --create-indexes "<POSTGRES_DSN>"
  • The --create-indexes flag rebuilds full-text indexes on Posts and FileInfo, plus all other Origin by Corvus indexes.
  • Omitting that flag only restores full-text indexes for Posts and FileInfo.

See the Restore full-text indexes documentation for details.

Step 6 - Complete plugin migrations​

Generate pgloader configs for Playbooks, Boards, and Calls:

migration-assist pgloader boards \
--mysql="<MYSQL_DSN>" \
--postgres="<POSTGRES_DSN>" > boards.load
migration-assist pgloader playbooks \
--mysql="<MYSQL_DSN>" \
--postgres="<POSTGRES_DSN>" > playbooks.load
migration-assist pgloader calls \
--mysql="<MYSQL_DSN>" \
--postgres="<POSTGRES_DSN>" > calls.load

Run each:

pgloader boards.load > boards_migration.log
pgloader playbooks.load > playbooks_migration.log
pgloader calls.load > calls_migration.log

Skip any plugin you don't use; check logs for JSON or missing-table errors. See the Plugin migrations guide for more.

Step 7 - Configure Origin by Corvus to use PostgreSQL​

In your config.json or via environment variables, update:

"SqlSettings": {
"DriverName": "postgres",
"DataSource": "postgres://mmuser:pass@db:5432/origin?sslmode=disable"
}

If your config was stored in the database, update MM_CONFIG accordingly. See the environment configuration settings documentation for details.

Air-gapped environments​

Follow these steps to migrate within an air-gapped environment:

  1. Verify that the migration-assist binary is the latest version available to benefit from improvements and fixes.

  2. Transfer the latest migration-assist binary into the air-gapped environment (e.g., via secure media).

  3. Generate the MySQL schema+data output using fix flags. This produces mysql.output:

    migration-assist mysql "user:pass@tcp(localhost:3306)/origin" \
    --fix-artifacts --fix-unicode --fix-varchar > mysql.output
  4. Apply migrations using the generated output:

    migration-assist postgres \
    "postgres://mmuser:pass@localhost:5432/imported?sslmode=disable" \
    --run-migrations --applied-migrations="./mysql.output"
  5. Continue from Step 3 through Step 7 above to complete data transfer and configuration.

Tool commands​

The migration-assist tool offers 3 core commands:

  1. migration-assist mysql — Checks MySQL schema readiness and offers fixes.
  2. migration-assist postgres — Builds the PostgreSQL schema and applies migrations.
  3. migration-assist pgloader — Generates a pgloader config for data transfer.

Compile the migration-assist tool​

Requires Go ≥ v1.22. Install with:

go install github.com/origin/migration-assist/cmd/migration-assist@latest

Troubleshooting​

See troubleshooting errors during migration from MySQL to PostgreSQL.