CREATE DATABASE fails because template1 is being accessed by other users
ERROR: source database "template1" is being accessed by other usersShort answer
CREATE DATABASE copies a template database, and PostgreSQL requires that no other session is connected to it during the copy. Something is connected to template1. Find the session in pg_stat_activity and end it, or create the database from template0 instead, which nobody normally connects to.
Fix it fast
1Find out which cause you have
List every session connected to template1 together with who owns it and what it is doing.
psql -U postgres -d postgres -c "SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE datname = 'template1';"2Match what you see, then jump to its fix
- 1
The query returns rows other than your own session, for example a GUI tool or a script that connected to template1 by defaultA client or tool is connected to template1Fix - 2
A pooler or tool reconnects to template1 right after you end its sessionSessions keep reconnecting to template1, so you cannot clear itFix
- 1
What does the error look like?
ERROR: source database "template1" is being accessed by other usersOperationalError: source database "template1" is being accessed by other users
Cause 1 of 2
A client or tool is connected to template1
1Check
Each row is a backend connected to template1. Ordinary users see full details only for their own sessions; connect as a superuser to see all of them.
psql -U postgres -d postgres -c "SELECT pid, usename, application_name FROM pg_stat_activity WHERE datname = 'template1';"2Fix
Close the tool, or end the session. pg_terminate_backend ends a session by process id; a superuser can end any, and others only their own roles' sessions. Then run CREATE DATABASE again.
SELECT pg_terminate_backend(12345); -- use the pid from the first step
CREATE DATABASE appdb;3Verify
The database exists.
psql -U postgres -d postgres -c '\l appdb'Confirmed by postgresql.org
Cause 2 of 2
Sessions keep reconnecting to template1, so you cannot clear it
1Check
While the copy runs, new connections to the template are locked out, but CREATE DATABASE fails at its start if any other connection exists. template0 is normally marked datallowconn = false, so no client is connected to it.
psql -U postgres -d postgres -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'template1';"2Fix
Copy template0 instead of template1. It is the pristine template, and the documentation names it for a clean copy or when you need different encoding and locale settings. Objects you added to template1 will not be in the copy.
CREATE DATABASE appdb TEMPLATE template0;
-- or from the shell
-- createdb -T template0 appdb3Verify
The new database is listed and accepts a connection.
psql -U postgres -d appdb -c 'select 1'Confirmed by postgresql.org
How do I stop it coming back?
- Point tools and scripts at the postgres database or your own database, not at template1.
- Use TEMPLATE template0 in database-creation scripts that must always work, and when you need a different encoding or locale.
- Do not add objects to template1 unless you want every new database to receive them.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DROP DATABASE fails because other sessions are connected
ERROR: database "mydb" is being accessed by other users
PostgreSQL refuses DROP DATABASE while anyone else is connected to it.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix