Как установить Postgresql на Linux и Windows с. 16

Название	Как установить Postgresql на Linux и Windows с. 16
Дата	10.07.2022
Размер	1.38 Mb.
Формат файла
Имя файла	potgresql.pdf
Тип	Документы #628132
страница	4 из 8

1 2 3 4 5 6 7 8

долговечность: зафикси- рованные данные не пропадут даже в случае сбоя (ко- нечно, при правильных настройках и регулярном вы- полнении резервного копирования).
Это крайне полезные свойства, без которых невозмож- но представить себе реляционную систему управления базами данных.
Подробнее о транзакциях:
postgrespro.ru/doc/tutorial-transactions.html
(и еще более подробно:
postgrespro.ru/doc/mvcc.html).
56

Полезные команды psql
\?
Справка по командам psql.
\h
Справка по SQL: список доступных команд или синтаксис конкретной команды.
\x
Переключает обычный табличный вывод
(столбцы и строки) на расширенный (каж- дый столбец на отдельной строке) и об- ратно. Удобно для просмотра нескольких
«широких» строк.
\l
Список баз данных.
\du
Список пользователей.
\dt
Список таблиц.
\di
Список индексов.
\dv
Список представлений.
\df
Список функций.
\dn
Список схем.
\dx
Список установленных расширений.
\dp
Список привилегий.
\d имя
Подробная информация по конкретному объекту.
\d+ имя
Еще более подробная информация по кон- кретному объекту.
\timing on
Показывать время выполнения операторов.
57

Заключение
Конечно, мы успели осветить только малую толику того,
что необходимо знать о СУБД, но надеемся, что вы убе- дились: начать использовать PostgreSQL совсем нетруд- но. Язык SQL позволяет формулировать запросы самой разной сложности, а PostgreSQL предоставляет каче- ственную поддержку стандарта и эффективную реализа- цию. Пробуйте, экспериментируйте!
И еще одна важная команда psql: для того, чтобы за- вершить сеанс работы, наберите test=# \q
58

Демонстрационная
база данных
Описание
Общая информация
Чтобы двигаться дальше и учиться писать более слож- ные запросы, нам понадобится более серьезная база данных — не три таблицы, а целых восемь, — и напол- нение ее данными. Схема такой базы данных изображе- на в виде диаграммы «сущность-связи» на с. 60.
В качестве предметной области мы выбрали авиапере- возки: будем считать, что речь идет о нашей (пока еще несуществующей) авиакомпании. Тем, кто хотя бы раз летал на самолетах, эта область должна быть понятна;
в любом случае мы сейчас все объясним. Хочется отме- тить, что мы старались сделать схему данных как можно проще, не загромождая ее многочисленными деталями,
но, в то же время, не слишком простой, чтобы на ней можно было учиться писать интересные и осмысленные запросы.
59

B
o
o
ki
n
g
s
Б
ро ни ров ан ия
# book
_r ef
* book
_dat e
* to tal
_am ount
T
icket
s
Б
ил ет ы
# tick et
_no
* book
_r ef
* pas sen ger
_i d
* pas sen ger
_nam e
° cont a
ct
_dat a
A
ir
cr
af
ts
С
ам ол ет ы
# ai rcr af t_
code
* m
odel
* range
S
eat
s
М
ест а
# ai rcr af t_
code
# se at
_no
* far e_c ondi tions
T
icket
_f
li
g
h
ts
П
ере л
ет ы
# tic ket
_no
# fli ght
_i d
* far e_c ondi tions
* a
m ount
B
o
ar
d
in
g
_p
a
sses
П
осад очн ы
е тал он ы
# tic ket
_no
# fli ght
_i d
* b
oar di ng
_no
* seat
_no
F
li
g
h
ts
Р
ей сы
# fli ght
_i d
* fli ght
_n o
* schedu led_depar tur e
* schedu led_ar ri val
* depar tur e_ai rpor t
* ar riv al
_ai rpor t
* st at us
* ai rc ra ft
_code
° ac tua l_depar tur e
° ac tua l_ar riv al
A
ir
p
o
rt
s
А
эроп ор ты
# ai rpor t_c ode
* ai rpor t_nam e
* ci ty
* coor di nat es
* tim ez one
60

Итак, основной сущностью является бронирование (book- ings).
В одно бронирование можно включить несколько пас- сажиров, каждому из которых выписывается отдельный
билет
(tickets). Как таковой пассажир не является от- дельной сущностью: для простоты можно считать, что все пассажиры уникальны.
Каждый билет включает один или несколько перелетов
(ticket_flights). Несколько перелетов могут включаться в билет в нескольких случаях:
1. Нет прямого рейса, соединяющего пункты отправ- ления и назначения (полет с пересадками);
2. Взят билет «туда и обратно».
В схеме данных нет жесткого ограничения, но предпо- лагается, что все билеты в одном бронировании имеют одинаковый набор перелетов.
Каждый рейс (flights) следует из одного аэропорта (air- ports) в другой. Рейсы с одним номером имеют одина- ковые пункты вылета и назначения, но будут отличаться датой отправления
При регистрации на рейс пассажиру выдается посадоч-
ный талон
(boarding_passes), в котором указано место в самолете. Пассажир может зарегистрироваться только на тот рейс, который есть у него в билете. Комбинация рейса и места в самолете должна быть уникальной, что- бы не допустить выдачу двух посадочных талонов на одно место.
61

Количество мест (seats) в самолете и их распределение по классам обслуживания зависит от конкретной моде- ли самолета (aircrafts), выполняющего рейс. Предполага- ется, что каждая модель имеет только одну компоновку салона. Схема данных не контролирует, что места в по- садочных талонах соответствуют имеющимся в салоне.
Далее мы подробно опишем каждую из таблиц, а так- же дополнительные представления и функции. Точное определение любой таблицы, включая типы данных и описание столбцов, вы всегда можете получить ко- мандой \d+.
Бронирование
Намереваясь воспользоваться услугами нашей авиа- компании, пассажир заранее (book_date, максимум за месяц до рейса) бронирует необходимые билеты. Бро- нирование идентифицируется своим номером book_ref
(шестизначная комбинация букв и цифр).
Поле total_amount хранит общую стоимость включен- ных в бронирование перелетов всех пассажиров.
Билет
Билет имеет уникальный номер ticket_no, состоящий из 13 цифр.
62

Билет содержит номер документа, удостоверяющего личность пассажира passenger_id, а также его фами- лию и имя passenger_name и контактную информацию contact_data.
Заметим, что ни идентификатор пассажира, ни имя не являются постоянными (можно поменять паспорт, мож- но сменить фамилию). Поэтому однозначно найти все билеты одного и того же пассажира невозможно. Для простоты можно считать, что все пассажиры уникальны.
Перелет
Перелет соединяет билет с рейсом и идентифицируется двумя их номерами.
Для каждого перелета указываются его стоимость amount и класс обслуживания fare_conditions.
Рейс
Естественный ключ таблицы рейсов состоит из двух по- лей — номера рейса flight_no и даты отправления scheduled_departure. Чтобы сделать внешние ключи на эту таблицу компактнее, в качестве первичного ис- пользуется суррогатный ключ flight_id.
Рейс всегда соединяет две точки — аэропорты вылета departure_airport и прибытия arrival_airport.
63

Такое понятие, как «рейс с пересадками» отсутствует:
если из одного аэропорта до другого нет прямого рей- са, в билет просто включаются несколько необходимых рейсов.
У каждого рейса есть запланированные дата и время вылета scheduled_departure и прибытия scheduled_ar- rival. Реальные время вылета actual_departure и прибытия actual_arrival могут отличаться: обычно не сильно, но иногда и на несколько часов, если рейс задержан.
Статус рейса status может принимать одно из следую- щих значений:
• Scheduled
Рейс доступен для бронирования. Это происходит за месяц до плановой даты вылета; до этого запись о рейсе не существует в базе данных.
• On Time
Рейс доступен для регистрации (за сутки до плано- вой даты вылета) и не задержан.
• Delayed
Рейс доступен для регистрации (за сутки до плано- вой даты вылета), но задержан.
• Departed
Самолет уже вылетел и находится в воздухе.
• Arrived
Самолет прибыл в пункт назначения.
• Cancelled
Рейс отменен.
65

