Testing postgres database accessibility from django

Question

I am using the django ORM with a postgres database. A small group of users interact with it using import and export scripts. The database is only available on our intranet. If someone tries to use the database when postgres is unavailable the scripts hang. I would like to make the scripts test whether the database is available before attempting to process any data.

I can connect to the database using the shell, import a model, and attempt to make a query:

from myapp.models import mymodel
mymodel.objects.count()

this results in a long delay, but then django raises an OperationalError with an informative message ("could not connect to server: Network is unreachable...").

I thought to test database access by making a minimal query to the database, something like:

from django.db import connection
cursor = connection.cursor()
cursor.execute("select 1")

but this never progresses beyond the cursor = connection.cursor() line. No error message.

• Why does one of these queries raise an error, but not the other?
• What's the best way to test from a script whether the database is available?
• How can I ensure that an error will be raised if the connection doesn't succeed within reasonable (perhaps user specified) time?

This is not a web application, so a middleware solution à la How do I test a database connection in Django? isn't possible.

Edit

Following @benjaoming's suggestion, I've made a function to test the connection:

import socket

def test_connection():
    """Test whether the postgres database is available. Usage:

        if "--offline" in sys.argv:
            os.environ['DJANGO_SETTINGS_MODULE'] = 'myapp.settings.offline'
        else:
            os.environ['DJANGO_SETTINGS_MODULE'] = 'myapp.settings.standard'
            from myapp.functions.connection import test_connection
            test_connection()
    """
    try:
        s = socket.create_connection(("example.net", 5432), 5)
        s.close()
    except socket.timeout:
        msg = """Can't detect the postgres server. If you're outside the
        intranet, you might need to turn the VPN on."""
        raise socket.timeout(msg)

This seems to do the trick.

Aida

Have you tried figuring out why the database connection is dropped? – Consulate

It's not actually dropped: the database is only available on our intranet. If you're trying to use it offsite you have to remember to connect via a virtual private network first. I'd like to warn users about this, rather than just landing them with a frozen terminal. – Aida

Answer 1

You can use another method for finding out if the postgres server is reachable: For instance the socket module -- just do a socket.create_connection(("server", port)) and see what exceptions it raises...

Genesia

I've made a function to do this which I call after setting the database into online/offline mode -- see edit to question – Aida

Looks nice! You might want to add an except block for socket.error exceptions. – Genesia

Would lack of VPN cause socket.error? Because if it's not something that the users should do something about then I'd be happy for the regular exception to be raised. – Aida

Yes, I think if the network interface is down or the remote end does not accept connections (but exists), socket.error is raised. – Genesia

Answer 2

Using multiple database routing,

The trick is to check if the database is reachable at django init time, and then route all the ORM queries to your fallback db.

I one way of checking if the database exists or not is running an ORM query inside a try..except block and set a variable which is accessible in your routers.py

from django.db import connections
conn = connections['default']
try:
    c = conn.cursor() #this will take some time if error
except OperationalError:
    reachable = False
else:
    reachable = True

you could put this code in urls.py or the routers.py itself.

the custom router will check if the variable is set and route to your fallback db

class AppRouter(object):
    def db_for_read(self, model, **hints):
       if reachable :
           return 'actual_db'
       else:
           return 'fallback_db'

    # define db_for_write likewise

Jocelyn

from django.db.utils import OperationalError – Caracal

Answer 3

from django.db import connections
db_conn = connections['default']
try:
    c = db_conn.cursor()
except Exception as e:
    connected = False
else:
    connected = True

This can be used to check connections with databases.

Hinda