File migration from MinIO to Cloudflare R2 via PostgreSQL
Posted on May 27, 2024 - by Andy Cinquin
MigrationMinIOCloudflare R2PostgreSQLFile storageDatabaseConfigurationURL
Introduction
In this tutorial, we'll look at how to migrate files stored on a MinIO server to Cloudflare R2 by updating URLs and configuration settings in a PostgreSQL database. This migration may be necessary when you decide to change your storage provider for reasons of performance, cost or functionality.
Prerequisites
- A PostgreSQL database containing references to files stored on MinIO.
- Connection details for your PostgreSQL database (host name, port, user name, password, database name).
- A Cloudflare account with R2 enabled and a bucket created to store your files.
- Have already made a copy of your data to R2 (I've already done a tutorial on this!).
Step 1: Update the "files" table
The first step is to update the file URLs in the "files" table. We'll replace the MinIO URL with the Cloudflare R2 one.
UPDATE files SET formats = regexp_replace( formats::text, 'https://minio.example.com/bucket/', 'https://r2.example.com/', 'g' )::jsonb WHERE formats::text LIKE '%https://minio.example.com/bucket/%';
This query uses the
regexp_replace function to replace all occurrences of the MinIO URL(https://minio.example.com/bucket/) with the Cloudflare R2 URL(https://r2.example.com/) in the formats column of the "files" table. The WHERE clause filters out rows where the formats column contains the MinIO URL.Step 2: Updating the provider in the "files" table
Next, we'll update the "provider" field in the "files" table to reflect the change of storage provider.
UPDATE files SET formats = regexp_replace( formats::text, '"provider": "minio-provider"', '"provider": "cloudflare-r2-provider"', 'g' )::jsonb WHERE formats::text LIKE '%"provider": "minio-provider"%';
This query replaces all occurrences of
"provider": "minio-provider" with "provider": "cloudflare-r2-provider" in the formats column of the "files" table.Step 3: Update the "settings" table
If you have configuration settings stored in a "settings" table, you must also update the references to MinIO in this table.
UPDATE settings SET value = regexp_replace( value::text, '"provider": "minio-provider"', '"provider": "cloudflare-r2-provider"', 'g' )::jsonb WHERE value::text LIKE '%"provider": "minio-provider"%';
UPDATE settings SET value = regexp_replace( value::text, 'https://minio.example.com/bucket/', 'https://r2.example.com/', 'g' )::jsonb WHERE value::text LIKE '%https://minio.example.com/bucket/%';
These queries update the provider and URL in the
value column of the "settings" table.Step 4: Updating content tables
If you have content tables (e.g. "articles" or "pages") that contain references to files stored on MinIO, you need to update these references.
UPDATE articles SET content = REGEXP_REPLACE( content, 'https://minio.example.com/bucket/(.[^)]+)', 'https://r2.example.com/\1', 'g' );
This query updates the file URLs in the
content column of the "articles" table. It uses a regular expression to capture the file path after the base URL and replaces it with the corresponding Cloudflare R2 URL.Step 5: Combined URL and provider update
Finally, you can combine URL and provider updates in a single query for the "files" table.
UPDATE files SET url = REGEXP_REPLACE( url, 'https://minio.example.com/bucket/([^>]+)', 'https://r2.example.com/\1', 'g' ), provider = REPLACE(provider, 'minio-provider', 'cloudflare-r2-provider') WHERE url LIKE 'https://minio.example.com/bucket/%' AND provider = 'minio-provider';
This query updates both the
url and provider columns of the "files" table in a single operation.Conclusion
By following the steps in this tutorial, you can successfully migrate your files from MinIO to Cloudflare R2 by updating the references in your PostgreSQL database. Be sure to replace URLs and provider names with the appropriate values for your environment.
Before running these queries on your production database, we strongly recommend testing the migration on a development or staging database to ensure that everything works as expected. Don't forget to back up your database before making any changes.
Once the migration is complete, you can enjoy the benefits offered by Cloudflare R2, such as performance, scalability and integration with other Cloudflare services.
Thank you for your visit, feel free to contact me for
any information, quote or collaboration proposal. I will
be happy to answer you as soon as possible.
Did you like this article? Feel free to share it!