Check PostgreSQL status using MS-DOS bat file?

On Wed, Aug 15, 2012 at 09:52:17AM +0000, Loughrey, Hugh wrote:

Hi All,

I want to write a MS-DOS command to check that the PostgreSQL database is up
and running and able to accept data being pushed to it. From a bit of reading
I’ve identified the pg_ctl status command, but can this be incorporated into a
*.bat file and can the resulting status be recorded in a *.txt file? If so does
anyone have a command which would enable this?

As I remember it is pretty tricky to call pg_ctl from a Windows batch
file. I know the Windows installers do it somehow --- you might want to
downlaod it and see if you can find the shell script they use. Dave
Page might know more --- CC'ing him.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Wed, Aug 15, 2012 at 4:04 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Wed, Aug 15, 2012 at 09:52:17AM +0000, Loughrey, Hugh wrote:

Hi All,

I want to write a MS-DOS command to check that the PostgreSQL database is up
and running and able to accept data being pushed to it. From a bit of reading
I’ve identified the pg_ctl status command, but can this be incorporated into a
*.bat file and can the resulting status be recorded in a *.txt file? If so does
anyone have a command which would enable this?

As I remember it is pretty tricky to call pg_ctl from a Windows batch
file. I know the Windows installers do it somehow --- you might want to
downlaod it and see if you can find the shell script they use. Dave
Page might know more --- CC'ing him.

You shouldn't try to start/stop the server with pg_ctl if it's
configured to run as a service (use "net start xxx", "net stop xxx"
for that), but you can check the status:

C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program
Files\PostgreSQL\9.2\data" status
pg_ctl: server is running (PID: 1040)
C:/Program Files/PostgreSQL/9.2/bin/postgres.exe "-D" "C:/Program
Files/PostgreSQL/9.2/data"

C:\>net stop postgresql-x64-9.2
The postgresql-x64-9.2 service is stopping.
The postgresql-x64-9.2 service was stopped successfully.

C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program
Files\PostgreSQL\9.2\data" status
pg_ctl: no server running

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Please keep the mailing list CC'd]

On Fri, Aug 17, 2012 at 11:52 AM, Loughrey, Hugh
<Hugh.Loughrey@hoopleltd.co.uk> wrote:

Hi Dave,

Thanks for the message below. The script you forwarded looks to be for an instance in which the DB is running of a windows box, apologies I should have mentioned, we currently run PostgreSQL on a Linux box. Does this mean calling pg_ctl is not an option? Or do we need to install additional drivers?

pg_ctl only checks the status of an instance running on the local
machine. To check on a remote linux box from windows using pg_ctl,
you'd have to run pg_ctl on the linux box, probably over SSH (look for
Putty for an SSH client for Windows).

An easier option might be to run a psql command on the remote
database. Just run something like "SELECT 1" and check you actually
get a 1 back, and not a connection error.

We'd be using FME to push data from other databases into PostgreSQL, however before running our FME scripts we'd want to check the DB is up and running. If the DB is running, then run the FME scripts, if not...don't run the FME scripts. All of this needs to be controlled via a windows server 2008 box.

I appreciate your help.

Regards

Hugh

-----Original Message-----
From: Dave Page [mailto:dpage@pgadmin.org]
Sent: 15 August 2012 16:30
To: Bruce Momjian
Cc: Loughrey, Hugh; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?

On Wed, Aug 15, 2012 at 4:04 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Wed, Aug 15, 2012 at 09:52:17AM +0000, Loughrey, Hugh wrote:

Hi All,

I want to write a MS-DOS command to check that the PostgreSQL
database is up and running and able to accept data being pushed to
it. From a bit of reading I’ve identified the pg_ctl status command,
but can this be incorporated into a *.bat file and can the resulting
status be recorded in a *.txt file? If so does anyone have a command which would enable this?

As I remember it is pretty tricky to call pg_ctl from a Windows batch
file. I know the Windows installers do it somehow --- you might want
to downlaod it and see if you can find the shell script they use.
Dave Page might know more --- CC'ing him.

You shouldn't try to start/stop the server with pg_ctl if it's configured to run as a service (use "net start xxx", "net stop xxx"
for that), but you can check the status:

C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program Files\PostgreSQL\9.2\data" status
pg_ctl: server is running (PID: 1040)
C:/Program Files/PostgreSQL/9.2/bin/postgres.exe "-D" "C:/Program Files/PostgreSQL/9.2/data"

C:\>net stop postgresql-x64-9.2
The postgresql-x64-9.2 service is stopping.
The postgresql-x64-9.2 service was stopped successfully.

C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program Files\PostgreSQL\9.2\data" status
pg_ctl: no server running

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
“Any opinion expressed in this e-mail or any attached files are those of the individual and not necessarily those of Hoople Ltd. You should be aware that Hoople Ltd. monitors its email service. This e-mail and any attached files are confidential and intended solely for the use of the addressee. This communication may contain material protected by law from being passed on. If you are not the intended recipient and have received this e-mail in error, you are advised that any use, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. If you have received this e-mail in error please contact the sender immediately and destroy all copies of it.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi ,

Dave's instructions are helpful for finding the status of the server..
However, I do have the below the script which is nothing but PgPing in
windows ... I hope it helps you in the implementation ..

