PGError: ERROR: source database "template1" is being accessed by other users
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
p开发者_运维问答assword: 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'
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.
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.
Just restart the service of database.
I restarted my system and the error was still showing. However, I followed the steps below to sort it out.
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 of0.0.0.0:5432
. Then usetaskkill /pid {pid} /f
to kill the task.Start the postgres service in windows services.
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.
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.
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
精彩评论