PostgreSQL

How to Install pgAdmin4 on Ubuntu 20.04

What is pgAdmin4?

pgAdmin is one of the most popular tools for managing PostgreSQL database. It was a feature-rich PostgreSQL administration tool. A complete rewrite from the previous pgAdmin version, built with Python and JavaScript.

The pgAdmin4 designed for multiple PostgreSQL versions supports for PostgreSQL 9.2 and above. Multiplatform that can run on Linux, Mac, and Windows. Also, it provides multiple deployment models, you can deploy as a single Desktop application, or deploy as a web-based application.

In this tutorial, you will learn how to install pgAdmin4 on Ubuntu 20.04 Server. You will deploy the pgAdmin4 as a web-based administration tool, accessible from anywhere and at any time through the web browser.

Prerequisites

For this tutorial, make sure you have got the PostgreSQL database installed on Ubuntu 20.04 server. Also, you will need the root privileges.

Below is the tutorial about the PostgreSQL installation on Ubuntu 20.04 server.

Install PostgreSQL on Ubuntu 20.04 Server

Before going any further, log in to your Ubuntu server and type the sudo command to get the root privileges on your system.

sudo su

Step 1 – Add pgAdmin4 Key and Repository for Ubuntu 20.04

For this tutorial, you’re going to install the pgAdmin4 from the official PostgreSQL repository on Ubuntu 20.04. So, you will add the pgAdmin4 key and repository to your server.

First, install some additional packages using the apt command below.

apt install curl ca-certificates gnupg

Now add the pgAdmin4 key to the Ubuntu system.

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

Then add the pgAdmin4 repository for Ubuntu 20.04 Bionic Beever.

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

After that, update all repository list using the apt command below.

apt update

As a result, you’re ready to install the pgAdmin4 on Ubuntu 20.04.

Add PostgreSQL pgAdmin4 repository for Ubuntu 20.04

Step 2 – Install pgAdmin on Ubuntu 20.04

Install pgAdmin4 packages with additional packages for Apache UwsGI application using the apt command below.

apt install pgadmin4 pgadmin4-common pgadmin4-apache2

During the installation, you will be asked for the configuration of the pgAdmin email and password. It will be used for the pgAdmin4 authentication.

Type your email address and choose ‘OK‘.

Setup pgAdmin4 User

Now type the password for the pgAdmin4 authentication and choose ‘OK‘.

setup password pgAdmin4

As a result, you’ve installed the pgAdmin4 on the Ubuntu 20.04 Server.

Step 3 – Setup PostgreSQL Authentication

In this step, you will change the default password for the ‘postgres’ user on the PostgreSQL database.Reference

Log in to the PostgreSQL shell using the ‘psql’ command below.

sudo -i -u postgres psql

Now change the password for the ‘postgres’ user using the following query.

\password postgres

Type ‘\q‘ to exit from the PostgreSQL shell.

Change password for postgres user

As a result, you’ve changed the password for default user ‘postgres’, and you’re ready to set up the pgAdmin4 with the PostgreSQL.

Step 4 – Login to pgAdmin4

Open your web browser and type the server IP address following by the ‘pgadmin4’ path URL.

http://10.5.5.35/pgadmin4/

On the pgAdmin4 login page, type your email address and password, then click the ‘Login‘ button.

pgAdmin4 Login Page on ubuntu 20.04

Now you will get the pgAdmin4 admin dashboard.

Step 5 – pgAdmin4 Connect to PostgreSQL on Ubuntu 20.04

Once you’ve logged in to the pgAdmin4 dashboard, add a new PostgreSQL server.

On the pgAdmin4 dashboard, click the ‘Add New Server‘ menu.

pgAdmin4 connect to PostgreSQL Server on Ubuntu 20.04

On the ‘General‘ tab, type the name of your connection.

Name: postgresql-server1

Move to the ‘Connection‘ tab and type details about your PostgreSQL server.

Host name/address: 127.0.0.1
Port: 5432
Maintenance database: your-database-name
Username : yoour-username
Password: your-password

Click the ‘Save‘ button to add the new PostgreSQL server.

