CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordrake
Giron
Giron

Reputation: 370

Rails db:drop in postgres doesn't work


I am having troubles with dropping tables in my database. I have recently changed the database from SQLite to Postgres 9.3 and my preparation rake task for recreating database doesn't work - db:drop does not drop the database, also there is no connection (session) to the postgres currently active and no error in log (with --trace on). My rake task looks like this:

task prepare_all: :environment do
    puts "==== PREPARING DATABASE ===="
    Rake::Task["db:drop"].invoke()
    Rake::Task["db:create"].invoke()
    Rake::Task["db:migrate"].invoke()
    Rake::Task["db:populate"].invoke()
    Rake::Task["db:codetables_populate"].invoke()
    Rake::Task["db:geocode"].invoke()
    puts "==== DATABASE PREPARED ===="
end

But it fails because database is not dropped, data is still present and when I am populating the database, it fails with first insert because of email validation (email already exist). When I tried manualy run task db:drop with trace, output was following:

bundle exec rake db:drop RAILS_ENV=development --trace
DL is deprecated, please use Fiddle
** Invoke db:drop (first_time)
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:drop

When I looked into database, all tables were full of data. I am using Postgres 9.3 (on Windows 7). This behavior does not occurred immediately after changing the database to Postgres, it worked at first, but than I tried manually edit existing migration files (and changing their names to enforce them) and all stopped working. When I manually drop the tables, task completes, but the second time...it fails again of course, because db:drop does nothing.

Does anyone has any idea how to make it work again? Thanks in advance.

EDIT:
In the end I have ended up with using direct SQL to drop the tables, but I don't want to use DB specific code, so I'm still interested in the answer, current code:

task prepare_all: :environment do
    puts "==== PREPARING DATABASE ===="
    # Rake::Task["db:drop"].invoke()
    ActiveRecord::Base.connection.execute("DROP SCHEMA public CASCADE;
                                           CREATE SCHEMA public;
                                           GRANT ALL ON SCHEMA public TO postgres;
                                           GRANT ALL ON SCHEMA public TO public;
                                           COMMENT ON SCHEMA public IS 'standard public schema';") 
    Rake::Task["db:create"].invoke()
    Rake::Task["db:migrate"].invoke()
    Rake::Task["db:populate"].invoke()
    Rake::Task["db:codetables_populate"].invoke()
    Rake::Task["db:geocode"].invoke()
    puts "==== DATABASE PREPARED ===="
  end

Upvotes: 2

Views: 1177

Answers (2)

Meekohi
Meekohi

Reputation: 10882

Postgres will not let you drop the database while there are still active connections.

To disconnect all connections, run

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();

see also:

How to drop a PostgreSQL database if there are active connections to it?

Why can't I successfully use rake db:drop or rake db:purge?

Upvotes: 0

user1322092
user1322092

Reputation: 4270

I ran into this issue recently after quickly creating a new rails project.

Short work-around - run rake db:rollback STEP=100

Likely reason? Your database.yml file doesn't specify a database. Be certain to add in default: or development: your postgres database: database: your_db_name

Hope this works...

Upvotes: 3

Related Questions