You can find the code for this chapter at the following GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL.

As a relational database, PostgreSQL provides a lot of features, and it is quite difficult to "scare" a PostgreSQL instance. In fact, a single instance can contain more than 4 billion individual databases, each with unlimited total size and capacity for more than 1 billion tables, each containing 32 TB of data. Moreover, if there's any concern that those upper limits won't suffice, please consider that a single table can have 1,600 columns, each 1 GB in size, with an unlimited number of multi-column (up to 32 columns) indexes. In short, PostgreSQL can store much more data than you can possibly think of!

Therefore, there is no amount of data that PostgreSQL cannot handle, but of course, in order to perform well with certain big databases, you need to understand PostgreSQL and its features.

PostgreSQL is fully ACID-compliant and has a very strong foundation in data integrity and concurrency. It ships with a procedural language, named PL/pgSQL, which can be used to write reusable pieces of code, such as functions and routines, and it supports before and after triggers, views, materialized views, and partitioned tables.

PostgreSQL can be extended with other embedded languages, such as Perl, Python, Java, and even Bash! And if you think the database does not provide you with enough features, you can plug in extensions to obtain different behaviors and enhancements—for instance, geospatial references (GIS), scheduled jobs, esoteric data types, and utilities in general.

PostgreSQL runs on pretty much every operating system out there, including Linux, Unix, Mac OS X, and Microsoft Windows, and can even run on commodity hardware such as Raspberry Pi boards. There are also several cloud computing providers that list PostgreSQL in their software catalog.

Thanks to its extensive tuning mechanism, it can be adapted very well to the hosting platform. The community is responsible for keeping the database and documentation at a very high-quality level, and also the mailing lists and IRC channels are very responsive and a valuable source for solutions and ideas.

In the experience of the authors, there has never been a case where PostgreSQL has not been able to adapt to an application scenario.

A brief history of PostgreSQL

PostgreSQL takes its name from its ancestor: Ingres. Ingres was a relational database developed by professor Michael Stonebraker. In 1986, Professor Stonebraker started a post-Ingres project to develop new cool features in the database landscape and named this project POSTGRES (POST-Ingres). The project aimed to develop an object-relational database, where "object" means the user would have the capability to extend the database with their own objects, such as data types, functions, and so on.

In 1994, POSTGRES was released with version 4.2 and an MIT license, which opened up collaboration from other developers around the world. At that time, POSTGRES was using an internal query language named QUEL.

Two Berkeley students, Andrew Yu and Jolly Chen, replaced the QUEL query language with the hot and cool SQL language, and the feature was so innovative that the project changed its name to Postgre95 to emphasize the difference compared to other preceding versions.

Eventually, in 1996, the project gained a public server to host the code, and five developers, including Marc G. Fournier, Tom Lane, and Bruce Momjan, started the development of the new branded project named PostgreSQL. Since then, the project has been kept in good shape and up to date.

This also means that PostgreSQL has been developed for over 30 years, again emphasizing the solidity and openness of the project itself. If you are curious, it is also possible to dig into the source code down to the initial commit in the open source world:

$ git log `git rev-list --max-parents=0 HEAD`
commit d31084e9d1118b25fd16580d9d8c2924b5740dff
 Author: Marc G. Fournier <[email protected]>
 Date:   Tue Jul 9 06:22:35 1996 +0000
 
 Postgres95 1.01 Distribution - Virgin Sources

What's new in PostgreSQL 12?

PostgreSQL 12 was released on October 3, 2019. It includes a rich set of new features with regard to its predecessor versions, including the following:

• Several performance optimizations, ranging from inlining Common Table Expressions to huge table partition management and an improved user-defined statistic hint for multi-column selections

• A few administrative optimizations, including the concurrent rebuilding of indexes, off-line check-summing, and, most notably, reporting about maintenance processes' progress
• Security features including multi-factor authentication and TCP/IP encryption via GSSAPI
• Support for the SQL JSON path language
• Stored generated columns

PostgreSQL 12 also contains a set of changes aimed to make the database administrator (DBA)'s life easier—for instance, removing conflicting options and obsolete SQL terms and types. This emphasizes the fact that PostgreSQL developers do always take care of the database and its adherence to the current SQL standard.

What's new in PostgreSQL 13?

PostgreSQL 13 will contain a very rich set of optimizations under the hood, with particular regard to the following:

• Partitioning, which now includes the ability to execute before triggers on partitioned tables, the capability to prune partitions in particular edge cases to speed up query execution, and a better way to join partitions in queries (referred to as partition-wise joins).
• Replication, which can now work at the logical level even on partitioned tables, automatically publishing all the partitions. Also, there is now no automatic promotion of a server if it does not reach the specific target to recovery and a slave server can be promoted without cancelling any pending pause requests. It is worth noting that it is possible to change the settings of a streaming replication without having to restart the cluster, therefore having a no-downtime impact.
• Indexes, which are now more efficient in general for storing data and accepting operators with parameters.
• Statistics, with particular regard to improvements in the extended statistics, the data collected and used by the optimizer and a few changes in the monitoring catalogs.

There are a lot more changes that will be discussed in an appropriate chapter at the end of the book, but as usual, a new release of PostgreSQL contains performance improvements as well as security improvements and, as always, a better configuration system.

PostgreSQL release policy, version numbers, and life cycle

PostgreSQL developers release a new major release once per year, usually near October. A major release is a stable version that introduces new features and possible incompatibilities with previous versions. During its life cycle, a major release is constantly improved by means of minor releases, which are usually bug-fixing and maintenance releases.

The PostgreSQL version number identifies the major and minor release. Since PostgreSQL 10 (released in 2011), the version number is specified as major.minor; so, for instance, 12.0 indicates the first major release, 12, while 12.1 indicates the minor release, 1, of major release 12. In short, the greater the number, the more recent the version you are managing.

However, before PostgreSQL 10, the version number was made by three different groups of digits—brand.year.minor—where the brand is the main development topic (for instance, "replication"), the year represents the year of development of that brand, and the minor is the minor version. What is important to keep in mind is that the brand and year pair made a major number in PostgreSQL versions prior to 10. So, for instance, PostgreSQL 9.6.16 is the 16^th minor release done on brand 9 during the 6^th year of development, and therefore could be incompatible with 9.5.20 because the two major versions are 9.6 and 9.5.

But what does it mean, in a practical sense, that two major versions are possibly incompatible?

PostgreSQL stores its own data on the storage system, often the hard disk. This data is stored in binary format, for optimization of performances and space consumption, and this format could possibly change between major versions. This means that, while you are able to upgrade PostgreSQL between minor versions on the fly, you probably will have to dump and restore your database content between major version upgrades. As you will see in this book, PostgreSQL provides ad hoc tools to support you even in the worst case of a major upgrade with a lot of incompatibilities, but keep in mind that while a minor version upgrade is something you usually do without any ahead planning, a major version upgrade could imply downtime.

The recommendation, as for much other software, is to run the most recent version of PostgreSQL available to you: PostgreSQL developers put in a lot of effort in order to provide bug-free products, but new features could introduce new bugs, and regardless of the very extensive testing platform PostgreSQL has, it is software after all, and software could have bugs. Despite internal bugs, new releases also include fixes for security exploits and performance improvements, so it is a very good habit to keep up to date with your running PostgreSQL server.

Last but not least, not all PostgreSQL versions will live forever. PostgreSQL provides support and upgrades for 5 years after a new release is issued; after this length of time, a major release will reach its end of life (EOL) and PostgreSQL developers will no longer maintain it. This does not mean you cannot run an ancient version of PostgreSQL, it simply means this version will not get any upgrades from the official project and, therefore, will be out of date. As an example, since PostgreSQL 12 was released in 2019, it will reach its EOL in 2024.

With that in mind, we'll now introduce the main PostgreSQL terminology, as well as further useful-to-understand concepts.

A PostgreSQL instance is called a cluster because a single instance can serve and handle multiple databases. Every database is an isolated space where users and applications can store data.

A database is accessed by allowed users, but users connected to a database cannot cross the database boundaries and interact with data contained in another database, unless they explicitly connect to the latter database too.

A database can be organized into namespaces, called schemas. A schema is a mnemonic name that the user can assign to organize database objects, such as tables, into a more structured collection. Schemas cannot be nested, so they represent a flat namespace.

Database objects are represented by everything the user can create and manage within the database—for instance, tables, functions, triggers, and data types. Every object belongs to one and only one schema that, if not specified, is the default public schema.

Users are defined at a cluster-wide level, which means they are not tied to a particular database in the cluster. A user can connect with and manage any database in the cluster they have been allowed to.

PostgreSQL splits users into two main categories:

• Normal users: These users are the ones who can connect to and handle databases and objects depending on their privilege set.
• Superusers: These users can do anything with any database object.

PostgreSQL allows the configuration of as many superusers as you need, and every superuser has the very same permissions: they can do everything with every database and object and, most notably, can also control the life cycle of the cluster (for instance, they can terminate normal user connections, reload the configuration, stop the whole cluster, and so on).

PostgreSQL internal data, such as users, databases, namespaces, configuration, and database runtime status, is provided by means of catalogs: special tables that present information in a SQL-interactive way. Many catalogs are trimmed depending on the user who is inspecting them, with the exception that superusers usually see the whole set of available information.

PostgreSQL stores the user data (for example, tables) and its internal status on the local filesystem. This is an important point to keep in mind: PostgreSQL relies on the underlying filesystem to implement persistence, and therefore tuning the filesystem is an important task in order to make PostgreSQL perform well. In particular, PostgreSQL stores all of its content (user data and internal status) in a single filesystem directory known as PGDATA. The PGDATA directory represents what the cluster is serving as databases, so it is possible for you to have a single installation of PostgreSQL and make it switch to different PGDATA directories to deliver different content. In effect, this is a possible way to implement quick upgrades between major versions. As you will see in the next sections, the PGDATA directory needs to be initialized before it can be used by PostgreSQL; the initialization is the creation of the directory structure within PGDATA itself and is, of course, a one-time operation.

The detailed content of PGDATA will be explained later in the next chapter, but for now, it will suffice for you to remember that the PGDATA directory is where PostgreSQL expects to find data and configuration files. In particular, the PGDATA directory is made by at least the write-ahead logs (WALs) and the data storage. Without either of those two parts, the cluster is unable to guarantee data consistency and, in some critical circumstances, even start.

WALs are a technology that many database systems use, and even some transaction filesystems (such as ZFS, ReiserFS, UFS with Soft Updates, and so on) provide. The idea is that, before applying any change to a chunk of data, an intent log will be made persistent. In this case, if the cluster crashes, it can always rely on the already-written intent log to understand what operations have been completed and what must be recovered (more details on this in later chapters). Please note that with the term "crash," we refer to any possible disaster that can hit your cluster, including a software bug, but more likely the lack of electrical power, hard disk failures, and so on. PostgreSQL does commit to providing to you the best data consistency it can, and therefore, it makes a great effort to ensure that the intent log (WAL) is as secure as possible.

Internally, PostgreSQL keeps track of the tables structures, indexes, functions, and all the stuff needed to manage the cluster in dedicated storage named the catalog. The PostgreSQL catalog is fundamental for the life cycle of the cluster and reflects pretty much every action the database does on the user's structures and data. PostgreSQL provides access to the catalog from database superusers by means of an SQL interface, which means the catalog is totally explorable and, to some extent, manipulable, via SQL statements.

When the cluster is started, PostgreSQL launches a single process called the postmaster. The aim of the postmaster is to wait for incoming client connections, often made over a TCP/IP connection, and fork another process named the backend process, which in turn is in charge of serving one and only one connection.

This means that every time a new connection against the cluster is opened, the cluster reacts by launching a new backend process to serve it until the connection ends and the process is, consequently, destroyed. The postmaster usually starts also some utility processes that are responsible to keep PostgreSQL in good shape while it is running; these processes will be discussed later in this process.

To summarize, PostgreSQL provides you with executables that can be installed wherever you want on your system and can serve a single cluster. The cluster, in turn, serves data out of a single PGDATA directory that contains, among other stuff, the user data, the cluster internal status, and the WALs. Every time a client connects to the server, the postmaster process forks a new backend process that is the minion in charge of serving the connection.

This is a quick recap of the main terms used within PostgreSQL:

• Cluster: Cluster refers to the whole PostgreSQL service.
• Postmaster: This is the first process the cluster executes, and this process is responsible for keeping track of the activities of the whole cluster. The postmaster forks itself into a backend process every time a new connection is established.
• Database: The database is an isolated data container to which users (or applications) can connect to. A cluster can handle multiple databases. A database can be made by different objects, including schemas (namespaces), tables, triggers, and other objects you will see as the book progresses.
• PGDATA: PGDATA is the name of the directory that, on persistent storage, is fully dedicated to PostgreSQL and its data. PostgreSQL stores the data within such a directory.
• WALs: WALs contains the intent log of database changes, used to recover data from a critical crash.

Now that we've discussed the basic terminology related to PostgreSQL, it is time to get it installed on your machine.

PostgreSQL can run on several Unix and Unix-like operating systems, such as Linux, as well as on Microsoft Windows. So far, the most supported platform remains Linux because most PostgreSQL developers work on this platform, and so it is the one with the most tested use cases. However, deploying on other platforms should not present any problems and, most importantly, is not going to put your data at any risk.

This section will focus on installing PostgreSQL 12, since it is the latest stable version available worldwide. You will learn, however, how to build your own version of PostgreSQL, and this may also be the way to install PostgreSQL 13 on your system.

Before installing PostgreSQL 12, you need to choose, or at least evaluate, how to install it. There are two main ways to get PostgreSQL 12 up and running, as follows:

• Compiling from sources
• Using a binary package

Binary packages are provided by the PostgreSQL community or the operating system, and using them has the advantage that it can provide you with a PostgreSQL installation very quickly. Moreover, binary packages do not require a compilation toolchain, and therefore are much easier to adopt. Lastly, a binary package adheres to the operating system conventions it has been built for (for instance, on where to place configuration files) and upgrades can be managed by the operating system as well. Since binary packages need to be pre-built from vendors, they could possibly not be the very latest released version.

On the other hand, installing from sources requires a compilation toolchain, as well as much more time and CPU consumption to build the PostgreSQL executables. You have full control over which components will be available in the final product, and can trim and optimize your instance for very high performances and shrink resource consumption to a minimum. In the long term, however, you will be responsible for maintaining the installation and upgrading it in a similar manner.

What to install

PostgreSQL is split across several components to install:

• The PostgreSQL server is the part that can serve your databases to applications and users and is required to store your data.
• The PostgreSQL client is the library and client tool to connect to the database server. It is not required if you don't need to connect to the database on the very same machine, while it is required on client machines.
• The PostgreSQL contrib package is a set of well-known extensions and utilities that can enhance your PostgreSQL experience.
• The PostgreSQL docs is the documentation related to the server and the client.
• PostgreSQL PL/Perl, PL/Python, and PL/Tcl are three components to allow the usage of programming languages— Perl, Python, and Tcl, respectively—directly within the PostgreSQL server.

The recommended set of components is the server, the client, and the contrib modules; these modules will be used across the book. You are free to decide whether to install the other components as you wish.

Installing PostgreSQL 12 from binary packages

In the following sections, you will see how to install PostgreSQL 12 on a few popular Linux and Unix operating systems, namely the following:

• GNU/Linux Debian, Ubuntu, and derivatives
• Fedora
• FreeBSD

It is not possible to provide detailed instructions for every operating system out there, but the concepts presented in the following sections should prove insightful regardless.

Installing PostgreSQL 12 on GNU/Linux Debian, Ubuntu, and derivatives

$ sudo /bin/sh -c '/bin/echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

$ sudo apt-get update
...

$ sudo apt install postgresql-12 postgresql-client-12 postgresql-contrib-12

$ sudo update-rc.d postgresql enable

$ sudo service postgresql start

Installing PostgreSQL 12 on Linux Fedora

Fedora PostgreSQL packages are provided by the PostgreSQL community. In order to allow dnf(8) to find PostgreSQL packages, you need to install the PGDG repository, and then proceed with the installation as a distribution package:

