PostgreSQL is a free-to-use Object Relational Database System released under an open-source license. It stores data in rows, and columns, and then employs SQL (Structured Query Language) to read and write data like other relational database management systems.

PostgreSQL further extends SQL by allowing queries to be reused, enabling more data modeling options, creation of custom data types, and support for programming languages like C and Python. It includes a rich ecosystem of extensions like PostGIS, Citus, and PG Embedding for various use cases. Further, it supports advanced features like inheritance and polymorphism.

This article will discuss two different methods of installing PostgreSQL on Ubuntu 22.04 LTS along with the basics of PostgreSQL. This article is organized as follows:

• How to Install PostgreSQL on Ubuntu 22.04?
• How to Set Up PostgreSQL?
• How to Start With PostgreSQL?
• How to Uninstall PostgreSQL From Linux/Ubuntu?
• Bonus Tip: Installing a Specific Version of PostgreSQL

How to Install PostgreSQL on Ubuntu 22.04?

Ubuntu is the most popularly operated feature-rich Linux distribution that offers a variety of features, such as free, secure, resource-friendly, etc. It is an open-source distribution that allows us to install any particular software of our choice without any hassles. More specifically, users can install PostgreSQL on their Ubuntu machines by using the following methods:

• Method 1: How to Install PostgreSQL via Ubuntu Repository?
• Method 2: How to Install PostgreSQL via PostgreSQL Apt Repository?

Method 1: How to Install PostgreSQL via Ubuntu Repository?

Ubuntu repository contains the latest and most stable version of PostgreSQL. To install PostgreSQL via the Ubuntu repository, the following steps are performed:

Step 1: Update System Repositories

Start the terminal with a combination of [Ctrl+Alt+T] keys. Then, update system repositories by executing the following command:

$ sudo apt update

Step 2: Install PostgreSQL

Advanced Package Tool (apt) is the default package management tool in Ubuntu. To install PostgreSQL using apt, execute the following command:

$ sudo apt install postgresql postgresql-contrib

The user will be prompted during installation. Press “Y” to continue the installation process:

The above output verifies the successful installation of PostgreSQL.

Step 3: Verify Installation

To verify PostgreSQL’s installation, execute the following command:

$ psql –version

It can be verified from the figure above that PostgreSQL version 14.9 has been successfully installed.

Method 2: How to Install PostgreSQL via PostgreSQL Apt Repository?

PostgreSQL apt repository is the official repository that contains the latest apt repository of Postgres packages for Ubuntu. To install PostgreSQL via the PostgreSQL apt repository, the following steps are performed:

Step 1: Update System Repositories

First, start the terminal with a combination of [Ctrl+Alt+T] keys and update system repositories by executing the following command

$ sudo apt update

Step 2: Add PostgreSQL Repository

Then, add the PostgreSQL repository by running the following command:

$ sudo sh -c ‘echo “deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’

Step 3: Import Repository Key

Next, import the repository key by executing the following command:

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

Step 4: Update System Repositories

After that, Update the system repositories again by running the following command:

$ sudo apt-get update

Step 5: Install PostgreSQL

Finally, Install the latest version of PostgreSQL by executing the following command:

From the above image, it can be observed that PostgreSQL has been installed successfully.

Step 6: Verify Installation

To verify PostgreSQL’s installation, execute the following command:

$ psql –version

It can be verified from the figure above that PostgreSQL version 16.0 is successfully installed.

After successful installation, next, the setup and usage of PostgreSQL are demonstrated in the following section.

How to Set Up PostgreSQL?

The setup of PostgreSQL is demonstrated in the following sections:

How to Enable, Start, Stop, Reload, and Disable PostgreSQL Service?

The PostgreSQL service is controlled by the Systemctl utility. The following commands are executed with sudo privileges.

How to Enable PostgreSQL Service?

To enable the PostgreSQL service, use the Systemctl utility with sudo privileges as follows:

$ sudo systemctl enable postgresql

How to Start a PostgreSQL Service?

To start/activate the PostgreSQL service, execute the following command:

$ sudo systemctl start postgresql

How to Reload PostgreSQL Service?

To reload the PostgreSQL service, execute the following command:

$ sudo systemctl reload postgresql

How to Check PostgreSQL Service Status?

To check the PostgreSQL service status, execute the following command:

$ sudo systemctl status postgresql

The above output indicates that the PostgreSQL service is currently active.

How to Stop PostgreSQL Service?

To stop/inactive the PostgreSQL service, execute the following command:

$ sudo systemctl stop postgresql

