What is pgAdmin4?
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.
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.
Before going any further, log in to your Ubuntu server and type the sudo command to get the root privileges on your system.
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.
As a result, you’re ready to install the pgAdmin4 on 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‘.
Now type the password for the pgAdmin4 authentication and choose ‘OK‘.
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.
Type ‘\q‘ to exit from the PostgreSQL shell.
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.
On the pgAdmin4 login page, type your email address and password, then click the ‘Login‘ button.
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.
On the ‘General‘ tab, type the name of your connection.
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.
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‘.
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.
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…‘.
On the ‘General‘ tab, type your new username.
Move to the ‘Definition‘ tab and type 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.
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‘.
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.
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‘.
Now make a mark for all objects on the database and click ‘Next‘.
Add your user by clicking the ‘+’ button and select the privilege as you want, then click ‘Next‘.
Now click the ‘Finish‘ button.
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‘.
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.
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.