postgresql - can't create database - OperationalError: source database "template1" is being accessed by other users

Question

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?

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) — , Jan 17 '13 at 08:19
I had to log in, because I needed to drop schema public and recreate it to fix one bug. — Andrius, Jan 17 '13 at 08: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? — Andrius, Jan 17 '13 at 08:25

mvp · Answer 1 · 2013-01-17T08:38:08.147

49

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.

edited Jan 17 '13 at 08:38

answered Jan 17 '13 at 08:25

mvp

111,019
13
122
148

I didn't want to log in, but our ERP support told us to do it, to fix one bug..:) – Andrius Jan 17 '13 at 08:31
1

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 '13 at 08: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. – Jan 17 '13 at 08: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) – Andrius Jan 17 '13 at 08: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 '13 at 08:42
I create database using Openerp, so it creates database using template1. – Andrius Jan 17 '13 at 08:43
What if you create empty database manually (using `psql` or `PgAdmin`), and then handoff this to OpenERP? It should be able to detect that this database is empty, and create necessary tables by itself? – mvp Jan 17 '13 at 08:45
I'll try. Also I wanted to ask, is using CTRL+C do not logout from template1? Because in terminal it show as I logged out, but in reality it stayed logged in and messed it like it is now. – Andrius Jan 17 '13 at 08:48
If you talking about `psql`, right way to quit is `\q` . BTW, if you really dropped public schema from `template1`, I think it is safe to say that `template1` is now **completely hosed**. You will likely need to recover `template1` by copying it from `template0`. – mvp Jan 17 '13 at 08:51
I tried to create db using template0, but I can't use that database in openerp. It says that database already exists – Andrius Jan 17 '13 at 08:54
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/22886/discussion-between-mvp-and-oerp) – mvp Jan 17 '13 at 08:56

Andrius · Accepted Answer · 2017-05-26T20:49:56.630

38

This helped me solve my problem:

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

--Use pid if PostgreSQL version 9.2 or above.

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

edited May 26 '17 at 20:49

answered Jan 17 '13 at 09:27

Andrius

19,658
37
143
243

3

9.3 tells me `column "procpid" does not exist` – dan-klasson Apr 20 '15 at 14:30
10

@dan-klasson use `pid` – Andrius Apr 21 '15 at 06:22
So @Soumya how did you terminated the active connections? – Ameen Rabea May 04 '16 at 13:03
@Andrius can u tell us what happened after this query. – Ketav Jun 13 '18 at 05:14
Amazing solution. – Mo1 Oct 22 '20 at 16:19
Very good suggestion, but note that, **IF** you have tools like pgadmin running and connected, they *can* sometimes ignore this type of fix. A server restart, which is "more powerful", did not fix it for me, you have to adjust/shut off pgadmin. – JL Peyret Oct 30 '20 at 21:04

score 17 · Answer 3 · answered May 14 '16 at 10:23

17

You can also try to terminate the current process thread by the Terminal

Search the Process :

sudo ps aux | grep template1

Kill the Process :

sudo kill -9 < your process id >

answered May 14 '16 at 10:23

DASADIYA CHAITANYA

2,850
3
18
41

Which one is the PID? – Mark A Jun 20 '19 at 00:22
2

@MarkA, 2nd column – Alen Peter Oct 23 '19 at 06:35

score 12 · Answer 4 · answered Jul 28 '15 at 16:25

12

To solve this, I have to disconnect the database connection from the pgAdmin III.

answered Jul 28 '15 at 16:25

roxdurazo

745
10
21

score 9 · Answer 5 · answered Mar 22 '18 at 00:44

I have a script that connects to a database and performs various operations on it, some requiring that no one else is logged in. I modified @Andrius's answer not to terminate my script's connection, but rather terminate everyone else's:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid();

From the docs: pg_backend_pid() is the Process ID of the server process attached to the current session.

score 5 · Answer 6 · answered Jun 16 '20 at 10:05

5

enter pgAdmin
right-click to server
disconnect server
again connect server
do what you want

answered Jun 16 '20 at 10:05

Aqil Aghamirzayev

109
2
3

score 5 · Answer 7 · answered Jan 05 '21 at 01:26

5

If you are using the PgAdminn, you can manually set the definitions provided in the image.

answered Jan 05 '21 at 01:26

Arefe

11,321
18
114
168

score 3 · Answer 8 · answered Aug 17 '22 at 22:53

You need to kill all the connections to the template database and leave other connections uninterrupted, so use the following query to kill all the connections to templateDb

    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 = 'templateDb'
    ;

