論盡蛋
心で書く

Blog


Drop PostgreSQL database

Update:

If you are using Rails 4.x, please read this. Thank you Manuel Meurer for letting me know the following implementation applies to Rails 3.x only.

If you are using Rails 3.x, you may continue use the following patch.


Some of my projects uses PostgreSQL as my database. However, running rake db:drop always gives me the following error:

Couldn't drop PROJECT_NAME_development : #<ActiveRecord::StatementInvalid: PG::Error: ERROR:  database "PROJECT_NAME_development" is being accessed by other users
DETAIL:  There are 2 other session(s) using the database.
: DROP DATABASE IF EXISTS "PROJECT_NAME_development">

To resolve this problem, as hinted by the message about, is to stop all sessions using that database.

However, I just found (I think) better solution from this stackoverflow answer (which points to this gist).

It is to monkey patch the rake db:drop command. To do so, simply copy the code into lib/tasks/databases.rake and now rake db:drop is patched with this code which stops the connections.

The code is as follow:

#!ruby #{Rails.root}/lib/tasks/databases.rake
=begin
  Monkey Patch 
  activerecord-3.0.9/lib/active_record/railties/databases.rake
  clears obstinate stale PG session to get parallel_tests working
  also, PG user must be superuser to use these low level PG functions
=end
def drop_database(config)
  case config['adapter']
  when /mysql/
    ActiveRecord::Base.establish_connection(config)
    ActiveRecord::Base.connection.drop_database config['database']
  when /sqlite/
    require 'pathname'
    path = Pathname.new(config['database'])
    file = path.absolute? ? path.to_s : File.join(Rails.root, path)

    FileUtils.rm(file)
  when /postgresql/
    ActiveRecord::Base.connection.select_all("select * from pg_stat_activity order by procpid;").each do |x|
      if config['database'] == x['datname'] && x['current_query'] =~ /<IDLE>/
        ActiveRecord::Base.connection.execute("select pg_terminate_backend(#{x['procpid']})")
      end
    end
    ActiveRecord::Base.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))
    ActiveRecord::Base.connection.drop_database config['database']
  end
end

ActiveRecord::RecordNotUnique and Postgresql

OK I am not sure if this is a postgresql-specific problem or not, but I believe it is. If you did something like this (such as in seed.rb):

#!ruby
User.create(id: 1, ...)

Then the next time you create new user, the ActiveRecord::RecordNotUnique error would be raised.

But why you specified id!?

I have to admit that this is a lazy and silly decide. I re-created ChordsPresent and migrated from Heroku bamboo stack to cedar stack. The models have some schema-level changes also. Thus the easier way for me to migrate the old data to the new database is:

  1. download the remote database
  2. create a rake file to output a seed.rb file which based on the existing data (collecting only the required data, and matching the new schema)
  3. add the outputted seed.rb file to the new app source code, and push and run it in the cedar stack (so the new database now has all the new data with correct schema)
  4. revert the seed.rb and destroy the last commit completely (git tracking sensitive data is always a bad practice!, although the most sensitive data are the users' provider and uid only)
  5. push to heroku again with -f flag to force replace it

As I want to retain the chords and users id, thus I included the id field of both table. However explicitly assigning the id field causes problem.

How to fix?

(Provided this is a postgresql problem)

#!ruby
ActiveRecord::Base.connection.execute("SELECT setval('table_name_id_seq', (SELECT max(id) FROM table_name));")

replace the table_name with your actual table name. In my case, it is:

#!ruby
ActiveRecord::Base.connection.execute("SELECT setval('users_id_seq', (SELECT max(id) FROM users));")
ActiveRecord::Base.connection.execute("SELECT setval('chords_id_seq', (SELECT max(id) FROM chords));")