Thread: Simple DB presence verifier.

Hello,
This is a real novice question!  We are developing a distributed
(~1-10 PCs) Java office application that uses Postgres as the central
data store. We are capable Java developers, but our Postgres skills
are limited because it is primarily hidden behind Hibernate ORM.  Our
product is aimed at a "technically illiterate small business" market,
so there will be no fancy equipment or system administrators, and
"forgetting to turn on the server" will be a common fail case.

Our use case involves the java desktops checking to see if Postgres is
available at (1) startup, and (2) ongoing "watchdog", perhaps every
minute or so.

My first attempt has been to write a query such as "SELECT last_value
FROM est_sequence". If the query fails then we can conclude that the
database is not present.

What we are looking for is a simple, low overhead, "quick succeed,
catchable fail", way to check if the database is available.

Can anyone suggest an improved technique to check for database presence??

All suggestions welcome.
Many thanks,
-Damian

If PostgreSQL is not running you won't be able to connect to any
database. When you do successfully connect, you have to specify a
database. So if your connection succeeds, you have already verified
the existence of the database. If the database was "missing" you
would get a "database does not exist error".

If Hibernate connects for you and you don't have any commands to
verify the connection, a simple "SELECT 1" should work. If you are
connected and need to verify the existence of a database other than
the one you are connected to then something like:

select count(*) from pg_database where datname = 'postgres';



John



On Mar 4, 2007, at 4:40 PM, Damian C wrote:

> What we are looking for is a simple, low overhead, "quick succeed,
> catchable fail", way to check if the database is available.
>
> Can anyone suggest an improved technique to check for database
> presence??



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Assuming u are familiar with TCP/IP, the problem is basically to see if
there is a TCP server listening at port 5432.
So the smallest overhead will be to try connecting to the IP of the
server on the said port,
if the connection is successful, disconnect and conclude that the server
is running.
I would not recommend keeping one client connected to to the server and
sending a query every now and then
just to verify server is running. I think the above check is good enough.
I remember u could create a client TCP socket using Java.
--Abbas

Damian C wrote:

> Hello,
> This is a real novice question!  We are developing a distributed
> (~1-10 PCs) Java office application that uses Postgres as the central
> data store. We are capable Java developers, but our Postgres skills
> are limited because it is primarily hidden behind Hibernate ORM.  Our
> product is aimed at a "technically illiterate small business" market,
> so there will be no fancy equipment or system administrators, and
> "forgetting to turn on the server" will be a common fail case.
>
> Our use case involves the java desktops checking to see if Postgres is
> available at (1) startup, and (2) ongoing "watchdog", perhaps every
> minute or so.
>
> My first attempt has been to write a query such as "SELECT last_value
> FROM est_sequence". If the query fails then we can conclude that the
> database is not present.
>
> What we are looking for is a simple, low overhead, "quick succeed,
> catchable fail", way to check if the database is available.
>
> Can anyone suggest an improved technique to check for database presence??
>
> All suggestions welcome.
> Many thanks,
> -Damian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

why dont you use class for connections and have something like this

bool isConnected {
try{
   if(Conneccion.State == System.Data.ConnectionState.Open){
    return true;
   }
  else{
     return false;
   }
  }
catch {
    MessageBox.Show("Error en IsConnected class_Coneccion");
    return false;
         }
}
after you conect this is a way to know if its connected or no with the npsql
driver, maybe the driver for java has something like "connection state"

Darkangel.

_________________________________________________________________
Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
Intro*Terms

https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117

Many thanks to John, Abbas and Darkangel for their informative replies.

Initially I will simply establish a socket to port 5432 of the server.
This is trivial to do in Java, quick to succeed (or fail), and
provides appropriate information. Something (notionally) like this
(for future googlers !!!)...
<snip>
try {
  InetAddress address = InetAddress.getByName("192.168.1.2");
  Socket dbSocket = new Socket(address, 5432);
  dbSocket.close();
  return true;
} catch (Exception e) {
  return false;
}
</snip>

Many thanks,
-Damian