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.loadand under theWITHblock 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-indexesflag rebuilds full-text indexes onPostsandFileInfo, plus all other Origin by Corvus indexes. - Omitting that flag only restores full-text indexes for
PostsandFileInfo.
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:
-
Verify that the
migration-assistbinary is the latest version available to benefit from improvements and fixes. -
Transfer the latest
migration-assistbinary into the air-gapped environment (e.g., via secure media). -
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 -
Apply migrations using the generated output:
migration-assist postgres \"postgres://mmuser:pass@localhost:5432/imported?sslmode=disable" \--run-migrations --applied-migrations="./mysql.output" -
Continue from Step 3 through Step 7 above to complete data transfer and configuration.
Tool commands
The migration-assist tool offers 3 core commands:
migration-assist mysql— Checks MySQL schema readiness and offers fixes.migration-assist postgres— Builds the PostgreSQL schema and applies migrations.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.