Home » Ubuntu » Install PostgreSQL on Ubuntu 20.04

Install PostgreSQL on Ubuntu 20.04

PostgreSQL is RDBMS ( relational database management system ) that is used to store, retrieve data using standard query language SQL. It is also known as Postgres which is provided free with an open-source license. PostgreSQL supports JSON, XML, and materialized views where other database management software doesn’t.

In this tutorial we learn how to install PostgreSQL on Ubuntu 20.04.

Related Read: How to Install PhpPgAdmin on Ubuntu 20.04

Prerequisites

Before installing Postgres in Ubuntu, we need to follow some basic requirements.

  1. Installed Ubuntu 20.04
  2. Sudo privileges in order to install packages.
  3. Proper Internet connection.

Install PostgreSQL on Ubuntu

PostgreSQL is available in the Ubuntu repositories by default. Simply use apt command to install PostgreSQL. The most common packages installed are PostgreSQL server, client, pgadmin, libraries, and headers.

To install a specific version you can use PostgreSQL Apt repository.

To go along with the installation follow the below procedures.

First, make sure your system in up to date by executing following command.

$ sudo apt update

Install Postgres package followed by -contrib package which adds additional utilities and functionality.

$ sudo apt install postgresql postgresql-contrib

After complete installation of PostgreSQL, start PostgreSQL service.

$ sudo systemctl start postgresql.service

Also enable the PostgreSQL service to start automatically at system boot.

$ sudo systemctl enable postgresql.service
Starting and enabling postgres service

After installation you can check PostgreSQL status by running following command.

$ sudo systemctl status postgresql.service
Postgres service status

By default, a Postgres user is created during installation. To access the Postgres shell first switch to Postgres user.

$ sudo su - postgres

Now enter psql command,

$ psql
Executing Postgres Shell

To check the PostgreSQL version run the following command:

psql --version

or

psql -V

Creating role in PostgreSQL

For authorization and authentication, Postgres uses the concept “roles” which is similar to Unix-style accounts in some ways. During installation, Postgres is set up in such a way that uses ident authentication. Ident authentication associates Postgres role with matching Linux/Unix system accounts. Only superusers and roles with “CREATEROLE” privilege are responsible to create new roles.

To create a new role named john, type following command in PostgreSQL shell.

postgres=# createuser john

Creating Database in PostgreSQL

Creating a new database requires a PostgreSQL database shell (psql) program. login to your psql shell and type the following command to create a user and assign a password. Here, I'm going to create the user name jacky with the password mystrongpassword. You can create your own.

postgres=# CREATE USER jacky WITH PASSWORD 'mystrongpassword';

Create database (eg. gallary) using following command,

postgres=# CREATE DATABASE gallery;

Grant privileges using the database just created. Here I'm going to provide all privileges on the database gallery to use jacky.

postgres=# GRANT ALL PRIVILEGES ON DATABASE gallery to jacky;

Finally to exit from PostgreSQL Shell, type \q and press enter.

postgres=# \q
Creating user and database

Enabling Remote Access in PostgreSQL

PostgreSQL by default listens on local loopback interface 127.0.0.1. If you want to access databases from other remote clients then you need to configure PostgreSQL to listen on the different network interfaces. To configure PostgreSQL for different interfaces open PostgreSQL’s configuration file by using nano or other text editing tools.

$ sudo vi /etc/postgresql/12/main/postgresql.conf

In this configuration file search for listen_addresses under “CONNECTIONS AND AUTHENTICATION ” section. Uncomment the line and change localhost to ‘*’. This gives instruction to PostgreSQL to listen on all network interfaces for incoming connections.

listen_addresses = '*'
Enabling remote connection in postgres

Save your configuration and restart PostgreSQL Server for the changes reflect to take place.

$ sudo systemctl restart postgresql.service

Now you should be able to see that PostgreSQL is listening on different interface. You can execute the following command to confirm it.

$ ss -ltn
Postgres port listening status

If you have enabled UFW firewall in the server, you need to open port 5432 for incoming TCP connections by executing the following command.

$ sudo ufw allow 5432/tcp

Also verify UFW firewall rule by executing the following command.

$ sudo ufw status verbose
Postgres ufw configuration

Conclusion

In this tutorial, we learned how to install PostgreSQL Server on Ubuntu 20.04 including some initial configurations. In addition to this, we also learned how to bind PostgreSQL to different interfaces to accept connections from a remote client.

Leave a Comment