Django Tests: Cannot Drop Database

Sometimes when tests crash, if you try to re-run them, the code will fail because the old database cannot be dropped and the error message will be something like:

ERROR: database "my_db" is being accessed by other users
DETAIL: There is 1 other session using the database.

Or if you are using PyCharm the message will be:

Got an error recreating the test database: database "my_db" is being accessed by other users
DETAIL: There is 1 other session using the database.

To resolve this situation, see this blog post.

Also, you can run SQL from PGAdmin.

Or you can make a Django management command:

from django.core.management.base import BaseCommand
from django.db import connection
from django.conf import settings


class Command(BaseCommand):
    help = 'Run this after tests crash'

    def handle(self, *args, **options):
        cursor = connection.cursor()

        database_name = 'test_{}'.format(settings.DATABASES['default']['NAME'])

        # Clears connections to db so that it can be dropped
        # http://www.leeladharan.com/drop-a-postgresql-database-if-there-are-active-connections-to-it
        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])

 

 

Advertisements

Postgresql: pg_dump and pg_restore

I had a problem with a database and I wanted to copy a table from a backup and install it in the current version. There are some subtleties. Here are my notes.

pg_dump

Use the “-t <table name>” argument to specify the table to dump. More multiple tables, use it multiple times.

Use the “–format custom” to make the dump compatible with pg_restore. If you do not set the format, the output will be a text file of SQL commands (useful in some cases) and you will need to use psql to restore the table.

Use –oids to maintain id numbers. This is especially important if the id is a foreign key else where.

Here is the command I used:

pg_dump roihubdiazlaw2db -f django_migrations.dump -t django_migrations -U my_username --format custom --oids

pg_restore

The trick here is not NOT use the -t flag! If you use this flag, pg_restore will NOT restore ids as primary keys. Instead they will become ordinary integers. The info needed to restore the table is already in the dump file. Restore with:

pg_restore django_migrations.dump -d roihubdiazlaw2db -U my_username --format custom

 

PostgreSQL: password authentication failed for user

Everything was working fine. Then for no apparent reason PostgreSQL started failing with the error message:

FATAL:  password authentication failed for user

WTF???

.pgpass had my user name along with postgres. Running this worked:

psql -U postgres -h localhost -p 5432

This failed:

psql -U my_username -h localhost -p 5432

This allowed me to manually enter the password and by pass .pgpass, but it still failed:

psql -U my_username -h localhost -p 5432 -W

This allowed me to list the users:

psql -c '\du' -U postgres

And that is how I discovered that my password had expired. I did not even know it could expire. I fixed it in pgadmin.

It’s Back…

So I had things working. I was do a lot of work using pg_restore. All of a sudden, password auth failed again. The above procedures were no help. In pgadmin I re-entered the password. Now its working again.

Configuring Postgresql and Dropdb

Seems like an on-going nightmare. I am configuring a new Ubuntu 14.04 system and having troubles with getting passwords from .pgpass. Here are the fundamentals:

  • .pgpass should be in the users home dir with permissions 0600 (rw). Lesser permissions will cause postgresql to ignore this file
  • /etc/postgresql/9.3/main/pg_hba.conf is set to md5

Let the fun begin. I have a this line in my .pgpass file:

localhost:5432:my_db:my_user:my_password

When I run:

psql -U my_user -d my_db

I am able to launch psql. When I run the psql command “\conninfo”, I get:

You are connected to database "my_db" as user "my_user" via socket in "/var/run/postgresql" at port "5432"

But when I exit psql and run:

dropdb my_db -U my_user -w

I get:

dropdb: could not connect to database template1: fe_sendauth: no password supplied

WTF? Then I saw this statement on Stackoverflow:

recall that creatdb and dropdb aren’t really acting on “myprojectdb” despite the fact that you may be asking them to create and drop that db

Maybe my attempts at tightening security are the problem. When I changed .pgadmin to:

localhost:5432:*:my_user:my_password

Then dropdb worked.

PyCharm, Django Dev Server and “password authentication failed for user”

This stuff drives me crazy. I was cruising along, run the Django dev server from the command line. Everything was working well. Then I decided to do some debugging, so I configured the PyCharm Django dev server and got this error:

django.db.utils.OperationalError: FATAL:  password authentication failed

Huh? It was just working. Here’s what was happening. I should mention I was using PostgreSQL. In my local settings file I have:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': DB_NAME,
        'USER': 'roi_erp',
        'PASSWORD': os.environ.get('LOCAL_DB_PASSWORD', get_secrets('DB_PASSWORD')),
        'HOST': os.environ.get('POSTGRESQL_HOST', 'localhost'),
        'PORT': '',
    }
}

First I try to get the DB password from a local environmental variable. If that fails, I look in my project secrets file. My local .pgpass has the LOCAL_DB_PASSWORD, but not the one in secrets. When I ran from the command line, LOCAL_DB_PASSWORD was defined. When I ran from PyCharm, it wasn’t, hence the error message. To solve the problem, I set LOCAL_DB_PASSWORD in PyCharm.

Maybe I should just make the python settings code fail if LOCAL_DB_PASSWORD.

Connecting to Remote POSTGRESQL Using PgAdmin

Here is how I connect to the PostgreSQL database on a Digital Ocean droplet using pgAdmin III.

Screenshot from 2015-02-17 21:57:05

Screenshot from 2015-02-17 21:55:25

Troubleshooting

Error connecting to the server: fe_sendauth: no password supplied

I got this error after not using pgadmin remotely for a long time. Turns out it was a brain-fart. SSH allows you to connect to the server without a password. But you still need to enter a database password. I did not enter the database password. Maybe that’s why the error message said “no password supplied”. Duh!

Vagrant, PostgreSQL and pgAdmin

Here’s how to use pgAdmin to inspect a database on a Vagrant virtual machine. Vagrant already has SSH setup so the easiest and most secure way to connect to that database is using pgAdmin’s SSH Tunnel feature. To do this, click the connection icon (a plug) and you will see something like this:

Screenshot from 2015-01-20 17:42:48

For Name you can enter anything. Note: Host is localhost, not the vagrant IP address . Username is the database username. Password is the database password. DO NOT CLICK OK yet. Instead, click the SSH Tunnel tab. You will see something like this:

pgadmin2

Tunnel host is the IP address of the Vagrant VM. Username is the Linux username used when you SSH in to the virtual machine. The identity file is the SSH private key for this virtual machine. When you run “vagrant up”, it creates a .vagrant folder. The private key is in there in a folder like:

~/my_project/.vagrant/machines/default/virtualbox/private_key

Now click OK and you should be connected.

Addendum

I just started getting this error:

Error connecting to the server: server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.

Turns out the error was in the first GUI (above) for setting up the connection. For Host, I put the vagrant IP address. However, since I was using SSH Tunneling, the correct Host is localhost. Ugh…