How to Install PostgreSQL on Ubuntu 20.04/18.04? | Made Easy

PostgreSQL is a popular Relational Database Management System (DBMS) that enables you to work with data for executing various data-related tasks. It provides you with a built-in and well-managed framework to write or implement queries, such as SQL commands on tables present within the Database Servers. PostgreSQL is a popular choice for a basic and high-end project that involves the Data Processing and manipulation phase in the end-to-end project development life cycle. Since PostgreSQL provides users with advanced features and functionalities, it is well designed to be compatible with different operating systems like Windows and Linux, allowing users to work flexibly with various platforms. 

In this article, you will learn how to install PostgreSQL on Ubuntu 20.04/18.04 version along with all the configurations options, where Ubuntu is one of the most popular Linux kernel-based distributions.

Table of Contents

• Prerequisites
• Understanding PostgreSQL
• Steps to Install PostgreSQL on Ubuntu
  • Prerequisites
  • Install PostgreSQL on Ubuntu
  • Securing PostgreSQL with Credentials
  • Install PostgreSQL on the Ubuntu Administration Package
  • Working with PostgreSQL 
• Conclusion

Prerequisites

Fundamental knowledge of the Ubuntu operating system.

Understanding PostgreSQL

PostgreSQL is an Open-Source Relational Database Management System that allows you to store, process, manage, and retrieve data present in the Database Servers. Since PostgreSQL is an Open-Source DBMS, it is free and easy for everyone to download and execute queries to perform data-related operations without paying for any Database Server space. In addition, PostgreSQL is compatible with various data type categories like primitive and structured data types. While the primitive data types are integer, numeric, string, and boolean, structured data types include the date, time, and arrays. PostgreSQL also supports multiple programming languages such as C, C++, Java, and Python. 

With PostgreSQL, you can work with high-end document files such as JSON and XML. Furthermore, it is one of the highly-compatible DBMS applications that supports various operating systems like Windows, MACOS, and Linux. Since PostgreSQL comprises such advanced features and functionalities that exhibit user-friendliness and flexibility, it is widely used by data enthusiasts worldwide for implementing data processing operations. According to DB-Engines’ ranking, PostgreSQL is currently ranked in the 4th position for popularity among 100s of databases worldwide.

Steps to Install PostgreSQL on Ubuntu

The below-given steps help you install and configure PostgreSQL on Ubuntu operating systems. You will be using the default terminal or command prompt to install, configure, and customize PostgreSQL to work accordingly on Ubuntu systems. 

Prerequisites

• Initially, you have to open a new terminal window for entering commands to execute the installation process of PostgreSQL on Ubuntu. You can also use the Keyboard shortcut Ctrl + Alt + t to open a new command prompt or terminal window.
• As a prerequisite, your ubuntu server should have sudo permissions and a basic firewall setup to start with the installation process.
• In the next step, ensure that your Ubuntu version is up to date. 
• If your Ubuntu is not updated, you should update the version before installing any new software package to your operating system. 
• Updating the version is mandatory since you have to work on an updated operating system to eliminate all the dependency issues that happen halfway while running an application.  
• Execute the following commands to update and upgrade the existing packages of your operating system.

sudo apt update
sudo apt -y upgrade

• After satisfying the above-mentioned prerequisites, you are now all set to install and configure PostgreSQL on Ubuntu.

Install PostgreSQL on Ubuntu

• In your command prompt, execute the following command to install PostgreSQL. 

sudo apt-get -y install postgresql postgresql-contrib

• The above-given command installs the latest version of PostgreSQL with added utilities and functionalities. 
• You can also install the desired version by mentioning the version number in the inline command. In other words, you can add the version number as a suffix to download the specific version of PostgreSQL.

sudo apt-get -y install postgresql-12

• The above command will install the PostgreSQL 12 version to Ubuntu.

• While the command helps install PostgreSQL on Ubuntu, you will be prompted to confirm the installation process. In the command prompt, it will ask, “Do you want to continue? [Y/n] as shown in the above image. Enter ‘y’ to confirm the installation.
• On executing the previous step, the installation process will be started on Ubuntu.  
• After completing the installation process, PostgreSQL services will start automatically.
• You can also start and enable the PostgreSQL services manually. 
• To start the PostgreSQL services manually, execute the following command.

sudo systemctl start postgresql.service

• To launch and enable the PostgreSQL services manually, execute the below command.

sudo systemctl enable postgresql.service

• You can check the status of PostgreSQL by executing the following command.

sudo systemctl status postgresql.service

• You will get the output, as shown in the above image, which confirms that PostgreSQL RDBMS is successfully installed on Ubuntu.

Securing PostgreSQL with Credentials

