Django can't drop database: psycopg2.OperationalError: cannot drop the currently open database

Question

Whenever I try to run my Django tests via manage.py, the tests run fine however at the end when Django is destroying the database the following error occurs:

Destroying test database for alias 'default'...
Traceback (most recent call last):
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
psycopg2.OperationalError: cannot drop the currently open database


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Applications/PyCharm.app/Contents/helpers/pycharm/django_test_manage.py", line 129, in <module>
    utility.execute()
  File "/Applications/PyCharm.app/Contents/helpers/pycharm/django_test_manage.py", line 104, in execute
    PycharmTestCommand().run_from_argv(self.argv)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/core/management/commands/test.py", line 30, in run_from_argv
    super(Command, self).run_from_argv(argv)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/core/management/base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/core/management/commands/test.py", line 74, in execute
    super(Command, self).execute(*args, **options)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/core/management/base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "/Applications/PyCharm.app/Contents/helpers/pycharm/django_test_manage.py", line 91, in handle
    failures = TestRunner(test_labels, verbosity=verbosity, interactive=interactive, failfast=failfast, keepdb='--keepdb' in sys.argv)
  File "/Applications/PyCharm.app/Contents/helpers/pycharm/django_test_runner.py", line 256, in run_tests
    extra_tests=extra_tests, **options)
  File "/Applications/PyCharm.app/Contents/helpers/pycharm/django_test_runner.py", line 156, in run_tests
    return super(DjangoTeamcityTestRunner, self).run_tests(test_labels, extra_tests, **kwargs)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/test/runner.py", line 534, in run_tests
    self.teardown_databases(old_config)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/test/runner.py", line 509, in teardown_databases
    connection.creation.destroy_test_db(old_name, self.verbosity, self.keepdb)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/db/backends/base/creation.py", line 264, in destroy_test_db
    self._destroy_test_db(test_database_name, verbosity)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/db/backends/base/creation.py", line 283, in _destroy_test_db
    % self.connection.ops.quote_name(test_database_name))
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.OperationalError: cannot drop the currently open database

I've checked to ensure that nothing else is connected to the database - why can't Django drop the database?

Note: I am using PostgreSQL

Triple

I am having the same issue. I filed a ticket with a lot of details of my setup but am getting "can't reproduce" for reasons that are unclear to me. code.djangoproject.com/ticket/27751 – Riposte

Answer 1

TL;DR

If you just want the solution, here it is. Make sure that your Postgres server has a "postgres" database. You can check by connecting via psql, and running /list or /l. You can create the database by running: CREATE DATABASE postgres in psql.

Extended

Django prefers to not run "initialization queries" (presumably things like creating the test database) from the "default" database specified in your DATABASES setting. This is presumed to be the production database, so it would be in Django's best interests to not mess around there.

This is why at the beginning of the tests, this is shown (ignore my filesystem):

    /Users/dcgoss/Desktop/Pickle/PickleBackend/venv/lib/python3.4/site-packages/django/db/backends/postgresql/base.py:247: 
    RuntimeWarning: Normally Django will use a connection to the 'postgres' database to avoid running initialization queries against the production database when it's not needed 
    (for example, when running tests). Django was unable to create a connection to the 'postgres' database and will use the default database instead.
    RuntimeWarning

Django looks for a database named "postgres" on the host specified in your DATABASES settings, this is where it runs the queries to create and delete your test database. (Note: Django does not need the "postgres" database to be explicitly specified in your settings, it just looks for it on whatever database server is specified in your settings).

It appears that if this "postgres" database does not exist, Django can create the test database and run the tests, but it cannot delete the test database. This may be because Postgres does not allow you to drop the database you are currently connected to, however to me it seems that there is no reason that Django couldn't just drop the test database from the "default" (production) database specified in the settings. I presume it is using the "default" database to create the test database, so it seems there's no reason why it can't delete it as well.

Regardless, the solution was just to create that "postgres" database with a simple SQL statement: CREATE DATABASE postgres. After that database was created, everything worked fine.

Triple

For psql commands, the forward slashes should be backslashes: \list or \l to list databases and tables. – Weft

Or as the other answer points out, you might have the database postgres, but are lacking access to it. – Harper

Thanks for the answer, my problem was however that it wasn't running at all. I got the warning and than I got a RuntimeError: generator didn't stop in self._execute_create_test_db. Don't understand where that is coming from! – Stepup

Answer 2

If postgres database exists, try adding access to 'postgres' database in pg_hba.conf. Refer: https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

Shout

Thanks! This solved my problem. I had too strict rules in the pg_hba.conf file on the server. Granting access on the database postgres to the django app user solved the problem . – Harper

Answer 3

You might want to add postgres database to the list of database in pgbouncer.ini file, if you're using pgbouncer with postgresql

Amalgamate