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.
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
PostgreSQL uses multi version concurrency control.
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.
