[Resolved] disconnect users postgresql

julioc

Hi all

Is there a way to disconnect users from a D.B in postgresql rather than kill -9 the pid user?

thanks in advance

Sxooter

If you just want to disconnect that one user, that's how I do it. If you want to shut down / restart the server, look at the -m flag for pg_ctl:

Options for stop or restart:
-m SHUTDOWN-MODE may be 'smart', 'fast', or 'immediate'

julioc

Hi thanks for you reply.

The real problem is that I have 3 differents D.B, each D.B has its own users connected to.

When i try to do kill -9 to a specific user the system kills all of them,
I wrote a script to find out which users are connected to which D.B
this is the output

This is the username,the D.B and the PID

Let say I want to disconnect 'akarton' what I do is kill -9 26756

Then the system kills all of them

any idea???

Thanks

Sxooter

Have you tried plain kill?

kill 19283

kinda thing? That should leave the others running and kill only the one backend.

Kill -9 causes a panic that results in postgresql killing all the backends to ensure the buffer cache isn't corrupted.

julioc

I was trying to do a plain kill, it works only when you are logged as postgres user only.
The problem is when you log in as a normal user, the system complains about the kill command.

thanks

Sxooter

Yep, that's unix for ya. Since you aren't root or the postgres user, you can't kill processes started by postgres.

A quick and dirty hack is to make a copy of kill and put it into /usr/local/pgsql/bin (or something like that) and chmod it so it runs as the postgres user instead of you.:

(This assumes your pgsql bin directory is in /usr/local/pgsql/bin by the way)

su - (become root...)
cd /usr/local/pgsql/bin
cp /bin/kill .
chown postgres.postgres kill
chmod 4775 kill
exit
(as joeuser🙂
/usr/local/pgsql/bin/kill pid

should now work.

Sxooter

Please note that safety wise, what I posted is NOT optimal at all. What you should actually do it put it in a directory ONLY you can access and set the chmod 4755 there. That way joe random user can't go killing your postgresql database server.

julioc

Hey Sexooter...... thanks a lot you rock
it is working now.........
🙂
thanks again....