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?

Best guess is killed backends you should not have. I'm guessing you will need to restart server. — Adrian Klaver
– Adrian Klaver, Commented Jul 16, 2021 at 19:08

jjanes · Accepted Answer · 2021-07-16 19:17:23Z

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.

answered Jul 16, 2021 at 19:17

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related