Введение
pgAdmin – является open-source продуктом связанный с системой управления базами данных PostgreSQL. PostgreSQL - является одной из востребованной системой управления базами данных в мире.
pgAdmin 4 – востребованный инструмент, удобен в использовании ради новичков и профессионалов, работающих с PostgreSQL используя объектно-реляционную базу данных, предоставляющий web-интерфейс и desktop версию, для удобного создания, обслуживания и использования объектов базы данных.
Подготовка к установке
Для корректной работы между pgAdmin 4 и PostgreSQL, необходимо:
- Развернутый Ubuntu Server 22.04. Пользователь с доступом на выполнение команды sudo. Утилита для конфигурирования межсетевого экрана ufw. Предлагаем воспользоваться нашей инструкцией;
- Установленный и настроенный Nginx как обратный прокси http://unix:/tmp/pgadmin4.sock;
- Установленный PostgreSQL, необходимо добавить нового пользователя, а также базу данных с целью выполнения подключения pgAdmin к нашей базе данных;
- Виртуальная среда развернутая в Python ;
Этап 1 – Развертывание pgAdmin и скачивание зависимостей
Обновление индексов пакетов в первую очередь, воспользуемся встроенной утилитой apt.
Скачаем зависимости libgmp3-dev, библиотека арифметических вычислений с высокой точностью; libpq-dev, библиотека позволяющая функционировать с серверной частью PostgreSQL.
Создание несколько директорий с целью хранения информации о подключенных сессиях, хранилище и журнальные файлы.
sudo mkdir /var/lib/pgadmin4/storage
sudo mkdir /var/log/pgadmin4
Следует изменить владельцев директорий с root на serverspace, некоторые утилиты не дают доступ запускать службы в режиме суперпользователя:
sudo chown -R serverspace:serverspace /var/log/pgadmin4
Затем перейдём в каталог созданный под названием environments. Название каталогов указываем в соответствии с официальной документацией Python 3. Перейдём в ранее созданный каталог environments и запустим среду my_env:
source my_env/bin/activate
Запустив виртуальную среду my_env, следует обновить пакет pip для установки нужной библиотеки. В случае не обновленного pip, мы можем столкнуться с некоторыми трудностями при установке pgAdmin.
Обновление pip происходит следующим образом:
С помощью web-браузера необходимо скачать последний релиз с официального сайта pgAdmin 4 файл с расширением .whl. Который является стандартным пакетным расширением для Python дистрибутива. Скопируем ссылку утилиту, в командной строке пропишем команду wget и вставим ссылку:
Настройка скачанного пакета происходит с использованием pip:
Установка пакета pgAdmin 4 выполняется с помощью команды:
Скачаем Gunicorn на Python WSGI, который будет взаимодействовать с Nginx, чтобы обеспечить web-интерфейс pgAdmin.
Первый этап выполнен успешно. Установили pgAdmin и его зависимости. Перед подключением к базе данных, необходимо внести изменения в конфигурационный файл.
Этап 2 – Конфигурирование pgAdmin 4
Необходимо сделать резервную копию файла config.py находящийся в директории установленного пакета pgAdmin. Внесение изменений в основной конфигурационный файл в будущем может привести к потере данных:
Файл config.py редактировать только при необходимости, чтобы не повредить данные и работоспособность pgAdmin 4. Добавим несколько изменений в новый файл с названием config_local.py. Для создания можно воспользоваться утилитой touch для создания файла, либо сразу создать и открыть в редакторе vim или nano. Мы воспользуемся редактором vim:
В открытом файле добавим несколько строк кода:
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
SERVER_MODE = True
Данный кусок кода выполняет:
LOG_FILE: Путь до файла логов
SQLITE_PATH: Путь к пользовательским данным в базе данных SQLite Файл хранится в каталоге /var/lib/pgadmin4/, наши пользовательские данные не подлежат к потере после обновления.
SESSION_DB_PATH: Путь указывающий к каталогу хранящему информацию о подключенных сеансах.
STORAGE_DIR: Путь к каталогу сохраняющему информации о хранилище и сертификатах.
SERVER_MODE: Режим сервера выбранная True (Истина).
Для выхода с сохранением нажмём ESC, введём :wq! и нажмём ENTER.
Для запуска конфигураций с данного места, запуска pgAdmin следует выполнить команду и внести наши данные:
После запуска файла требуется внести наши данные дальнейшего входа в web-интерфейс pgAdmin:
NOTE: Configuring authentication for SERVER mode. #Уведомление о конфигурации аутентификации
Enter the email address and password to use for the initial pgAdmin user account: #Ввод адреса электронной почты для регистрации в pgAdmin
Email address: test@serverspace.team #Адрес электронной почты
Password: #Пароль
Retype password: #Повтор пароля
Установка pgAdmin завершена и доступен в локальной сети, развернём для удалённых рабочих столов с помощью Gunicorn и Nginx.
Этап 3 – Развертывание Gunicorn и Nginx
За работу pgAdmin в качестве web-приложения отвечает Gunicorn. В данный момент к pgAdmin есть возможность подключения из локальных компьютеров, для получения доступа за пределами локальной сети конфигурирует web-сервер Nginx. Внесём изменения в файл /etc/nginx/sites-available/serverspace.ru:
listen 80; #Прослушивание 80 порта IPv4
listen [::]:80; #В большинстве случаев при запуске nginx -t выдаёт ошибку, отсылается на эту строку, при получении ошибки закомментируйте строку
server_name serverspace.ru www.serverspace.ru; #Название сервера
location / { #Путь
proxy_pass http://unix:/tmp/pgadmin4.sock; #обратный прокси
include proxy_params;
}
}
Запустим виртуальную среду Python. Подключим конфигурацию обратного прокси использовав несколько команд:
gunicorn --bind unix:/tmp/pgadmin4.sock --workers=1 --threads=25 --chdir ~/environments/my_env/lib/python3.10/site-packages/pgadmin4 pgAdmin4:app
[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
Команда должна выполняться из виртуального пространства my_env и в ответ на выходе не должны быть ошибки связанные с библиотеками.
Этап 4 - Доступ к pgAdmin 4
На локальной машине запустим web-браузер и в адресной строке укажем адрес хоста, на котором развёрнут pgAdmin http://server_ip/pgadmin4. При авторизации и укажем электронную почту, пароль и выполним авторизацию нажав на кнопку Login (Войти) (см. рисунок 1)
Об успешной авторизации нам показано на главной странице web-интерфейс pgAdmin (см. рисунок 2). В стандартном окне хранятся разные функции и инструменты от добавления нового сервера до сообщества поддержки.
После получения доступа к pgAdmin, внесём небольшое изменение в конфигурационном файле для подключения к базе данных PostgreSQL.
Этап 5 – Конфигурация пользователя PostgreSQL
С учётной записи ранее созданного пользователя необходимо подключиться к базе данных:
В терминале базы данных добавим пароль для пользователя serverspace. При развёртывании СУБД, каждому пользователю необходимо создать сложные пароли и указать привилегии пользователя:
Выполним выход нажав Ctrl + C либо \q:
В web-интерфейс pgAdmin 4 добавим новый сервер с помощью инструмента Add New Server (Добавить новый сервер) (см. рисунок 3).
Введём название (см. рисунок 4).
Перейдём во вкладку Connection, заполним поля Hostname/address (Название хоста/адрес) - localhost, Maintenance database (Обслуживающая база данных) - serverspace, Username (Имя пользователя) - serverspace, Password (Пароль) – указанный ранее в командной строке. После заполнения полей нажмём кнопку Save (сохранить) (см. рисунок 5).
Правильность подключения проверяется во вкладке Browser (Браузер). Во вкладке Servers появятся символы (1), уведомляющие о подключении нового сервера. В развёрнутом виде получаем информацию о Database (Базы данных), Login/Group Roles (Роли/Группа входа), Tablespace (Табличные пространства). В разделе Базы данных хранится две базы данных postgres (стандартная база данных установленная стандартно в PostgreSQL) и serverspace ранее созданная. (см. рисунок 6).
Добавили сервер с помощью несколько кликов по мыши. Связь между web-интерфейс pgAdmin и PostgreSQL (psql) построен правильно и при связывании конфликты не наблюдались. Удалённый хост может подключиться к pgAdmin через web-интерфейс использовав IP-адрес сервера.
Этап 6 – Создание таблицы с помощью Web-Интерфейса.
Чтобы создать таблицу в базе данных, необходимо:
1. Выбрать Schemas в нужной базе данных и правой кнопкой мыши нажимаем на Tables (Таблицы)
2. Выбрать Create (Создать/Добавить) и Table (Таблицу). (см. рисунок 7)
Откроется окно Create table (Создание таблицы), во вкладке General (Общие). Далее внесём название таблицы в поле Name (Название). Owner (Владелец) и Schemas (Схемы) заполняются автоматически. При необходимости можно внести изменения на своё усмотрение (см. рисунок 8).
Перейдём во вкладку Columns (Столбцы), добавление столбца происходит следующим образом, нажимаем кнопку «+» указываем Name (Название), Data type (Тип данных) и значение Not NULL (Не пустое), чтобы все столбцы не были пустыми и выберем Primary key (Первичный ключ) для указания первичного ключа и нажимаем Save (Сохранить) (см. рисунок 9).
Имеется добавленная база данных и созданная таблица со столбцами. Приступим к заполнению таблицы с помощью языка SQL. Необходимо нажать правой кнопкой мыши по созданной таблице и выбрать пункт Scripts (Скрипты) и CREATE Script (Создать скрипт) (см. рисунок 10).
Добавим скрипт для заполнения столбцов, также можно добавить больше информации разделяя запятыми (см. рисунок 11).
INSERT INTO public."test_table"(
/*Название строк*/
test_col1,
test_col2,
test_col3)
/*Значения*/
VALUES
/*Информация о строках*/
('Test1', 10, 'True'),
('Test2', 20, 'False'),
('Test3', 30, 'True');
Запустим скрипт на выполнение запроса с помощью кнопки (▶) и закрываем окно с редактором. Просмотр внесённых изменений в таблице выполняется следующим образом, с помощью правой кнопкой мыши нажмём на нужную таблицу, выберем View/Edit Data (Посмотреть/Изменить данные) и выберем All Rows (Все строки) (см. рисунок 12)
Откроется окно с результатами выполненного запроса, в нижней части экрана, в разделе Data Output (Вывод данных) хранятся заполненные данные(см. рисунок 13)
Использованный метод считается одним из используемых для создания таблицы и заполнения. Однако, следующий пример использует только SQL запрос и для этого необходимо нажать правой кнопкой мыши на меню Tables и выбираем Query Tool (Инструмент запросов) (см. рисунок 14).
В открывшемся окне используем следующий запрос, для создания таблицы со столбцами и их типы данных, и заполнения столбцов информацией со значениями «15, ‘test_table2’, ‘True’» (см. рисунок 15).
Create table test_table2 (
/*Атрибуты таблицы*/
test_table1 int,
test_table2 text,
test_table3 boolean );
/*Скрипт для заполнения таблицы*/
insert into test_table2 (
/*Ввод данных в таблицы и в их атрибуты*/
test_table1,
test_table2,
test_table3 )
/*Заполняемые значения атрибутов*/
values (15, ‘test_table2’, ‘True’);
При проверке новой таблицы мы не обнаружим новую таблицу, пока не обновим базу данных serverspace. Нажимаем на базу данных правой кнопкой мыши и выбираем пункт Refresh (Обновить) (см. рисунок 16).
Теперь посмотрим результаты запроса нашего кода. Данные заполнены в соответствии с написанным запросом (см. рисунок 17)
Создали две таблицы используя графический интерфейс и использовав SQL запросы. Также для выдачи привилегии на одного или на группу пользователей, можно воспользоваться SQL запросом и командой GRANT.
Выводы
pgAdmin уникальный инструмент для работы с базой данных в режиме web-интерфейса.
Как работать:
1. Установить Ubuntu Server 22.04
2. Настройка ufw для порта 80/tcp
3. Установите Nginx, Python, PostgreSQL
4. Разверните виртуальную среду на Python
5. Установите библиотеку Gunicorn
6. Настройте обратный прокси в Nginx
7. Сконфигурируйте Gunicorn и Nginx для их совместной работы
8. Запустите скрипт для установки виртуального сервера pgAdmin
9. Запустите сервер
10. Перейдите в браузере по адресу сервера локального хоста
11. Добавьте сервер, укажите данные для PostgreSQL
12. Создайте таблицу, заполняйте данными и редактируйте