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

    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'

      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

      akarton|qa|26756
      postgres|test|25317
      postgres|dev|26758
      postgres|dev|26582
      postgres|test|26849

      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

        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.

          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

            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.

              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.

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

                  Write a Reply...