news
Serverspace launched CDN
DW
February 27, 2023
Updated February 27, 2023

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

NGINX Ubuntu

Introduction

pgAdmin is an open-source product associated with the PostgreSQL database management system. PostgreSQL is one of the most popular database management systems in the world.

pgAdmin 4 is a popular tool, easy to use for beginners and professionals working with PostgreSQL using an object-relational database that provides a web interface and a desktop version for easy creation, maintenance and use of database objects.

Preparing for installation

For correct work between pgAdmin 4 and PostgreSQL, you need to:

  • 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.

sudo apt update

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

sudo apt install libgmp3-dev libpq-dev

Creation of several directories to store information about connected sessions, storage and log files.

sudo mkdir -p /var/lib/pgadmin4/sessions
sudo mkdir /var/lib/pgadmin4/storage
sudo mkdir /var/log/pgadmin4

You should change the directory owners from root to serverspace, some utilities do not allow access to run services in superuser mode:

sudo chown -R serverspace:serverspace /var/lib/pgadmin4
sudo chown -R serverspace:serverspace /var/log/pgadmin4

Then let's go to the directory created called environments. We specify the name of the directories in accordance with the official documentation of Python 3. Let's go to the previously created environments directory and start the my_env environment:

cd environments/
source my_env/bin/activate

After running my_env virtual environment, you should update the pip package to install the required library. In the case of a non-updated pip, we may encounter some difficulties installing pgAdmin.
The pip update goes like this:

python3 -m pip install -U pip

Using a web browser, you need to download the latest release from the official site of pgAdmin 4 file with the .whl extension. Which is the standard package extension for the Python distribution. Copy the utility link, write the wget command on the command line and paste the link:

wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.10/pip/pgadmin4-6.10-py3-none-any.whl

The downloaded package is configured using pip:

python -m pip install wheel

Installing the pgAdmin 4 package is done with the command:

python -m pip install pgadmin4-6.10-py3-none-any.whl

Скачаем Gunicorn на Python WSGI, который будет взаимодействовать с Nginx, чтобы обеспечить web-интерфейс pgAdmin.

python -m pip install gunicorn

The first stage was completed successfully. Installed pgAdmin and its dependencies. Before connecting to the database, you need to make changes to the configuration file.

Stage 2 – Configuring pgAdmin 4

You need to make a backup copy of the config.py file located in the directory of the installed pgAdmin package. Making changes to the main configuration file in the future may result in data loss:

cp config.py config.py.orig

Edit the config.py file only if necessary, so as not to damage the data and performance of pgAdmin 4. Let's add a few changes to a new file called config_local.py. To create, you can use the touch utility to create a file, or immediately create and open in the vim or nano editor. We will use the vim editor:

vim /my_env/lib/python3.10/site-packages/pgadmin4/config_local.py

In the open file, add a few lines of code:

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
SERVER_MODE = True

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 run configurations from this place, launch pgAdmin, run the command and enter our data:

python environments/my_env/lib/python3.10/site-packages/pgadmin4/setup.py

After running the file, we need to enter our further login data into the pgAdmin web interface:

NOTE: Configuring authentication for SERVER mode. #Authentication Configuration Notice
Enter the email address and password to use for the initial pgAdmin user account: #Entering an email address to register with pgAdmin
Email address: test@serverspace.team #E-mail address
Password: #Password
Retype password: #Retype your password

pgAdmin installation is complete and available on the local network, let's deploy it to remote desktops using Gunicorn and Nginx.

Stage 3 – Deploying Gunicorn and Nginx

Gunicorn is responsible for running pgAdmin as a web application. At the moment, it is possible to connect to pgAdmin from local computers; to gain access outside the local network, the Nginx web server is configured. Let's make changes to the /etc/nginx/sites-available/serverspace.io file:

server {
listen 80; #Listening on port 80 IPv4
listen [::]:80; #In most cases, when starting nginx -t gives an error, is sent to this line, when you get an error, comment out the line
server_name serverspace.io www.serverspace.io; #Server name
location / { #Path
proxy_pass http://unix:/tmp/pgadmin4.sock; #reverse proxy
include proxy_params;
}
}

Let's start the Python virtual environment. Enable the reverse proxy configuration using a few commands:

source environments/my_env/bin/activate
gunicorn --bind unix:/tmp/pgadmin4.sock --workers=1 --threads=25 --chdir ~/environments/my_env/lib/python3.10/site-packages/pgadmin4 pgAdmin4:app
#Output
[2022-11-04 21:55:37 +0000] [6338] [INFO] Starting gunicorn 20.1.0
[2022-11-04 21:55:37 +0000] [6338] [INFO] Listening at: unix:/tmp/pgadmin4.sock (6338)
[2022-11-04 21:55:37 +0000] [6338] [INFO] Using worker: gthread
[2022-11-04 21:55:37 +0000] [6339] [INFO] Booting worker with pid: 6339

