How to Install PostgreSQL on Ubuntu 20.04 Server Tutorial (Step by Step)

How to Install PostgreSQL on Ubuntu 20.04 Server. In this article we will introduce what PostgreSQL is with the main pros and features and we will move onto installation guide. Let’s start. 

What is PostgreSQL

PostgreSQL is a reliable and robust, open source object relational database that supports JSON for extensibility and SQL command language to decipher complex queries. Most companies prefer the popular, free relational database management system for its robust features and excellent performance.

In 1996, to show the support towards SQL, the project name of the RDBMS was changed from Postgres to PostgreSQL. No specific corporation or private entity controls the database. It is a free open source developed by a team of volunteers.

Backed by 20+ years of development by an open source community, PostgreSQL server has become a highly stable database with various advanced features. Today, companies use the RDBMS to solve various complex analytical processes, manage dynamic websites and web applications and geographic information systems (GIS). It supports multiple programming languages like Python, C#, Ruby, Java, Perl, Go, etc., and is compatible with most operating systems, including Windows, Mac OS X, all Linux and Unix distributions.

In PostgreSQL, users have full power to define their data types, design custom functions and write codes into other programming languages. Another benefit of choosing PostgreSQL over other relational database management systems is it is easy to upgrade or extend the database as it is open source.

Further, it has in built user defined data types that support complex structures and enables administrators to safeguard data integrity and develop fault tolerant environments. The database comes with a large number of functions and features that aid programmers in creating easy to use applications and building a secure environment. PostgreSQL manages data well without thinking about the size of the database.

It comprises various features that make it a top choice on comparing with other open source SQL databases available in the market, like Firebird, MySQL, and MariaDB.

Features of PostgreSQL

PostgreSQL supports various features that enable developers to easily create applications and manage datasets while maintaining data integrity and a secure environment.

 

PostgreSQL is ACID compliant, it has the capacity for fault tolerance and it has a Table inheritance.

Please take a look at few other features of PostgreSQL:

A free to download RDBMS

Being open source, developers have the advantage to download PostgreSQL from its official website. Also, they have full authority to use, modify and implement the database as per the business requirement. Released under the OSI approved PostgreSQL License, developers are charged no fee for the same even if used for commercial software products.

MVCC model

Supports Various Operating Systems

Another feature that convinces developers to choose PostgreSQL over other RDBMS is it runs on all major operating systems, including macOS X, UNIX, Microsoft Windows, Linux, etc.

Supports multiple programming languages

PostgreSQL is highly compatible with all programming languages, such as Python, Perl, Ruby, Go, C++, Java, etc. There are multiple procedural languages available for developers, and one can easily choose one that best fits their specific requirements with PostgreSQL.

Community Driven and Reliable

PostgreSQL is highly reliable as it supports WAL, Tablespaces, different replications, and PITR (Point in time recovery) features. It also has a strong community where multiple companies and users contribute to the project and ensure no bugs are left.

Highly Secure

PostgreSQL supports multiple Authentications including Generic Security Service Application Program Interface (GSSAPI), Lightweight Directory Access Protocol (LDAP), Security Support Provider Interface (SSPI), SCRAM SHA 256, etc. that makes it a secure and safe platform. Also, it supports various access control systems, column and row level security.

Scalability

PostgreSQL is a scalable open source object relational database management system. It supports multiple technical options for operating at scale, which helps you grow and extend as much you need.

Inheritance

The RDBMS supports an advanced object relational feature known as the inheritance enables developers to create tables and inherit a few columns attributes from different tables and model their structure. It’s more like creating a child table comprising all of the columns in the parent table.

Install PostgreSQL on Ubuntu 20.04 Server

Add the PostgreSQL Repository

By default, the latest version of PostgreSQL is not included in the Ubuntu 20.04 default repository. So you will need to add the PostgreSQL repository to your server.

First, install all required dependencies by running the following command:

				
					apt-get install wget curl gnupg2 -y
				
			

Once all the dependencies are installed, add the PostgreSQL repsoitory and GPG key using 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'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
				
			

Once the repository is added, update it using the following command:

				
					apt-get update -y
				
			

When the PostgreSQL repository is updated, you can proceed to install the PostgreSQL server.

Install PostgreSQL on Ubuntu 20.04

Please run the following command to install the latest PostgreSQL server on your server.

				
					apt-get install postgresql-14 -y
				
			

Once the PostgreSQL is installed, start the PostgreSQL service and enable it to start after the system reboot:

				
					systemctl start postgresql
systemctl enable postgresql
				
			

You can now check the status of the PostgreSQL service using 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 Mon 2022-03-28 16:15:55 CEST; 5s ago
  Process: 20321 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 20321 (code=exited, status=0/SUCCESS)

March 28 16:15:55 thor-KVM systemd[1]: Starting PostgreSQL RDBMS...
March 28 16:15:55 thor-KVM systemd[1]: Started PostgreSQL RDBMS.


				
			

By default, PostgreSQL listens on port 5432. You can check it with the following command:

				
					ss -antpl | grep 5432
				
			

You will get the following output:

				
					LISTEN 0      128        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=1955,fd=7))
				
			

Check the PostgreSQL version using the following command:

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

You will get the following output:

				
					                                                             version                                                             
---------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.0 (Ubuntu 20.0-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~20.04) 7.5.0, 64-bit
(1 row)
				
			

How to Use PostgreSQL Server

At that part of our article about how to Install PostgreSQL on Ubuntu 20.04 Server we can connect to the PostgreSQL locally using the following command:

				
					sudo -u postgres psql
				
			

You will get the following output:

				
					psql (14.0)
Type "help" for help.

postgres=#
				
			

To create a database named wpdb, run the following command:

				
					CREATE DATABASE wpdb;
				
			

To list all PostgreSQL databases, run the following command:

				
					\l
				
			

You should see all databases in the following output:

				
					                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 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
 wpdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

				
			

To switch the database to wpdb, run the following command:

				
					\c wpdb
				
			

To create a table named persons, run the following command:

				
					CREATE TABLE persons (id INTEGER PRIMARY KEY, name VARCHAR);
				
			

Now, you can list all tables using the following command:

				
					\dt
				
			

You should see the following output:

				
					          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | persons | table | postgres
(1 row)
				
			

To exit from the PostgreSQL shell, run the following command:

				
					exit
				
			

Great job! You have followed all the steps to Install PostgreSQL on Ubuntu 20.04 Server.

How to Install PostgreSQL on Ubuntu 20.04 Server Conclusion

PostgreSQL is still very popular tool to choose from with all the built in features and innumerable ways in which we can customize or extend it further to suit our needs.

We have covered a handful of capabilities that make PostgreSQL distinct from other open source SQL solutions

 

In this guide we have learned how to install PostgreSQL server on Ubuntu 20.04 server. But also how to create a database and table in PostgreSQL. You can now integrate PostgreSQL with any web based applications.

Avatar for Hitesh Jethva
Hitesh Jethva

I am a fan of open source technology and have more than 10 years of experience working with Linux and Open Source technologies. I am one of the Linux technical writers for Cloud Infrastructure Services.

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x