Настройка PostgreSQL в Angie

Источник: Introbook_v11_Postgres17-RU.pdf

Установка и начало работы

Что нужно для начала работы с PostgreSQL? В этой главе мы объясним, как установить службу PostgreSQL и управлять ею, а в следующей создадим простую базу данных и на ее примере изложим основы языка SQL, на котором формулируются запросы.

Мы возьмем обычный («ванильный») дистрибутив PostgreSQL 17. Сервер PostgreSQL устанавливается и запускается по-разному в зависимости от того, какая у вас операционная система:

• если Windows, читайте дальше; • если Linux семейства Debian или Ubuntu — переходите к с. 32.

Инструкции по установке для других операционных систем есть здесь: postgresql.org/download.

С тем же успехом вы можете воспользоваться и дистрибутивом Postgres Pro Standard 17: он полностью совместим с обычной СУБД PostgreSQL, включает некоторые разработки, выполненные в нашей компании Postgres Professional, и бесплатен при использовании в ознакомительных и образовательных целях. В этом случае инструкции по установке ищите на сайте postgrespro.ru/products/download.

Установка в Debian или Ubuntu

Если вы используете Linux, то для установки необходимо подключить пакетный репозиторий PGDG (PostgreSQL Global Development Group). В настоящее время для системы Debian поддерживаются версии 10 «Buster», 11 «Bullseye» и 12 «Bookworm», а для Ubuntu — 20.04 «Focal», 22.04 «Jammy», 23.10 «Mantic» и 24.04 «Noble».

Автоматическая настройка репозитория:

sudo apt install -y postgresql-common

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Чтобы вручную настроить репозиторий Apt, выполните следующие действия:

sudo apt install curl ca-certificates  

sudo install -d /usr/share/postgresql-common/pgdg

sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

Репозиторий подключен, обновим список пакетов:

sudo apt update

Перед установкой PostgreSQL проверьте настройки локализации:

locale

Чтобы работать с данными на языке, отличном от английского, может потребоваться изменить значение переменных LC_CTYPE и LC_COLLATE. Для русского языка подходит и локаль en_US.UTF8, но все-таки лучше ее сменить:

export LC_CTYPE=ru_RU.UTF8
export LC_COLLATE=ru_RU.UTF8

Также убедитесь, что в операционной системе установлена соответствующая локаль:

 locale -a | grep ru_RU

ru_RU.utf8

Если это не так, сгенерируйте ее:

sudo locale-gen ru_RU.utf8

Теперь можно приступить к установке:

sudo apt -y install postgresql

Это был последний этап; теперь СУБД PostgreSQL установлена, запущена и готова к работе. Чтобы проверить это, выполните команду:

sudo -u postgres psql -c 'select version()'

Если все проделано успешно, в ответ вы должны получить версию PostgreSQL.

Управление службой и основные файлы

При установке PostgreSQL создает специальную учетную запись postgres, от имени которой работают процессы, обслуживающие сервер, и которой принадлежат все файлы, относящиеся к СУБД. PostgreSQL будет автоматически запускаться при перезагрузке операционной системы. С настройками по молчанию это не проблема, так как при отсутствии обращений к серверу ресурсов системы тратится совсем немного. Если вы все-таки захотите отключить автозапуск, выполните:

sudo systemctl disable postgresql

Чтобы временно остановить службу сервера баз данных, выполните команду:

sudo systemctl stop postgresql

Запустить службу сервера можно командой:

sudo systemctl start postgresql

Можно также проверить текущее состояние:

sudo systemctl status postgresql

Если служба не запускается, найти причину поможет журнал сообщений сервера. Внимательно прочитайте самые последние записи из журнала, который находится в файле var/log/postgresql/postgresql-17-main.log. Вся информация, которая содержится в базе данных, располагается в файловой системе в специальном каталоге /var/lib/postgresql/17/main/. Если вы собираетесь хранить очень много данных — убедитесь, что для них хватит места.

Есть несколько важных конфигурационных файлов, которые определяют настройки сервера. При начале работы изменять эти файлы не нужно, но лучше ознакомиться с ними заранее — в дальнейшем они непременно понадобятся:

/etc/postgresql/17/main/postgresql.conf — основной конфигурационный файл, содержащий значения параметров сервера;
/etc/postgresql/17/main/pg_hba.conf — файл, определяющий настройки доступа. В целях безопасности по умолчанию доступ разрешен только с локального компьютера и только от лица пользователя базы данных, имя которого совпадает с именем учетной записи операционной системы.

Самое время подключиться к базе данных и попробовать SQL в деле.

Подключение с помощью psql

Чтобы подключиться к серверу СУБД и выполнить какие либо команды, требуется программа-клиент. В главе «PostgreSQL для приложения» мы будем говорить о том, как посылать запросы из программ на разных языках программирования, а сейчас речь пойдет о терминальном клиенте psql, работа с которым происходит интерактивно в режиме командной строки.

