Postgres ERROR: database “dev” is being accessed by other users

If you are having issues dropping a postgres database the issue is more than likely that a user is accessing the database you are attempting to drop. There isn’t technically a way to force drop the postgres database however there are steps you can take to prevent others from accessing the database and if needed kick the users whom are accessing the database.

If you receive an error similar to the below the issue with your attempt to drop the database is the fact that either a user is accessing the database or an application of some sort has an open connection to the database. In the second scenario PostgreSQL considers the application the same as a user. In the code below the name of the example database is dev.

Please note that dropping the database will destroy all data located within the database. I strongly suggest always backing up your data before dropping the database. You can backup the data with pg_dump and regardless of if you think you will need the data or not it is best to keep a copy just in case.

postgres=# drop database dev;
ERROR:  database "dev"is being accessed by other users

So again the issue is there is some form of active connection open to the database and as a safety measure PostgreSQL will not allow the database to be dropped. The first thing to do is get a list of any active connections to the database you are attempting to drop. The command below will provide these results to you. First connect to the database and shown and issue the command below. Also shown below is the command without the WHERE statement which lists all active connections for all of the databases located on this PostgreSQL server.

[root@server pgsql]# psql -U postgres
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
 
Type: copyright fordistribution terms
hforhelp with SQL commands
?forhelp with psql commands
g or terminate with semicolon to execute query
q to quit
 
postgres=#
postgres=# select * from pg_stat_activity where datname='dev';
datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port
-------+---------+---------+----------+----------+---------------+---------+------------+-------------------------------+-------------------------------+-------------+-------------
28091 | dev | 8481 | 10 | postgres | | f | | 2008-11-12 09:12:50.277096+00 | 2008-11-12 09:11:10.328231+00 | 127.0.0.1 | 43152
28091 | dev | 8621 | 10 | postgres | | f | | 2008-11-12 09:13:01.097768+00 | 2008-11-12 09:12:57.347203+00 | 127.0.0.1 | 43174
28091 | dev | 8557 | 10 | postgres | | f | | 2008-11-12 09:12:12.43479+00 | 2008-11-12 09:12:11.71946+00 | 127.0.0.1 | 43163
(3 rows)
 
postgres=#
postgres=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port
-------+----------+---------+----------+----------+---------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
28091 | dev | 8481 | 10 | postgres | | f | | 2008-11-12 09:12:01.288466+00 | 2008-11-12 09:11:10.328231+00 | 127.0.0.1 | 43152
11511 | postgres | 7101 | 10 | postgres | select* from pg_stat_activity; | f | 2008-11-12 09:12:32.092138+00 | 2008-11-12 09:12:32.092138+00 | 2008-11-12 08:59:39.016751+00 | | -1
28091 | dev | 8583 | 10 | postgres | | f | | 2008-11-12 09:12:31.391735+00 | 2008-11-12 09:12:28.394225+00 | 127.0.0.1 | 43169
28091 | dev | 8557 | 10 | postgres | | f | | 2008-11-12 09:12:12.43479+00 | 2008-11-12 09:12:11.71946+00 | 127.0.0.1 | 43163
(4 rows)
 
postgres=#

As you can see above the second field in the pg_stat_activity table is the database name field. The primary clue to use will be the procpid field which stands for process PID (Process ID). Also note the client_addr field because that will be another clue to the location of the user accessing the database. If you see 127.0.0.1 as the client address then its either an application accessing the database or someone is connected to the local server and accessing the database.

Initially you will want to attempt to let users know to not access Postgres on this server or block access. If you think that people are accessing the database through an application you could maybe shutdown the web server in an attempt to block the access to the application. So regardless the initial goal will be to be as sane as possible about blocking access or letting other users know that they need to stop accessing the database at this time.

If you are unable locate the users or if users have not stopped accessing the database and you are not worried about corrupting this data since you are dropping it anyway then you can kill the PID associated with the connection. The command to kill a process ID is “kill -9
” where PID is the number located in the procpid field of the pg_stat_activity table. Once you run the select query and there are no more active connections you should be able to drop the database without issue as shown below.

[root@server pgsql]# psql -U postgres
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
 
Type: copyright fordistribution terms
hforhelp with SQL commands
?forhelp with psql commands
g or terminate with semicolon to execute query
q to quit
 
postgres=#
postgres=# select * from pg_stat_activity where datname='dev';
datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port
-------+---------+---------+----------+---------+---------------+---------+------------+-------------+---------------+-------------+-------------
(0 rows)
 
postgres=#
postgres=# drop database dev;
DROP DATABASE
postgres=#

Now the database and all of the data located in that database has been deleted you can recreate the database or just be done with that database. The PostgreSQL database can be recreated using the below syntax.

