I cannot duplicate or drop database from the Odoo interface because there are sessions using the database. How to fix it permanently?

Question

Every time I try to duplicate or drop databases from the interface I always get this error, even if all the users are logged out:

ERROR:  source database "database_name" is being accessed by other users
DETAIL:  There are 5 other sessions using the database.

Then if want to duplicate or drop a database I have to run this query to terminate all the connections before:

SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'database_name' AND pid <> pg_backend_pid();

What I would like to do is to override the following controllers in order to run the query always before the execution of the functions duplicate_database and drop:

@http.route('/web/database/duplicate', type='json', auth="none")
def duplicate(self, fields):
    params = dict(map(operator.itemgetter('name', 'value'), fields))
    duplicate_attrs = (
        params['super_admin_pwd'],
        params['db_original_name'],
        params['db_name'],
    )
    return request.session.proxy("db").duplicate_database(*duplicate_attrs)

@http.route('/web/database/drop', type='json', auth="none")
def drop(self, fields):
    password, db = operator.itemgetter(
        'drop_pwd', 'drop_db')(
            dict(map(operator.itemgetter('name', 'value'), fields)))
    try:
        if request.session.proxy("db").drop(password, db):
            return True
        else:
            return False
    except openerp.exceptions.AccessDenied:
        return {'error': 'AccessDenied', 'title': 'Drop Database'}
    except Exception:
        return {'error': _('Could not drop database !'), 'title': _('Drop Database')}

And I found this note in the Documentation of Odoo :

[...] This operation requires that there be no connection to the database being duplicated, but Odoo doesn't currently break existing/outstanding connections, so restarting the server is the simplest way to ensure everything is in the right state.

Is safe to override these controllers? What is the better way to do this? I would like to do this using the interface because if I do it manually or if I have to stop the server every time is needed is very annoying.

Answer 1

You can't drop postgres database while clients are connected to it. Quite robust way to work around it, is

Make sure noone can connect to this database

update pg_database set datallowconn = 'false' where datname = 'mydb';

Force disconnection of all clients connected to this database.

For postgres < 9.2:

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'mydb';

for postgres versions >= 9.2 change procpid to pid:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';

Drop it

DROP DATABASE mydb;

Steps 1 and 2 require superuser privileges, step 3 requires database owner privilege.

You can't do it all using only dropdb utility - which is a simple wrapper around DROP DATABASE server query.