Аэропорт
Каждый аэропорт идентифицируется трехбуквенным кодом airport_code и имеет название airport_name.
Название города city указывается как атрибут аэропор- та; отдельной сущности для него не предусмотрено. На- звание можно использовать для того, чтобы определить аэропорты одного города. Также указываются коорди- наты (долгота и широта) coordinates и часовой пояс timezone.
Посадочный талон
При регистрации на рейс, которая возможна за сутки до плановой даты отправления, пассажиру выдается посадочный талон. Он идентифицируется так же, как и перелет — номером билета и номером рейса.
Посадочным талонам присваиваются последовательные номера boarding_no в порядке регистрации пассажиров на рейс (этот номер будет уникальным только в преде- лах данного рейса). В посадочном талоне указывается номер места seat_no.
Самолет
Каждая модель воздушного судна идентифицируется своим трехзначным кодом aircraft_code. Указывается также название модели model и максимальная даль- ность полета в километрах range.
66

Место
Места определяют схему салона каждой модели. Каж- дое место определяется своим номером seat_no и име- ет закрепленный за ним класс обслуживания fare_condi- tions — Economy, Comfort или Business.
Представление для рейсов
Над таблицей flights создано представление flights_v,
содержащее дополнительную информацию:
• расшифровку данных об аэропорте вылета departure_airport, departure_airport_name,
departure_city,
• расшифровку данных об аэропорте прибытия arrival_airport, arrival_airport_name, arrival_city,
• местное время вылета scheduled_departure_local, actual_departure_local,
• местное время прибытия scheduled_arrival_local, actual_arrival_local,
• продолжительность полета scheduled_duration, actual_duration.
Представление для маршрутов
Таблица рейсов содержит избыточность: из нее мож- но было бы выделить информацию о маршруте (номер
67

рейса, аэропорты отправления и назначения, модель самолета), не зависящую от конкретных дат рейсов.
Именно такая информация и составляет представле- ние routes. Кроме того, это представление показывает массив дней недели days_of_week, по которым совер- шаются полеты, и плановую продолжительность рейса duration.
Функция now
Демонстрационная база содержит временной «срез»
данных — так, как будто в некоторый момент была сде- лана резервная копия реальной системы. Например, ес- ли некоторый рейс имеет статус Departed, это означает,
что в момент резервного копирования самолет вылетел и находился в воздухе.
Позиция «среза» сохранена в функции bookings.now.
Ей можно пользоваться в запросах там, где в обычной жизни использовалась бы функция now.
Кроме того, значение этой функции определяет версию демонстрационной базы данных. Актуальная версия на момент подготовки этого выпуска книги — от 15.08.2017.
68

Установка
Установка с сайта
База данных доступна в трех версиях, которые отлича- ются только объемом данных:
• edu.postgrespro.ru/demo-small.zip — небольшая,
данные по полетам за один месяц (файл 21 МБ,
размер БД 280 МБ),
• edu.postgrespro.ru/demo-medium.zip — средняя,
данные по полетам за три месяца (файл 62 МБ,
размер БД 702 МБ),
• edu.postgrespro.ru/demo-big.zip — большая,
данные по полетам за один год (файл 232 МБ,
размер БД 2638 МБ).
Небольшая база годится для того, чтобы тренироваться писать запросы, и при этом не займет много места на диске. Если же вы хотите погрузиться в вопросы опти- мизации, выберите большую базу, чтобы сразу понять,
как ведут себя запросы на больших объемах данных.
Файлы содержат логическую резервную копию базы demo, созданную утилитой pg_dump. Имейте в виду, что если у вас уже существует база данных demo, она будет удалена и создана заново при восстановлении из ре- зервной копии. Владельцем базы demo станет тот поль- зователь СУБД, под которым выполнялось восстановле- ние.
69

Чтобы установить демонстрационную базу данных в Li- nux, скачайте один из файлов, предварительно пере- ключившись на пользователя postgres. Например, для базы небольшого размера:
$ sudo su - postgres
$ wget https://edu.postgrespro.ru/demo-small.zip
Затем выполните команду:
$ zcat demo-small.zip | psql
В Windows любым веб-браузером скачайте с сайта файл edu.postgrespro.ru/demo-small.zip , дважды клик- ните на нем, чтобы открыть архив, и скопируйте файл demo-small-20170815.sql в каталог C:\Program Files\
PostgresPro10.
Затем запустите psql (ярлык «SQL Shell (psql)») и выпол- ните команду:
postgres# \i demo-small-20170815.sql
(Если файл не будет найден, проверьте свойство «Start in» («Рабочая папка») ярлыка — файл должен находить- ся именно в этом каталоге.)
70

