Checking that Pg is running from a shell script

Fran Fabrizio wrote:

What's the best way to ensure that Pg is running from a shell script?
I'm crafting a few RPMs for my projects, one of which will attempt to
load data into a database, but I want to check that it's running first
before I try this.

I'm a shell scripting newbie so apologies if the answer is really
straightforward. I don't know if UNIX processes leave a standard trail
so that I could just look up if something is listening on 5432, for
example.

Sure, pg_ctl status:

$ pg_ctl status
pg_ctl: postmaster is running (pid: 7340)
Command line was:
/usr/var/local/postgres/./bin/postmaster '-o' ''

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hi,

if You have fuser on Your system You can check the process
listening on the postgres port 5432:
fuser -n tcp 5432 | wc -l
returns 1 (a line containing only "1" and whitespace) if there
is a process listening on this port and 0 if is not.
Another choice is to look for a process with the pid from
../data/postmaster.pid (probably the better choice).

-tb

On Mon, Jun 10, 2002 at 10:29:06AM -0400, Fran Fabrizio wrote:

What's the best way to ensure that Pg is running from a shell script?
I'm crafting a few RPMs for my projects, one of which will attempt to
load data into a database, but I want to check that it's running first
before I try this.

I'm a shell scripting newbie so apologies if the answer is really
straightforward. I don't know if UNIX processes leave a standard trail
so that I could just look up if something is listening on 5432, for
example.

Thanks,
Fran

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Thomas Beutin tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

On Monday 10 June 2002 10:29 am, Fran Fabrizio wrote:

What's the best way to ensure that Pg is running from a shell script?
I'm crafting a few RPMs for my projects, one of which will attempt to
load data into a database, but I want to check that it's running first
before I try this.

See the man page for pg_ctl. There is, IIRC, a status option, but you have to
know the location of PGDATA (which, in a vanilla RPM install is
/var/lib/pgsql/data).
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen wrote:

On Monday 10 June 2002 10:29 am, Fran Fabrizio wrote:

What's the best way to ensure that Pg is running from a shell script?
I'm crafting a few RPMs for my projects, one of which will attempt to
load data into a database, but I want to check that it's running first
before I try this

See the man page for pg_ctl. There is, IIRC, a status option, but you have to
know the location of PGDATA (which, in a vanilla RPM install is
/var/lib/pgsql/data).

Thanks all, the pg_ctl -D /var/lib/pgsql/data status should be
sufficient for my needs.

-Fran

Given the requirement for having to know PGDATA in order to get pg_ctl to
work, and to offer flexibility for cases where the server will live on a
different machine, I prefer checking status of $?

bash-2.05$ psql quickview -c '\q';
bash-2.05$ echo $?
0
bash-2.05$ export PGPORT=9999
bash-2.05$ psql quickview -c '\q';
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.9999"?
bash-2.05$ echo $?
2

-ron

Can pg_ctl across the network? I'd like my middleware server to know
if my postgres db is up and running OK. I guess I could just make a
db connection but I was hoping for a more elegant solution.
Thanks.

Fran Fabrizio wrote:

Lamar Owen wrote:

On Monday 10 June 2002 10:29 am, Fran Fabrizio wrote:

What's the best way to ensure that Pg is running from a shell script?
I'm crafting a few RPMs for my projects, one of which will attempt to
load data into a database, but I want to check that it's running first
before I try this

See the man page for pg_ctl. There is, IIRC, a status option, but you
have to know the location of PGDATA (which, in a vanilla RPM install
is /var/lib/pgsql/data).

Thanks all, the pg_ctl -D /var/lib/pgsql/data status should be
sufficient for my needs.

-Fran

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Vincent Stoessel
Linux Systems Developer
vincent xaymaca.com

Vincent Stoessel <vincent@xaymaca.com> writes:

Can pg_ctl across the network?

No.

I'd like my middleware server to know
if my postgres db is up and running OK. I guess I could just make a
db connection but I was hoping for a more elegant solution.

Making a connection is the only way at present. There was some talk of
writing a "pg_ping" utility that would just send a probe packet to the
postmaster (and not, for example, require you to supply a password).
No one's got round to it, but it's just a small matter of programming...

regards, tom lane

Hi,

We use this small attached perl script to directly check the port on 2
running DB servers and write the status to a file. Our webserver farm
then checks this file every time we need to use the DB.

