Установка PostgreSQL. Установка PostGIS. Изучение интерфейсов подключения к БД

1. Установка PostgreSQL

PostgreSQL — одна из самых популярных и мощных систем управления реляционными базами данных (СУБД). У неё открытый исходный код, плюс она абсолютно бесплатная. Система помогает создавать и хранить базы данных, а также работать с ними на языке SQL.

1. Скачайте установочный файл с официального сайта PostgreSQL.
2. Запустите установку и в мастере установки пройдите шаги:
   • 2.1 — начало установки;
   • 2.2 — выберите директорию установки и отметьте галочками компоненты (PostgreSQL Server, pgAdmin 4, Stack Builder, Command Line Tools);
   • 2.3 — укажите данные и задайте пароль для суперпользователя (обычно это postgres). Имя суперпользователя не рекомендуется менять; пароль не рекомендуется забывать;
   • 2.4 — настройте порт для сервера (по умолчанию 5432);
   • 2.5 — проверьте настроенную конфигурацию.

3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы.

1.1. Установка PostGIS

PostGIS — программное расширение с открытым исходным кодом, расширяющее СУБД PostgreSQL до пространственной базы данных: добавляется поддержка пространственных объектов и запросов местоположения в SQL.

1. Как только PostgreSQL установлен, запустите Application Stack Builder: Пуск → Программы → PostgreSQL → Application Stack Builder.
2. Выберите в категории Spatial Extensions пункт «PostGIS».
3. Произведите установку.

1.2. Создание БД и активация расширения

1. Чтобы создать базу данных, достаточно нажать ПКМ по названию сервера.
2. Затем дайте имя базе данных и назначьте владельца.
3. Активируйте расширение: раскрыть список БД → Extensions → ПКМ → Create → Extension → в поле Name выбрать «postgis».
4. Если всё сделано верно, вы увидите PostGIS в списке «Расширений».

1.3. Подключение QGIS

QGIS — географическая информационная система с открытым исходным кодом. Проект зародился в мае 2002 года и был создан как проект на SourceForge в июне того же года. QGIS работает на большинстве платформ Unix, Windows и macOS, разработан с использованием инструментария Qt и C++, работает быстро и имеет приятный, простой в использовании графический интерфейс.

1. Если всё настроено верно, QGIS должен «увидеть» БД: Слой → Добавить слой → Добавить слой PostGIS.
2. Во всплывающем окне «создайте» подключение и заполните параметры; разрешите сохранение и загрузку данных.
3. Введите имя пользователя и пароль для подключения (очень надеемся, что вы их не забыли).
4. Поскольку данных пока нет, единственное, что можно увидеть, — таблицы без геометрии: поставьте галочку «Показать таблицы без геометрии».

Плагин DB Manager — удобный инструмент для работы с пространственными базами в QGIS (PostGIS, SpatiaLite, GeoPackage, Oracle Spatial в одном интерфейсе): импорт слоёв перетаскиванием, перемещение таблиц между базами. Установка: Модули → Управление модулями → поиск → активировать.

1.4. Ограничения PostgreSQL

2. Определения понятий реляционной модели

Прежде чем создавать таблицы, повторим фундамент (подробный разбор — в экспресс-занятии 2 и лекции 1).

Информация — любой вид знаний о предметах, фактах, понятиях проблемной области, которыми обмениваются пользователи информационной системы (ГОСТ 34.320-96); сведения (сообщения, данные) независимо от формы их представления (Федеральный закон № 149-ФЗ «Об информации, информационных технологиях и о защите информации»).

Данные — представление информации в формализованном виде, пригодном для передачи, интерпретации или обработки (ГОСТ 33707-2016); формы представления информации, с которыми имеют дело информационные системы и их пользователи (ГОСТ Р ИСО/МЭК 10746-2-2000).

