I tried to copy a database within the same server using postgresql server
I tried the below query
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
And got the below error
ERROR: source database "originaldb" is being accessed by 1 other user
So, I executed the below command
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();
Now none of us are able to login/connect back to the database.
When I provide the below command
psql -h 192.xx.xx.x -p 9763 -d originaldb -U postgres
It prompts for a password and upon keying password, it doesn't return any response
May I understand why does this happen? How can I connect to the db back? How do I restart/make the system to let us login back?
Can someone help us with this?
It sounds like something is holding an access exclusive lock on a shared catalog, such as pg_database. If that is the case, no one will be able to log in until that lock gets released. I wouldn't think the session-killing code you ran would cause such a situation, though. Maybe it was just a coincidence.
If you can't find an active session, you can try using system tools to figure out what is going on, like ps -efl|fgrep postgre. Or you can just restart the whole database instance, using whatever method you would usually use to do that, like pg_ctl restart -D <data_directory> or sudo service postgresql restart or some GUI method if you are on an OS that does that.