The command must be executed from the virtual space my_env and the output must not contain errors related to libraries.

Stage 4 - Access to pgAdmin 4

On the local machine, launch a web browser and in the address bar specify the address of the host where pgAdmin is deployed http://server_ip/pgadmin4. When authorizing, we will specify the e-mail, password and perform authorization by clicking on the Login button (See Screen 1)

Access to pgAdmin
Screen 1 - Access to pgAdmin

Successful authorization is shown to us on the main page of the pgAdmin web interface (see Screen 2). The standard window stores various features and tools from adding a new server to the support community.

pgAdmin control panel
Screen 2 - pgAdmin control panel

After gaining access to pgAdmin, let's make a small change in the configuration file to connect to the PostgreSQL database.

Stage 5 – PostgreSQL user configuration

From the account of the previously created user, you need to connect to the database:

su - serverspace && psql

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

ALTER USER serverspace PASSWORD 'p@55w0rd';

Exit by pressing Ctrl + C or \q:

Add a new server to the pgAdmin 4 web interface using the Add New Server tool (see Screen 3).

Adding a new server
Screen 3 - Adding a new server

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

Server name
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) - serverspace, Username (User name) - serverspace, Password (Password) - specified earlier on the command line. After filling in the fields, click the Save button (see Screen 5).

Filling in the fields for connecting to the database
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 serverspace previously created. (See Screen 6).

Added server with PostgreSQL tables
Screen 6 - Added server with PostgreSQL tables

Added a server with a few mouse clicks. The connection between the pgAdmin web interface and PostgreSQL (psql) is built correctly and no conflicts were observed during the connection. A remote host can connect to pgAdmin via the web interface using the server's IP address.

Stage 6 – Creating a table using the Web Interface.

To create a table in a database, you must:

1. Select Schemas in the desired database and right-click on Tables

2. Select Create and Table. (see Screen 7)

Creating a table
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).

Table name
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).

Creating columns in the table
Screen 9 - Creating columns in the table

There is an added database and a created table with columns. Let's start filling the table using the SQL language. You must right-click on the created table and select Scripts and CREATE Script (see Screen 10).

Creating a script
Screen 10 - Creating a script

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

/*Adding information to a table*/
INSERT INTO public."test_table"(
/*Line names*/
test_col1,
test_col2,
test_col3)
/*Values*/
VALUES
/*Row Information*/
('Test1', 10, 'True'),
('Test2', 20, 'False'),
('Test3', 30, 'True');
Adding a script
Screen 11 - Adding a script

Run the script to execute the request using the button (▶) and close the window with the editor. Viewing the changes made in the table is performed as follows, using the right mouse button, click on the desired table, select View / Edit Data and select All Rows (see Screen 12)

View all lines
Screen 12 - View all lines

A window will open with the results of the completed query, at the bottom of the screen, in the Data Output section, the completed data is stored (see Screen 13)

Recorded data
Screen 13 - Recorded data

The method used is considered one of those used to create the table and fill. However, the following example only uses an SQL query, and to do so, right-click on the Tables menu and select Query Tool (see Screen 14).

Opening the query tool
Screen 14 - Opening the query tool

In the window that opens, use the following query to create a table with columns and their data types, and fill the columns with information with the values ​​"15, 'test_table2', 'True'" (see Screen 15).

/*Create a table called test_table2*/
Create table test_table2 (
/*Table attributes*/
test_table1 int,
test_table2 text,
test_table3 boolean );
/*Script to fill the table*/
insert into test_table2 (
/*Entering data into tables and their attributes*/
test_table1,
test_table2,
test_table3 )
/*Fillable Attribute Values*/
values (15, ‘test_table2’, ‘True’);
Creating and filling a table
Screen 15 - Creating and filling a table

When checking a new table, we won't find the new table until we update the serverspace database. We click on the database with the right mouse button and select Refresh (see Screen 16).

Database update
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)

The result of the executed request
Screen 17 - The result of the executed request

Created two tables using a graphical interface and using SQL queries. Also, to grant a privilege to one or a group of users, you can use the SQL query and the GRANT command.

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 a Python virtual environment.
5. Install the Gunicorn Library.
6. Set up a reverse proxy in Nginx.
7. Configure Gunicorn and Nginx to Work Together.
8. Run the script to install the pgAdmin virtual server.
9. Start the server.
10. Navigate your browser to the address of the localhost server.
11. Add a server, provide data for PostgreSQL.
12. Create a table, fill in data and edit.

Vote:
5 out of 5
Аverage rating : 5
Rated by: 1
1101 CT Amsterdam The Netherlands, Herikerbergweg 292
+31 20 262-33-82
700 300
ITGLOBAL.COM NL
700 300
We use cookies to make your experience on the Serverspace better. By continuing to browse our website, you agree to our
Use of Cookies and Privacy Policy.