How to Install and Configure pgAdmin 4 in Server Mode on Ubuntu 22.04

Introduction

pgAdmin is an open source project designed to manage PostgreSQL databases. PostgreSQL is one of the most popular database management systems in the world.

The tool is easy to use for beginners and professionals working with PostgreSQL. It uses an object-relational database and provides a web interface and desktop version for easy interaction with database objects.

Cloud Servers from €4 / moIntel Xeon Gold 6254 3.1 GHz CPU, SLA 99,9%, 100 Mbps channeltry

Preparing for installation

For proper operation between pgAdmin 4 and PostgreSQL, it is necessary to prepare in advance:

• Deployed Ubuntu Server 22.04. A user with sudo command access. Utility for configuring the ufw firewall. We invite you to use our instruction;
• Installed and configured Nginx as reverse proxy http://unix:/tmp/pgadmin4.sock;
• Installed PostgreSQL, you need to add a new user, as well as database to connect pgAdmin to our database;
• Virtual environment deployed in Python;

Stage 1 – Deploying pgAdmin and downloading dependencies

Updating package indexes first, let's use the built-in apt utility.

Download the dependencies of libgmp3-dev, a high-precision arithmetic library; libpq-dev, a library that allows you to work with the PostgreSQL backend.

Create several directories for the purpose of storing information on connected sessions, storage and log files:

Create user serverspace:

Then a new passphrase for the user serverspace must be set:

Add it to the sudo group:

Next, change the directory owners from root to serverspace, some utilities do not allow access to run services as root:

Then go to the folder created under the name "test". In these instructions, the names of the directories are given in the same way as in the instructions. Go to the previously created test directory and run the test_env environment:

Running the test_env virtual environment, update the pip package to install the required library. In the case of a non-updated pip, we may encounter some difficulties in installing pgAdmin.

To update the pip, proceed as follows:

Use a web browser to download the latest release of a file with a .whl extension. Which is the standard package extension for the Python distribution. Copy the utility link and type in the command line:

Using pip, configure the downloaded package:

Set it up:

Download Gunicorn in Python WSGI, which will communicate with Nginx:

The first step has been completed successfully. Modify the configuration file, then you can connect to the database.

Stage 2 – Configuring pgAdmin 4

You need to back up the config.py file, which is located in the folder of the installed pgAdmin package. In this example the path to the file:
/test/test_env/lib/python3.10/site-packages/pgadmin4/

Changing the master file - can result in loss of data:

The config.py file should only be edited if necessary to avoid damaging the data and performance of pgAdmin 4.

Make edits to the config_local.py file.

Important! The file must be located at the address: /site-packages/pgadmin4/

You can use the touch utility to create a file, or you can create and open a file with vim or nano. We will use nano:

After opening, we will make the edits:

This piece of code does:
LOG_FILE: Path to the log file
SQLITE_PATH: Path to user data in SQLite database The file is stored in /var/lib/pgadmin4/ directory, our user data will not be lost after upgrade.
SESSION_DB_PATH: Path to a directory containing information about connected sessions.
STORAGE_DIR: Path to a directory containing storage and certificate information.
SERVER_MODE: The server mode selected is True.

To exit and save, press ESC, type :wq! and press ENTER.

To start configurations from this location, start pgAdmin with a number of commands.

Set up a public key:

To proceed, add a configuration file. Send the following command to the terminal:

Install pgadmin4:

Let's run the configuration:

Next we will write our details (email, passphrase) for the future login to the pgAdmin web interface:

pgAdmin is now available on our network.

Stage 3 – Deploying Gunicorn and Nginx

Gunicorn is responsible for running pgAdmin as a web application. It is currently possible to connect to pgAdmin from local computers. A Nginx web server is configured for access outside our network.

Go to the root directory, then go to the address:

Make changes to the /test_domain.ru file:

Let's run the Python virtual environment. Connect the reverse proxy using a few commands:

The command must be executed from the test_env virtual space and there must be no library related errors in the output.

Stage 4 - Access to pgAdmin 4

On the machine on our network, start a web browser and in the address bar specify the address of the host where pgAdmin is deployed http://server_ip/pgadmin4.

The current example uses the address http://10.0.0.1/pgadmin4

When logging in, specify the email and password used to create the configuration (End "Step 2 - Configuring pgAdmin 4").

Log in by clicking on the Login button (see Screen 1)

Screen 1 - Access to pgAdmin

