Rails应用从SQLite3迁移至PostgreSQL时sequel gem报错排查

Hey there, let's break down why you're hitting that "relation 'applicants' already exists" error and get your Rails data migration sorted out smoothly.

What's Causing This? #

The error is pretty straightforward: your target PostgreSQL database tuma_development already has an applicants table in it. This could happen for a few reasons:

• You tried running the migration command earlier and it partially succeeded
• Someone (or a script) manually created the applicants table in the PostgreSQL database
• You ran Rails migrations on the PostgreSQL database first, which built out schema tables before the Sequel copy

How to Fix It #

Pick the solution that matches your situation:

1. Start Fresh (Best if you don't need existing PostgreSQL data)

If the PostgreSQL database has no valuable data and you want a clean copy of your SQLite data, wipe and rebuild the target database:

# Delete the existing PostgreSQL database (double-check you don't need this data!)
dropdb tuma_development

# Create a brand new empty database
createdb tuma_development

# Re-run the Sequel copy command
sequel -C sqlite://db/development.sqlite3 postgres://keemtaker:password@localhost/tuma_development

2. Remove Only the Duplicate Table

If you want to keep other tables in PostgreSQL but replace the applicants table with your SQLite data:

# Connect to PostgreSQL and delete the existing applicants table
psql -d tuma_development -c "DROP TABLE IF EXISTS applicants;"

# Now run the migration command again
sequel -C sqlite://db/development.sqlite3 postgres://keemtaker:password@localhost/tuma_development

3. Skip Existing Tables (For Partial Migrations)

If you want Sequel to skip any tables that already exist in PostgreSQL (note: this won't sync data in existing tables, just skip creating them), use the --if-not-exists flag:

sequel -C --if-not-exists sqlite://db/development.sqlite3 postgres://keemtaker:password@localhost/tuma_development

Warning: If the existing applicants table has a different schema than the SQLite version, you might run into data compatibility issues later. Double-check schema consistency before using this option.

Quick Check to Confirm the Issue

Before making changes, you can verify that the applicants table really exists in PostgreSQL:

psql -d tuma_development -c "\dt"

This command lists all tables in the database—look for applicants in the output to confirm the issue.

内容的提问来源于stack exchange，提问作者Hakeem Baba