• The safety and security of PostgreSQL are mandatory before starting working with PostgreSQL. 
• For ensuring security, you have to set credentials with the respective username and password. By default, the PostgreSQL database admin user named “postgres” is created when you install PostgreSQL on Ubuntu. On doing this, you establish a connection with the PostgreSQL Database Server. 
• In the next step, you will be logging into the PostgreSQL database server using the default user name. Before logging in, you have to create a new password for securing the database server. 
• Execute the following command to access the Postgres shell and create a new password to the Postgres account. 

sudo su - postgres

• On executing the above command, you get access to the Postgres command shell. Now, for setting the password, execute the following command.

psql -c "ALTER USER postgres WITH PASSWORD 'newpassword'"

• In the above command, ‘newpassword’ is the preferred password you set to the Postgres account.
• You can also set an exclusive or specific password to the particular database present within PostgreSQL. 
• Execute the following command to set an exclusive password to the database.

psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword'";

• After successfully setting up the user account to start working with PostgreSQL, you must check whether the PostgreSQL is properly installed and currently running on your local machine. 
• Execute the following command to confirm the proper installation of PostgreSQL.

psql -c "SELECT version();"

• On executing the above command, you will get the following output.

PostgreSQL 12.2 (Ubuntu 12.2-4) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-8ubuntu1) 9.3.0, 64-bit

• In the next step, run the below command to log in to the PostgreSQL account with “postgres” and a newly created password. 

psql postgres

• After executing the above command, you will be prompted to enter the previously created password. On entering the password, you will get access to the PostgreSQL prompt or shell for working with different databases. 

Install PostgreSQL on Ubuntu Administration Package

• Once you install PostgreSQL on Ubuntu, the application comes with an adminpack module by default. The adminpack module provides you with a suite of management tools and administration utilities. Such tools help provide additional functionality to PostgreSQL like the main window, status dialogue, etc. 
• If you use the pgInstaller distribution to run PostgreSQL, the adminpack module is installed and activated by default. Else you have to install it manually to your PostgreSQL as an add-on feature.
• For installing the mobile, you have to log in to PostgreSQL. Execute the following command to log in to PostgreSQL as a “postgres” user.

psql postgres

• In the next step, you have to add and enable the extension or package. Run the below command to enable the adminpack module of PostgreSQL.

CREATE EXTENSION adminpack;

• On executing the above command, enter “dx” in the command prompt to check whether the adminpack module is properly installed to PostgreSQL. You will get the output as shown in the below image. 

• As shown in the output image, you can see that the adminpack module is listed among the list of installed extensions. This confirms that the adminpack module is installed successfully. 

Working with PostgreSQL

A) Creating Databases

The database is a collection of tables that comprises rows and columns, where PostgreSQL manages tables, databases, and database clusters collectively. Initially, you have to create a database in PostgreSQL before creating or adding tables or records.  

• In a command prompt, execute the following command to create a new database.

createdb testdatabase

• You created a database named testdatabase in PostgreSQL.
• Use the below command to directly connect and work with the newly created database.

psql testdatabase

• Now, write the meta-command “1” to list all the databases present within PostgreSQL.

• In the above-given image, you can find that the newly created database is listed along with its properties.

B) Creating Tables

• Using the command terminal, create a new table inside the previously created database. For creating tables inside the database, you have to connect to the database initially.
• Execute the following command to connect to the respective database.

psql testdatabase

• Now, you are ready to create tables inside the database.
• Create a new table with the below command.

CREATE TABLE customers (customer_id int, first_name varchar(80), last_name varchar(80));

• With the above command, you are creating a new table named customers. You also provide the list of columns that the table comprises, along with the respective data types.
• After creating a new table, execute the meta-command ‘dt” to view the list of tables present inside the database.

• Then, to view the overall schema of the customers table, run the below command.

d+ customers

• If you have to delete the respective table, execute the following command.

DROP TABLE customers;

• The DROP TABLE command deletes both the table schema and data present in the table.

On following the above-mentioned steps, you successfully installed and configured PostgreSQL with Ubuntu.

Conclusion 

In this article, you learned about PostgreSQL, Ubuntu, how to install PostgreSQL on Ubuntu Operating System. This article mainly focused on the installation methods and basic commands to create Databases and tables in PostgreSQL. However, you can also read, write, manipulate, and filter data present in the table with simple commands. Keeping this article as a base, you can explore and try other data processing methods in the future. In case you want to export data from a source of your choice into your desired Database/destination like PostgreSQL, then Hevo Data is the right choice for you! 

Visit our Website to Explore Hevo

Hevo Data provides its users with a simpler platform for integrating data from 100+ sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources. You can use it to transfer data from multiple data sources into your Data Warehouses such as PostgreSQL, Database, or a destination of your choice. It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about how to install PostgreSQL on Ubuntu! Let us know in the comments section below!