Все типы данных делятся на две категории: скалярные (простые) и составные. Множество хранит данные без определённого порядка и без повторяющихся значений; особенность — высокая скорость проверки принадлежности элемента. Кортеж — упорядоченный набор фиксированной длины. Мощность множества |A| — количество его элементов. Декартово произведение A × B — множество всех упорядоченных пар элементов исходных множеств.

Мостик от теории множеств к базе данных:

SQL (Structured Query Language) — язык программирования, основанный на реляционной модели данных. SQL расширяет идеи реляционной алгебры и считается полным языком: помимо операций запросов у него есть операторы DDL (описание данных) и операторы административного управления базами данных.

3. DDL: создаём свою первую таблицу

Операторы DDL используются для создания, изменения и удаления структуры базы данных и её объектов. Основные типы данных PostgreSQL:

Создадим первую таблицу. В pgAdmin раскройте Schemas, найдите Tables, нажмите ПКМ → Query Tool и выполните:

CREATE TABLE Продукты (
    ID INT PRIMARY KEY,
    Наименование VARCHAR(50),
    Количество INT,
    Цена MONEY,
    Дата_привоза DATE
);

3.1. DML: наполняем таблицу

Операторы DML позволяют работать с данными внутри таблицы: INSERT, UPDATE, DELETE. Вставим тридцать продуктов:

INSERT INTO Продукты (ID, Наименование, Количество, Цена, Дата_привоза) VALUES
(1, 'Рябчики', 100, 45, '2024-10-01'),
(2, 'Ананас', 200, 95, '2024-10-02'),
(3, 'Яблоко', 150, 30, '2024-10-03'),
(4, 'Груша', 120, 40, '2024-10-04'),
(5, 'Киви', 80, 50, '2024-10-05'),
(6, 'Банан', 300, 25, '2024-10-06'),
(7, 'Мандарины', 200, 65, '2024-10-07'),
(8, 'Апельсин', 250, 70, '2024-10-08'),
(9, 'Арбуз', 400, 15, '2024-10-09'),
(10, 'Дыня', 180, 85, '2024-10-10'),
(11, 'Виноград', 130, 75, '2024-10-11'),
(12, 'Клубника', 220, 120, '2024-10-12'),
(13, 'Черешня', 140, 110, '2024-10-13'),
(14, 'Персик', 160, 95, '2024-10-14'),
(15, 'Нектарин', 180, 105, '2024-10-15'),
(16, 'Слива', 190, 60, '2024-10-16'),
(17, 'Абрикос', 170, 55, '2024-10-17'),
(18, 'Манго', 100, 140, '2024-10-18'),
(19, 'Лайм', 90, 75, '2024-10-19'),
(20, 'Лимон', 120, 80, '2024-10-20'),
(21, 'Гранат', 130, 115, '2024-10-21'),
(22, 'Авокадо', 80, 150, '2024-10-22'),
(23, 'Кокос', 60, 160, '2024-10-23'),
(24, 'Финики', 110, 135, '2024-10-24'),
(25, 'Инжир', 140, 145, '2024-10-25'),
(26, 'Папайя', 100, 155, '2024-10-26'),
(27, 'Маракуйя', 70, 165, '2024-10-27'),
(28, 'Личи', 90, 175, '2024-10-28'),
(29, 'Рамбутан', 85, 185, '2024-10-29'),
(30, 'Саподилла', 75, 195, '2024-10-30');

3.2. DQL: извлекаем данные

DQL (Data Query Language) — набор команд SQL для выполнения запросов к базе данных с целью извлечения данных. Цель DQL — получение информации, а не изменение структуры или содержимого. Основная команда — SELECT:

SELECT Столбец1, Столбец2, Столбец3
FROM Таблица1;

WHERE фильтрует строки. Условия могут включать:

1. сравнения: =, != (не равно), >, <, >=, <= (например, age > 18, name = 'John');
2. логические операторы AND, OR, NOT для комбинирования условий;
3. IN и NOT IN — проверка на вхождение значения в список;
4. NULL и NOT NULL — проверка на наличие или отсутствие значений NULL;
5. выражения с функциями и арифметикой (например, price * quantity > 100).

