Thread: postgresql start/stop/status script

postgresql start/stop/status script

From
Baldur Norddahl
Date:
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


Re: postgresql start/stop/status script

From
Peter Eisentraut
Date:
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/

Re: postgresql start/stop/status script

From
Baldur Norddahl
Date:
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

Re: postgresql start/stop/status script

From
Tom Lane
Date:
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