Last Updated: February 25, 2016
· alistairholt

Rails & Postgres: Better dropping of the database


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:="development"}
db_config_path = File.expand_path("config/database.yml", Dir.getwd)
abort 'NO_CONFIG' unless File.exists?(db_config_path)
db_config = YAML.load(["$RAILS_ENV"]
abort 'NO_POSTGRES' unless db_config["adapter"] == "postgresql"
abort db_config["database"]
  DB_NAME=$(ruby -ryaml -e $RUBY_SCRIPT 2>&1)
  if [[ $DB_NAME = 'NO_CONFIG' ]]
    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' ]]
    echo "Oops! It doesn't look like your using Postgres for your database"
    return 0

  # Assuming the latest verison of Postgres (9.2+)
  psql -c "SELECT pg_terminate_backend( 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."
    echo 'Database dropped!'

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

An alternative is to monkeypatch the code in ActiveRecord. I just wrote a quick blog entry on how to do this:

over 1 year ago ·

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

over 1 year ago ·

I'm using a new solution now:

over 1 year ago ·