@ECHO OFF

set PSQL="C:\Program Files\PostgreSQL\9.1\bin"

set DBNAME="template1"

set USER="postgres"

set PORT="5432"

set RES="Not Pinging"

%PSQL%\psql -Atq -c "SELECT 'ping'" -p %PORT% -U %USER% %DBNAME% > _Res.txt

set /p RES=<_Res.txt

echo %RES%

IF %RES% EQU ping (echo "No need to raise any exception ") else (echo
"PostgreSQL seems not pinging.. Need to raise an exception")

Best Regards,
Dinesh
manojadinesh.blogspot.com

On Fri, Aug 17, 2012 at 4:32 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi Dinesh,

Managed to get this up and running...thanks!!

Regards

Hugh

From: dinesh kumar [mailto:dineshkumar02@gmail.com]
Sent: 17 August 2012 13:15
To: Loughrey, Hugh
Cc: Postgres General
Subject: Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?

Hi ,

Dave's instructions are helpful for finding the status of the server.. However, I do have the below the script which is nothing but PgPing in windows ... I hope it helps you in the implementation ..

@ECHO OFF

set PSQL="C:\Program Files\PostgreSQL\9.1\bin"

set DBNAME="template1"

set USER="postgres"

set PORT="5432"

set RES="Not Pinging"

%PSQL%\psql -Atq -c "SELECT 'ping'" -p %PORT% -U %USER% %DBNAME% > _Res.txt

set /p RES=<_Res.txt

echo %RES%

IF %RES% EQU ping (echo "No need to raise any exception ") else (echo "PostgreSQL seems not pinging.. Need to raise an exception")

Best Regards,
Dinesh
manojadinesh.blogspot.com<http://manojadinesh.blogspot.com&gt;

On Fri, Aug 17, 2012 at 4:32 PM, Dave Page <dpage@pgadmin.org<mailto:dpage@pgadmin.org>> wrote:
[Please keep the mailing list CC'd]

On Fri, Aug 17, 2012 at 11:52 AM, Loughrey, Hugh
<Hugh.Loughrey@hoopleltd.co.uk<mailto:Hugh.Loughrey@hoopleltd.co.uk>> wrote:

Hi Dave,

Thanks for the message below. The script you forwarded looks to be for an instance in which the DB is running of a windows box, apologies I should have mentioned, we currently run PostgreSQL on a Linux box. Does this mean calling pg_ctl is not an option? Or do we need to install additional drivers?

pg_ctl only checks the status of an instance running on the local
machine. To check on a remote linux box from windows using pg_ctl,
you'd have to run pg_ctl on the linux box, probably over SSH (look for
Putty for an SSH client for Windows).

An easier option might be to run a psql command on the remote
database. Just run something like "SELECT 1" and check you actually
get a 1 back, and not a connection error.

We'd be using FME to push data from other databases into PostgreSQL, however before running our FME scripts we'd want to check the DB is up and running. If the DB is running, then run the FME scripts, if not...don't run the FME scripts. All of this needs to be controlled via a windows server 2008 box.

I appreciate your help.

Regards

Hugh

-----Original Message-----
From: Dave Page [mailto:dpage@pgadmin.org<mailto:dpage@pgadmin.org>]
Sent: 15 August 2012 16:30
To: Bruce Momjian
Cc: Loughrey, Hugh; pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?

On Wed, Aug 15, 2012 at 4:04 PM, Bruce Momjian <bruce@momjian.us<mailto:bruce@momjian.us>> wrote:

On Wed, Aug 15, 2012 at 09:52:17AM +0000, Loughrey, Hugh wrote:

Hi All,

I want to write a MS-DOS command to check that the PostgreSQL
database is up and running and able to accept data being pushed to
it. From a bit of reading I've identified the pg_ctl status command,
but can this be incorporated into a *.bat file and can the resulting
status be recorded in a *.txt file? If so does anyone have a command which would enable this?

As I remember it is pretty tricky to call pg_ctl from a Windows batch
file. I know the Windows installers do it somehow --- you might want
to downlaod it and see if you can find the shell script they use.
Dave Page might know more --- CC'ing him.

You shouldn't try to start/stop the server with pg_ctl if it's configured to run as a service (use "net start xxx", "net stop xxx"
for that), but you can check the status:

C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program Files\PostgreSQL\9.2\data" status
pg_ctl: server is running (PID: 1040)
C:/Program Files/PostgreSQL/9.2/bin/postgres.exe "-D" "C:/Program Files/PostgreSQL/9.2/data"

C:\>net stop postgresql-x64-9.2
The postgresql-x64-9.2 service is stopping.
The postgresql-x64-9.2 service was stopped successfully.

C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program Files\PostgreSQL\9.2\data" status
pg_ctl: no server running

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
"Any opinion expressed in this e-mail or any attached files are those of the individual and not necessarily those of Hoople Ltd. You should be aware that Hoople Ltd. monitors its email service. This e-mail and any attached files are confidential and intended solely for the use of the addressee. This communication may contain material protected by law from being passed on. If you are not the intended recipient and have received this e-mail in error, you are advised that any use, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. If you have received this e-mail in error please contact the sender immediately and destroy all copies of it.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general