Testing postgres database accessibility from djang

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.

Answer 1

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

Answer 2

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...