How to install PostgreSQL 14 on Ubuntu 22.04|20.04|21.10

In this article, we are going to learn how to install and get PostgreSQL up and running on an Ubuntu server. PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

Step 1: Installing PostgreSQL

First, update your server packages by issuing the following command.

$ sudo apt update 
$ sudo apt upgrade -y

Create file repository

Use the following script to create a file repository for PostgreSQL.

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

After you have added the following script, now you can add GPG key.

Import GPG Key

Import GPG key pair in order to for it to be signed correctly. Use the following command to import the key.

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

You will get an ok as output meaning you have successfully added the GPG key.

Next we will run the updates so that introduced changes can take effect immediately.

$ sudo apt update

Now we can install the latest version of PostgreSQL

Install PostgreSQL 14 on Ubuntu 22.04|20.04

Use the following command to install a specific version of PostgreSQL

$ sudo apt install postgresql-14 -y

The sample output will be as follows

....
Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-14 (14.1-2.pgdg20.04+1) ...
Creating new PostgreSQL cluster 14/main ...
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions
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.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/14/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for systemd (245.4-4ubuntu3.13) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.2) ...

To switch to the Postgres account use the following command.

$ sudo -i -u postgres

You can access the Postgres account by using the following command.

$ psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# 

Step 2: Creating a new role

Log in to Postgres account in order to create a new role

$ createuser --interactive
Enter name of role to add: nextgentips
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

Step 3: Creating a new Database

Postgres authentication system assumes that for any role used to log in, that role will have a database with the same name.

For example, we created a user with the name user, this role will attempt to connect to the database which is called user by default. Use creatdb to create an appropriate database.

To access the PostgreSQL command line, you need a Linux account with the same name as the role and database name. If there is no such user, you can create one using the command:

$ sudo adduser nextgentips

Once this new account is available, you can either switch over and connect to the database by typing:

$ sudo -i -u nextgentips

$ psql

To connect to a different database, use the following command.

$ psql -d postgres

Conclusion

PostgreSQL is now installed in your system. You can now explore more by reading the PostgreSQL documentation.