Deciding on Useful Extensions

In Chapter 10, Making Sense of Backups and Replication, our focus was on replication, transaction log shipping, and logical decoding. After looking at mostly administration-related topics, the goal now is to aim at a broader topic. In the PostgreSQL world, many things are done using extensions. The advantage of extensions is that functionality can be added without bloating the PostgreSQL core. Users can choose from competing extensions and find what is best for them. The philosophy is to keep the core slim, relatively easy to maintain, and ready for the future. The PostgreSQL development community puts heavy emphasis on extensibility to make sure that the core is not bloated because of features that only a handful of people will need in the long run.

In this chapter, we will discuss some of the most commonly used extensions for PostgreSQL. However, before digging deeper into this issue, I want to state that it is totally impossible to explain all the...

Understanding how extensions work

Before digging into the available extensions, it is a good idea to take a look at how extensions work in the first place. Understanding the inner workings of the extension machinery can be quite beneficial.

Let’s take a look at the syntax first:

test=# \h CREATE EXTENSION
Command: CREATE EXTENSION
Description: install an extension
Syntax:
 CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ CASCADE ]
 URL: https://www.postgresql.org/docs/15/sql-createextension.html

When you want to deploy an extension, simply call the CREATE EXTENSION command. It will check for the extension and load it into your database. Note that the extension will be loaded into a database and not into the entire instance.

If we load...

Making use of contrib modules

Now that we have had a look at a theoretical introduction to extensions, it is time to take a look at some of the most important extensions. In this section, you will learn about modules that are provided to you as part of the PostgreSQL contrib module. When you install PostgreSQL, I recommend that you always install these contrib modules, as they contain vital extensions that can really make your life easier.

In the upcoming section, you will be guided through some extensions that I find the most interesting and the most useful for a variety of reasons (for debugging, performance tuning, and so on).

Using the adminpack module

The idea behind the adminpack module is to give administrators a way to access the filesystem without SSH access. The package contains a couple of functions to make this possible.

To load the module into the database, run the following command:

test=# CREATE EXTENSION adminpack;
 CREATE EXTENSION

One of the most...

Other useful extensions

The extensions that we have described so far are all part of the PostgreSQL contrib package, which is shipped as part of the PostgreSQL source code. However, the packages that we’ve looked at here aren’t the only ones that are available in the PostgreSQL community. Many more packages allow us to do all kinds of things.

The number of modules is growing daily, and it is impossible to cover them all. Therefore, I only want to point out the ones I find the most important.

PostGIS (http://postgis.net/) is the geographical information system (GIS) database interface in the open source world. It has been adopted around the globe and is a de facto standard in the relational open source database world. It is a professional and extremely powerful solution.

If you are looking for geospatial routing, pgRouting may be just the thing. It offers various algorithms that you can use to find the best connections between locations and works on top of PostgreSQL...

Summary

In this chapter, we learned about some of the most promising modules that are shipped with the PostgreSQL standard distribution. These modules are pretty diverse and offer everything from database connectivity to case-insensitive text and modules so that we can inspect the server. However, in this section, you have learned about the most important modules around. This will help you to deploy even greater database setups.

Now that we have dealt with extensions, in the next chapter, we will shift our attention to migration. There, we will learn how we can move to PostgreSQL most simplistically.