Then use the usual query to copy the templateDb

CREATE DATABASE dbname TEMPLATE templateDb ;

Er.Ankit H Gandhi · Answer 9 · 2017-11-01T09:33:26.840

This problem occur when you had logged(psql template1 or psql template0) in template1 and template0 database and exit using below command.

Ctrl + z

Better way exist from db use below postgres command then problem will not create:

\q + enter

There are 2 solutions, If have problem.

Solution - 1

Restart posgres service like.

sudo service postgresql restart

Solution - 2

sudo ps aux | grep template1

Make sure don't delete this processes

postgres 8363 0.0 0.0 111760 7832 pts/11 T 09:49 0:00 /usr/lib/postgresql/9.5/bin/psql template1 ankit 18119 0.0 0.0 14224 976 pts/14 S+ 12:33 0:00 grep --color=auto template1

rest of process should be kill using below command.

sudo kill -9

Now try to create db again.

Hope this help you.

Ankit H Gandhi.

what worked for me on a mac: `brew services restart postgresql` — MevlütÖzdemir, Jan 04 '19 at 09:55

score 1 · Answer 10 · answered Oct 30 '20 at 15:56

If you use pgadmin4 or similar tools, makes sure it is either doing the create database itself or that it's shut off.

I had generated a create database with pgadmin4 then used its scripting functionality to export the script it used, which I then copied and altered to put it in my own automated scripts.

Problem was that the active webpage pgadmin somehow ended up on database template1.

Stopping and restarting the server itself didn't fix anything, pgadmin was smart enough to reconnect to the server as soon as it came back up.

score 0 · Answer 11 · answered Feb 17 '17 at 19:12

0

You can try to restart the postgresql service that is running in the background.

answered Feb 17 '17 at 19:12

ezzadeen

1,033
10
9

score 0 · Answer 12 · answered May 16 '19 at 14:17

0

On Windows I had to reinstall PostgreSQL, restart did not help.

answered May 16 '19 at 14:17

Tomas Kubes

23,880
18
111
148

score 0 · Answer 13 · answered Dec 29 '19 at 22:57

if you go to the side panel , browser, servers, database, then on the right is a summary of the database...becareful here, there is a press right and find the dropdown to delete the database, so if sure, get rid of existing database, and create a new one, so that you can claim ownership of the newly created one

score 0 · Answer 14 · answered Jan 20 '20 at 13:00

0

In windows. shutdown the GUI. do it from psql command prompt.

answered Jan 20 '20 at 13:00

Anupam Kumar

153
2
9

score 0 · Answer 15 · answered Feb 25 '20 at 23:54

I had a same problem but after googling, I understood I have a connection to (postgis_30_sample) database (the template that I want to copy from). This connection was created by GeoServer when I created some stores in Geo Server application.

So I Stopped the service and the issue was fixed !

score 0 · Answer 16 · answered Aug 09 '20 at 16:28

0

I have solved problem like this via reconnecting to the server (pgAdmin -> disconnect -> connect)

answered Aug 09 '20 at 16:28

Tim Uzlov

181
2
6

score 0 · Answer 17 · answered Oct 26 '20 at 13:09

I have the same problem with: ERROR: source database "template1" is being accessed by other users DETAIL: There is 1 other session using the database.

And i Solved the problem in postgreSQL 13 by clicking in the left column on PostgreSQL 13 where the tables showing some graphs appeared. I was interested in the sever activity table, here I found a line that contained the word template1 and I turned it off with a cross, then restarted the application here and everything works.

error: template1-solved

score 0 · Answer 18 · answered Nov 04 '20 at 00:45

0

If you are using Docker, have to set host to '0.0.0.0' than 'localhost' in your sequelize config.

answered Nov 04 '20 at 00:45

Eduardo Vieira

55
3

Where should this be set ? – Elias Ghali Jan 18 '21 at 09:32

score 0 · Answer 19 · answered Mar 14 '22 at 21:44

0

I came across almost same problem. Solution I found is--> "closed dbeaver GUI on my UBUNTU machine and used terminal to create database(lc_db1) using my other database(lc_db) as a TEMPLATE by using following command-->

    CREATE DATABASE lc_db1 TEMPLATE lc_db;

Output of this code is:-->

answered Mar 14 '22 at 21:44

Nikhil Patil

1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 14 '22 at 22:16

postgresql - can't create database - OperationalError: source database "template1" is being accessed by other users

19 Answers19

Linked