1. Add the repository using the following command:

$ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm

The list of available repositories can be obtained by the PostgreSQL official website at the download page (see the References section).

2. Install the PostgreSQL packages using the following command. Please note that the postgresql12 package installs only the client part of the product, not the server:

$ sudo dnf -y install \
              postgresql12-server  \
              postgresql12-contrib \
              postgresql12-docs    \
              postgresql12
...
Installed:
  postgresql12-contrib-12.1-2PGDG.f30.x86_64        postgresql12-docs-12.1-2PGDG.f30.x86_64        postgresql12-server-12.1-2PGDG.f30.x86_64
  postgresql12-12.1-2PGDG.f30.x86_64

Complete!

3. Configure the system specifying the PGDATA directory and enabling the option to start the service at boot time. In order to specify the PGDATA directory, you need to use systemd(1) to edit an overriding configuration file for the postgresql-12 service:

$ sudo systemctl edit postgresql-12

The preceding command will open your default text editor with an empty file; you can, therefore, set the PGDATA variable as follows and then save and exit the editor to apply changes:

[Service]
Environment=PGDATA=/postgres/12

4. Initialize the database directory; this can be done with a specific Fedora installation command named postgresql-12-setup, as follows:

$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

5. Enable PostgreSQL 12 to start at boot time and launch the server immediately:

$ sudo systemctl enable postgresql-12

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-12.service
                → /usr/lib/systemd/system/postgresql-12.service.

$ sudo systemctl start postgresql-12.service

If your Fedora installation contains the service(8) command, you can also start the service with the following:

$ sudo service postgresql-12 start

Redirecting to /bin/systemctl start postgresql-12.service

Installing PostgreSQL 12 on FreeBSD

PostgreSQL 12 is available on FreeBSD by means of ports and packages. Thanks to the pkg(1) command, it is very easy to install PostgreSQL 12 here, as shown in the following steps:

1. Search for available packages (execute an update command in order to scan for new packages):

$ pkg update
...
$ pkg search postgresql12
pgtcl-postgresql12-2.1.1_2     TCL extension for accessing a PostgreSQL server (PGTCL-NG)
postgresql12-client-12.1       PostgreSQL database (client)
postgresql12-contrib-12.1      The contrib utilities from the PostgreSQL distribution
postgresql12-docs-12.1         The PostgreSQL documentation set
postgresql12-plperl-12.1       Write SQL functions for PostgreSQL using Perl5
postgresql12-plpython-12.1     Module for using Python to write SQL functions
postgresql12-pltcl-12.1        Module for using Tcl to write SQL functions
postgresql12-server-12.1       PostgreSQL is the most advanced open-source database available anywhere

2. Install packages by executing pkg(1) and specify the set of packages you need. Of course, the installation must be executed as a user with administrative privileges, as follows:

$ sudo pkg install  postgresql12-server-12.1  \
                    postgresql12-client-12.1  \
                    postgresql12-contrib-12.1 \
                    postgresql12-docs-12.1
...

3. Initialize the directory to serve the database and to enable the server startup at the machine boot. The minimal parameters to set are postgresql_enable and postgresql_data. For example, to edit (as an administrative user) the /etc/rc.conf file, add the options as follows:

# to enable PostgreSQL at boot time
postgresql_enable="YES"

# PGDATA to use
postgresql_data="/postgres/12"

4. Then, run the following command to create and initialize the directory where PostgreSQL 12 will store the data:

$ sudo /usr/local/etc/rc.d/postgresql initdb

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /postgres/12 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Rome
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /postgres/12 -l logfile start

5. Start the PostgreSQL 12 instance with the following command:

$ sudo service postgresql start

2019-12-09 14:20:50.344 CET [67267] LOG:  starting PostgreSQL 12.1 on amd64-portbld-freebsd12.0, compiled by FreeBSD clang version 6.0.1 (tags/RELEASE_601/final 335540) (based on LLVM 6.0.1), 64-bit
2019-12-09 14:20:50.344 CET [67267] LOG:  listening on IPv6 address "::1", port 5432
2019-12-09 14:20:50.344 CET [67267] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-12-09 14:20:50.345 CET [67267] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-12-09 14:20:50.352 CET [67267] LOG:  ending log output to stderr
2019-12-09 14:20:50.352 CET [67267] HINT:  Future log output will go to log destination "syslog".

