Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Whats Postgres doing?
New on LowEndTalk? Please Register and read our Community Rules.

Whats Postgres doing?

DeorDeor Member
edited November 2011 in General

I'm trying to get to grips with a server that I've inherited. Its rather old and out of date, but its serving web sites for about 70 clients who i dont want to pi$$ off. Server is running Debian 5 and has aprox 80 outstanding updates!

To my knowledge, nothing is using postgres but i want to be sure before i remove it. These are the processes i see running:

postgres  4925  0.0  0.1  42092  1392 ?        S    Sep16   0:17 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf
postgres  4928  0.0  0.0  42092   448 ?        Ss   Sep16   3:42 postgres: writer process
postgres  4929  0.0  0.0  42092   384 ?        Ss   Sep16   2:13 postgres: wal writer process
postgres  4930  0.0  0.0  42092   660 ?        Ss   Sep16   0:53 postgres: autovacuum launcher process
postgres  4931  0.0  0.0  13412   476 ?        Ss   Sep16   0:30 postgres: stats collector process

I also looked up a couple of commands and tried them, with these results:

server1:~# psql
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
server1:~# postgres
-bash: postgres: command not found
server1:~#

Does this all imply there is only a client of some sort running and no server? Or am i a mile off?

Comments

  • Why remove it? Postgres owns.

    Thanked by 1Xeoncross

    Postgres

  • XeoncrossXeoncross Member
    edited November 2011

    PostgreSQL uses a lot less memory than MySQL InnoDB. However, if all your clients are using MySQL then that doesn't mean a thing unless you want to migrate each one over. :P

    /etc/init.d/postgresql status
    

    Will confirm that PostgreSQL is up and running. As usual you can stop/start/restart it.

    You can learn more about administrating PostgreSQL here even if some of that data is starting to get old with the new psql stuff.

    I would GREP the system to see if any of the sites are making calls to psql using PHP (PDO or psql functions) or whatever languages the sites are built in. I would also enter $ psql and see what databases exist $ \l.

    Thanked by 1Deor
  • You don't have a control panel that's managing the sites that uses Postgres, do you?

  • simple ones that you can get free is just install virtualmin gpl to check all these instances easily via web gui? - just a suggestion

  • cPanel does :-) But probably not what you are looking for!

  • Thanks for the input guys. Yeah everyone uses MySQL and I really don't fancy trying to migrate! The only control panel installed is webmin, but oddly it doesn't see postgres in a working state when i try and use the admin module.

    @Xeoncross Ive run the commands you mentioned, here's the output:

    server1:~# /etc/init.d/postgresql status
    -bash: /etc/init.d/postgresql: No such file or directory
    server1:~# psql
    psql: could not connect to server: No such file or directory
            Is the server running locally and accepting
            connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
    server1:~#
    

    This implies that postgres isnt installed correctly? Or partly?

    i checked what was installed with dpkg --get-selections and got this

    postgresql                                      install
    postgresql-7.4                                  deinstall
    postgresql-8.3                                  install
    postgresql-client                               install
    postgresql-client-7.4                           install
    postgresql-client-8.3                           install
    postgresql-client-common                        install
    postgresql-common                               install
    
  • What do you get with "top" command? Is there any postgres process? Also since you have webmin installed check under system>boot and shutdown , and check if postgres is running at startup, if yes then you can mark it and press "disable now and on boot". But as you have 70 clients, I just think there's a big possibility that someone is using it.

    Thanked by 1Deor

    ☻☻ VPS ☺ as of now:- 384-256-128-512x2 ☺☺

  • There are running processes, see my original post. Im still trying to work out if they are client or server processes. Im very confused as none of the server related commands stated above seem to work. Given the output ive shown, can anyone say whats running and whats not?

    There are startup processes listed for PostgreSQL 7.4 RDBMS server and PostgreSQL 8.3 RDBMS server, which doesnt make sense to me as it says 7.4 is deinstalled in the package selection.

    This server is a real mess :/

  • gwautgwaut Member
    edited November 2011

    Can you try:

    server1:~# su - postgres

    server1:~$ pg_ctl status

    If that works:

    server1:~$ psql

    and then:

    select * from pg_stat_activity;

    to list the open connections.

    Thanked by 1Deor
  • @gwaut Ok just tried that and here is the output:

    server1:~# su - postgres
    [email protected]:~$ pg_ctl status
    -su: pg_ctl: command not found
    [email protected]:~$ psql
    psql: could not connect to server: No such file or directory
            Is the server running locally and accepting
            connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
    [email protected]:~$
    
  • gwautgwaut Member
    edited November 2011

    and if you first export the PATH as postgres user?

    export PATH=/usr/lib/postgresql/8.3/bin:$PATH

  • Here you go:

    [email protected]:~$ export PATH=/usr/lib/postgresql/8.3/bin:$PATH
    [email protected]:~$ psql
    psql: could not connect to server: No such file or directory
            Is the server running locally and accepting
            connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
    [email protected]:~$ pg_ctl status
    pg_ctl: no database directory specified and environment variable PGDATA unset
    Try "pg_ctl --help" for more information.
    

    Thanks for the help :)

  • ok, you have to set the PGDATA variable:



    export PGDATA=/var/lib/postgresql/8.3/main

    which you find in your first post (-D argument)

  • And now we have:

    [email protected]:~$ export PGDATA=/var/lib/postgresql/8.3/main
    [email protected]:~$ pg_ctl status
    pg_ctl: server is running (PID: 4925)
    /usr/lib/postgresql/8.3/bin/postgres "-D" "/var/lib/postgresql/8.3/main" "-c" "config_file=/etc/postgresql/8.3/main/postgresql.conf"
    [email protected]:~$ psql
    psql: could not connect to server: No such file or directory
            Is the server running locally and accepting
            connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
    [email protected]:~$
    
  • gwautgwaut Member
    edited November 2011

    I guess your postgres is not listening on its standard port, but I'm not sure.
    You can determine the portnumber by 'cat /etc/postgresql/8.3/main/postgresql.conf | grep port' and then do an export of PGPORT.

  • Ahha! Looks like its on port 5433 for some reason.

    postgres=# select * from pg_stat_activity;
     datid | datname  | procpid | usesysid | usename  |          current_query          | waiting |          xact_start           |          query_start          |         backend_start         | client_addr | client_port
    -------+----------+---------+----------+----------+---------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
     11511 | postgres |     305 |       10 | postgres | select * from pg_stat_activity; | f       | 2011-11-03 10:25:51.816945+00 | 2011-11-03 10:25:51.816945+00 | 2011-11-03 10:25:14.260894+00 |             |          -1
    (1 row)
    

    Looks like there's nothing going on after all that!

  • At the moment nothing is using your postgres server. You can list the existing databases by typing '\l' inside the psql process. This might give you an idea if there are some apps which may connect to the databases.

    Thanked by 1Deor
  • postgres=# \l
            List of databases
       Name    |  Owner   | Encoding
    -----------+----------+----------
     postgres  | postgres | UTF8
     template0 | postgres | UTF8
     template1 | postgres | UTF8
    (3 rows)
    

    Well looks like its pretty empty. I've also fixed the webmin module with the info you've helped me extract and there are no tables in these databases. I'm pretty happy I can turn it off now.

    Thanks for your help everyone, especially you gwaut for pulling the last bits together for me today.

Sign In or Register to comment.