The PostgreSQL version allows the developer to know the supported features and helps them write compatible SQL queries for different versions. For example, the process ID attribute name in the pg_stat_activity view in PostgreSQL versions older than 9.2 is procpid; in PostgreSQL version 9.2, this attribute name is pid.
Getting the database and build version right is important because it allows the developer to know the supported features and compatibility between client tools and the backend version.
The version() function, as shown in the following query, shows the version information as well as build system:
car_portal=# SELECT version (); version ------------------------------------------------------------- PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit (1 row)
It is also important to check the client tools' version, whether it is pg_restore, pg_dumpall or psql, in order to check its compatibility with the server. This can be done as follows:
pg_dump --version pg_dump (PostgreSQL) 9.3.5
The pg_dump utility generates dumps that can be restored on newer versions of PostgreSQL. Also, pg_dump can dump data from PostgreSQL versions older than its own version but not for a PostgreSQL version newer than its own version, as shown in the following query:
$ pg_dump -h localhost -U postgres -d car_portal > car_portal.sql pg_dump: server version: 9.4.1; pg_dump version: 9.3.5 pg_dump: aborting because of server version mismatch