If the server cannot be started, for any reason, the command output will print out an error message that should help you to understand what went wrong.

Installing PostgreSQL from sources

Installing PostgreSQL from sources requires downloading a tarball, which is a compressed package with all the source code files, and starting the compilation. Usually, this takes several minutes, depending on the power of the machine and the I/O bandwidth. In order to compile PostgreSQL from source, you will need tar(1), GNU make(1) (at least at version 3.80), and a C compiler compliant to the C99 standard (or higher). Usually, you already have these tools on a Linux or Unix system; otherwise, please refer to your operating system documentation on how to install these tools.

Once you have all the dependencies installed, follow the steps given here to compile and install PostgreSQL:

1. The very first step is to download the PostgreSQL tarball related to the version you want to install, verifying that it is correct. For instance, to download version 12.1, you can do the following:

$ wget https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.bz2
...
$ wget https://ftp.postgresql.org/pub/source/v12.1/postgresql-12.1.tar.bz2.md5
...

If you want to install the available second beta version of PostgreSQL 13, you can repeat the preceding steps with a different tarball URL:

$ wget https://ftp.postgresql.org/pub/source/v13beta2/postgresql-13beta2.tar.bz2
...
$ wget https://ftp.postgresql.org/pub/source/v13beta2/postgresql-13beta2.tar.bz2.md5
...

2. Before starting the compilation, check that the downloaded tarball is intact:

$ md5sum --check postgresql-12.1.tar.bz2.md5
postgresql-12.1.tar.bz2: OK

3. Once you are sure that the downloaded tarball is not corrupt, you can extract its content and start the compilation (please consider that the extracted archive will take around 200 MB of disk space, and the compilation will add some more extra space):

$ tar xjvf postgresql-12.1.tar.bz2
$ cd postgresql-12.1
$ ./configure --prefix=/usr/local
...
$ make && sudo make install
...
PostgreSQL installation complete.

If you want or need the systemd(1) service file, add the --with-systemd option to the configure line.

4. Once the database has been installed, you need to create a user to run the database with, usually named postgres, and initialize the database directory:

$ sudo useradd postgres
$ sudo mkdir /postgres/12
$ sudo chown postgres:postgres /postgres/12
$ /usr/local/bin/initdb -D /postgres/12
...

Installing PostgreSQL via pgenv

pgenv is a nice and small tool that allows you to download and manage several instances of different versions of PostgreSQL on the same machine. The idea behind pgenv is to let you explore different PostgreSQL versions—for instance, to test your application against different major versions. pgenv does not aim to be an enterprise-class tool to manage in-production instances; rather, it is a tool to let developers and DBAs experiment with different versions of PostgreSQL and keep them under control easily.

Of course, being an external tool, pgenv must be installed before it can be used. The installation, however, is very simple, since the application is made by a single Bash script:

1. The fastest way to get pgenv installed is to clone the GitHub repository and set the PATH environment variable to point to the executable directory, as follows:

$ git clone https://github.com/theory/pgenv

Cloning into 'pgenv'...
remote: Enumerating objects: 79, done.
remote: Counting objects: 100% (79/79), done.
remote: Compressing objects: 100% (34/34), done.
remote: Total 642 (delta 34), reused 72 (delta 29), pack-reused 563
Receiving objects: 100% (642/642), 173.78 KiB | 801.00 KiB/s, done.
Resolving deltas: 100% (300/300), done.

$ export PATH=$PATH:./pgenv/bin

2. Now, the pgenv command is at your fingertips, and you can run the command to get a help prompt and see the available commands:

$ pgenv
Using PGENV_ROOT /home/luca/git/pgenv
Usage: pgenv <command> [<args>]

