DigitalOcean
SnapShooter

How to Install PostgreSQL with pgAdmin4 on Ubuntu 20.04

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.

Table Of Contents

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:

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

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

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

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

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.

Was this page helpful?

Thank you for helping us improve!