How to install PostgreSQL 13 on Ubuntu 20.04

Introduction

PostgreSQL, commonly known as Postgres, is an advanced open-source relational database management system (RDBMS) that emphasizes extensibility and SQL compliance. It's a powerful tool for developers and system administrators looking for a robust database solution. This guide will walk you through the process of installing PostgreSQL on an Ubuntu 20.04 system.

Prerequisites

Before proceeding with the installation, ensure that your system is up-to-date by running the following commands in the terminal:

sudo apt update
sudo apt upgrade

These commands refresh your system's package index and upgrade all your installed packages to their latest versions.

Step 1: Installing PostgreSQL

Ubuntu's default repositories contain PostgreSQL packages, which can be installed using the apt packaging system. To install PostgreSQL along with the postgresql-contrib package that adds some additional utilities and functionality, execute the following commands:

sudo apt update
sudo apt install postgresql postgresql-contrib

The postgresql-contrib package provides several additional features that are not included in the main PostgreSQL distribution, such as additional data types, functions, operators, and index types.

  libwind0-heimdal libxml2 libxslt1.1 locales logrotate netbase openssl perl perl-modules-5.30 postgresql postgresql-12 postgresql-client-12 postgresql-client-common
  postgresql-common postgresql-contrib readline-common ssl-cert sysstat tzdata ucf xz-utils
0 upgraded, 55 newly installed, 0 to remove and 0 not upgraded.
Need to get 52.0 MB of archives.
After this operation, 230 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

After running the installation command, you will be prompted to confirm the installation. Press Y and then Enter to proceed.

Please select the geographic area in which you live. Subsequent configuration questions will narrow this down by presenting a list of cities, representing the time
zones in which they are located.

  1. Africa  2. America  3. Antarctica  4. Australia  5. Arctic  6. Asia  7. Atlantic  8. Europe  9. Indian  10. Pacific  11. SystemV  12. US  13. Etc
Geographic area: 

After the installation is complete, you will be prompted to select the geographic area in which you live. This will help set the time zone for PostgreSQL. Choose the appropriate geographic area number or select 0 for none of the above.

Step 2: Starting and Verifying the Installation

After installing PostgreSQL, the PostgreSQL service should start automatically. You can verify this by running the following command:

sudo systemctl status postgresql

If PostgreSQL is running, you should see output similar to the following:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2021-06-29 14:45:45 UTC; 1min 30s ago
   Main PID: 1234 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2281)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

If PostgreSQL is not running, you can start it using the following command:

sudo systemctl start postgresql

To enable PostgreSQL to start automatically at boot time, run:

sudo systemctl enable postgresql

You can also check the version of PostgreSQL that was installed by running:

psql --version

This will display the PostgreSQL version, such as:

psql (PostgreSQL) 13.3 (Ubuntu 13.3-1.pgdg20.04+1)

This confirms that PostgreSQL has been successfully installed on your Ubuntu 20.04 system.

Step 3: Using PostgreSQL Roles and Databases

PostgreSQL uses the concept of roles to handle authentication and authorization. By default, PostgreSQL creates a new role called postgres that has superuser privileges. To switch to the postgres user, use the following command:

sudo -i -u postgres

You will now be logged in as the postgres user. To access the PostgreSQL prompt, use the psql command:

psql

You should now see the PostgreSQL prompt, which looks like this:

psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
Type "help" for help.

postgres=#

You can now interact with the PostgreSQL database using SQL commands. To exit the PostgreSQL prompt, type:

\q

This will return you to the regular command line.

Step 4: Creating a New User and Database

To create a new user in PostgreSQL, you can use the createuser command. For example, to create a new user named myuser, run

createuser --interactive

You will be prompted to answer a few questions to set up the new user. You can choose to make the user a superuser, allow the user to create new databases, and more.

To create a new database in PostgreSQL, you can use the createdb command. For example, to create a database named mydatabase, run

createdb mydatabase

You can also create a new user and a new database at the same time by using the createuser and createdb commands together. For example, to create a new user named myuser and a new database named mydatabase, run

createuser myuser --createdb
createdb mydatabase --owner=myuser

This will create a new user with the ability to create databases and a new database owned by that user.

After creating a new user and database, you can grant the user specific privileges on the database. For example, to grant all privileges on the mydatabase database to the myuser user, you can run the following command:

psql

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

This grants the user myuser all privileges on the mydatabase database.

You can now connect to the mydatabase database using the myuser user by running:

psql -d mydatabase -U myuser

This will connect you to the mydatabase database as the myuser user.

To exit the PostgreSQL prompt, type:

\q

Step 5: Accessing PostgreSQL Remotely

By default, PostgreSQL is configured to be accessed only from the local machine. If you want to access PostgreSQL from a remote server, you need to make some changes to the configuration file.

To allow remote access, you need to modify the pg_hba.conf file. This file is located in the pgdata directory, which is typically /etc/postgresql/13/main/. Open the file using a text editor:

sudo nano /etc/postgresql/13/main/pg_hba.conf

Find the line that looks like this:

# "local" is for Unix domain socket connections only
local   all             all                                     peer

And add the following line below it:

host    all             all

This line allows all users to connect to all databases from all IP addresses. You can modify it to be more restrictive based on your requirements.

After making the changes, save the file and restart the PostgreSQL service:

sudo systemctl restart postgresql.service

You should now be able to access PostgreSQL remotely.

Conclusion

You have now successfully installed PostgreSQL on your Ubuntu 20.04 system and are ready to start developing your applications with one of the most advanced open-source databases available. For more detailed information on managing your PostgreSQL installation, refer to the official documentation.

Remember, database management is a critical skill in today's data-driven world, and mastering PostgreSQL is a valuable asset for any developer or system administrator.

Happy coding!