If authorised successfully, you will see the home page of the pgAdmin web interface (see Screen 2).

Screen 2 - pgAdmin control panel

Now edit the configuration file used to connect to the PostgreSQL database.

Stage 5 – PostgreSQL user configuration

Pressing CTRL + C will stop the process, thus going back to the console.

By following the Setup Process item from the instructions. Let's proceed to setup.

Log in to the "built-in" administrator account:

Create a user and the database itself using commands:

For example:

In the database terminal, add a password for the serverpace user. When deploying the DBMS, each user needs to create complex passwords and specify user privileges:

Exit by pressing \q.

Use the browser to access the pgAdmin 4 console. Add a new server using the Add New Server tool (see Screen 3).

Screen 3 - Adding a new server

Let's enter the name (see Screen 4).

Screen 4 - Server name

Let's go to the Connection tab, fill in the fields Hostname / address (Host name / address) - localhost, Maintenance database (Serving database) - serverspacedb, Username (User name) - serverspace, Password (Password) - specified earlier on the command line. After filling in the fields, click the Save button (see Screen 5).

Consider! The illustrations show the name of the database: serverspace.

Screen 5 - Filling in the fields for connecting to the database

The correctness of the connection is checked in the Browser tab. Symbols (1) will appear in the Servers tab, notifying that a new server has been connected. In expanded form, we get information about Database (Databases), Login / Group Roles (Roles / Login Group), Tablespace (Tablespaces). The Databases section stores two postgres databases (the standard database installed by default in PostgreSQL) and serverspacedb previously created. (See Screen 6).

Screen 6 - Added server with PostgreSQL tables

Added a server with a few clicks of the mouse.

Stage 6 – Creating a table using the Web Interface.

To create a table in a database, you must:

1. Select Schemas in the serverspacedb database, then Tables;
2. Select Create -> Table.(see Screen 7)

Screen 7 - Creating a table

The Create table window will open, in the General tab. Next, enter the name of the table in the field Name. Owner and Schemas are filled in automatically. If necessary, you can make changes at your discretion (see Screen 8).

Screen 8 - Table name

Let's go to the tab Columns, adding a column is as follows, press the "+" button, specify Name, Data type and the value Not NULL so that all columns are not empty and select Primary key to specify the primary key and click Save (See Screen 9).

Screen 9 - Creating columns in the table

The set up database and our table with columns appeared. Let's start filling the table with SQL. Right-click on our table and click on Scripts -> CREATE Script (see Screen 10).

Screen 10 - Creating a script

Add a script to fill in the columns, you can also add more information separated by commas (see Screen 11).

Screen 11 - Adding a script

Run the script with the (▶) button and close the editor window.

To view the changes made to the table, proceed as follows:

• Right-click on the desired table;
• Select View/Edit Data;
• Then select All Rows. (see Screen 12)

Screen 12 - View all lines

In the window that opens with the results of the completed query, the completed data is stored in the Data Output section. (see Screen 13)

Screen 13 - Recorded data

The method used is considered one of those used to create the table and fill it in.
However, the following example uses only a SQL query:

• Call the context menu on the Tables item;
• Select Query Tool. (see Screen 14).

Screen 14 - Opening the query tool

Create a table and columns. Fill the created fields with the data type, and information with the values "15, 'test_table2', 'True'" (see Screen 15):

Screen 15 - Creating and filling a table

When checking for a new table, we will not find the new table until we update the serverspacedb database.
Right-click on the database and select Refresh. (see Screen 16)

Screen 16 - Database update

Now let's see the results of our code query. The data is filled in accordance with the written request (see Screen 17)

Screen 17 - The result of the executed request

We have two tables: one using a visual interface and one using SQL.

Cloud Servers from €4 / moIntel Xeon Gold 6254 3.1 GHz CPU, SLA 99,9%, 100 Mbps channeltry

Conclusions

pgAdmin is a unique tool for working with a database in web-interface mode.
How to work:

1. Install Ubuntu Server 22.04;
2. Configure ufw for port 80/tcp;
3. Install Nginx, Python, PostgreSQL;
4. Deploy the virtual environment in Python;
5. Install the Gunicorn library;
6. Configure a reverse proxy in Nginx;
7. Configure Gunicorn and Nginx to work together;
8. Run the pgAdmin virtual server installation script;
9. Start the server;
10. Go to the local host server in your browser;
11. Add server, specify data for PostgreSQL;
12. Create the table, fill in the data and edit.