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 server1.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:
postgres@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]