К сожалению, в наше время многие недолюбливают командную строку. Почему имеет смысл научиться с ней работать?

Во-первых, psql — стандартный клиент, он входит в любую сборку PostgreSQL и поэтому всегда под рукой. Иметь настроенную под себя среду — это, конечно, хорошо, но оказаться беспомощным в среде незнакомой просто нелогично.

Во-вторых, psql действительно удобен для решения повседневных задач по администрированию баз данных, для написания небольших запросов и для автоматизации процессов, например, периодической установки обновлений программного кода на сервер СУБД. Он имеет собственные команды, позволяющие сориентироваться в объектах, хранящихся в базе данных, и представить информацию из таблиц в наглядном виде.

Но если вы привыкли работать с графическими пользовательскими интерфейсами, попробуйте pgAdmin — мы еще упомянем эту программу ниже — или другие аналогичные продукты: wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

Чтобы запустить psql в операционной системе Linux, выполните команду:

sudo -u postgres psql

В ОС Windows запустите программу «SQL Shell (psql)» из меню «Пуск». В ответ на запрос вве- дите пароль пользователя postgres, указанный при установке PostgreSQL.

Пользователи Windows могут столкнуться с проблемой неправильного отображения символов кириллицы в терминале. В этом случае убедитесь, что свойствах окна терминала установлен TrueType- шрифт (обычно «Lucida Console» или «Consolas»).

Итак, приглашение выглядит одинаково в обеих операционных системах: postgres=# («postgres» здесь — это имя базы данных, к которой вы сейчас подключены). Один сервер может обслуживать несколько БД, но работать в каждый момент времени можно только с одной.

Теперь изучим первые команды. Вводите только то, что выделено жирным; приглашение и ответ системы на команду приведены исключительно для удобства.

База данных

Создадим новую базу данных с именем test. Выполните:

postgres=# CREATE DATABASE test;

CREATE DATABASE

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

Теперь переключимся на созданную базу:

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=#

Как видите, приглашение сменилось на test=#.

Команда, которую мы только что ввели, не похожа на SQL — она начинается с обратной косой черты. Так выглядят специальные команды, которые понимает только psql (поэтому, если у вас открыт pgAdmin или другое графическое средство, пропускайте все, что начинается с косой черты, или поищите замену).

Команд psql довольно много; с некоторыми из них мы познакомимся чуть позже, а полный список с краткими описаниями можно получить прямо сейчас:

test=# \?

Поскольку справочная информация довольна объемна, она будет показана с помощью настроенной в операционной системе команды-пейджера (обычно more или less).

Таблицы

В реляционных СУБД данные представляются в виде таблиц. Структура таблицы определяется ее столбцами. Собственно данные располагаются в строках; они хранятся неупорядоченными и даже не обязательно располагаются в порядке их добавления в таблицу.

Для каждого столбца устанавливается тип данных; значения полей в строках должны соответствовать этим типам. PostgreSQL располагает большим числом встроенных типов (postgrespro.ru/doc/datatype) и возможностями для создания новых, но мы ограничимся самыми основными:

integer — целые числа;
text — текстовые строки;
boolean — логический тип, принимающий значения true («истинно») или false («ложно»).

Помимо обычных значений, определяемых типом данных, поле может иметь неопределенное значение NULL — его можно рассматривать как «значение неизвестно» или «значение не задано».

Давайте создадим таблицу дисциплин, читаемых в вузе:

