How to login to postgresql db - After session kill (for copy database)

Question

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?

Jacaranda

Best guess is killed backends you should not have. I'm guessing you will need to restart server. – Rumpf

Answer 1

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.

Velar