psycopg2.errors.ObjectInUse: database is being accessed by other users

Question

I am new to Database operations and i am writing a python script to take backup and restore the backup of an db in postgres

Below is my python script

import subprocess
import psycopg2



user = "postgres"
password = "postgres"
host = "localhost"
port = "5432"
database_name = "test"
dest_file = "/home/admin/temp/db.sql"

#Taking db backup
process = subprocess.Popen(['pg_dump','--dbname=postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port, database_name),'-Fc','-f', dest_file],stdout=subprocess.PIPE)
output = process.communicate()[0]
if process.returncode != 0:
    print('Command failed. Return code : {}'.format(process.returncode))
    exit(1)
print(str(process))


#Doing db changes




#Restoring db in a chance of error
conn = psycopg2.connect(user = user,password = password,host = host,port = port)
conn.autocommit = True
with conn.cursor() as cursor:
    cursor.execute('DROP DATABASE test;')
    cursor.execute('CREATE DATABASE test;')

process = subprocess.Popen(['pg_restore', '--no-owner','--dbname=postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port, database_name), dest_file],stdout=subprocess.PIPE)

output = process.communicate()[0]
if process.returncode != 0:
    print('Command failed. Return code : {}'.format(process.returncode))
    exit(1)
print(output)

While executing code i am getting the follwing error..

psycopg2.errors.ObjectInUse: database "test"  is being accessed by other users

Not sure whats wrong.. Please help

You need to close the connection - conn.close() already answered [here](https://stackoverflow.com/questions/44802617/database-is-being-accessed-by-other-users-error-when-using-threadpoolexecutor) — Pradip, Feb 22 '22 at 09:55

score 1 · Answer 1 · answered Jun 16 '22 at 14:33

You have connections on your test database which must be closed before proceeding which can be achieved using the following:

-- Stop further connections
alter database test allow_connections = off;

-- Drop remaining (except this connection)
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'test' 
  AND pid <> pg_backend_pid();

The database can now be dropped - either via your code or SQL i.e.

drop database test;

psycopg2.errors.ObjectInUse: database is being accessed by other users

1 Answers1