How to install and configure PostgreSQL and pgAdmin4 on Ubuntu-Mate 22.04

Introduction

This guide concerns databases, specifically relational SQL databases. Databases are usually installed as database-servers on some remote computer and accessed by users via a so-called "front-end" program which typically today will be programmed to run in a browser such as Firefox, Chrome, etc.

However, if you want to learn and practice the SQL language for interacting with databases this kind of "setup" may not be available to you. Fortunately, it is possible to install both a working PostgreSQL server and a front-end program (in this case pgAdmin4) to easily access and use the PostgreSQL database, which is running on the PostgreSQL server, all on your own computer.

There are a number of reasons why you may wish to install the PostgreSQL Open Source Relational Database on your own computer, rather than on a server. Two that spring to mind are (a) using it as an environment to learn the SQL database manipulation language and (b) running queries against single "flat files" e.g. ".csv" files obtained as the output of some other program (in some countries public health authorities made COVID-19 pandemic related data available to the public as ".csv" files).

This guide will walk you through (1) installing a PostgreSQL server, (2) the initial setting up of the PostgreSQL server, (3) installing pgAdmin4 and (4) connecting pgAdmin4 to the PostgreSQL server. At the end of these steps you will have a GUI tool (pgAdmin4) with which you can run SQL queries against tables of data on the PostgreSQL server installed on your own computer (even if it's a notebook).

**You can find many sources of SQL tutorials, available for free, on the Internet.

If you are not sure where to begin, there is a series of around 30 video tutorials from the Youtube channel "Analytics Excellence" [https://www.youtube.com/c/AnalyticsExcellence], which are easy to understand and will give you an idea of what it is all about.

LINK:

https://www.youtube.com/watch?v=-g8rVKZsVEE&list=PL0hSJrxggIQrz5OKtnaFuPiyMDTXUF5J1

Note that this is by no means a full course covering everything about relational databases and PostgreSQL, however, it will give you an idea of how you can query data stored in such databases, and what is possible.

Security. As all the programs are going to be installed on your own computer no attention has been paid to questions of securely setting up the PostgreSQL server or protecting the pgAdmin4 connection to the server. If you wish to install the PostgreSQL server on a "remote" computer you should find out about the necessary security measures that you should apply; numerous sources of information are available on the internet.

This guide assumes that you are familiar with using the terminal to issue commands to your computer.

The four steps

(1) Installing a PostgreSQL server

1.1 - Installing the server

Open a terminal (Ctrl+Alt+T)

Enter:

$ sudo apt install postgresql postgresql-contrib

Note: Postgres will be setup to use port 5432 by default.

Note: The Postgres server will start each time you boot (start) or re-boot (re-start) your computer. If you are not going to use it, you may want to shut it down (see section 2.2)

(2) Initial setting up of the PostgreSQL server

2.1 - Introduction

Most distributions install the Postgres database without starting it, but provide you with a script or systemd service to help it start reliably. However, before you start PostgreSQL, you must create a database cluster. On Debian-based distributions, setup is performed automatically by apt during installation. Ubuntu-Mate is based on Ubuntu, which is based on Debian. A systemd service was installed when you installed the database.

2.2 - systemd - start, status and stop controls

Always begin by checking if the PostgreSQL server is already running:

$ sudo systemctl status postgresql

To start the PostgreSQL server:

$ sudo systemctl start postgresql

To stop the PostgreSQL server:

$ sudo systemctl stop postgresql

2.3 - Switch over to the PostgreSQL server

Enter the command:

$ sudo su postgres

2.4 - The psql shell

To issue commands to the PostgreSQL server you need to enter the psql-shell.

Enter the command:

psql

Note that the 'psql shell prompt' looks like this:

postgres=#

MAN pages for psql:

You can open the MAN pages for psql in a standard bash shell (terminal), before you enter the psql shell:

$ MAN psql

2.5 - List all installed databases

At the PostgreSQL prompt (in the psql shell) issue the following command:

\l

The output should look something like this:

List of databases

Name Owner Encoding Collate Ctype Access privileges
postgres postgres UTF8 en_US.UTF-8 en_US.UTF-8

2.6 - List all users

At the PostgreSQL prompt issue the following command:

\du

The output should look something like this:

Role name List of roles, Attributes Member of
postgres Superuser, Create role, Create DB, Replication, Bypass RLS {}

2.7 - Change the password of the default user 'postgres'

At the PostgreSQL prompt issue the following command:

ALTER USER postgres WITH PASSWORD 'postgresjohn';

Note - in the psql shell ALL comands must end with a semi-colon ';'

If successful this returns:

ALTER ROLE

Here we have reset the password of the default user to 'postgresjohn'. You must of course choose a password of your own.

2.8 - Create a new user

At the PostgreSQL prompt issue the following command:

CREATE USER john WITH PASSWORD 'johnpostgres';

If successful this returns:

CREATE ROLE

now list all users again:

\du

The output should look something like this:

Role name List of roles, Attributes Member of
john {}
postgres Superuser, Create role, Create DB, Replication, Bypass RLS {}

To give user "john" superuser privileges:

ALTER USER john WITH SUPERUSER;

If successful this returns:

ALTER ROLE

now list all users again:

\du

The output should look something like this:

Role name List of roles, Attributes Member of
john Superuser {}
postgres Superuser, Create role, Create DB, Replication, Bypass RLS {}

2.9 - How to get out of the psql shell, and PostgreSQL server environment

To exit the postgres psql shell:

\q

This leaves you at this prompt:

[email protected]name-of-your-computer:/home/your-user-name$

To exit the postgres database:

exit

This leaves you at this prompt:

your-user-name@name-of-your-computer:~$

(3) How to install pgAdmin4

3.1 - Install the repository key

Open a terminal (Ctrl+Alt+T)

Enter:

wget -qO - https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/pgadmin-keyring.gpg

Then enter:

echo "deb [signed-by=/usr/share/keyrings/pgadmin-keyring.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/jammy pgadmin4 main" | sudo tee /etc/apt/sources.list.d/pgadmin4.list

3.2 - Install pgAdmin4

In the same terminal, enter:

sudo apt-get update

Then enter:

sudo apt-get install pgadmin4

3.3 - Install for desktop mode only

In the same terminal, enter:

sudo apt install pgadmin4-desktop

(4) How to connect pgAdmin4 to the PostgreSQL server

4.1 - Make sure the postgres server is running

Open a terminal (Ctrl+Alt+T)

Check if the PostgreSQL server is already running:

Enter:

sudo systemctl status postgresql

If the PostgreSQL server is not active, start it.

To start the PostgreSQL server enter:

sudo systemctl start postgresql

4.2 - Launch pgAdmin4 for the first time

From the programs-menu launch pgAdmin4

it takes a few seconds, just wait...

You will be asked to enter a (new) password for the program pgAdmin4...

Enter a password, in this example "pgadminroot".

The pgAdmin4 dashboard opens...

4.3 - The pgAdmin4 dashboard - setting up the connection to the server

Under the section "Quick Links" (it's more or less in the middle of the window) click on "Add New Server"

The "Register-Server" window opens...

Go through the tabs of the window, making sure that the following settings are entered.

TAB - General
Name: postgres
Server group: Servers

TAB - Connection
Host name/address localhost
Port: 5432
Maintenance database: postgres
Username: postgres
Kerberos authentification: off
Password: postgresroot
Save password: off
Role: empty
Service: empty

TAB - SSL
SSL mode: Prefer
Client certificate: empty
Client certificate key: empty
Root certificate: empty
Certificate revocation list: empty
SSL compression: off

TAB - SSH Tunnel
Use SSH tunneling: off

TAB - Advanced
do not make any changes here

Click on "save".

The pgAdmin4 dashboard changes to show some graphs of the status of the server.

4.4 - Shut down pgAdmin4 and the postgres server

To close the pgAdmin4 window, simply click on the window-button to close the window.

To close down the PostgreSQL server, open a terminal and enter:

sudo systemctl status postgresql

If the PostgreSQL server is active, stop it. To stop the PostgreSQL server enter:

sudo systemctl stop postgresql

You should now have a working PostgreSQL server and an instalation of pgAdmin4, on your own computer, with which you can learn and practice SQL coding for data manipulation.


Sources consulted for this guide

[1] [https://youtu.be/-LwI4HMR_Eg], from the youtube chanel "ProgrammingKnowledge"

[2] [https://www.itzgeek.com/how-tos/linux/ubuntu-how-tos/install-pgadmin-on-ubuntu-22-04.html]

[3] [https://www.cybrosys.com/blog/how-to-install-pgadmin-in-ubuntu]

4 Likes