test=# CREATE TABLE courses(
test(# c_no text PRIMARY KEY,
test(# title text,
test(# hours integer
test(# );

CREATE TABLE

Обратите внимание, как меняется приглашение psql: это подсказка, что ввод команды продолжается на новой строке. В дальнейшем для удобства мы не будем дублировать приглашение на каждой строке.

Этой командой мы определили, что таблица с именем courses будет состоять из трех столбцов: c_no — текстовый номер курса, title — название курса, и hours — целое число лекционных часов.

Кроме столбцов и типов данных, можно ввести ограничения целостности, которые будут проверяться автоматически, — СУБД не допустит появления в базе некорректных данных. В нашем примере добавлено ограничение PRIMARY KEY для столбца c_no; теперь в нем не допускаются повторяющиеся, а также неопределенные значения. С помощью такого столбца можно отличать строки друг от друга. Полный список ограничений целостности есть на странице postgrespro.ru/doc/ddl-constraints.

Точный синтаксис команды CREATE TABLE можно посмотреть в документации, а можно прямо в psql:

test=# \help CREATE TABLE

Такая справка есть по каждой команде SQL, а полный список команд покажет \help без параметров.

Наполнение таблиц

Добавим в созданную таблицу несколько строк:

test=# INSERT INTO courses(c_no, title, hours)
VALUES ('CS301', 'Базы данных', 30),
       ('CS305', 'Сети ЭВМ', 60);

INSERT 0 2

Для массовой загрузки данных из внешнего источника команда INSERT подходит плохо, зато есть специально предназначенная для этого команда COPY: postgrespro.ru/doc/sql-copy.

Создадим в базе еще две таблицы: «Студенты» и «Экзамены». Пусть по каждому студенту хранится его имя и год поступления, а идентифицироваться он будет номером студенческого билета.

test=# CREATE TABLE students(
  s_id integer PRIMARY KEY,
  name text,
  start_year integer
);

CREATE TABLE

test=# INSERT INTO students(s_id, name, start_year)
VALUES (1451, 'Анна', 2014),
       (1432, 'Виктор', 2014),
       (1556, 'Нина', 2015);
       
INSERT 0 3

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

Запись в таблице экзаменов идентифицируется совокупностью номера студбилета и номера курса. Такое ограничение целостности, относящее сразу к нескольким столбцам, определяется с помощью предложения CONSTRAINT:

test=# CREATE TABLE exams(
  s_id integer REFERENCES students(s_id),
  c_no text REFERENCES courses(c_no),
  score integer,
  CONSTRAINT pk PRIMARY KEY(s_id, c_no)
);

CREATE TABLE

Кроме того, с помощью предложения REFERENCES мы добавили два ограничения ссылочной целостности, называемые внешними ключами. Такие ограничения показывают, что значения в одной таблице ссылаются на строки в другой таблице.

Теперь при любых действиях СУБД будет проверять соответствие всех идентификаторов s_id, указанных в таблице экзаменов, реальным студентам (то есть записям в таблице студентов), а также номера c_no — реальным курсам. Таким образом, будет исключена возможность поставить оценку несуществующему студенту или же по несуществующей дисциплине — независимо от действий пользователя или возможных ошибок в приложении.

Поставим нашим студентам несколько оценок:

test=# INSERT INTO exams(s_id, c_no, score)
VALUES (1451, 'CS301', 5),
       (1556, 'CS301', 5),
       (1451, 'CS305', 5),
       (1432, 'CS305', 4);
       
INSERT 0 4

Выборка данных

Простые запросы

Чтение данных из таблиц выполняется оператором SQL SELECT. Для примера выведем только два столбца из таблицы courses. Конструкция AS позволяет переименовать столбец, если это необходимо:

test=# SELECT title AS course_title, hours
FROM courses;

course_title  | hours
--------------+-------
Базы данных   | 30
Сети ЭВМ      | 60

(2 rows)

Чтобы вывести все столбцы, достаточно указать символ звездочки:

test=# SELECT * FROM courses;

c_no   | title       | hours
-------+-------------+-------
CS301  | Базы данных | 30
CS305  | Сети ЭВМ    | 60

(2 rows)

В промышленном коде лучше явно перечислять только необходимые столбцы, чтобы запрос выполнялся эффективнее, а результат не зависел от появления новых столбцов. Но для интерактивных запросов «звездочка» очень удобна.

Выдача по запросу может содержать одинаковые строки. Когда выводятся не все столбцы — дубликаты могут появиться даже если в исходной таблице их не было:

test=# SELECT start_year FROM students;

start_year
------------
      2014
      2014
      2015

(3 rows)

Чтобы выбрать все различные года поступления, после SELECT надо добавить слово DISTINCT:

test=# SELECT DISTINCT start_year FROM students;

start_year
------------
     2014
     2015
     
(2 rows)

Подробнее смотрите в документации: postgrespro.ru/doc/sql-select#SQL-DISTINCT

Вообще после слова SELECT можно указывать любые выражения. А без предложения FROM запрос вернет одну строку.

Например:

test=# SELECT 2+2 AS result;

result
--------
4

(1 row)

Обычно при выборке данных требуется получить не все строки, а только те, которые удовлетворят какому-либо условию. Такое условие фильтрации записывается в предложении WHERE:

test=# SELECT * FROM courses WHERE hours > 45;

c_no   |  title   | hours
-------+----------+-------
CS305  | Сети ЭВМ | 60

(1 row)

Условие должно иметь логический тип. Например, оно может содержать операторы =, <> (или !=), >, >=, <, <=, а также может объединять более простые условия с помощью логических операций AND, OR, NOT и круглых скобок — как в обычных языках программирования.

Тонкий момент представляет собой неопределенное значение NULL. В выборку попадают только те строки, для которых условие фильтрации истинно; если же значение ложно или не определено, строка отбрасывается.

Учтите:

результат сравнения чего-либо с неопределенным значением не определен;
результат логических операций с неопределенным значением, как правило, не определен (исключения: true OR NULL = true, false AND NULL = false);
для проверки определенности значения используются специальные операторы IS NULL (IS NOT NULL) и IS DISTINCT FROM (IS NOT DISTINCT FROM).

При работе с неопределенными значениями часто используют выражение coalesce (читается «коуэлес») для замены NULL на что-нибудь другое, например, на пустую строку для текстовых типов или на ноль для числовых.

Подробнее смотрите в документации: postgrespro.ru/doc/functions-comparison.

... смотри далее в книжке ...

llcawc/postsql-angie.md