db-migration-mysql-postgres

Migrating Rails from MySQL to PostgreSQL without downtime uses a dual-write cutover workflow. This involves writing to both databases simultaneously, transferring bulk data via pgloader, verifying consistency, and swapping the primary database to prevent service interruption.

pgloader handles MySQL to PostgreSQL type conversions by casting TINYINT to BOOLEAN, JSON to JSONB, and datetime to timestamptz. It also manages MySQL-specific edge cases like zero dates to prevent data loss during the bulk transfer process.

Duplicate key errors after migrating Rails from MySQL to PostgreSQL occur because sequences are not reset post-migration. Resetting PostgreSQL sequences after the pgloader data transfer ensures auto-incrementing primary keys generate correct, non-conflicting values.

Broken case-insensitive search queries after moving Rails to PostgreSQL occur because MySQL searches are case-insensitive by default. You can fix this by using the Postgres-native ILIKE operator for case-insensitive matching instead of translating MySQL schema verbatim.

Yes, you can use PostgreSQL CHECK constraints for ActiveRecord enums in Rails. Upgrading your schema to leverage Postgres-exclusive features like CHECK constraints ensures enum compatibility while enforcing data integrity at the database level.

Best practices for upgrading Rails schema to PostgreSQL involve leveraging GIN indexes for JSONB, partial indexes, and ILIKE for case-insensitive queries. This approach optimizes query performance rather than simply translating the MySQL schema verbatim.