Install and Configure PostgreSQL and pgAdmin on Ubuntu 20.04 | 22.04

PostgreSQL

PostgreSQL, often referred to as “Postgres,” is a powerful open-source relational database management system (RDBMS). It serves as a reliable and efficient platform for storing, organizing, and managing data. PostgreSQL supports SQL (Structured Query Language) and offers a wide range of features, making it a popular choice for various applications.

In this tutorial, you will see how to Install and Configure PostgreSQL and pgAdmin on your Ubuntu 20.04 and 22.04.

Getting packages on Ubuntu distributions

You can go to the PostgreSQL downloads page website or download the packages using the below 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'

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

Update and Upgrade Ubuntu

Before installation process, it’s always a good practice to ensure that your system is up to date.
Open a terminal and run the following commands:

sudo apt update
sudo apt upgrade

Install PostgreSQL

To install latest PostgreSQL, use the following command:

# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt install postgresql

Configure PostgreSQL

By default, PostgreSQL creates a user named “postgres.” Switch to this user and access the PostgreSQL prompt:

sudo -i -u postgres
psql

Check PostgreSQL version

You can use the PostgreSQL version command to check the currently installed version.

SELECT version();

Now, you can set a password for the PostgreSQL user:

ALTER USER postgres WITH PASSWORD 'your_password';

Exit the PostgreSQL prompt:

\q

Verify your PostgreSQL installation

Check the status of the PostgreSQL service to make sure it is running.
To verify it, run the following command:

sudo service postgresql status

PostgreSQL should run automatically, but if this is not the case, make sure to start it.

sudo service postgresql start

In the future to stop and restart the PostgreSQL, the commands are:

Restart

sudo service postgresql restart

Stop

sudo service postgresql stop

Allow PostgreSQL TCP port 5432 in the Firewall

PostgreSQL default HTTP port is 5432, you’ll need to allow access to this port on the firewall.

If your firewall is UFW type the following commands:

sudo ufw allow 5432/tcp

That’s it! You have now installed PostgreSQL on Ubuntu

Install pgAdmin Desktop

Getting packages on Ubuntu distributions
You can go to the pgAdmin downloads page website or download the packages using the below command:

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Next, let’s install pgAdmin, the graphical administration tool for PostgreSQL. Use the following commands:

# Install for both desktop and web modes:
sudo apt install pgadmin4

# Install for desktop mode only:
sudo apt install pgadmin4-desktop

# Install for web mode only: 
sudo apt install pgadmin4-web 

Add PostgreSQL Server to pgAdmin

In pgAdmin, click on the “Servers” menu on the left sidebar, then right-click and choose “Register” then select “Server” Enter a name for your server and switch to the “Connection” tab. Fill in the following details:

Host name/address: localhost
Port: 5432
Maintenance database: postgres
Username: postgres
Password: (Enter the password you set for the PostgreSQL user)

Click “Save” to add the server.

Congratulations! You’ve successfully installed and configured PostgreSQL and pgAdmin on your Ubuntu system. You can now use pgAdmin to manage your databases, tables, and perform various administrative tasks through its intuitive interface.

Conclusion:

PostgreSQL and pgAdmin form a powerful combination for managing relational databases, and installing them on Ubuntu is a straightforward process. With this setup, you’re ready to start building and managing your databases with ease. Happy coding!