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 type of server is this?

PostgreSQL is an open source object-relational database management system (ORDBMS) distributed under a very permissive license and developed by an active community.

There are a number of PostgreSQL-related services and software (, either open source or not, that are provided by other software companies. Here, we discuss how to recognize which one you are using.

It is not so easy to detect the variant of PostgreSQL from the name; many of the products and services involving PostgreSQL include the word Postgres or PostgreSQL.

However, if you need to check the documentation, or to buy services such as support and consulting, you need to find out exactly what type your server is, as the available options will vary.

If you are paying a license fee or a cloud service subscription, you will already know the name of the company you are paying, and of the specific variant of PostgreSQL you are subscribed to. But it's not rare to have multiple servers of different types, so it is still useful to be able to tell them apart.

How to do it…

Unfortunately, there isn't a single function or parameter that works on each variant of PostgreSQL and, at the same time, is able to answer that question. The closest you can get is the version() function, which is used in the next recipe, What version is the server?, which returns a textual description of the version you are running, including (but not limited to) the version number.

In some cases, this is enough but, otherwise, you have to determine the specific version from other clues, such as the following:

  • The version number for stable releases of community PostgreSQL is either X.Y (with X=10 or above) or X.Y.Z (up to X=9). An extra number usually indicates that you are running a variant of PostgreSQL.
  • The presence of certain objects that are available only on a specific variant, for instance, an extension. More details on how to work with extensions can be found in the Listing extensions in this database recipe in this chapter.

There's more...

Some of the PostgreSQL-based services on the cloud will return the same value of version() as community PostgreSQL does. While this is correct, in the sense that they are indeed running that version of PostgreSQL, it doesn't mean that you have the same level of control. For instance, you might not be given a superuser account, and you will probably be unable to install extensions freely.