4. Практика SELECT: задачи и песочница

Таблица «Продукты» уже загружена в песочницу — все 30 строк. Решайте задачи из практики самостоятельно, затем сверяйтесь с решением. Даты в формате ISO ('2024-10-05') корректно сравниваются операторами >= и <=.

SELECT * FROM Продукты;

SELECT Наименование, Дата_привоза FROM Продукты;

SELECT Наименование FROM Продукты
WHERE Количество > 100;

SELECT Наименование FROM Продукты
WHERE Дата_привоза >= '2024-10-03' AND Дата_привоза <= '2024-10-30';

SELECT Наименование, Количество * Цена AS Стоимость
FROM Продукты;

SELECT * FROM Продукты
WHERE Наименование LIKE 'Р%';

SELECT * FROM Продукты
WHERE Цена >= 100 AND Цена <= 200
  AND NOT (Дата_привоза >= '2024-10-06' AND Дата_привоза <= '2024-10-10');

SELECT * FROM Продукты
WHERE Наименование LIKE '%я';

5. Импорт готовой БД

5.1. Через консоль (psql)

1. Найдите место на диске, где установлен PostgreSQL: если вы не меняли путь установки, ищите на диске C, примерный путь — C:\Program Files\PostgreSQL\15\bin (15 — номер вашей версии).
2. Откройте данную директорию в командной строке: в адресной строке Проводника вместо пути введите cmd и нажмите Enter. Альтернатива для продвинутых пользователей — команда cd.
3. Импорт БД осуществляется командой:

   psql -f demo_small_YYYYMMDD.sql -U postgres

   где -f (file) — путь к файлу с данными, -U (User) — пользователь, от имени которого вы действуете.

5.2. Через интерфейс pgAdmin

1. Нажмите ПКМ по существующей БД.
2. Нажмите кнопку Restore (восстановить) во всплывшем окне.
3. Выберите в пункте Filename нужный файл.
4. Если проблем не возникло, процесс завершится со статусом Finished, в противном случае — Failed. В случае возникновения проблем воспользуйтесь загрузкой через консоль.

6. Демо-база «Авиаперевозки»: схема и запросы

Схема демонстрационной базы bookings знакома по лекции 2 — бронирования, билеты, перелёты, рейсы, аэропорты, самолёты и места.

QGIS не может отобразить данные, если отсутствуют координаты и информация о проекции. Создадим столбец geom с системой координат 4326 и заполним его точками из полей координат:

ALTER TABLE bookings.airports_data
ADD COLUMN geom geometry(Point, 4326);

UPDATE bookings.airports_data
SET geom = ST_SetSRID(
    ST_MakePoint(
        coordinates[0],  -- долгота
        coordinates[1]   -- широта
    ), 4326);

Теперь аэропорты можно отобразить в QGIS: включите «поле геометрии», выберите столбец geom и загрузите данные как слой.

Три запроса для самостоятельного разбора (демонстрируют JOIN, агрегатные функции с группировкой и подзапрос):

-- 1. Места Sukhoi Superjet-100 (JOIN + JSON-доступ к полю model)
SELECT a.aircraft_code,
       a.model->>'en' AS model_en,
       a.model->>'ru' AS model_ru,
       s.seat_no, s.fare_conditions
FROM bookings.aircrafts_data a
JOIN bookings.seats s ON a.aircraft_code = s.aircraft_code
WHERE a.model->>'en' = 'Sukhoi Superjet-100'
ORDER BY s.seat_no;

-- 2. Компоновка салонов: классы обслуживания и число мест по самолётам
SELECT s2.aircraft_code,
       string_agg(s2.fare_conditions || '(' || s2.num::text || ')', ', ') AS fare_conditions
