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
Before installing Postgres in Ubuntu, we need to follow some basic requirements.
- Installed Ubuntu 20.04
- Sudo privileges in order to install packages.
- 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
After installation you can check PostgreSQL status by running following command.
$ sudo systemctl status postgresql.service
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,
To check the PostgreSQL version run the following command:
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.
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 = '*'
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
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
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.