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
– dahrens, Commented Aug 17, 2016 at 15:14
@dahrens, thank you. Fixed up settings file a bit and works like a charm. — fiacre
– fiacre, Commented Aug 17, 2016 at 15:35
In my case, I used a pgbouncer in front of the postgres, so it couldn't close the connection. — Phoenix
– Phoenix, Commented Dec 3, 2024 at 15:01

Aamish Baloch · Accepted Answer · 2016-08-17 14:58:38Z

8

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

answered Aug 17, 2016 at 14:58

Aamish Baloch

1,19812 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fiacre Over a year ago

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 Over a year ago

"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

MikeyE Over a year ago

@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!

mcastle · Accepted Answer · 2020-08-11 09:32:59Z

5

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

answered Aug 11, 2020 at 9:32

mcastle

3,0033 gold badges28 silver badges44 bronze badges

Comments

morkun · Accepted Answer · 2020-07-30 15:37:23Z

4

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])

answered Jul 30, 2020 at 15:37

morkun

793 bronze badges

Comments

quiet_penguin · Accepted Answer · 2021-02-16 09:58:49Z

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, 2021 at 9:58

answered Aug 6, 2017 at 5:27

quiet_penguin

8507 silver badges20 bronze badges

1 Comment

quiet_penguin Over a year ago

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.

Priom · Accepted Answer · 2021-08-17 20:50:29Z

0

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.

answered Aug 17, 2021 at 20:50

Priom

537 bronze badges

Collectives™ on Stack Overflow

Django Test -- Unable to drop and recreate test database

5 Answers 5

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related