up vote 0 down vote favorite

I logged in to source database template1 and now I can't create database. When I try to create database, I get this error:

OperationalError: source database "template1" is being accessed by other users
DETAIL:  There are 5 other session(s) using the database.

Every time I login to template1, I use 'exit' command to logout, but as you can see it does not logout and number of sessions increases everytime I login. Is there a way to force disconnect every connection to template1 that logged in now?

share|improve this question
There is no exit command in psql. The command to leave psql is \q (and you shouldn't log in/use the template database in the first place) – a_horse_with_no_name Jan 17 at 8:19
I had to log in, because I needed to drop schema public and recreate it to fix one bug. – oerp Jan 17 at 8:23
Also thanks for reply. Now it does not increase in logged in users count, but it stays as 5 users connected. How to force quit these connections? – oerp Jan 17 at 8:25
feedback

2 Answers

up vote 1 down vote accepted

Database template1 exists only to provide barebone structure to create another empty database. You should never logon to template1, otherwise you will have problems.

Probably easiest solution for you is to restart PostgreSQL server process, and logon again. Database that should always exist and is safe to logon is postgres.

If restarting is not an option, you can use another emergency template database: template0.

By default, this statement:

CREATE DATABASE dbname;

is equivalent to:

CREATE DATABASE dbname TEMPLATE template1;

If template1 is not available or corrupted, you can use template0 as last resort:

CREATE DATABASE dbname TEMPLATE template0;

You can read more about template databases here.

share|improve this answer
I didn't want to log in, but our ERP support told us to do it, to fix one bug..:) – oerp Jan 17 at 8:31
From doc link: CREATE DATABASE will fail if any other connection to template1 exists. You can use last resort template0: CREATE DATABASE dbname TEMPLATE template0 – mvp Jan 17 at 8:34
In older Postgres version there was one reason to log onto the template database: create the plpgsql language in there so that every new database would automatically get it. – a_horse_with_no_name Jan 17 at 8:35
So there is no way to clear connections from template1? (I don't have permissions to restart postgresql server, so I asked for our server providers to do that, but its in pending state now) – oerp Jan 17 at 8:39
You should be able to clear them by disconnecting. But, note that template databases are NOT supposed to have any connections, almost ever. If you use template0, you don't really need template1. I think this is probably the reason why there are 2 template databases - for situations like yours. – mvp Jan 17 at 8:42
show 6 more comments
feedback
up vote 1 down vote

This helped me solve my problem:

SELECT * pg_terminate_backend(procpid) 
FROM pg_stat_activity 
WHERE usename='username';

I terminated all active connections to template1 and could create database normally

share|improve this answer
I was just about to suggest this. :) – Scott Marlowe Jan 18 at 19:35
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.