postgresql - can't create database - OperationalError: source database "template1" is being accessed by other users
PostgresqlPostgresql 8.4Postgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
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.
Solution 2 - Postgresql
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
Solution 3 - Postgresql
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 >
Solution 4 - Postgresql
To solve this, I have to disconnect the database connection from the pgAdmin III.
Solution 5 - Postgresql
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.
Solution 6 - Postgresql
- enter pgAdmin
- right-click to server
- disconnect server
- again connect server
- do what you want
Solution 7 - Postgresql
If you are using the PgAdminn, you can manually set the definitions provided in the image.
Solution 8 - Postgresql
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.
Solution 9 - Postgresql
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.
Solution 10 - Postgresql
On Windows I had to reinstall PostgreSQL, restart did not help.
Solution 11 - Postgresql
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
Solution 12 - Postgresql
In windows. shutdown the GUI. do it from psql command prompt.
Solution 13 - Postgresql
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 !
Solution 14 - Postgresql
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.
Solution 15 - Postgresql
If you are using Docker, have to set host to '0.0.0.0' than 'localhost' in your sequelize config.
Solution 16 - Postgresql
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;
Solution 17 - Postgresql
You can try to restart the postgresql service that is running in the background.
Solution 18 - Postgresql
I have solved problem like this via reconnecting to the server (pgAdmin -> disconnect -> connect)