FROM (
    SELECT s.aircraft_code, s.fare_conditions, count(*) AS num
    FROM bookings.seats s
    GROUP BY s.aircraft_code, s.fare_conditions
    ORDER BY s.aircraft_code, s.fare_conditions
) s2
GROUP BY s2.aircraft_code
ORDER BY s2.aircraft_code;

-- 3. Города с несколькими аэропортами (подзапрос + HAVING)
SELECT a.airport_code AS code, a.airport_name, a.city,
       a.coordinates[0] AS longitude,
       a.coordinates[1] AS latitude,
       a.timezone
FROM bookings.airports_data a
WHERE a.city IN (
    SELECT aa.city
    FROM bookings.airports_data aa
    GROUP BY aa.city
    HAVING COUNT(*) > 1
)
ORDER BY a.city, a.airport_code;

Контрольные вопросы

• Что такое PostgreSQL и для чего нужен PostGIS?
  PostgreSQL — популярная мощная СУБД с открытым исходным кодом, бесплатная; работает на языке SQL. PostGIS — её расширение с открытым кодом, добавляющее пространственные объекты и запросы местоположения в SQL: превращает PostgreSQL в пространственную базу данных.
• Какие параметры фиксируются при установке PostgreSQL и почему их важно записать?
  Имя суперпользователя (обычно postgres — менять не рекомендуется), его пароль (не забывать!) и порт сервера (по умолчанию 5432). Без них не подключиться ни из pgAdmin, ни из QGIS, ни через psql.
• Назовите три уровня языка SQL, с которыми вы работали на занятии.
  DDL — определение структуры (CREATE TABLE Продукты…); DML — изменение данных (INSERT INTO … VALUES …); DQL — запросы на извлечение (SELECT … FROM … WHERE …). SQL — полный язык: включает все уровни плюс административные операторы.
• Какие типы данных PostgreSQL вы использовали в таблице «Продукты» и за что отвечает каждый?
  INT (ID, Количество) — четырёхбайтное целое; VARCHAR(50) (Наименование) — строка переменной длины до 50 символов; MONEY (Цена) — денежная сумма; DATE (Дата_привоза) — календарная дата. ID объявлен PRIMARY KEY — уникальный идентификатор строки.
• Как импортировать готовую базу из SQL-файла через консоль?
  Открыть в командной строке каталог bin установки PostgreSQL (в адресной строке Проводника набрать cmd) и выполнить psql -f файл.sql -U postgres: -f — путь к файлу, -U — пользователь. Ключи чувствительны к регистру. Альтернатива для .backup-файлов — Restore в pgAdmin.
• Зачем демо-базе «Авиаперевозки» добавляли столбец geom и как он заполняется?
  QGIS не может отобразить таблицу без геометрии и проекции. ALTER TABLE добавляет столбец geometry(Point, 4326), а UPDATE заполняет его: ST_MakePoint(долгота, широта) создаёт точку, ST_SetSRID приписывает ей систему координат 4326 (WGS 84).
• Каковы ограничения PostgreSQL на размер таблицы, строки, поля и длину идентификатора?
  Таблица — до 32 ТБ; строка — до 1,6 ТБ; поле — до 1 ГБ; идентификатор — до 63 байт. Размер базы, число строк и число индексов не ограничены; столбцов в таблице — от 250 до 1600 в зависимости от типов.

Источники

1. PostgreSQL: Windows installers : [сайт]. — URL: https://www.postgresql.org/download/windows/ (дата обращения: 18.08.2026).
2. PostgreSQL 15 Documentation. Data Types : [сайт]. — URL: https://www.postgresql.org/docs/15/datatype.html (дата обращения: 18.08.2026).
3. Демобаза данных «Авиаперевозки» — Postgres Professional : [сайт]. — URL: https://postgrespro.ru/education/demodb (дата обращения: 18.08.2026).
4. PostGIS. Getting Started : [сайт]. — URL: https://postgis.net/documentation/getting_started/ (дата обращения: 18.08.2026).