demo=# SELECT bookings.now();
now
------------------------
2017-08-15 18:00:00+03
(1 row)
Как вы заметили, названия самолетов выводятся по- русски. Так же обстоит дело и с названиями городов и аэропортов:
demo=# SELECT airport_code, city
FROM airports LIMIT 5;
airport_code |
city
--------------+--------------------------
YKS
| Якутск
MJZ
| Мирный
KHV
| Хабаровск
PKC
| Петропавловск-Камчатский
UUS
| Южно-Сахалинск
(5 rows)
Если вы предпочитаете английские названия, установи- те параметр bookings.lang в значение en. Это можно сделать, например, так:
demo=# ALTER DATABASE demo SET bookings.lang = en;
ALTER DATABASE
demo=# \c
You are now connected to database "demo" as user
"postgres".
72

demo=# SELECT airport_code, city
FROM airports LIMIT 5;
airport_code |
city
--------------+-------------------
YKS
| Yakutsk
MJZ
| Mirnyj
KHV
| Khabarovsk
PKC
| Petropavlovsk
UUS
| Yuzhno-sakhalinsk
(5 rows)
Как это устроено, вы можете разобраться, посмотрев определение aircrafts или airports с помощью ко- манды psql \d+.
Подробнее про управление схемами:
postgrespro.ru/doc/ddl-schemas.html и про установку конфигурационных параметров:
postgrespro.ru/doc/config-setting.html.
Простые запросы
Ниже мы покажем некоторые примеры задач на демон- страционной схеме. Большинство из них приведены вместе с решениями, остальные предлагается решить самостоятельно.
Задача.
Кто летел позавчера рейсом Москва (SVO) — Но- восибирск (OVB) на месте 1A, и когда он забронировал свой билет?
73

Решение.
«Позавчера» отсчитывается от booking.now,
а не от текущей даты.
SELECT t.passenger_name,
b.book_date
FROM
bookings b
JOIN tickets t
ON t.book_ref = b.book_ref
JOIN boarding_passes bp
ON bp.ticket_no = t.ticket_no
JOIN flights f
ON f.flight_id = bp.flight_id
WHERE f.departure_airport = 'SVO'
AND
f.arrival_airport = 'OVB'
AND
f.scheduled_departure::date =
bookings.now()::date - INTERVAL '2 day'
AND
bp.seat_no = '1A';
Задача.
Сколько мест осталось незанятыми вчера на рейсе PG0404?
Решение.
Задачу можно решить несколькими спосо- бами. Первый вариант использует конструкцию NOT
EXISTS, чтобы определить места, на которые нет поса- дочных талонов:
SELECT count(*)
FROM
flights f
JOIN seats s
ON s.aircraft_code = f.aircraft_code
WHERE f.flight_no = 'PG0404'
AND
f.scheduled_departure::date =
bookings.now()::date - INTERVAL '1 day'
AND
NOT EXISTS (
SELECT NULL
FROM
boarding_passes bp
WHERE bp.flight_id = f.flight_id
AND
bp.seat_no = s.seat_no
);
74

Второй вариант использует операцию вычитания мно- жеств:
SELECT count(*)
FROM
(
SELECT s.seat_no
FROM
seats s
WHERE s.aircraft_code = (
SELECT aircraft_code
FROM
flights
WHERE flight_no = 'PG0404'
AND
scheduled_departure::date =
bookings.now()::date - INTERVAL '1 day'
)
EXCEPT
SELECT bp.seat_no
FROM
boarding_passes bp
WHERE bp.flight_id = (
SELECT flight_id
FROM
flights
WHERE flight_no = 'PG0404'
AND
scheduled_departure::date =
bookings.now()::date - INTERVAL '1 day'
)
) t;
Какой вариант использовать, во многом зависит от лич- ных предпочтений. Необходимо только учитывать, что выполняться такие запросы будут по-разному, так что если важна производительность, то имеет смысл попро- бовать оба.
75

Задача.
На каких маршрутах произошли самые длитель- ные задержки рейсов? Выведите список из десяти «ли- деров».

1 2 3 4 5 6 7 8