How to Install PostgreSQL on Ubuntu (pgAdmin Setup)

How to Install PostgreSQL on Ubuntu?

PostgreSQL is a well-known open-source Relational Database Management System (RDBMS). It is popular in terms of rich features, scalability, and stability. This RDBMS supports data integrity, complex queries, and concurrent access.

Most organizations select PostgreSQL because of its extensibility, reliability, and its cross-platform nature. Moreover, it also provides a solid base for handling extensive amounts of data. This RDMS is preferred by data analysts, developers, and businesses seeking a flexible and powerful database solution.

Prerequisites

To follow our guide, you must have:

• Ubuntu 22.04 installed on your system
• A non-root user with sudo privileges

Now, let’s check the procedure to install Postgres on Ubuntu using the following:

• The Ubuntu repository
• The PostgreSQL official repository

Method 1: How to Install and Setup PostgreSQL on Ubuntu Using the Ubuntu Repository

In this method, we will utilize the Ubuntu repository for downloading and installing PostgreSQL. This repository already comprises the official packages updated and maintained by the Ubuntu developers. Ultimately, this makes sure that the installation process remains hassle-free and straightforward.

Step 1) To install PostgreSQL in Linux, firstly, update your system packages:

sudo apt update

Output:

Step 2) Then, go for PostgreSQL installation:

sudo apt install postgresql -y

Step 3) Lastly, check out the PostgreSQL version for verification:

psql --version

As you can see, PostgreSQL version 14.8 has been successfully installed on our Ubuntu system:

Method 2: Install PostgreSQL on Ubuntu Using the PostgreSQL Official Repository

The official PostgreSQL repository permits you to access the latest version of PostgreSQL and the relevant features that are not available in the Ubuntu official repository. With PostgreSQL’s official repository, you can easily install and update PostgreSQL.

Now, follow the provided instructions to install PostgreSQL on Ubuntu.

Step 1) The first step is to add the repository of PostgreSQL:

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

Step 2) Then, move toward adding PostgreSQL repository key:

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

Step 3) Update your system packages to move ahead:

sudo apt update

Step 4) Now, go for PostgreSQL installation:

sudo apt install postgresql -y

Step 5) View the PostgreSQL version for confirmation:

psql --version

It can be observed that with the help of the PostgreSQL repository, we have installed its 15.3 version on Ubuntu:

How to Manage PostgreSQL Users on Ubuntu?

Management of PostgreSQL users involves the process of creating, modifying their access, and removing users to control the database privileges. It ensures that the administrator grants required permissions and manages the PostgreSQL user accounts in an effective manner.

Step 1) To create a new Postgres user on Ubuntu, run this command:

sudo -u postgres createuser --interactive

As a result, PostgreSQL prompt to type out the username and confirm whether the newly created user should have the superuser privileges:

In our case, we have entered “huzail” as a username and permitted it to be a super user.

Step 2) You can also separately give sudo privileges to the user by specifying its name in this command:

ALTER USER huzail WITH SUPERUSER;sudo -u postgres psql

Step 3) In case you want to drop the newly created user, write out its name in the “DROP USER” command:

DROP USER huzail;

How to Manage PostgreSQL Database on Ubuntu?

Next comes the management of the PostgreSQL database on Ubuntu. This involves different operations related to creating, modifying, and modifying databases for better organization.

Step 1) First of all, utilize the “CREATE DATABASE” command for creating a database and specify its name as we added “guru99” here:

CREATE DATABASE guru99;

Step 2) Then, grant the database access to the required user:

GRANT ALL PRIVILEGES ON DATABASE guru99 TO huzail;

In our case, we have granted the “guru99” access to the user “huzail”:

Step 3) In case you want to delete or drop the database, run “DROP DATABASE” command with the desired database name:

DROP DATABASE guru99;

Resultantly, the given database will get deleted:

A fresh install needs hardening before being exposed to applications.

Securing Default PostgreSQL User

The operation of securing the default PostgreSQL user comprises different measures, such as configuring a strong password, enabling the relevant password encryption, and restarting the PostgreSQL service. By doing so, the default user will be protected from unauthorized access.

Step 1) Firstly, log in to the PostgreSQL database as we already did:

sudo -u postgres psql

Step 2) Then, add the password for the default PostgreSQL user:

ALTER USER postgres PASSWORD 'my-secret-password123';

Step 3) Open up the relevant configuration file for enabling the password encryption:

sudo nano /etc/postgresql/{version}/main/pg_hba.conf

In the above command, replace the version number with the PostgreSQL version you have installed, as we added “15” here:

Your PostgreSQL configuration file will somehow look like this:

Look for the line that starts with “local all all” and change the encryption method from “peer” to “md5”. After that, hit “CTRL+O” to save changes and press CTRL+X” to exit the nano editor:

Step 4) Restart the PostgreSQL service so that it can take effect from the configuration changes:

sudo service postgresql restart

Step 5) Lastly, check PostgreSQL status:

sudo service postgresql status

That’s it! You have secured the default PostgreSQL user with the encrypted password.

Bonus Step: Install pgAdmin

pgAdmin is a Graphical User Interface (GUI) PostgreSQL client that simplifies the management tasks. It also enables efficient query execution, database administration, and PostgreSQL server monitoring. Moreover, it also assists in enhancing the productivity of PostgreSQL users.

Step 1) First of all, you have to add the PostgreSQL repository key:

url -fsSL https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgadmin.gpg

In our case, we have already added it, so we will press “y” for overwriting it:

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

Step 2) Then, add the pgAdmin repository to your Ubuntu system:

Step 3) Next, update packages for further proceeding:

Sudo apt update

Step 4) Now, install pgAdmin4 by typing out this command:

sudo apt install pgadmin4

Step 5) Now, you have to enter the given command for initiating the primary configuration of pgAdmin:

sudo /usr/pgadmin4/bin/setup-web.sh

Then, specify the required information:

Step 6) Launch pgAdmin4 on your web browser by navigating to the link that was provided in the last output:

Use your pre-configured email and password to login to pgAdmin:

Here comes the main dashboard of the pgAdmin that is all ready to use:

How to Uninstall PostgreSQL from Ubuntu?

Step 1) In other to uninstall PostgreSQL from your Ubuntu system, firstly, stop the PostgreSQL service:

sudo systemctl stop postgresql

Step 2) Then, remove PostgreSQL and its relevant dependencies in case you have installed them earlier:

sudo apt purge postgresql postgresql-contrib -y

Step 3) Remove all configuration files of the PostgreSQL:

sudo rm -rf /etc/postgresql/

Resultantly, PostgreSQL will be removed from your Ubuntu system.

How to Uninstall pgAdmin from Ubuntu?

Step 1) In case it is required to remove pgAdmin from Ubuntu, remove its package first:

sudo apt remove pgadmin4

Step 2) Lastly, remove the relevant configuration files from your system:

sudo rm -rf ~/.pgadmin

That’s it! You have successfully uninstalled pgAdmin from Ubuntu.

FAQs