Запуск и настройка pgAdmin 4
PostgreSQL 12 и pgAdmin 4 мы установили, теперь давайте запустим pgAdmin 4, подключимся к серверу и настроим рабочую среду pgAdmin.
Чтобы запустить pgAdmin 4, зайдите в меню пуск, найдите пункт PostgreSQL 12, а в нем pgAdmin 4.
Подключение к серверу PostgreSQL 12
pgAdmin 4 имеет веб интерфейс, поэтому в результате у Вас должен запуститься браузер, а в нем открыться приложение pgAdmin 4.
При первом запуске pgAdmin 4 появится окно «Set Master Password», в котором мы должны задать «мастер-пароль», это можно и не делать, однако если мы будем сохранять пароль пользователя (галочка «Сохранить пароль»), например, для того чтобы каждый раз при подключении не вводить его, то настоятельно рекомендуется придумать и указать здесь дополнительный пароль, это делается один раз.
Вводим и нажимаем «ОК».
Чтобы подключиться к только что установленному локальному серверу PostgreSQL в обозревателе серверов, щелкаем по пункту «PostgreSQL 12».
В итоге запустится окно «Connect to Server», в котором Вам нужно ввести пароль системного пользователя postgres, т.е. это тот пароль, который Вы придумали, когда устанавливали PostgreSQL. Вводим пароль, ставим галочку «Save Password», для того чтобы сохранить пароль и каждый раз не вводить его (благодаря функционалу «мастер-пароля», все сохраненные таким образом пароли будут дополнительно шифроваться).
Нажимаем «OK».
В результате Вы подключитесь к локальному серверу PostgreSQL 12 и увидите все объекты, которые расположены на данном сервере.
Установка русского языка в pgAdmin 4
Как видите, по умолчанию интерфейс pgAdmin 4 на английском языке, если Вас это не устраивает, Вы можете очень просто изменить язык на тот, который Вам нужен. pgAdmin 4 поддерживает много языков, в том числе и русский.
Для того чтобы изменить язык pgAdmin 4, необходимо зайти в меню «File -> Preferences».
Затем найти пункт «User Languages», и в соответствующем поле выбрать значение «Russian». Для сохранения настроек нажимаем «Save», после этого перезапускаем pgAdmin 4 или просто обновляем страницу в браузере.
В результате pgAdmin 4 будет русифицирован.
Демонстрационная база данных для PostgreSQL
В этой заметке я расскажу о нашей демонстрационной базе данных для PostgreSQL: почему она важна для нас и как может пригодиться вам, как устроена схема и какие данные в ней содержатся.
Сразу приведу ссылку на полное описание (там же написано, где взять демо-базу и как ее установить).
Зачем?
С нашей точки зрения, необходимость в демонстрационной базе назрела давно. Чтобы обсудить практически любую возможность СУБД, нужны какие-то данные, нужна таблица или несколько таблиц. Изобретать этот велосипед каждый раз заново — впустую тратить и внимание слушателя, и свое собственное время. Недаром каждый производитель СУБД имеет базу, которую и использует каждый раз, когда нужно что-либо продемонстрировать.
Для чего может понадобиться такая база данных?
Во-первых, для самостоятельного изучения SQL. Допустим, вы студент, осваиваете SQL и прочитали, скажем, про рекурсивные запросы. Надо ведь на чем-то потренироваться?
С другой стороны, чтобы студент смог прочитать про рекурсивные запросы, нужно, чтобы кто-нибудь об этом написал. Наша компания сотрудничает с несколькими авторами и сейчас идет работа над университетским курсом по технологиям баз данных и учебным пособием по SQL — обе книги будут использовать демонстрационную базу. Можно будет не просто прочитать книгу, а тут же воспроизвести приведенные в ней примеры, «поиграться» с ними, выполнить практические задания.
Другой вариант — проведение практики по курсу баз данных в вузе (или даже чтение коммерческого курса по SQL: лицензия PostgreSQL, под которой выпущена демо-база, это разрешает). Примеры такого использования уже есть.
Демонстрационную базу полезно задействовать и для написания заметок в блог или статей про PostgreSQL и его возможности. Вместо того, чтобы каждый раз начинать со слов «создадим табличку и вставим какие-нибудь данные с помощью generate_series», можно сразу приступать к делу.
Мы думаем и над переработкой со временем документации PostgreSQL, чтобы и она максимально опиралась на схему и данные демо-базы.
Что нужно?
К демонстрационной базе данных мы выдвинули несколько требований:
- Схема данных должна быть достаточно простой, чтобы быть понятной без особых пояснений.
- В то же время схема данных должна быть достаточно сложной, чтобы позволять строить не самые тривиальные запросы.
- База данных должна быть наполнена данными, напоминающими реальные, с которыми будет интересно работать.
Разумеется, первым делом мы посмотрели, какие базы уже существуют, но ни одна из них нас не устроила. Ни в коем случае не хочу сказать, что они «плохие», но создавались для других задач: в каких-то слишком простая схема, какие-то слишком специализированы, в каких-то слишком примитивное наполнение.
Схема данных
Поэтому базу данных в итоге мы сделали свою собственную. Как вы, возможно, уже догадались по картинке, в качестве предметной области были выбраны авиаперевозки: речь идет о нашей дочерней авиакомпании (пока, увы, еще несуществующей). Схема данных приведена на рисунке:
Основной сущностью здесь является бронирование (bookings).
В одно бронирование можно включить несколько пассажиров, каждому из которых выписывается отдельный билет (tickets). Как таковой пассажир не является отдельной сущностью: для простоты можно считать, что все пассажиры уникальны.
Билет включает один или несколько перелетов (ticket_flights). Несколько перелетов могут включаться в билет в нескольких случаях:
- Нет прямого рейса, соединяющего пункты отправления и назначения (полет с пересадками);
- Взят билет «туда и обратно».
В схеме данных нет жесткого ограничения, но предполагается, что все билеты в одном бронировании имеют одинаковый набор перелетов.
Каждый рейс (flights) следует из одного аэропорта (airports) в другой. Рейсы с одним номером имеют одинаковые пункты вылета и назначения, но будут отличаться датой отправления.
При регистрации на рейс пассажиру выдается посадочный талон (boarding_passes), в котором указано место в самолете. Пассажир может зарегистрироваться только на тот рейс, который есть у него в билете. Комбинация рейса и места в самолете должна быть уникальной, чтобы не допустить выдачу двух посадочных талонов на одно место.
Количество мест (seats) в самолете и их распределение по классам обслуживания зависит от модели самолета (aircrafts), выполняющего рейс. Предполагается, что каждая модель имеет только одну компоновку салона. Схема данных не контролирует, что места в посадочных талонах соответствуют имеющимся в самолете.
Все объекты схемы подробно описаны в документе, на который я уже ссылался в начале статьи. Там же приведен «путеводитель» по таблицам в виде простых запросов.
Что внутри?
Чтобы учиться писать запросы, нужно, чтобы база данных уже содержала какие-то данные, и не пару строк, а достаточно большой массив. Наша демонстрационная база доступна в трех вариантах, отличающихся объемом данных:
- Небольшая база содержит данные о полетах за один месяц; она не занимает много места на диске, но вполне позволяет писать запросы.
- Средняя база распространяется на три месяца.
- Большая база по полетам за год уже позволит непосредственно ощутить нюансы, связанные с производительностью.
Вообще, генерация тестовых данных — само по себе увлекательное занятие и дальше речь пойдет именно о ней. А что же тут интересного, ведь давно существуют инструменты (например, DataFiller), которые решают эту задачу? Да, существуют, но все зависит от того, какое качество информации вас устраивает.
Например, в билете есть имя и фамилия пассажира. Как можно сгенерировать данные для этого поля? Можно придумать несколько вариантов.
Самый простой — формировать строки заданной длины из случайных символов. Рей Бредбери мог позволить себе мистера Ааа, но готовы ли вы встретиться с QDEMFI TGBSWAJVZH (это, к слову, не выдуманный пример)?
Можно выбирать значения из заранее заготовленного списка имен и фамилий. Это будет больше похоже на правду, но есть еще и такая штука, как распределение данных. Если выбирать одно из имен равновероятно, то Александров в базе окажется примерно столько же, сколько и Полуэктов. Казалось бы, какая разница? А разница есть, и большая: если нужно получить всех Александров, в реальной базе данных вам придется отобрать порядка 10% всех строк, а Полуэктов может и вовсе не найтись. А это значит, что планы запросов в одном и в другом случаях должны отличаться — именно для этого СУБД собирают статистику по распределению данных в столбцах.
Более честный способ состоит в использовании частотных характеристик для каждого имени и для каждой фамилии. Именно так мы и поступили. (Можно было бы еще учесть национальные особенности и изменение популярности имен со временем, но тут важно вовремя остановиться.)
Вот другой пример. В нашей базе данных содержится порядка ста аэропортов. Прямые рейсы соединяют далеко не все аэропорты, но из любого можно добраться в любой другой с несколькими пересадками. Говоря иначе: граф связей должен быть неполным, но связным. Как его сгенерировать? И снова все зависит от того, какое качество данных нас устраивает.
В простом случае можно связать первый произвольный аэропорт со вторым не менее произвольным аэропортом, затем связать второй со следующим и так далее несколько раз. Если каждый раз отдавать предпочтение еще не связанным аэропортам, то формально мы получим подходящий граф. Будет ли он похож на правду? Ни в малейшей степени. Вот что у нас может получиться (цвет линий зависит от пассажиропотока: чем темнее, тем более нагружен маршрут):
Если приглядеться, то видно, что все города связаны друг с другом довольно равномерной паутиной. А вот как выглядит реальный граф авиарейсов по России (по данным OpenFlights.org):
Характерная особенность здесь состоит в том, что основная масса связей сосредоточена в небольшом числе узлов. Такие графы называются безмасштабными; по ссылке можно найти и алгоритмы их генерации.
В нашем же случае нужно не просто сгенерировать граф, но и наложить его на реальные города (ведь понятно, что при любом раскладе самым большим хабом в России будет Москва). На самом деле это упрощает задачу, если выйти за рамки собственно демо-базы и посмотреть чуть шире: для описания каждого аэропорта мы используем не только координаты, но и еще несколько характеристик. Одна из них — объем пассажиропотока, а сгенерированный с ее помощью граф вы видели в самом начале статьи.
А почему бы просто не взять маршруты какой-нибудь существующей авиакомпании? Можно, конечно, и так, но потеряется гибкость: имея алгоритм, можно сгенерировать правдоподобный граф для любого числа городов, или для вымышленной страны, или вообще для межгалактических перелетов.
— Кстати, какое максимальное число пересадок необходимо, чтобы добраться из любого аэропорта в любой другой? (Конечно, ответом на этот вопрос должен быть запрос на SQL.)
Ну хорошо, вот мы сгенерировали граф маршрутов, но его еще надо превратить в расписание регулярных рейсов. Причем рейсов между пунктами А и Б должно быть достаточно, чтобы перевезти всех желающих, но и не слишком много, иначе самолеты будут летать пустыми. А еще надо принять во внимание тип воздушного судна. Можно взять самолет поменьше, а рейсов сделать побольше.
— Есть ли в демо-базе рейсы, превышающие максимальную дальность назначенного на них самолета?
А можно наоборот — рейсов поменьше, зато самолет побольше. Вот только не все аэропорты могут принимать тяжелые широкофюзеляжные суда; это тоже при желании можно проверить, хотя в саму демо-базу мы не выносили информацию о классах аэропортов.
Ну и так далее. Вот еще несколько вопросов, которые намекают на то, что генерация данных не столь тривиальна, как это могло бы показаться на первый взгляд:
— Как реальное время полета отличается от запланированного?
— Обычно полеты с запада на восток длинные (вылетаем ночью, прилетаем утром следующего дня), а с востока на запад — короткие (прилетаем в тот же день почти в то же время). А что происходит в демо-базе?
— Как распределено время бронирования и время регистрации по отношению к дате и времени рейса?
— Сколько человек обычно входит в одно бронирование?
— Бывают ли пассажиры, летящие туда-обратно? Всегда ли маршрут «туда» совпадает с маршрутом «обратно»?
— У всех ли пассажиров места в посадочных талонах соответствуют классу обслуживания, выбранному при бронировании?
— Может ли получиться, что пассажиру выдан билет на место, которого нет в салоне? Могут ли два пассажира претендовать на одно место?
— Всегда ли билеты на места одного класса обслуживания на одном рейсе стоят одинаково (и почему)?
Напоследок
Надеемся, что вам будет не менее интересно работать с этими данными, чем нам было интересно работать над ними. В дальнейшие (хоть и не ближайшие) планы входит развитие схемы, чтобы охватить более «продвинутые» области: полнотекстовый поиск, слабоструктурированную информацию, темпоральные данные, различные стратегии индексирования.
Если вы обнаружите какие-либо несоответствия демонстрационных данных со здравым смыслом (а такое вполне может случиться — ведь сложно предусмотреть все на свете), не постесняйтесь написать нам на
Нам также очень интересно услышать о реальном использовании схемы данных. Поделитесь вашим опытом, а мы, в свою очередь, открыты для общения и готовы делиться своим.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
- Запустите командную строку.
Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните: chcp 1251 - Перейдите в каталог bin выполнив команду: CD C:\Program Files\PostgreSQL\11\bin
Основные команды PostgreSQL:
- Проверка установленной версии СУБД: psql –V
- Для создания новой базы данных воспользуйтесь утилитой createdb: createdb -U postgres testdb (где postgres суперпользователь, testdb новая база данных)Введите пароль суперпользователя.
- Проверить список активных баз: Psql -U postgres –l (пароль)
- С помощью инструмента createuser cоздадим нового пользователя: createuser –U postgres operator (где operator -имя нового пользователя)
- Предоставим пользователю привилегии суперпользователя (на практике этого делать не надо). Запустите интерактивную командную оболочку управления PostgreSQL (shell): psql –U postgres. С помощью SQL команды ALTER ROLE предоставим нужные права нашему пользователю: ALTER ROLE operator SUPERUSER CREATEROLE CREATEDB;. Мы предоставили пользователю права суперпользователя, права на создание ролей и баз данных.
- Для выводы списка пользователей и ролей в СУБД выполните команду: \du
Удаление базы или пользователя
Для этой операции используется команда drop: она умеет удалять как пользователя, так и БД.
drop database drop user
Данную команду нужно использовать очень осторожно, иначе удаленные данные будут потеряны, а восстановить их можно только из бэкапа (если он был).
Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:
psql [database_name] [user_name]
Давайте войдем в базу proglib_db под пользователем author. Нажмите \q, чтобы выйти из текущей БД, а затем выполните следующую команду:
- Beginning PostgreSQL on the Cloud. Работа с PostgreSQL начинается с этой книги. Приступать к изучению чего-то нового лучше с практики. Эта публикация включает в себя огромное количество полезных рабочих примеров, взятых из реальных проектов.
- Администрирование PostgreSQL 9. Книга рецептов. Отличная настольная книга для разработчиков боевых проектов на PHP, Ruby, .NET, Java и Python. Рассматриваются распространенные общие вопросы по архитектуре, восстановлению, репликации и т. д.
- Семь баз данных за семь недель. Данная книга подойдет для общего развития специалиста с любым уровнем знаний. Автор рассказывает об организации каждой СУБД, а также о том, в каком случае выгодно выбрать ту или иную БД.
Надеемся, что наш небольшой туториал помог вам разобраться с основами.
Команды PostgreSQL Linux
Основные команды для работы с данной СУБД для Ubuntu выглядят так:
- Запуск работы с СУБД — sudo su postgres.
- Переключение в консоль – psql.
- Переход на работу с базой от имени созданного пользователя — psql -h localhost mybase username.
- Создание простейшей таблицы о, например, роботах -.
CREATE TABLE Robots (
robots varchar(55),
engine_ver int, вариация двигателя.
color_ver int, вариация цвета.
usab real, уровень удобства управления.
date date
);
Просмотр таблиц — \d
Добавление данных выполняется в две команды:
INSERT INTO Robots (type, color,) VALUES (‘slide’, ‘blue’, ‘south’, ‘2018-01-15’);
INSERT INTO Robots (type, color,) VALUES (‘swing’, ‘yellow’, ‘northwest’, ‘2018-01-15’);
Удаление – DELETE FROM playground WHERE type = ‘blue’.
Перезапуск Postgresql – из нового окна Терминала выполнить sudo service postgresql restart.
Команды для крайнего случая, переустановка PostgreSQL :
apt-get purge postgresql
apt-get install postgresql