In our case, we just serve up static content if there is no database,
otherwise we continue our work.

This script will also timeout after 10 seconds, so it wont wait forever
checking a dead PG port.

- Ericson Smith
info@w3matter.com
http://www.swapyourcrap.com

This seems to work on UNIX systems. (Tested on Linux and Solaris)
You have to change the path of PIDFILE variable below.

#!/usr/bin/ksh
# $Source$
#*DESCRIPTION:**************************************************************
****
# is_postgres_running:
# This script returns either YES or NO, depending on whether Postgresql is
running.
#

## Hard-code the response for testing:
#echo "YES" #*DEBUG*
#exit #*DEBUG*

# the path to your PID file
PIDFILE=/pgdata/postmaster.pid

if [ -f $PIDFILE ] ; then
PID=`cat $PIDFILE`
if [ "x$PID" != "x" ] && kill -0 $PID 2>/dev/null ; then
STATUS="YES"
else
STATUS="NO"
fi
else
STATUS="NO"
fi

echo $STATUS

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, June 10, 2002 1:51 PM
To: Vincent Stoessel
Cc: Fran Fabrizio; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Checking that Pg is running from a shell script

Vincent Stoessel <vincent@xaymaca.com> writes:

Can pg_ctl across the network?

No.

I'd like my middleware server to know
if my postgres db is up and running OK. I guess I could just make a
db connection but I was hoping for a more elegant solution.

Making a connection is the only way at present. There was some talk of
writing a "pg_ping" utility that would just send a probe packet to the
postmaster (and not, for example, require you to supply a password).
No one's got round to it, but it's just a small matter of programming...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

This is very cool, thanks!

Ericson Smith wrote:

Hi,

We use this small attached perl script to directly check the port on 2
running DB servers and write the status to a file. Our webserver farm
then checks this file every time we need to use the DB.

In our case, we just serve up static content if there is no database,
otherwise we continue our work.

This script will also timeout after 10 seconds, so it wont wait forever
checking a dead PG port.

- Ericson Smith
info@w3matter.com
http://www.swapyourcrap.com

On Mon, 2002-06-10 at 15:33, Vincent Stoessel wrote:

Can pg_ctl across the network? I'd like my middleware server to know
if my postgres db is up and running OK. I guess I could just make a
db connection but I was hoping for a more elegant solution.
Thanks.

Fran Fabrizio wrote:

Lamar Owen wrote:

On Monday 10 June 2002 10:29 am, Fran Fabrizio wrote:

What's the best way to ensure that Pg is running from a shell script?
I'm crafting a few RPMs for my projects, one of which will attempt to
load data into a database, but I want to check that it's running first
before I try this

See the man page for pg_ctl. There is, IIRC, a status option, but you
have to know the location of PGDATA (which, in a vanilla RPM install
is /var/lib/pgsql/data).

Thanks all, the pg_ctl -D /var/lib/pgsql/data status should be
sufficient for my needs.

-Fran

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Vincent Stoessel
Linux Systems Developer
vincent xaymaca.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

------------------------------------------------------------------------

#!/usr/bin/perl
use IO::Socket;

$SIG{ALRM} = \&fail;
alarm 10;

$remote = IO::Socket::INET->new(Proto=>"tcp", PeerAddr=>"192.168.1.1", PeerPort=>"5432");
$remote2 = IO::Socket::INET->new(Proto=>"tcp", PeerAddr=>"192.168.1.2", PeerPort=>"5432");
unless($remote) { &fail; }
close($remote);

unless($remote2) { &fail; }
close($remote2);

open(FP,">/httpd/htdocs/status2.db");
print FP "OK";
close(FP);

open(FP,">/httpd/htdocs/status.db");
print FP "OK";
close(FP);

sub fail
{
if(!$remote)
{
open(FP,">/httpd/htdocs/status.db");
print FP "FAIL";
close(FP);
$msg = "OLD Database Failed!";
`echo "$msg" | mail development@did-it.com`;
}

if(!$remote2)
{
open(FP,">/httpd/htdocs/status2.db");
print FP "FAIL";
close(FP);
$msg = "NEW Database Failes!";
`echo "$msg" | mail development@did-it.com`;
}

exit(0);
}

------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Vincent Stoessel
Linux Systems Developer
vincent xaymaca.com