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.

score 1 · Answer 1 · answered Oct 12 '15 at 12:30

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.

I already have that query in the question. What I want to do is add that query to the controller in order to run it always automatically when I drop or when I duplicate the database. But I don't know how I should do it. For example I don't know where is the method `drop(password, db):` declared — ChesuCR, Oct 12 '15 at 19:22

score -1 · Answer 2 · answered Sep 08 '15 at 11:31

-1

Go to postgresql database and right click on your database, u will get an option of dropping/deleting your database. At that time if u get this error, try stopping the odoo server from services list and try again.

answered Sep 08 '15 at 11:31

Kiran

1,481
6
36
66

I think you are not understanding what I'm trying to do. I'm trying to remove the database from the Odoo Database Manager Interface, breaking the connections right before of doing it, in order to avoid the ERROR `source database "database_name" is being accessed by other users`. And I don't want to stop the Odoo Server, and I don't want to stop the connections manually – ChesuCR Sep 08 '15 at 16:41

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

2 Answers2