How to remotely check PostgreSQL version

Today I will shortly describe the way in which you can remotely check PostgreSQL version and use it inside shell scripts. This ability comes in handy at times, as it can be used to perform different tasks depending on the returned database version.

The first method - the descriptive one

The easiest way to get detailed database version is to execute the following SQL query.

postgres=# SELECT version();
                                            version
-----------------------------------------------------------------------------------------------
 PostgreSQL 9.1.12 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)

You can use this query inside simple shell script to iterate over database servers.

#!/bin/sh
# Simple shell script designed to display PostgreSQL version for selected servers

# PostgreSQL servers
servers="localhost    \
         192.168.1.145 \
         192.168.1.138 \
         192.168.0.130"

# PostgreSQL settings
PGUSER=pguser
PGPASSWORD=pgpass
PGDATABASE=postgres

# print version for each server
for server in $servers; do
  export PGUSER PGPASSWORD PGDATABASE
  export PGHOST=$server
  echo -n "$server:\t"
  psql -A -t -c "select version()"
done

Sample script output:

localhost:      PostgreSQL 9.1.12 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
192.168.1.145:  PostgreSQL 8.4.20 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit
192.168.1.138:  PostgreSQL 9.1.12 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.8.1-10ubuntu8) 4.8.1, 32-bit
192.168.0.130:  PostgreSQL 9.1.12 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.8.1-10ubuntu8) 4.8.1, 32-bit

The second method - the parsable one

To get easily parsable database version use the following SQL query.

postgres=# SHOW server_version;
 server_version
----------------
 9.1.12
(1 row)

Now you can split version to major and minor number using shell script.

#!/bin/sh
# Shell script designed to check major and minor PostgreSQL version for desired servers

# PostgreSQL servers
servers="localhost \
        192.168.1.145 \
        192.168.1.138 \
        192.168.0.130"

# current/previous major, minor version number
curr_major="9.1"
curr_minor="11"
prev_major="8.4"
prev_minor="21"

# PostgreSQL settings
PGUSER=pguser
PGPASSWORD=pgpass
PGDATABASE=postgres

# iterate over each server
for server in $servers; do
  export PGUSER PGPASSWORD PGDATABASE
  export PGHOST=$server

  version=$(psql -A -t -c "show server_version")
  major=$(echo $version | cut -d. -f1,2)
  minor=$(echo $version | cut -d. -f3)

  if [ "$major" = "$curr_major" ]; then
    if [ "$minor" -lt "$curr_minor" ]; then
      echo "$server:\tPlease update server to the latest version ($minor -> $curr_minor)";
    else
      echo "$server:\tAlready using current version ($version)"
    fi
  elif [ "$major" = "$prev_major" ]; then
    if [ "$minor" -lt "$prev_minor" ]; then
      echo "$server:\tPlease update server to the latest version ($major.$minor -> $major.$prev_minor)";
    else
      echo "$server:\tAlready using current version ($version)"
    fi
  else
    echo "$server:\tSkipped - Version mismatch ($major)"
  fi
done

Sample script output.

localhost:      Already using current version (9.1.12)
192.168.1.145:  Please update server to the latest version (8.4.20 -> 8.4.21)
192.168.1.138:  Already using current version (9.1.12)
192.168.0.130:  Already using current version (9.1.12)

Third method - the comparable one

To get database version as a number execute the following SQL query.

postgres=# SHOW server_version_num;
 server_version_num
--------------------
 90112
(1 row)

Use it to quickly compare version numbers using shell script.

#!/bin/sh
# Shell script designed to check minimal/preferred PostgreSQL version for desired servers

# PostgreSQL servers
servers="localhost \
        192.168.1.145 \
        192.168.1.138 \
        192.168.0.130"

# minimal and preferred version number
minimal_version="80420"
preffered_version="90112"

# PostgreSQL settings
PGUSER=pguser
PGPASSWORD=pgpass
PGDATABASE=postgres

# iterate over each server
for server in $servers; do
  export PGUSER PGPASSWORD PGDATABASE
  export PGHOST=$server

  version=$(psql -A -t -c "show server_version_num")

  if [ "$version" -ge  "$preffered_version" ]; then
    echo "$server:\tRequirements fully met ($version)"
  else
    if [ "$version" -ge "$minimal_version" ]; then
      echo "$server:\tMinimal requirements met ($version)"
    else
      echo "$server:\tRequirements are not met ($version)"
    fi
  fi
done

Sample script output:

localhost:      Requirements fully met (90112)
192.168.1.145:  Minimal requirements met (80420)
192.168.1.138:  Requirements fully met (90112)
192.168.0.130:  Requirements fully met (90112)

References

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system.

Gdansk, Poland https://sleeplessbeastie.eu