Check if Postgresql is listening

Question

Given an IP Address and port number, is it possible to check if the machine with that IP address has Postgresql listening on the specified port? If so, how?

I just want to obtain a boolean value of whether Postgresql is listening on the specified port of the specified machine.

Solution 1

I think you need to define what you're trying to achieve better. Do you just want to know if anything is listening on a certain point? If PostgreSQL is listening on a given port? If PostgreSQL is running and actually accepting connections? If you can connect to PostgreSQL, authenticate successfully and issue queries?

One option is to invoke psql to connect to it and check the result code. Do not attempt to parse the output text, since that's subject to translation into different languages.

Better, use the client library for the language of your choice - psycopg2 for Python, PgJDBC for Java, the Pg gem for Ruby, DBD::Pg for Perl, nPgSQL for C#, etc. This is the approach I'd recommend. The SQLSTATE or exception details from any connection error will tell you more about why the connection failed - you'll be able to tell the difference between the server not listening, authentication failure, etc this way. For example, in Python:

import psycopg2
try:
    conn = psycopg2.connect("host=localhost dbname=postgres")
    conn.close()
except psycopg2.OperationalError as ex:
    print("Connection failed: {0}".format(ex))

There are exception details in ex.pgcode (the SQLSTATE) to tell you more about errors that're generated server-side, like authentication failures; it'll be empty for client-side errors.

If you just want to see if something is listening on a given IP and TCP port, you can use netcat (*nix only), or a simple script in the language of your choice that creates a socket and does a connect() then closes the socket if it gets a successful response. For example, the following trivial Python script:

import socket                                                                                                                                                              
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
try:
    s.connect(('localhost',5432))
    s.close()
except socket.error as ex:
    print("Connection failed with errno {0}: {1}".format(ex.errno, ex.strerror))            

The same approach applies in any programming language, just the details of the socket library and error handling vary.

For some purposes it can also be useful to use the netstat tool to passively list which processes are listening on which network sockets. The built-in netstat on Windows is pretty brain-dead so you have to do more parsing of the output than with netstat for other platforms, but it'll still do the job. The presence of a socket in netstat doesn't mean that connecting to it will succeed, though; if the process has failed in some way that leaves it broken but still running (stuck in an infinite loop, blocked by a debugger, SIGSTOPed, etc) then it won't respond to an actual connection attempt.

Solution 2

You can use, for example, nmap tool:

=$ sudo nmap -v -p 5930 127.0.0.1

Starting Nmap 6.00 ( http://nmap.org ) at 2013-06-25 19:28 CEST
Initiating SYN Stealth Scan at 19:28
Scanning localhost (127.0.0.1) [1 port]
Discovered open port 5930/tcp on 127.0.0.1
Completed SYN Stealth Scan at 19:28, 0.03s elapsed (1 total ports)
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000045s latency).
PORT     STATE SERVICE
5930/tcp open  unknown

Read data files from: /usr/bin/../share/nmap
Nmap done: 1 IP address (1 host up) scanned in 0.08 seconds
           Raw packets sent: 1 (44B) | Rcvd: 2 (88B)

Alternatively you can just "SELECT 1" with psql, and check output:

=$ psql -h 127.0.0.1 -p 5930 -c "select 1"
 ?column? 
----------
        1
(1 row)

=$ psql -h 127.0.0.1 -p 5940 -c "select 1"
psql: could not connect to server: Connection refused
        Is the server running on host "127.0.0.1" and accepting
        TCP/IP connections on port 5940?

Solution 3

In brief

In details

Fastest way is to use netcat aka nc with timeout ability as shared here

Results as 0/1 means postgres working/not-working

echo 'QUIT' | nc -w SECONDS YOUR_HOST PORT; echo $?
# eg
echo 'QUIT' | nc -w 1       localhost 5432; echo $?

---

Another also-faster way that works for me is to use telnet as discussed here.

echo -e '\x1dclose\x0d' | telnet YOUR_HOST PORT
# eg
echo -e '\x1dclose\x0d' | telnet localhost 5432

Comments

[Marc B]

try to connect with a pg client library and attempt a login. if nothing's listening, or it's not PG, you'll get various errors. if it's pg and running, you'd at least get permission denied/unknown user/whatever errors.

[ksun]

@Marc Thanks for the tip Marc. I'm trying to automate this task as part of a windows install. I'm considering writing some C# code to accomplish this. Do you know of a good C# library that I could attempt to connect to postgresql with? Also, if you could help enumerate which errors indicate postgresql is listening and which errors indicate otherwise that would also be helpful information.

[ksun]

I'm looking into using Npgsql

[Craig Ringer]

In future please provide some more background in the original question; the "why" for the "how" that you're asking. Details like platform and language are rather useful to know. Always include your PostgreSQL version in questions too.

[Victor]

what about curl -v -sL -I localhost:5432/ping taken from github.com/influxdata/influxdb/issues/4595

[ksun]

Thank you depesz. I suppose I could parse the nmap output and check whether the STATE = open and SERVICE = postgresql. The main concern I have with that approach is that, because I'm planning to execute this task as part of an install, nmap would need to be installed on the client's machine. I'm not sure we want to require that. Perhaps your alternative solution will work out though.

[Mike Sherrill 'Cat Recall']

I think netstat is part of Windows. It can also determine whether PostgreSQL is listening.

[ksun]

Actually, just ran a test with postgres listening on another machine and when I ran nmap I found that STATE=filtered. But I think using the psql command with "Select 1" and checking for a connection error may be the best solution I've come across so far.

[ksun]

@"Mike Sherrill 'Catcall'" Mike, thanks I will also look into netstat.

[ksun]

@Mike, after a brief look into netstat it seems that while it is part of windows, and is a great tool for listing out all the ports that are listening or established connections on your machine, I'm trying to see if postgres is listening on a specified port on another host machine. Is there a way to do this with netstat?

[Mike Sherrill 'Cat Recall']

@ksun: Hmm. Not sure. Maybe through PowerShell?

[ksun]

Wow, thanks for providing such an in-depth answer, Craig. I apologize for not including all the details in my original question. I'm interested in knowing if postgreSQL specifically is listening on the port, and if it is accepting connections. - And this is for Windows OS. I've decided to use the npsql C# library to accomplish this.

[Nam G VU]

Awesome answer!