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 updateDownload 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-devCreation 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/pgadmin4You 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/pgadmin4Then 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/activateAfter 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 pipUsing 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.whlThe downloaded package is configured using pip:
python -m pip install wheelInstalling 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 gunicornThe 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.origEdit 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.pyIn 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 = TrueThis 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.pyAfter 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: 6339The 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)
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.
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 && psqlIn 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).
Let's enter the name (see Figure 4).
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).
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 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)
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).
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).
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).
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');
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)
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)
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).
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’); 
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).
Now let's see the results of our code query. The data is filled in accordance with the written request (see Screen 17)
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.
