Atlantic.Net Blog

How to Install and Configure Postgres 14 on Ubuntu 20.04

PostgreSQL is a popular open-source object-relational database system. It is very popular due to its reliability, robustness of its features, and performance. It has many advanced features that allow developers to build fault-tolerant and complex applications. At the time of writing this tutorial, the latest available version is PostgreSQL 14.

In this post, we will explain how to install and configure PostgreSQL 14 on Ubuntu 20.04.

Prerequisites

  • A server running Ubuntu 20.04 on the Atlantic.Net Cloud Platform
  • A root password configured on your server

Step 1 – Create Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing Ubuntu 20.04 as the operating system with at least 2GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged in to your server, run the following command to update your base system with the latest available packages.

apt-get update -y

Install the following prerequisites:

apt install gnupg gnupg2 gnupg1 -y

Step 2 – Install PostgreSQL 14

The latest version of PostgreSQL is not included in the Ubuntu default repository, so you will need to add the PostgreSQL official repository to the APT.

You can add it with the following command:

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

Next, download and add the PostgreSQL GPG key using the following command:

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

Next, update the repository cache and install PostgreSQL 14 using the following command:

apt-get update -y
apt-get install postgresql-14 -y

After installing PostgreSQL, check the status of the PostgreSQL with the following command:

systemctl status postgresql

You will get the following output:

‚óŹ postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2021-11-26 04:57:30 UTC; 43s ago
   Main PID: 17268 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2353)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Nov 26 04:57:30 ubuntu2004 systemd[1]: Starting PostgreSQL RDBMS...
Nov 26 04:57:30 ubuntu2004 systemd[1]: Finished PostgreSQL RDBMS.

You can also verify the PostgreSQL version using the command below:

sudo -u postgres psql -c "SELECT version();"

You will get the following output:

                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

Step 3 – Create a Database and User in PostgreSQL

First, you can connect to PostgreSQL with the following command:

su - postgres
psql

You will get the following shell:

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

postgres=# 

Next, create a superuser named root with the following command:

CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'securepassword';

Run the following command to verify the superuser:

\du

You will get the following output:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser, Create role, Create DB                          | {}

To create a database named dbname, run:

create database dbname;

To create a new user named dbuser, run:

create user dbuser with encrypted password 'dbpassword';

To grant all privileges to the dbname to dbuser, run:

grant all privileges on database dbname to dbuser;

To list all databases, run:

\l

You will get the following output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 dbname    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | dbuser=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

To exit from the PostgreSQL shell, run:

\q
exit

Step 4 – Configure PostgreSQL for Remote Access

By default, PostgreSQL is configured for local access only. If your application server and database servers are hosted on a different server, then you will need to configure PostgreSQL for remote access. You can configure it by editing the file pg_hba.conf:

nano /etc/postgresql/14/main/pg_hba.conf

Find the following line:

local   all             all                                     peer

And replace it with the following line:

local   all             all                                     trust

Next, add the following line:

host    all             all             0.0.0.0/0                md5

Save and close the file when you have finished.

Next, you will also need to edit the PostgreSQL main configuration file and change the listening port:

nano /etc/postgresql/14/main/postgresql.conf

Change the following line:

listen_addresses='*'

Save and close the file, then restart the PostgreSQL service to apply the changes:

systemctl restart postgresql

You can now check the PostgreSQL listening port using the following command:

ss -antpl | grep 5432

You will get the following output:

LISTEN    0         244                0.0.0.0:5432             0.0.0.0:*        users:(("postgres",pid=19030,fd=5))                                            
LISTEN    0         244                   [::]:5432                [::]:*        users:(("postgres",pid=19030,fd=6)) 

Now, go to the remote system and connect to the PostgreSQL instance using the following command:

psql 'postgres://root:[email protected]:5432/postgres?sslmode=disable'

If everything is fine, you will get the following shell:

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

postgres=# 

Conclusion

In the above post, you learned how to install and configure PostgreSQL 14 on Ubuntu 20.04. You can now start developing high-performance and complex applications with the PostgreSQL database backend. Try it on dedicated hosting from Atlantic.Net!

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G3.2GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year