Thread: postgresql start/stop/status script

Hi,

I am working on making a postgresql/drbd/heartbeat high availability
cluster. I need a script for heartbeat to start, stop and query the
service. I wrote the following:

pgStart() {
        su - pg0 -c "cd data ; /mnt/data0/postgresql/bin/pg_ctl start -D
/mnt/data0/postgresql/data -w -o '-i -h 192.168.2.50'"
}

pgStop () {
        su - pg0 -c "cd data ; /mnt/data$user/postgresql/bin/pg_ctl stop
-D /mnt/data0/postgresql/data -m fast -w"
}

pgStatus () {
        if su - pg0 -c "cd data ; /mnt/data0/postgresql/bin/pg_ctl
status -D /mnt/data0/postgresql/data" | grep -q "postmaster is running"
        then
                echo running
        else
                echo stopped
        fi
}

This works fine. The only problem is that status - it seems to only
check for the existance of the PID file. If the file is there, it
assumes that postgresql is running. In the case of a failover, the PID
file will of course still be there, but it will be stale. The effect is
that heartbeat never starts postgresql because my pgStatus claims it is
already running, even though it is not.

Is there a better way to query the status of postgresql? I would expect
it to at least check that the process in the PID is actually running and
that it is a postgresql process.

I am also confused by the need to specify "-h 192.168.2.50" - that is
already in the postgres.conf file, but "pg_ctl start" ignores it.

Thanks,

Baldur

Baldur Norddahl wrote:
> Is there a better way to query the status of postgresql? I would
> expect it to at least check that the process in the PID is actually
> running and that it is a postgresql process.

Maybe try

test $(readlink /proc/$pid/exe) = /usr/bin/postgres

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

Baldur Norddahl wrote: 

Is there a better way to query the status of postgresql? I would
expect it to at least check that the process in the PID is actually
running and that it is a postgresql process.   

Maybe try

test $(readlink /proc/$pid/exe) = /usr/bin/postgres 

I have now changed my script to use this instead:

        if pgrep -U pg0 'postmaster' > /dev/null
        then
                echo running
        else
                echo stopped
        fi

I would still hold that "pg_ctl status" is broken though.

Baldur

Peter Eisentraut <peter_e@gmx.net> writes:
> Baldur Norddahl wrote:
>> Is there a better way to query the status of postgresql? I would
>> expect it to at least check that the process in the PID is actually
>> running and that it is a postgresql process.

> Maybe try
> test $(readlink /proc/$pid/exe) = /usr/bin/postgres

I think the question stands though: why doesn't pg_ctl check that the
PID in the lockfile corresponds to a live process?  A quick kill(pid,0)
would raise the robustness of the status check quite a lot, and now that
the code is in C it's a trivial addition.

Unless someone sees a reason not to do this, I will throw the change
into current and 8.0 branches.

            regards, tom lane