Installing PostgreSQL 12 or higher

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

The PostgreSQL Global Developers Group (PGDG) provides binary packages for Debian and its derivatives, including the Ubuntu operating system family. In order to use the PGDG repositories, it is required for you to first install the source and signature of the repository:

1. To import the repository on an Ubuntu 19.10 disc, you need to run the following commands:

$ 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
...

This will ensure the repository sources for your operating system are up to date so that you can install the PostgreSQL 12 packages. In the Debian/Ubuntu repositories, the packages are named after the component and the version, and the postgresql-12 package includes the server and the contrib module.

2. Install the modules needed:

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

Debian and Ubuntu provide their own command to control the cluster, pg_ctlcluster(1). The rationale for that is that on a Debian/Ubuntu operating system, every PostgreSQL version is installed in its own directory with separate configuration files, so there is a way to run different versions concurrently and manage them via the operating system. For example, configuration files are under the /etc/postgresql/12/main directory, while the data directory is set by default to /var/lib/postgresql/12/main.

3. Enable PostgreSQL 12 at boot time by executing the following command:

$ sudo update-rc.d postgresql enable

4. Start the cluster immediately using the service(1) command:

$ sudo service postgresql start

You have thus installed PostgreSQL on GNU/Linux Debian, Ubuntu, and derivatives.

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

You have now successfully installed PostgreSQL 12 on Linux Fedora.

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.