How alter database name

Question

I'm trying to alter database name on phppgadmin but i'm always getting this error:

ERROR:  database "X" is being accessed by other users
DETAIL:  There are 19 other sessions using the database.

Nobody is accessed except me so i don't understand this error.

How can i change the database name, or force it even when there are 19 other sessions using the database ?

Thank you

I am inclined to believe Postgres ("19 other sessions") more then your claim that "nobody else" is using that database. Probably all those 19 sessions all come from your computer (e.g from your client tool). Run `select * from pg_stat_activity` to verify — , Jul 12 '17 at 12:26
Did you/your code close the connections properly? Otherwise, try to force the connections to close before renaming your database. — martennis, Jul 12 '17 at 12:52

techhunter · Answer 1 · 2017-07-12T12:52:50.183

There may some auto connectivity. you may first terminate all the connection to your DB using below command:

SELECT 
    pg_terminate_backend(pid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

Before executing this query, you have to REVOKE the CONNECT privileges to avoid new connections:

REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;

If you're using Postgres 8.4-9.1 use procpid instead of pid

SELECT 
    pg_terminate_backend(procpid) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    procpid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

After the above , try renaming your DB.

reference: Kill a postgresql session/connection

UPDATE

For future reference, you should be able to:

-- disconnect from the database to be renamed

\c postgres

-- force disconnect all other clients from the database to be renamed

SELECT pg_terminate_backend( pid )
FROM pg_stat_activity
WHERE pid <> pg_backend_pid( )
    AND datname = 'name of database';

-- rename the database (it should now have zero clients)

ALTER DATABASE "name of database" RENAME TO "new name of database";

Note that table pg_stat_activity column pid was named as procpid in versions prior to 9.2. So if your PostgreSQL version is lower than 9.2, use procpid instead of pid.

reference: PostgreSQL - Rename database

the 19 sections were killed but then i'm still getting ´DETAIL: There are 5 other sessions using the database.´ and even after running your command for a second time i'm always getting 5 other sessions using the database — qwerty123, Jul 12 '17 at 12:41

How alter database name

1 Answers1