How to Install PostgreSQL 14 on Ubuntu 20.04 LTS Focal Fossa

The installation process for PostgreSQL on Ubuntu is, like a lot of things in the Linux world, less than intuitive for new users. As much for my own benefit as anyone else's, now I am going to walk through the steps to getting PostgreSQL installed and configured on a Linux box.

Note: if you are an experienced Linux user, or a PostgreSQL DBA, this is not the post for you, even though your remarks and constructive criticism are very welcome – in case you see something either incorrect or missing, please do let me know inside the comments below.

So why Postgres?

PostgreSQL is a amazing database platform. PostgreSQL is free, cross-platform, and gives an high-quality function set which, in my mind, exceeds the ones of its similar platform in the relational database area.

PostgreSQL offers all of the (mostly) standard-compliant SQL/relational database function you'll anticipate, plus a bunch of thrilling and innovative features. We’ll take a tour of PostgreSQL in every other posts, but first, let’s get the thing installed and running.

Installation steps for PostgreSQL 14 on Ubuntu 20.04 LTS Focal Fossa

By the time of this writing, the most up-to-date version of PostgreSQL is version 14.1, which was introduced at November 11, 2021. However, the 14.1 release is not available directly using the Advanced Packaging Tool (APT) or the Ubuntu Software Manager.

Fortunately, the PostgreSQL Global Development Group (PGDG) maintain an apt repository of PostgreSQL packages for Debian and Ubuntu-derived Linux distributions, located at http://apt.postgresql.org/pub/repos/apt/

Before we can install PostgreSQL, we have to add the package source for the distribution we are currently using. In case, I'm using Ubuntu 20.04 Focal Fossa. If your distribution is different than mine, adjust as necessary.

What's currently supported:

• Debian 9 (stretch), 10 (buster), 11 (bullseye), 12 (bookworm), and unstable (sid)
• Ubuntu 18.04 (bionic), 20.04 (focal), 21.04 (hirsute, amd64 only), 21.10 (impish, amd64 only)
• Architectures: amd64 (64-bit x86), i386 (32-bit x86, being phased out), arm64 (64-bit ARM), ppc64el (little-endian 64-bit POWER)
• PostgreSQL 9.6, 10, 11, 12, 13, 14, 15 devel
• Server extensions such as Slony-I, various PL languages, and datatypes
• Applications like omnidb, pgbouncer, and pgpool-II

Add the PostgreSQL Package Source

We need to create a sources file reflecting the proper PostgreSQL source for our specific distribution. As noted above, in my case I'll need the source appropriate for the "Focal" release of Ubuntu. We can accomplish this from the terminal to add the file (make sure to use sudo in all of the following steps):

Add the PGDG APT source file

Create a file at /etc/apt/sources.list.d/pgdg.list with the following command:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main" > \
/etc/apt/sources.list.d/pgdg.list'

image goes here

Add the PostgreSQL Package Repository Key

Before importing package repository key, make sure all dependencies are installed.

$ sudo apt install curl ca-certificates gnupg

Next, add the package repository key:

$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null

Update, Upgrade, and Install PostgreSQL

Next step is updating system package source. Once this task is done, we proceed with upgrade packages to the latest versions.

$ sudo apt update && apt upgrade

Please note that this can be a long process. Also, you may be prompted at several points to make some choices about configuration items. Specifically, you may informed that this that or the other configuration file has been changed, and asked if you want to keep your original version, or replace with the package maintainer’s version. Select "Y" to accept the package maintainer’s version in these cases.

Finally, installing PostgreSQL 14:

$ sudo apt install postgresql-14

Press "Y" and then "Enter". It will then download all necessary files and continue with installation. By default, the PostgreSQL service is started automatically after the installation. It will also start automatically when the system boot.

Once the installation process finished, we can verify that we've installed PostgreSQL correctly. First we'll check if the service is running. Then we can check what version of PostgreSQL server is running.

$ sudo systemctl status postgresql

We can use the psql tool to connect with the PostgreSQL database server and printing its version:

$ psql --version

The output will be something like this:

psql (PostgreSQL) 14.1 (Ubuntu 14.1-2.pgdg20.04+1)

That’s it. You've successfully installed PostgreSQL to your system and you can start using it.

Configuring Postgres for Use

The postgres user

While PostgreSQL become installed, a system user account named postgres was also created with an identical user account in postgres. By default, the postgres user account isn't configured with a password, so it isn't viable to log into the server the use of the postgres user account without first creating a password for it. This postgres account has an all-access pass on your PostgreSQL database server, permission-wise. The postgres user account has similarities to the sa account in SQL server.

The postgres database

PostgreSQL is installed with a default database postgres. For the most part, we use the postgres database for administration functions, and create new databases on the PostgreSQL server to suit our needs.

The psql Command Line Utility

PostgreSQL consists of psql, a command line application for managing your databases and server. While a GUI-based software such as pgadmin4 is often  less complicated to use in the daily task, the command line utilty psql is always handy. psql gives total control of your postgres system from the terminal, together with the ability to execute SQL queries.

We will use psql to perform our preliminary configuration and to create an initial database super user.

Create super user account

In this step we will super-user account to deals with our database in the daily task.

To do this, we will get access to the postgres account through your machine root user. Then we'll use that postgres account to create a brand new super-user account for your PostgreSQL installation which can be regulated more efficiently. As an example we will use ethan as our new PostgreSQL super-user account.

$ sudo su -
[sudo] password for adjie: 
root@ubuntu-20:~# su - postgres
postgres@ubuntu-20:~$ psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# CREATE USER ethan
postgres-# WITH SUPERUSER CREATEDB CREATEROLE
postgres-# PASSWORD 'pleasechangeme';

Notice in the above we can enter multiple lines of command in SQL shell. The SQL is not executed until semi-colon followed enter is found. Which means, the semi-colon will make the shell execute entered commands. Once it detects semi-colon, the command will be executed and it will respond with "CREATE ROLE" message and showing postgresql shell again.

CREATE ROLE
postgres=#

Login using our newly created account

Let's verify that everything is working correctly. Try to log in with psql using our new super-user account and create a quick test database:

$ psql -h localhost -U ethan postgres
Password for user ethan: 
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#  

Note in the above terminal session, we specified the postgres default database when we logged in, since there aren' t yet any other databases to connect to.

Create test database

Next test is to create test database test_database using our new super-user account:

postgres=# CREATE DATABASE test_database WITH OWNER ethan;
CREATE DATABASE
postgres=# \connect test_database;
You are now connected to database "test_database" as user "ethan".
test_database=# 

If everything works as shown, then congratulations. Now you have a working installation of PostgreSQL 14.1 on Ubuntu 20.04 Focal Fossa.

Final Words

I hope that you now know how to install PostgreSQL 14.1 on Ubuntu 20.04 Focal Fossa. If you run into any issues or have any feedback feel free to drop a comment below.

Reference

https://wiki.postgresql.org/wiki/Apt