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
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
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" > \
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
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
account to create a brand new super-user account for your PostgreSQL
installation which can be regulated more efficiently. As an example we
ethan as our new PostgreSQL super-user account.
$ sudo su -
[sudo] password for adjie:
root@ubuntu-20:~# su - postgres
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.
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.
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;
postgres=# \connect test_database;
You are now connected to database "test_database" as user "ethan".
If everything works as shown, then congratulations. Now you have a working installation of PostgreSQL 14.1 on Ubuntu 20.04 Focal Fossa.
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.