I had recently added one column in postgres database, After the db migration, I wanted to update same migration file instead of creating new migrations. So I had added one new column in existing migration file and tried to run migrations again. Landed up with rails DB migration failed due to duplicate column.

Problem Statement

Caused by:
PG::DuplicateColumn: ERROR: column

01:17 deploy:migrate
[deploy:migrate] Checking changes in db
[deploy:migrate] Run rake db:migrate
01:19 deploy:migrating
01 ~/.rvm/bin/rvm 2.7.0 do bundle exec rake db:migrate

01 rake aborted!
01 StandardError: An error has occurred, this and all later migrations canceled:

Step zero analysis

Check all migration –

rails db:migrate status
Status   Migration ID    Migration Name
--------------------------------------------------
up     20211007025051  ********** NO FILE **********
up     20211014021137  Add order number to erp export
up     20211114172704  Add payment schedule event

Step 1 Solution

Run db:rollback

rails db:rollback
Status   Migration ID    Migration Name
--------------------------------------------------
up     20211007025051  ********** NO FILE **********
up     20211014021137  Add order number to erp export
Down   20211114172704  Add payment schedule event

Step 2 – Solve It My Way

Run rails dbconsole and provide migration ID to delete the migrations

$  rails dbconsole 
Enter DB password 
<db_name>=# delete from schema_migrations where version='20211114172704';

Check with db:migration status, if its status is still showing as ‘down’ then delete migrations using following command

rails d migration add_payment_schedule_event

This will remove/delete a migration file. But it will not remove the table entries from the database table.

So you will have to delete or drop the table column, For this login to database and run drop column

ALTER TABLE spree_products
DROP COLUMN on_completion_of_parking_level,
DROP COLUMN on_completion_of_podium_1_parking_level;

Now run the cap production deploy and you will see that deployment is success. To know more about setting up cap deployment step by step guide read more.

Further Reading

Following are few important rails commands.

db:migrate  – Runs migrations for the current environment that have not run yet. By default it will run migrations only in the development environment.

db:migrate:redo  – Runs db:migrate:down and db:migrate:up or db:migrate:rollback and db:migrate:up depending on the specified migration.

db:migrate:up  – Runs the up for the given migration VERSION.

db:migrate:down  – Runs the down for the given migration VERSION.

db:migrate:status  – Displays the current migration status.

db:migrate:rollback  – Rolls back the last migration.

db:version  – Prints the current schema version.

db:forward  – Pushes the schema to the next version.

db:seed  – Runs the db/seeds.rb file.

db:schema:load  Recreates the database from the schema.rb file. Deletes existing data.

db:schema:dump  Dumps the current environment’s schema to db/schema.rb.

db:structure:load  – Recreates the database from the structure.sql file.

db:structure:dump  – Dumps the current environment’s schema to db/structure.sql. (You can specify another file with  SCHEMA=db/my_structure.sql )

db:setup  Runs db:create, db:schema:load and db:seed.

db:reset  Runs db:drop and db:setup.  db:migrate:reset  – Runs db:drop, db:create and db:migrate.

db:test:prepare – Check for pending migrations and load the test schema. (If you run rake without any arguments it will do this by default.)

source – databse.rake