The pgAdmin4 will be automatically connected to the PostgreSQL server. Once it’s complete, you will get details PostgreSQL as below.

pgAdmin4 Dashboard connected to PostgreSQL Server on Ubuntu 20.04

As a result, you’ve been added a new PostgreSQL server to the pgAdmin4.

Step 6 – Create a New Database using pgAdmin4

To create a new database using the pgAdmin4, make sure that you’re connected to the PostgreSQL server.

Now look at the left menu inside your PostgreSQL connecttion, click the ‘Databases‘ section, then click ‘Object‘ > ‘Create‘ > ‘Database‘.

Create Database using pgAdmin4

On the ‘General‘ tab, type your database name and change the owner with your own, or you can use the default user ‘postgres’ as the owner.

Database: database-name
Owner: username/default-postgres

Now click the ‘Save‘ button.

Create Database using pgAdmin4 on Ubuntu 20.04

As a result, you’ve created a new PostgreSQL database using the pgAdmin4.

Step 7 – Create a New User/Role using pgAdmin4

After creating a new database, you will create a new user/Role using the pgAdmin4.

On the left side of your PostgreSQL connection, clikc the ‘Login/Group Roles‘ section, then click ‘Object‘ > ‘Create‘ > ‘Login/Group Role…‘.

Create Role PostgreSQL using pgAdmin4

On the ‘General‘ tab, type your new username.

Name: username

Move to the ‘Definition‘ tab and type your password.

Password: your-password

Next, move again to the ‘Privileges‘ tab and enable the following privileges.

Can login? Yes
Create database? Yes

Now click the ‘Save‘ button.

Create Role PostgreSQL using pgAdmin4

As a result, you’ve created a new PostgreSQL user/Role using pgAdmin4.

Step 8 – Create a New Table using pgAdmin4 on Ubuntu 20.04

To create a new table, click the drop-down icon on your database name, click ‘Schemas‘, then click ‘Tables‘.

Now click the ‘Object‘ menu on top, then click ‘Create‘ and ‘Table‘.

Create Table PostgreSQL using pgAdmin4

On the ‘General‘ tab, type your table name, choose the owner, and default tablespace.

Name: table-name
Owner: username
Scheme: Public
Tablespace: pg_default

Now move to the ‘Columns’ section, click the ‘+‘ button to create new columns as below.

Create Table Columns PostgreSQL

At this stage, you can click the ‘Save‘ button.

As a result, you’ve created a new table on your PostgreSQL database through the pgAdmin4 dashboard.

Step 9 – Grant Privileges of Database to User

After creating the PostgreSQL tables, you will grant privileges of your database to another user.

Right-click on your database name and click the ‘Grant Wizard‘.

Grant Privileges using pgAdmin4

Now make a mark for all objects on the database and click ‘Next‘.

Select Tables

Add your user by clicking the ‘+’ button and select the privilege as you want, then click ‘Next‘.

Select privileges

Now click the ‘Finish‘ button.

grant Privileges Database using pgAdmin4

As a result, you’ve successfully granted your user privileges to access the database.

Step 10 – Insert Data using the pgAdmin4 Query Tool

The pgAdmin4 provides a powerful ‘Query Tool’ that allows you to execute arbitrary SQL query command and see and review the result of your queries.

To insert data to your table, click on your table name, and choose the ‘Tools‘ menu on top, click the ‘Query Tool‘.

Insert Data using pgAdmin4 Query Tool

And you will get the new tab of Query Tool on your dashboard.

Insert your PostgreSQL query on the box and click the ‘Play‘ button as below.

Insert Data using pgAdmin4 Query Tool

Now you will get the successful response of your SQL queries at the bottom ‘Messages‘ tab.

Query returned successfully in 350 msec.

As a result, you’ve successfully insert new data to the PostgreSQL database using the pgAdmin4 Query Tool.

Finally, you’ve successfully installed the pgAdmin4 on the Ubuntu 20.04 Server. Also, you’ve successfully changed the default password for the ‘postgres’ user, and you’ve been added new PostgreSQL to the pgAdmin4.

Write A Comment