postgres=# create database dev;
CREATE DATABASE
postgres=#

Now you have a new empty database called dev. At this point you can start manually adding tables or use the psql command to import database from a data dump or whatever else you choose. 

Reference: http://www.question-defense.com/2008/11/12/postgres-error-database-dev-is-being-accessed-by-other-users

<think>嗯,用户遇到了PostgreSQL数据库被其他用户访问导致无法操作的问题,具体错误是“database zzpush is being accessed by other users”。我需要帮他们解决这个问题。首先,我得回忆一下PostgreSQL中如何处理这类问题。 首先,当数据库被其他用户或进程占用时,常见的解决方法包括终止这些连接。用户可能正在运行一些查询或者有应用程序保持连接没有关闭。这时候,使用pg_terminate_backend函数来终止这些会话应该可行。 不过,用户可能需要具体的步骤。比如,如何查询当前的活动连接,如何识别哪些进程在占用目标数据库。记得在终止连接前最好先检查,避免误操作。可能需要使用SQL命令,比如SELECT * FROM pg_stat_activity WHERE datname = 'zzpush'; 这样可以列出所有相关的活动连接。 然后,终止这些连接可能需要管理员权限。用户可能需要以超级用户身份执行这些操作,比如使用postgres用户或者有权限的用户登录。另外,在终止连接后,用户应该能够执行他们的操作,比如删除或修改数据库。 还有,用户可能是在生产环境中操作,这时候直接终止连接可能会有风险,比如导致正在运行的任务失败。可能需要提醒用户谨慎操作,并建议在维护窗口进行,或者确保没有重要进程在运行。 另外,用户提供的引用中提到了他们运行PostgreSQL 11的Docker容器,挂载了存储目录,并设置了用户、密码和数据库都为sonar。这可能意味着他们是在部署SonarQube时遇到的问题,可能涉及到SonarQube本身的连接没有正确关闭。可能需要检查SonarQube服务是否正常停止,或者是否有其他容器或应用在使用这个数据库。 此外,用户提到使用Docker,可能需要进入容器内部执行psql命令,或者从宿主机使用psql客户端连接到容器内的PostgreSQL实例。可能需要指导用户如何进入容器,或者如何安装psql客户端。 还有一个可能的解决方法是设置连接限制,但用户的问题已经发生,所以终止现有连接可能是更直接的解决方案。不过,预防措施方面,可以建议在以后的操作中,确保应用程序正确关闭连接,或者配置连接池以避免过多的闲置连接。 总结下来,步骤应该是: 1. 查询当前连接到目标数据库的活动会话。 2. 终止这些会话。 3. 执行所需的数据库操作。 同时,提醒用户权限问题和生产环境下的注意事项。 另外,用户提供的引用中提到了SonarQube和Jenkins的集成,可能需要检查是否有CI/CD流程中的任务持有了数据库连接,比如Jenkins的构建步骤是否没有正确释放连接。不过这可能超出了当前问题的范围,但可以作为相关问题提到。 可能还需要考虑数据库是否处于恢复模式或者有未完成的事务,但错误信息明确提到是用户访问导致的问题,所以更可能的是活动连接的问题。 最后,确保用户在执行命令时使用正确的数据库名称,替换示例中的zzpush为实际的数据库名,避免拼写错误。</think>### 分步解决方案 1. **查询当前活动连接** 使用以下SQL命令查看占用数据库`zzpush`的所有会话: ```sql SELECT pid, usename, application_name, client_addr, query FROM pg_stat_activity WHERE datname = 'zzpush'; ``` 这会列出所有连接该数据库的进程ID(PID)、用户名、来源IP和正在执行的查询[^1]。 2. **终止占用连接** 若确认需要强制断开,使用以下命令终止指定PID的会话: ```sql SELECT pg_terminate_backend(pid); -- 替换pid为实际进程号 ``` 或批量终止所有连接: ```sql SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'zzpush'; ``` 3. **修改数据库配置(可选)** 若需彻底避免类似问题,可在`postgresql.conf`中添加: ```properties idle_in_transaction_session_timeout = 60000 -- 单位毫秒(自动终止空闲事务) ``` 4. **重启数据库(谨慎操作)** 对于Docker容器部署的PostgreSQL: ```bash docker restart postgresql ``` ### 操作示例(通过Docker容器) ```bash # 进入容器执行SQL docker exec -it postgresql psql -U sonar -d sonar # 在psql命令行中执行终止操作 sonar=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='zzpush'; ``` ### 注意事项 - 终止连接可能导致业务中断,建议在维护窗口操作 - 确保操作账户有`SUPERUSER`权限(如默认的`postgres`用户)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值