The pgenv commands are:
    use        Set and start the current PostgreSQL version
    clear      Stop and unset the current PostgreSQL version
    start      Start the current PostgreSQL server
    stop       Stop the current PostgreSQL server
    restart    Restart the current PostgreSQL server
    build      Build a specific version of PostgreSQL
    rebuild    Re-build a specific version of PostgreSQL
    remove     Remove a specific version of PostgreSQL
    version    Show the current PostgreSQL version
    current    Same as 'version'
    versions   List all PostgreSQL versions available to pgenv
    help       Show this usage statement and command summary
    available  Show which versions can be downloaded
    check      Check all program dependencies
    config     View, edit, delete the program configuration

For full documentation, see: https://github.com/theory/pgenv#readme

This is 'pgenv' version [72faf1a]

The idea behind pgenv is pretty simple: it is a tool to automate the "boring" stuff—that is, downloading, compiling, installing, and start/stopping a cluster. In order to let pgenv manage a specific instance, you have to "use" it. When you use an instance, pgenv detects whether the instance has been initialized or not, and in the latter case, it does the initialization for you.

3. In order to install versions 12.0 and 12.1 of PostgreSQL, you simply have to run the following commands:

$ pgenv build 12.0
...
PostgreSQL 12.0 built


$ pgenv build 12.1
...

PostgreSQL 12.1 built

The preceding commands will download and compile the two versions of PostgreSQL, and the time required for the operations to complete depends on the power and speed of the machine you are running on.

4. After that, you can decide which instance to start with the use command:

$ pgenv use 12.0
...
server started
PostgreSQL 12.0 started
Logging to /home/luca/git/pgenv/pgsql/data/server.log

pgenv is smart enough to see whether the instance you are starting has been already initialized, or it will initialize (only the first time) for you.

5. Once you have started the instance, you can connect to it with any client tool you like, most notably psql (you will learn more about psql in the next chapters):

$ psql -U postgres -h localhost template1
psql (12.1 (Ubuntu 12.1-1.pgdg18.04+1), server 12.0)
Type "help" for help.

template1=#

6. If you need to stop and change the PostgreSQL version to use, you can issue a stop command followed by a use command with the targeted version. For instance, to stop running the 12.0 instance and start a 12.1 instance, you can use the following:

$ pgenv stop
...
PostgreSQL 12.0 stopped

$ pgenv use 12.1
...
PostgreSQL 12.1 started
Logging to /home/luca/git/pgenv/pgsql/data/server.log

7. pgenv allows you to see which instances are currently installed and which one is currently active—that is, "in use"—and this does not mean it is running:

$ pgenv versions
Using PGENV_ROOT /home/luca/git/pgenv
      11.5      pgsql-11.5
      11beta4    pgsql-11beta4
      12.0      pgsql-12.0
  *   12.1      pgsql-12.1

If you are searching for a quick way to test and run different PostgreSQL versions on the same machine, pgenv is a good tool.

Installing PostgreSQL 13 beta 2 using pgenv is really simple—just repeat the preceding process, changing the version number of the cluster you want to build:

$ pgenv build 13beta2
...
PostgreSQL 13beta2 built

$ pgenv use 13beta1
...
server started
PostgreSQL 13beta1 started

$ psql -U postgres -c "SELECT version();" template1
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 13beta2 on x86_64-unknown-freebsd12.1, compiled by gcc (FreeBSD Ports Collection) 9.2.0, 64-bit
(1 row)

You now know how to use your preferred method to install the version of PostgreSQL that you need.

This chapter has introduced you to PostgreSQL, its history, and its main features. You have learned about PostgreSQL terminology, as well as how to install a cluster on Unix-like operating systems, such as GNU/Linux Debian, Fedora, and FreeBSD, as well as installing the tool from various sources.

In the following chapters, you will start using this great database engine and learn details about every main single feature it provides.

• PostgreSQL 12 release note: https://www.postgresql.org/docs/12/release-12.html
• Upgrading documentation: https://www.postgresql.org/docs/current/upgrading.html
• PostgreSQL version policy: https://www.postgresql.org/support/versioning/
• PostgreSQL initdb official documentation: https://www.postgresql.org/docs/12/app-initdb.html
• PostgreSQL pg_ctl official documentation: https://www.postgresql.org/docs/12/app-pg-ctl.html
• pgenv GitHub repository and documentation: https://github.com/theory/pgenv