Подготовка к работе
Установка и настройка программного обеспечения для работы с базами данных. Следуйте инструкциям по порядку.
PostgreSQL — это объектно-реляционная система управления базами данных с открытым исходным кодом.
DBeaver Community — это бесплатный кросс-платформенный инструмент для работы с базами данных для разработчиков, администраторов баз данных, аналитиков и всех, кто работает с данными.
Учебные данные:
— резервная копия учебной базы данных dvd-rental — https://nkhcode.ru/uploads/backups/dvd-rental_custom.backup
— резервная копия учебной базы данных hr — https://nkhcode.ru/uploads/backups/hr.backup
Ссылка на PostgreSQL 17 версии (предпочтительный вариант) — https://nkhcode.ru/uploads/backups/p17.exe
Ссылка на страницу с дистрибутивом PostgreSQL — https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
Альтернативная ссылка на страницу с дистрибутивом PostgreSQL для Windows — https://postgrespro.ru/windows
На MacOS устаноку можно производить через Homebrew: brew install postgresql.
Так же установку можно производить с использованием Docker — https://hub.docker.com/_/postgres
Ниже инструкция по установке PostgreSQL на примере версии 16.2 в ОС Windows 10.
Другие версии PostgreSQL в других операционных системах имеют аналогичный алгоритм. В Mac OS можно использовать Homebrew, в Linux установка происходит из репозитория.
Открыв ссылку будет выбор версии PostgreSQL с учетом операционной системы:
Как выбрать нужную версию PostgreSQL?
Если находитесь в начале изучения SQL, то скачиваете последнюю актуальную версию.
Если уже знакомы с SQL и нужно понимать различия в версионности, тогда скачиваете 10, последнюю и несколько промежуточных версий. Можно на одном компьютере запустить несколько серверов с разными версиями. В учебных целях при небольших данных сервера затрачивают незначительные ресурсы системы.
Скачав нужную версию запускаете загруженный дистрибутив для установки PostgreSQL, и нажимаете — Next
Выбираете директорию для установки PostgreSQL, нажимаете — Next
Отмечаете программные модули, которые нужно установить:
PostgreSQL Server — сервер базы данных PostgreSQL
pgAdmin4 — графический интерфейс для работы с базой данных
Stack Builder — система для установки дополнительных инструментов и драйверов для PostgreSQL
Command Line Tools — инструменты для работы с базой данных в командной строке
Выбираете первый и последний пункты.
Далее нажимаете — Next
Указываете директорию для хранения базы данных, нажимаете — Next
Устанавливаете пароль для суперпользователя базы данных.
Так как не предполагается работа с коммерческими или иными данными, то не нужно придумывать очень сложный пароль, кнопки "восстановить пароль" не будет.
Нажимаете — Next
Указываете номер порта на котором будет работать PostgreSQL, по умолчанию — 5432. Если данный порт занят другим приложением, то будет конфликт.
Если планируете установить несколько версий PostgreSQL, то для каждой версии нужно указать разные значения портов, которые должны быть свободны и не заняты другими приложениями.
Нажимаете — Next
Выбираете язык для работы с PostgreSQL. Если будет выбрано значение [Default locale], будет использован язык, который используется в операционной системе.
Нажимаете — Next
Мастер установки выведет сводную информацию перед установкой, чтобы вы могли её проверить и исправить в случае ошибки. Нажимаете кнопку — Next
Запускаете установку PostgreSQL нажатием кнопки — Next
Ждете завершения процесса установки
Установка PostgreSQL завершена. Нажимаете кнопку — Finish
Фоновый процесс PostgreSQL Server запускается в автоматическом режиме после завершения установки. Если этого не произошло — перезагрузите компьютер.
Теперь можно запускать DBeaver и создавать соединение к данному серверу PostgreSQL.
Ссылка на страницу с дистрибутивом DBeaver — https://dbeaver.io/download/
Если ссылка выше не работает, можно скачать DBeaver 25.0.3 версии — https://nkhcode.ru/uploads/backups/d.exe
Ниже инструкция по установке DBeaver на примере версии 24.0.3 в ОС Windows 10.
Другие версии DBeaver в других операционных системах имеют аналогичный алгоритм.
Переходите по ссылке https://dbeaver.io/download/ и скачиваете DBeaver Community (бесплатная версия) в соответствии с вашей операционной системой.
Если у вас 32 битная операционная система, то нужно перейти в раздел Archive, в папку с версией 6.0.0 и работать с ней, так как это последняя версия, поддерживающая 32 битные системы: https://dbeaver.io/files/6.0.0/
После скачивания установочного файла запускаете его.
Выбираете язык установки и нажимаете ОК
Пропускаете экран приветствия, нажимаете — Далее
Принимаете условия лицензионного соглашения, нажимаете — Принимаю
Выбираете для кого будет установлено программное обеспечение, то есть, для всех пользователей компьютера или только для одного пользователя. Выбираете, к примеру, пункт — For me (Имя пользователя) и нажимаете — Далее
Отмечаете компоненты, которые необходимо установить: DBeaver Community и Include Java, нажимаете — Далее. Пункта Include Java на некоторых системах может не быть, значит Java установлена и дополнительная установка не требуется.
Указываете директорию для установки DBeaver и нажимаете — Далее
Выбираете директорию в меню пуск, в которую будут помещены ярлыки DBeaver. Нажимаете — Установить
Ждете завершения процесса установки
По окончанию установки DBeaver, при необходимости ставите галочку на пункте — Create desktop Shortcut, и нажимаете — Готово
Запускаете DBeaver.
При первом запуске DBeaver предложит создать тестовую базу данных SQLite, нажимаете — Нет
Если ни одно из соединений заранее не подготовлено, то нажимаете — Отмена.
Если есть готовое соединение, то выбираете необходимое и указываете параметры подключения
Теперь можете приступать к работе.
Как настроить соединение к базе данных на примере PostgreSQL указано в другом разделе.
Ссылка на документацию DBeaver — https://dbeaver.com/docs/dbeaver/
Для того, что бы в DBeaver создать соединение к источнику данных, необходимо нажать на кнопку — Новое соединение
Выбираете PostgreSQL и нажимаете — Далее
В настройках соединения указываете следующие параметры:
Хост: если подключение локальное — localhost, если облачное решение — IP адрес внешнего сервера
Порт: по умолчанию 5432 или тот порт, который был указан при установке сервера PostgreSQL или порт внешнего сервера
База данных: по умолчанию postgres или иное название, если оно отличается от значения по умолчанию
Пользователь: по умолчанию postgres или иное имя учетной записи под которым производите подключение к источнику данных
Пароль: указываете пароль от учетной записи под которой производите подключение, при локальном подключении указываете пароль, который указывали при установке сервера PostgreSQL
Показать все базы данных: ставите галочку, что бы в подключении отображались все базы данных, а не только та к которой производите подключение
Нажимаете — Тест соединения
Для подключения к серверу PostgreSQL необходимы драйвера.
Если есть все необходимые права доступа на компьютере, нажимаете — Скачать.
Если необходимых прав доступа для установки драйвера нет, тогда нужно будет вручную скачать и установить JDBC драйвер (как это сделать описано в разделе Ручное добавление JDBC драйвера в DBeaver)
После скачивания и установки драйверов пройдет тест на подключение, если подключение есть, нажимаете — ОК.
Если подключения нет — значит допущена ошибка в настройках подключения или сервер не запущен.
Нажимаете — Готово и в левой части отобразится новое соединение
Для того, что бы отображались все необходимые служебные объекты, нажимаете правую кнопку мыши на соединении — Connection view — Advanced
На предложение о переподключении к базе данных из-за смены параметров нажимаете — Да
Теперь можете раскрыть подключение и приступить к восстановлению данных
Восстаноавление данных на примере учебной базы данных dvd-rental. База данных hr восстанавливается по такому же алгоритму.
Скачиваете по ссылке https://nkhcode.ru/uploads/backups/dvd-rental_custom.backup файл с резервной копией данных и сохраняете в любую папку.
В DBeaver нажимаете правую кнопку мыши на базе данных postgres, выбираете Инструменты -Восстановить
Где Файл резерва нажимаете на иконку папки и выбираете заранее скачанный файл с резервной копией. Галочки ставить никакие не нужно. Нажимаете - Старт
В окне с предупреждением нажимаете - Да
Ожидаете процесса восстановления данных
По окончании процесса нажимаете - Закрыть или через крестик закрываете окно
После восстановления базы dvd-rental данные будут находится в схеме public. База hr восстановится в схему hr.
Если нажать правую кнопку мыши на названии схемы public и выбрать — View Diagram, то откроется Entity Relation Diagram (Диаграмма отношений — связей)
По диаграмме можно предварительно изучить структуру данных, какие есть таблицы, столбцы в таблицах, определить примерные типы данных и ограничения
Для того, что бы начать писать запросы к данным нужно нажать правую кнопку мыши на схему public и выбрать — Редактор SQL — Новый редактор SQL
В редакторе можете писать запросы. Для выполнения запроса нажимаете на кнопку Выполнить SQL запрос или нажимаете сочетание клавиш Ctrl + Enter
Результат запроса отображается в нижней части DBeaver
Если отсутствует возможность автоматической установки драйверов, что бы DBeaver мог работать с PostgreSQL, то переходите в настройки соединения. Нажимаете — Настройки драйвера
Выбираете вкладку Библиотеки
Выделяете все пункты, которые есть в списке. Нажимаете — Удалить
Соглашаетесь с удалением библиотек, нажимаете — Да
Переходите по ссылке — https://jdbc.postgresql.org/download/
Выбираете последнюю версию, к примеру, Java 8 и нажимаете — Download
Выбираете место сохранения файла и сохраняете jar файл.
Важно: в дальнейшем менять местоположение файла или переименовывать файл нельзя.
В свойствах библиотек драйвера нажимаете — Добавить файл
Выбираете заранее скачанный jar файл и нажимаете — ОК
Нажимаете — ОК
Соглашаетесь с изменением параметров соединения, нажимаете — Да
Можете приступать к работе.
Если в настройках соединения в пункте Локальный клиент отсутствует указание установленной версии PostgreSQL и присутствует только одно значение Выбрать, то нажимаете — Выбрать
В левой части будут отсутствовать клиенты БД. Нажимаете — Добавить
В проводнике указываете пусть до папки с установленным сервером PostgreSQL.
По умолчанию в Windows: c:\program files\postgresql\(версия)\
По умолчанию в Mac OS: \library\postgresql\(версия)\
Нажимаете — Выбор папки
Если пусть указан верно, то в левой части появится клиент БД и главное, что бы в пункте Версия продукта было значение, если значение отсутствует, то путь до папки с PostgreSQL указан не верно. Нажимаете — ОК
Если забыли пароль от учетной записи суперпользователя postgres, то необходимо перейти в папку data с установленным сервером PostgreSQL.
По умолчанию в Windows: c:\program files\postgresql\(версия)\data
По умолчанию в Mac OS: \library\postgresql\(версия)\data
Открываете файл pg_hba.conf в любом текстовом редакторе и опускаетесь в самый низ файла
В зависимости от версии PostgreSQL значение METHOD будет scram-sha-256 или md5. Это значение меняете на trust.
Перезагружаете компьютер.
Запускаете DBeaver.
Открываете настройки соединения. Для этого нажимаете правую кнопку мыши на соединении, выбираете — Редактировать объект "Соединение"
В пункте Пароль оставляете пустое значение и нажимаете — ОК
На предложение о переподключении к базе данных нажимаете — Да
Открываете редактор SQL и набираете запрос:
alter role postgres with password 'новый_пароль';
где в одинарных кавычках указываете новый пароль от учетной записи суперпользователя postgres.
Выполняете запрос.
После переходите в папку data с установленным сервером PostgreSQL.
Открываете файл pg_hba.conf в любом текстовом редакторе и опускаетесь в самый низ файла.
Меняете значение trust на scram-sha-256 или md5 в зависимости от версии PostgreSQL.
Перезагружаете компьютер.
Открываете DBeaver и в настройках соединения указываете новый пароль для учетной записи.
База данных dvd-rental является учебной, не содержит персональных или коммерческих данных, может содержать ошибки или неточности в данных.
База данных является фрагментом базы данных компании, занимающейся арендой DVD дисков.
Диаграмма данных:
actor — список актёров
actor_id — идентификатор актера
first_name — имя актера
last_name — фамилия актера
last_update — дата и время изменения записи
city — города
city_id — идентификатор города
city — название города
country_id — идентификатор страны
last_update — дата и время изменения записи
address — адреса клиентов, сотрудников и магазинов
address_id — идентификатор адреса
address — значение адреса
address2 — значение дополнительного адреса
district — район
city_id — идентификатор города
postal_code — индекс
phone — номер телефона
last_update — дата и время изменения записи
customer — клиенты магазина
customer_id — идентификатор клиента
store_id — идентификатор магазина
first_name — имя клиента
last_name — фамилия клиента
email — эмейл клиента
address_id — идентификатор адреса проживания клиента
activebool — флаг активности
create_date — дата создания записи
last_update — дата и время изменения записи
active — флаг активности (целочисленный)
category — категории фильмов (Action, Comedy и др.)
category_id — идентификатор категории
name — название категории
last_update — дата и время изменения записи
country — страны
country_id — идентификатор страны
country — название страны
last_update — дата и время изменения записи
film — фильмы (карточка фильма)
film_id — идентификатор фильма
title — название фильма
description — описание
release_year — год издания
language_id — язык озвучки
original_language_id — оригинальный язык
rental_duration — длительность аренды
rental_rate — стоимость аренды
length — длительность фильма
replacement_cost — стоимость возмещения
rating — возрастное ограничение
last_update — дата и время изменения записи
special_features — атрибуты фильма
fulltext — поле для полнотекстового поиска
film_actor — связка актёров и фильмов (многие-ко-многим)
actor_id — идентификатор актера
film_id — идентификатор фильма
last_update — дата и время изменения записи
film_category — связка фильмов и категорий (многие-ко-многим)
category_id — идентификатор категории
film_id — идентификатор фильма
last_update — дата и время изменения записи
inventory — DVD диски с фильмами
inventory_id — идентификатор диска
film_id — идентификатор фильма
store_id — идентификатор магазина
last_update — дата и время изменения записи
language — языки фильмов
language_id — идентификатор языка
name — название языка
last_update — дата и время изменения записи
payment — платежи за аренду
payment_id — идентификатор платежа
customer_id — идентификатор пользователя
staff_id — идентификатор сотрудника
rental_id — идентификатор аренды
amount — размер платежа
payment_date — дата и время платежа
staff — сотрудники магазина
staff_id — идентификатор сотрудника
first_name — имя сотрудника
last_name — фамилия сотрудника
address_id — идентификатор адреса проживания сотрудника
email — эмейл сотрудника
store_id — идентификатор магазина
active — флаг активности
username — логин сотрудника
password — пароль сотрудника
last_update — дата и время изменения записи
picture — фотография сотрудника
store — магазины (точки проката)
store_id — идентификатор магазина
manager_staff_id — идентификатор сотрудника, который является менеджером магазина
address_id — идентификатор адреса магазина
last_update — дата и время изменения записи
rental — факты аренды фильмов
rental_id — идентификатор аренды
rental_date — дата и время аренды
inventory_id — идентификатор диска
customer_id — идентификатор клиента
return_date — дата и время возврата диска
staff_id — идентификатор сотрудника
last_update — дата и время изменения записи
mpaa_rating — используется в таблице film для обозначения возрастного рейтинга фильма (по классификации MPAA), тип данных enum
'G', 'PG', 'PG-13', 'R', 'NC-17'
year — используется для хранения года выпуска фильма, тип domain над integer (ограничение: 1901–2155)
actor_info — объединяет актёров с фильмами и категориями, в которых они участвовали
customer_list — список клиентов с контактной информацией, активностью и адресом
film_list — список фильмов с актёрами, категориями и языками
nicer_but_slower_film_list — то же самое, что film_list, но в более удобном для чтения формате (с дополнительными объединениями)
staff_list — список сотрудников (ФИО, email, адрес, магазин)
sales_by_film_category — отчёт по доходам, сгруппированным по категориям фильмов
sales_by_store — отчёт по доходам магазинов, с указанием управляющего
База данных hr является учебной, не содержит персональных или коммерческих данных, может содержать ошибки или неточности в данных.
База данных является фрагментом базы данных компании, позволяющей вести кадровый учёт сотрудников.
Диаграмма данных:
address — адреса структурных подразделений, где работают сотрудники
address_id — идентификатор адреса
full_address — полный адрес с учетом области, города, улицы, дома
city_id — идентификатор города
postal_code — индекс
candidate — соискатели
candidate_id — идентификатор соискателя
vac_id — идентификатор вакансии
rec_source — источник вакансии
interviews — количество пройденных собеседований
status — статус по кандидату
city — города
city_id — идентификатор города
city — название города
emp_type — тип сотрудника
emp_type_id — идентификатор типа сотрудника
emp_type — наименование типа сотрудника
employee — сотрудники
emp_id — идентификатор сотрудника
emp_type_id — идентификатор типа сотрудника
person_id — идентификатор физического лица, являющегося сотрудником
pos_id — идентификатор занимаемой должности
rate — ставка по должности
hire_date — дата найма
employee_salary — оклады сотрудников
order_id — идентификатор приказа о назначении оклада
emp_id — идентификатор сотрудника
salary — размер оклада
effective_from — дата, с которой оклад является актуальным
grade_salary — зарплатные вилки по должности
grade — идентификатор зарплатной вилки
min_salary — минимальная граница зарплатной вилки
max_salary — максимальная граница зарплатной вилки
hours — учет рабочего времени
hours_id — идентификатор учета рабочего времени
emp_id — идентификатор сотрудника
project_id — идентификатор проекта
hours — количество затраченных часов
date — дата, в которую сотрудник затратил часы по проекту
person — физические лица, которые являются сотрудниками
person_id — идентификатор физического лица
first_name — имя физического лица
middle_name — отчество физического лица
last_name — фамилия физического лица
taxpayer_number — номер налогоплательщика
dob — дата рождения
position — должности (штатное расписание)
pos_id — идентификатор должности
pos_title — название должности
pos_category — категория должности
unit_id — идентификатор структурного подразделения
grade — идентификатор зарплатной вилки
address_id — идентификатор адреса нахождения структурного подразделения, где предстоит работать по должности
manager_pos_id — идентификатор сотрудника, который является прямым руководителем у должности
projects — проекты
project_id — идентификатор проекта
name — наименование проекта
employees_id — массив с идентификаторами сотрудников, которые задействованы на проектах
amount — стоимость проекта
assigned_id — идентификатор сотрудника, который является руководителем проекта
created_at — дата подписания проекта
structure — структура компании (иерархическая структура)
unit_id — идентификатор структурного подразделения
parent_id — идентификатор родительского структурного подразделения
unit_type — тип структурного подразделения
unit_title — наименование структурного подразделения
vacancy — размещенные вакансии
vac_id — идентификатор вакансии
vac_title — название вакансии
pos_id — идентификатор должности
create_date — дата создания вакансии
closure_date — дата закрытия вакансии
researcher_flag — флаг проактивного поиска (1 — поиском соискателя занимается специалист, 0 — вакансия размещена в открытом доступе и принимает все отклики)
recruiter_id — идентификатор сотрудника, ответственного за вакансию
Установка ПО:
Ресурс supabase.com на данный момент не доступен в России.