Django Test -- Unable to drop and recreate test database

Question

I am running Django 1.9, Postgres 9.5.1 on Mac OS X

When I run /manage.py test --settings=myproj.settings.local

I get :

Creating test database for alias 'default'...
Creating test database for alias 'userlocation'...
Got an error creating the test database: database "test_myproj" already exists

Type 'yes' if you would like to try deleting the test database 'test_myproj', or 'no' to cancel: yes
Destroying old test database for alias 'userlocation'...
Got an error recreating the test database: database "test_myproj" is being accessed by other users
DETAIL:  There is 1 other session using the database.

So, as per This post, I run:

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 

    pid <> pg_backend_pid()

    AND datname = 'test_myproj'
    ;

The proceed to DROP DATABASE test_myproj and try to run tests again only to get the error DETAIL: There is 1 other session using the database.

Looking as the process list, nothing is attached to the database. I kill the server and restart, but the management command to run tests still gives me the error that there is another session attached to the database.

This is a real head scratcher, I have never seen this before -- has anyone else?

My settings:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'myproj',
        'USER': 'myuser',
        'PASSWORD': 'mypass',
        'HOST': 'localhost',
        'PORT': '',
    },
    'userlocation': {
        'ENGINE': 'django.contrib.gis.db.backends.postgis',
        'NAME': 'og_myproj',
        'USER': 'og_myuser',
        'PASSWORD': 'mypasswd',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

i don't have a solution, but your problem seems to be, that both databases in case of test will be named `test_myproj` - for default it works, and than for userlocation this database is detected as existing. The existing session is the one that is still used by your current django running the tests. And after the script dies there is no session anymore ;) — dahrens, Aug 17 '16 at 15:14
have a look at those [docs](https://docs.djangoproject.com/en/1.10/topics/testing/advanced/#controlling-creation-order-for-test-databases) — dahrens, Aug 17 '16 at 15:17
@dahrens, thank you. Fixed up settings file a bit and works like a charm. — fiacre, Aug 17 '16 at 15:35

score 8 · Answer 1 · answered Aug 17 '16 at 14:58

8

sudo /etc/init.d/postgresql restart may be a restart will solve this issue

answered Aug 17 '16 at 14:58

Aamish Baloch

1,200
12
9

I am on a Mac, `$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart` then `./manage.py test --settings=ohmgear.settings.local --no-input` gives the same error: `Destroying old test database for alias 'userlocation'... Got an error recreating the test database: database "test_og_myproj" does not exist` – fiacre Aug 17 '16 at 15:06
"I kill the server and restart, but the management command to run tests still gives me the error that there is another session attached to the database." -- I had tried this – fiacre Aug 20 '16 at 21:21
1

@cs_stackX Telling us "This is not the correct answer" doesn't help. This looks like a workaround to me, but at least it corrects the problem. If you have a more permanent solution posting it would really help! – MikeyE Oct 23 '18 at 13:03

score 5 · Answer 2 · answered Aug 11 '20 at 09:32

In terminal, run:

ps aux | grep postgres

This will output different processes that are running using postgres, such as:

my_user 5983 0.0 0.0 7325299 4583 ?? Ss 3:15PM 0:00.02 postgres: my_user test_myproj [local] idle

Find the process running test_myproj, and that process's correspondent ID, which is 5983 in this example. Kill this process by running:

kill -9 5983

score 4 · Answer 3 · answered Jul 30 '20 at 15:37

I found a useful answer here ported by CHUCKCMARTIN

To sum up, you need to run this code to make it available again.

from django.core.management.base import BaseCommand
from django.db import connection
from django.conf import settings
cursor = connection.cursor()
database_name = 'test_<FAILING_DB_NAME>'
cursor.execute(
    "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity "
    "WHERE pg_stat_activity.datname = %s AND pid <> pg_backend_pid();", [database_name])

quiet_penguin · Answer 4 · 2021-02-16T09:58:49.300

0

There is 1 other session using the database.

Have seen this message when the test database was open in pg admin ui and trying to run django tests at same time. Before dropping the db django/psycopg2 checks if there are any sessions active on that db. Closed the connection to the server in pg admin and it just works. Check if you have a connection to the db in shell or ui. Should not need to restart server.

edited Feb 16 '21 at 09:58

answered Aug 06 '17 at 05:27

quiet_penguin

808
7
18

After this if tests run ok but this shows up django.db.utils.OperationalError: cannot drop the currently open database: then Django needs to connect to the postgres databse and that needs to be defined in pg_hba.conf. – quiet_penguin Aug 06 '17 at 06:50

score 0 · Answer 5 · answered Aug 17 '21 at 20:50

I faced the same issue while trying to run some unit tests from pycharm. As previously mentioned, restarting my postgres solved the issue for me.

If someone is using a docker container, the command you will need to use is,

docker-compose restart postgresql-11

postgresql-11 is the name of my PostgreSQL database server name.

Django Test -- Unable to drop and recreate test database

5 Answers5