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.

Answer 4

I restarted my system and the error was still showing. However, I followed the steps below to sort it out.

1. Stop all processes using the postgres port 5432 by doing this in command prompt (Admin): Type netstat -ano in command prompt. Find the pid with Local Address of 0.0.0.0:5432. Then use taskkill /pid {pid} /f to kill the task.

2. Start the postgres service in windows services.

Answer 5

I also got this error while trying to reset the database while I had the default Ruby on Rails server WEBrick running:

$ bin/rake db:reset
PG::Error: ERROR:  database "dev" is being accessed by other users
DETAIL:  There is 1 other session using the database.
: DROP DATABASE IF EXISTS "dev"

The other user here was the running Rails app. After shutting down the server with CTRL + c, I was able to re-run the database reset command without any problems.

It makes sense too. You can't drop the database if someone else is currently connected to it, as Mike Sherrill also points out.

Answer 6

Solution for me was to delete old server and create a new one from Postgresql administration web interface. Could now create new database without this error.

Answer 7

I was also stuck setting up postgres on ruby on rails project, ensure that you have installed pg locally and created a user with its password then on your database.yml should have:- host: localhost, password: (set password) then run:

$ rails db:create

$ rails db:migrate