SnapShooter Learning Center

How to Install PostgreSQL with pgAdmin4 on Ubuntu 20.04

Learn how install PostgreSQL with pgAdmin4 on Ubuntu 20.04

Simon Bennett]
Simon Bennett
Last Updated: Apr 14, 2021
Table of Contents

pgAdmin is a free, open-source and one of the most popular database management platforms for PostgreSQL. It has simple and powerful web interface that helps you to write simple SQL queries to develop complex databases. It is written in Python and JQuery and has a lot of improvements compared to pgAdmin 3. It is cross-platform and can be installed on multiple platforms such as Linux, Windows and Mac OS X.

In this guide, we will show you how to install PostgreSQL with pgAdmin4 on Ubuntu 20.04.


# Requirements

  • A server running Ubuntu 20.04.
  • A root password is set up on your server.

# Install PostgreSQL

By default, the latest version of PostgreSQL packages is included in the Ubuntu 20.04 default repository. You can install it by running the following command:

apt-get install postgresql -y

Once the PostgreSQL has been installed, start the PostgreSQL service and enable it to start at system reboot:

systemctl start postgresql
systemctl enable postgresql

Next, verify the status of the PostgreSQL service using the command below:

systemctl status postgresql

You should get the following output:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2021-04-12 02:06:54 UTC; 25s ago
   Main PID: 2284 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2353)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Apr 12 02:06:54 ubuntu systemd[1]: Starting PostgreSQL RDBMS...
Apr 12 02:06:54 ubuntu systemd[1]: Finished PostgreSQL RDBMS.

BY default, PostgreSQL listen on port 5432. You can verify it using the following command:

ss -antpl | grep 5432

You should get the following output:

LISTEN    0         244              127.0.0.1:5432             0.0.0.0:*        users :(("postgres",pid=2526,fd=4))                                             
LISTEN    0         244                  [::1]:5432                [::]:*        users:(("postgres",pid=2526,fd=3))  

Next, verify the PostgreSQL connection with the following command:

pg_isready

You should get the following output:

/var/run/postgresql:5432 - accepting connections

# Create a PostgreSQL Database and User

Next, you will need to create a database and user in PostgreSQL.

First, connect to the PostgreSQL shell with the following command:

su - postgres
psql

Once connected, create a user and database with the following command:

postgres=# CREATE USER testuser WITH PASSWORD 'password';
postgres=# CREATE DATABASE testdb;

Next, grant all the privileges to PostgreSQL database with the following command:

postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;

Next, exit from the PostgreSQL shell with the following command:

postgres=# \q
exit

At this point, you have a database and a user is created in the PostgreSQL server.


# Configure PostgreSQL Client Authentication

By default, PostgreSQL uses a client authentication to control the remove database connections. All settings are located in /etc/postgresql/12/main/pg_hba.conf file.

nano /etc/postgresql/12/main/pg_hba.conf

Change the following lines:

host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Save and close the file then restart the PostgreSQL service to apply the changes:

systemctl restart postgresql

# Install pgAdmin4

By default, pgAdmin4 is not included in the Ubuntu 20.04 default repository. So you will need to add the pgAdmin4 repository to the APT.

First, install all the required dependencies using the following command:

apt-get install curl gnupg2 -y

Once installed, download and add the GPG key with the following command:

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | apt-key add

Next, add the pgAdmin4 repository using the following command:

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

Next, update the repository cache and install the latest version of pgAdmin4 with the following command:

apt-get update
apt-get install pgadmin4

After installing pgAdmin4, you will need to run a web setup script to configure the system to run in web mode. You can run it using the following command;

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

You will be asked to provide your Email and password to finish the configuration as shown below:

Setting up pgAdmin 4 in web mode on a Debian based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: admin@example.com
Password: 
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Creating storage and log directories...
We can now configure the Apache Web server for you. This involves enabling the wsgi module and configuring the pgAdmin 4 application to     mount at /pgadmin4. Do you wish to continue (y/n)? y
The Apache web server is running and must be restarted for the pgAdmin 4 installation to complete. Continue (y/n)? y
Apache successfully restarted. You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4

# Access pgAdmin4 Web Interface

Now, you can access the pgAdmin4 web interface using the URL http://your-server-ip/pgadmin4. You will be redirected to the pgAdmin4 login page:

pgAdmin Login

Provide your email address, password and click on the Login button. You should see the pgAdmin4 dashboard in the following page:

pgAdmin Dashboard

Next, click on the Add New Server to connect to the PostgreSQL server. You should see the following page:

pgAdmin Connection1

Provide your servername in General settings then click on the Connections tab. You should see the following page:

pgAdmin Connection2

Provide your PostgreSQL host, database user, password and click on the Save button. Once the connection is established, you should see the following page:

pgAdmin Database


# Conclusion

Congratulations! you have successfully installed PostgreSQL and pgAdmin4 on Ubuntu 20.04 server. You can now explore the pgAdmin4 web interface and start managing your PostgreSQL server through a web browser.


Did you find this article helpful?