PGError: ERROR: source database "template1" is being accessed by other users

Question

I'm having problems getting testing to work with Postgresql and Rails 3.

Both development and production databases I can get to work fine, however the test database throws the following errors when I run rake or db:test:prepare, etc.

PGError: ERROR: source database "template1" is being accessed by other users

Update

Googling around, it seems that one should use template0 instead of template1 when using createdb to create a new database in Postgres. In typical “So I’ll remove the cause. But not the symptom” fashion, I found vendor/rails/railities/lib/task/databases.rake and changed line 109 to read:

createdb #{enc_option} \
-U "#{abcs["test"]["username"]}" \
-T template0 #{abcs["test"]["database"]}

But I don't really wanna do that, as I'm using Rails as a GEM, any one know of another work around or fix?

database.yml:

development:
  adapter: postgresql
  encoding: unicode
  database: test1234_development
  pool: 5
  username: holden
  password: postgres

test:
  adapter: postgresql
  encoding: unicode
  database: test1234_test
  pool: 5
  username: holden
  password: postgres

Full error:

NOTICE: database "test1234_test" does not exist, skipping
PGError: ERROR: source database "template1" is being accessed by other users
DETAIL: There are 1 other session(s) using the database.
: CREATE DATABASE "test1234_test" ENCODING = 'unicode'

Answer 1

Short story: CREATE DATABASE works by copying an existing database. PostgreSQL won't let you copy a database if another session is connected to it. If template1 is being accessed by other users, CREATE DATABASE will fail.

The question you need to answer: Why are other sessions connected to template1?

The difference between template0 and template1

At the point you initialize a database cluster, template0 and template1 are the same. Any location-specific stuff you want to make available to every database you create by using CREATE DATABASE should go into template1. So, for example, if you add the procedural langauge PL/python to template1, every database you create later will include PL/python.

The database template0 is intended to be a "virgin" template. It should contain only standard database objects--the ones created by initializing the cluster. As a "virgin" template, it should never be changed. Never.

If you need to specify encoding and locale settings (collation), then you can do that by copying template0. You can't do that by copying template1.

Answer 2

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.

Answer 3

Just restart the service of database.