Install PostgreSQL Linux

Summary: in this tutorial, you will learn how to download and install PostgreSQL on Linux.

Most Linux platforms such as Debian, Red Hat / CentOS, SUSE, and Ubuntu have PostgreSQL integrated with their package management.

It is recommended that you install PostgreSQL this way since it ensures a proper integration with the operating system including automatic patching and other update management functionality.

To download and install PostgreSQL on Linux, you visit the download page and select your Linux distribution.

In this tutorial, we’ll show you how to install PostgreSQL on Ubuntu 20.04.

Install PostgreSQL on Ubuntu

Once visiting the PostgreSQL download for Ubuntu page, you find the script that allows you to install PostgreSQL on Ubuntu. You need to execute them one by one

First, execute the following command to create the file repository configuration:

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

Second, import the repository signing key:

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Code language: Shell Session (shell)

Third, update the package list:

$ sudo apt-get updateCode language: Shell Session (shell)

Finally, install the latest version of PostgreSQL:

$ sudo apt-get install postgresqlCode language: Shell Session (shell)

If you want to install a specific version, you can use postgresql-version instead of postgresql. For example, to install PostgreSQL version 12, you use the following command:

$ sudo apt-get install postgresql-12Code language: Shell Session (shell)

It will take few minutes to download and install the PostgreSQL.

Connect to the PostgreSQL database server via psql

In PostgreSQL, a user account is referred to as a role. By default, PostgreSQL uses ident authentication.

It means that PostgreSQL will associate its roles with the system accounts of Linux. If a role exists in PostgreSQL, the same Linux user account with the same name is able to log in as that role.

When you installed PostgreSQL, the installation process created a user account called postgres associated with the default postgres role.

To connect to PostgreSQL using the postgres role, you switch over to the postgres account on your server by typing:

$ sudo -i -u postgresCode language: Shell Session (shell)

It’ll prompt for the password of the current user. You need to provide the password and hit the Enter keyboard.

Then, you can access the PostgreSQL using the psql by typing the following command:

$ psqlCode language: Shell Session (shell)

You’ll access the postgres prompt like this:

postgres=#Code language: Shell Session (shell)

From here, you can interact with the PostgreSQL like issuing a query.

To quit the PostgreSQL prompt, you run the following command:

postgres=# \qCode language: Shell Session (shell)

This above command will bring you back to the postgres Linux command prompt.

postgres@ubuntu-dev:~$Code language: Shell Session (shell)

To return to your regular system user, you execute the exit command like this:

postgres@ubuntu-dev:~$ exitCode language: Shell Session (shell)

Load the sample database

To load the sample database into the PostgreSQL database server, you follow these steps:

First, switch over the postgres account using the following command:

$ sudo -i -u postgresCode language: Shell Session (shell)

It’ll prompt you for the password of the current user. You need to type the password of the current user and press the Enter keyboard.

Second, download the sample database using the curl tool:

$ curl -O https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zipCode language: Shell Session (shell)

Third, unzip the dvdrental.zip file to get the dvdrental.tar file:

$ unzip dvdrental.zipCode language: Shell Session (shell)

Fourth, access the PostgreSQL using the psql tool:

$ psqlCode language: Shell Session (shell)

Fifth, create the dvdrental database using the CREATE DATABASE statement:

postgres=# create database dvdrental;Code language: Shell Session (shell)

Sixth, quit the psql by using the \q command:

postgres=# \qCode language: Shell Session (shell)

Seventh, use the pg_restore tool to restore the dvdrental database:

$ pg_restore --dbname=dvdrental --verbose dvdrental.tarCode language: Shell Session (shell)

Eighth, access PostgreSQL database server again using psql:

$ psqlCode language: Shell Session (shell)

Ninth, switch to the dvdental database:

postgres=# \c dvdrentalCode language: Shell Session (shell)

Now, you’re connected to the dvdrental database:

dvdrental=#Code language: Shell Session (shell)

Finally, enter the following command to get the number of films in the film table:

dvdrental=# select count(*) from film;Code language: Shell Session (shell)

Here is the output:

count
-------
1000
(1 row)Code language: Shell Session (shell)

Congratulations! you have successfully installed PostgreSQL on Ubuntu, connect to PostgreSQL database server using psql, and load the sample database.