qtkppg
Last Updated: February 25, 2016
·
2.107K
· alistairholt

Rails & Postgres: Better dropping of the database

UPDATE

Please see my new tip for a better solution!

If this sounds familiar...

  • You're running your Rails apps locally via Pow
  • You're running Postgres
  • You want to drop a database entirely every now and then

...then you've probably been running bundle exec rake db:drop and getting error messages from Postgres about other sessions using the database. Annoying.

To solve this issue for myself I've created a small bash function that will close all the active connections for a Postgres database and then drop it.

pgdrop() {
  RAILS_ENV=$1
  : ${RAILS_ENV:="development"}
  RUBY_SCRIPT=$(cat <<SCRIPT
db_config_path = File.expand_path("config/database.yml", Dir.getwd)
abort 'NO_CONFIG' unless File.exists?(db_config_path)
db_config = YAML.load(File.open(db_config_path))["$RAILS_ENV"]
abort 'NO_POSTGRES' unless db_config["adapter"] == "postgresql"
abort db_config["database"]
SCRIPT)
  DB_NAME=$(ruby -ryaml -e $RUBY_SCRIPT 2>&1)
  if [[ $DB_NAME = 'NO_CONFIG' ]]
  then
    echo "No database config file could be found (./config/database.yml)\nMake sure you're running this command from the root of your Rails app"
    return 0
  elif [[ $DB_NAME = 'NO_POSTGRES' ]]
  then
    echo "Oops! It doesn't look like your using Postgres for your database"
    return 0
  fi

  # Assuming the latest verison of Postgres (9.2+)
  psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = \"$DB_NAME\" AND pid <> pg_backend_pid();" > /dev/null 2>&1
  RESULT=$(psql -c "DROP DATABASE IF EXISTS \"$DB_NAME\";" 2>&1)
  if [[ $RESULT == *'does not exist'* ]]; then
    echo "Database doesn't exist! Nothing to do."
  else
    echo 'Database dropped!'
  fi
}

With this I can now run pgdrop from within my Rails app directory and drop the database easily. The script defaults to dropping the development environment database so if you want to specify a different environment pass the environment name as an argument e.g. pgdrop staging.

oh-my-zsh users: add it to ~/.zshrc and then source ~/.zshrc or open a new terminal window.

bash users: add it to ~/.bashrc and then source ~/.bashrc or open a new terminal window.

NOTE: I've only tested this with Postgres 9.3+. I believe it should work with 9.2+ but I'm offering no guarantees!

If you find any bugs or have any improvements, please update the Gist.

3 Responses
Add your response

12015

An alternative is to monkeypatch the code in ActiveRecord. I just wrote a quick blog entry on how to do this: http://www.krautcomputing.com/blog/2014/01/10/how-to-drop-your-postgres-database-with-rails-4/

over 1 year ago ·
12017

Thanks Manuel, it's certainly a lot less code!

over 1 year ago ·
13342

I'm using a new solution now: https://coderwall.com/p/kh0yjg

over 1 year ago ·