Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

CREATE DATABASE fails because template1 is being accessed by other users

ErrorERROR: source database "template1" is being accessed by other users

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, psql
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';"
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2A pooler or tool reconnects to template1 right after you end its sessionSessions keep reconnecting to template1, so you cannot clear itFix

What does the error look like?

  • ERROR: source database "template1" is being accessed by other users
  • OperationalError: 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

Did this fix it?

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 appdb

3Verify

The new database is listed and accepts a connection.

psql -U postgres -d appdb -c 'select 1'

Confirmed by postgresql.org

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.