From the above figure, it can be observed that PostgreSQL services are stopped.

How to Start With PostgreSQL?

The basic usage of PostgreSQL is demonstrated in the following sections:

How to Login to PostgreSQL?

To log in to PostgreSQL, execute the following command:

$ sudo -i -u postgres

To access the shell of PostgreSQL, execute the following command:

$ psql

Alternatively, the following command can be executed to access the interactive interface of PostgreSQL:

$ sudo -u postgres psql

How to Create a New Database in PostgreSQL?

To create a New Database, for example, testdatabase, execute the following command:

# create database testdatabase;

From the above image, it can be seen that a new database is created.

How to List DataBases?

To check existing databases, run the following command.

# \l

From the above figure, it can be observed that there are four databases available: postgres, template0, template1, and testdatabase, among which postgres, template0, and, template1 are the default databases. In contrast, “testdatabase” is the database that was created in the last section.

How to Create a Table in PostgreSQL?

To create a table inside a database, run the following command:

# CREATE TABLE testTable (testID INT, testName TEXT);

• testTable: Name of table (user-defined)
• testID INT: INT is type of column1 (testID)
• testName TEXT: TXT is type of column2 (testName)

From the above image, it can be seen that the table is created successfully. Next, insert data in the testTable by executing the following command:

# INSERT INTO testTable (testID, testName)

VALUES (001, ‘LinuxGenie’);

• 001: value to be inserted in column1
• ‘LinuxGenie’: value to be inserted in column2

In the above figure, 0 1, indicates that 1 record is successfully entered in the table.

How to View Table Contents in PostgreSQL?

To fetch/view the contents of a particular table, for example, testTable that was created in the previous section, run the following command:

# SELECT * FROM testTable;

From the above figure, it can be seen that the testTable contains the values “1” and “LinuxGenie” that were inserted in the previous section.

How to Check Users in PostgreSQL?

To check the list of users, run the following command:

# \du

From the above figure, it can be seen that there exist only one user “postgres”.

How to Create a New User in PostgreSQL?

To create a new user, run the following command:

# CREATE USER user1 WITH ENCRYPTED PASSWORD ‘PssWord’;

• user1: User Name (User Defined).
• PssWord: Password (User Defined).

From the above image, it can be verified that a user “user1” is successfully created.

How to Grant Privileges to a User in PostgreSQL?

The privileges enable a user to execute specific actions on the database. For example, the below figure shows that there are no access privileges on testdatabase.

# \l

To grant all privileges to the “user1” on the “testdatabase”, execute the following command:

# GRANT ALL PRIVILEGES ON DATABASE testdatabase TO user1;

• testdatabase: Name of the Database (User Defined).
• user1: Name of the User (User Defined).

From the above figure, it can be seen that all permissions are granted to the user1.

How to Exit from PostgreSQL?

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

# exit

From the above figure, it can be seen that the prompt is back to the Ubuntu command line.

In this section, just a few ways among many are discussed to interact with PostgreSQL.

How to Uninstall PostgreSQL From Linux/Ubuntu?

If Postgres is no longer needed, you can remove it to free up some space. PostgreSQL can be removed by executing the following command:

$ sudo apt remove postgresql postgresql-contrib

PostgreSQL has been successfully removed.

Bonus Tip: Installing a Specific Version of PostgreSQL

To install a specific version of PostgreSQL, the version number can be added in the below syntax

$ sudo apt -y install postgresql-<version>

$ sudo apt -y install postgresql-13

That’s all about installing, configuring, setting, using, and removing PostgreSQL from Ubuntu 22.04.

Conclusion

PostgreSQL can be installed on Ubuntu 22.04 by two methods: via the Ubuntu repository and the PostgreSQL apt repository. To install PostgreSQL’s latest stable version via the Ubuntu repository execute the “sudo apt install postgresql postgresql-contrib” command after updating the systems repositories. Alternatively, to install PostgreSQL via the PostgreSQL apt repository, first, add the PostgreSQL repository, next import the repository’s key. Then, update the system repositories and finally execute the “sudo apt-get -y install postgresql” command.

This article demonstrated two different methods of installing PostgreSQL, i.e., via Ubuntu Repository and PostgreSQL Apt Repository along with setting up, basics, and uninstallation of PostgreSQL. PostgreSQL apt repository always contains the latest version of PostgreSQL. However, the Ubuntu repository might contain a version that is supported by Ubuntu, tested and approved but is not necessarily the latest version. You can opt for any of these methods according to your necessities.