Book Image

PostgreSQL 14 Administration Cookbook

By : Simon Riggs, Gianni Ciolli
5 (1)
Book Image

PostgreSQL 14 Administration Cookbook

5 (1)
By: Simon Riggs, Gianni Ciolli

Overview of this book

PostgreSQL is a powerful, open-source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 14 allows you to scale up your PostgreSQL infrastructure. With this book, you'll take a step-by-step, recipe-based approach to effective PostgreSQL administration. This book will get you up and running with all the latest features of PostgreSQL 14 while helping you explore the entire database ecosystem. You’ll learn how to tackle a variety of problems and pain points you may face as a database administrator such as creating tables, managing views, improving performance, and securing your database. As you make progress, the book will draw attention to important topics such as monitoring roles, validating backups, regular maintenance, and recovery of your PostgreSQL 14 database. This will help you understand roles, ensuring high availability, concurrency, and replication. Along with updated recipes, this book touches upon important areas like using generated columns, TOAST compression, PostgreSQL on the cloud, and much more. By the end of this PostgreSQL book, you’ll have gained the knowledge you need to manage your PostgreSQL 14 database efficiently, both in the cloud and on-premise.
Table of Contents (14 chapters)

What version is the server?

PostgreSQL has internal version numbers for the data file format, database catalog layout, and crash recovery format. Each of these is checked as the server runs to ensure that the data doesn't become corrupt. PostgreSQL doesn't change these internal formats for a single minor release; they only change across major releases.

From a user's perspective, each release differs in terms of the way the server behaves. If you know your application well, then it should be possible to assess the differences simply by reading the release notes for each version. In many cases, a retest of the application is the safest thing to do.

If you experience any general problems related to setup and configuration with your database, then you'll need to double-check which version of the server you have. This will help you to report a fault or to consult the correct version of the manual.

How to do it…

We will find out the version by querying the database server directly:

  1. Connect to the database and issue the following command:
    postgres=# SELECT version();
  2. You'll get a response that looks something like this:
    PostgreSQL 14.0 (Debian 14.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

That's probably too much information all at once!

Another way of checking the version number in your programs is as follows:

postgres=# SHOW server_version; 

The preceding shows the version in text form, so you may also want a numerical value that is easier to compare using a greater than symbol, in which case you execute this command instead:

postgres=# SHOW server_version_num;

Another alternative is via command-line utilities, such as this:

bash # psql --version
psql (PostgreSQL) 14.0 (Debian 14.0-1.pgdg100+1)

However, be wary that this shows the client software version number, which may differ from the server software version number. This will usually be reported to you so that you're aware.

How it works…

The current PostgreSQL server version format is composed of two numbers; the first number indicates the major release, and the second one denotes subsequent maintenance releases for that major release. It is common to mention just the major release when discussing what features are supported, as they are unchanged on a maintenance release.

14.0 is the first release of PostgreSQL 14, and subsequent maintenance releases will be 14.1, 14.2, 14.3, and so on. In the preceding example, we see that 14.0 is the version of that PostgreSQL server.

For each major release, there is a separate version of the manual, since the feature set is not the same. If something doesn't work exactly the way you think it should, make sure you are consulting the correct version of the manual.

There's more…

Prior to release 10, PostgreSQL was using a three-part numbering series, meaning that the feature set and compatibility related to the first two numbers, while maintenance releases were denoted by the third number. For instance, version 9.6 contained more additional features and compatibility changes when compared to version 9.5; version 9.6.0 was the initial release of 9.6, and version 9.6.1 was a later maintenance release.

The release support policy for PostgreSQL is available at This article explains that each release will be supported for a period of 5 years. Since we release one major version per year, this means 5 major releases.

Support for all releases up to and including 9.6 ended in September 2021. So, by the time you're reading this book, only PostgreSQL 10 and higher versions will be supported. The earlier versions are still robust, although many performance and enterprise features are missing from those releases. The future end-of-support dates are as follows:

Figure 2.1 – A table showing PostgreSQL version release dates

Figure 2.1 – A table showing PostgreSQL version release dates