PostgreSQL с нуля: база и первое подключение

PostgreSQL с нуля: как устроена база и куда вы подключаетесь

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

Например, интернет-магазин может хранить покупателей, товары и заказы. В табличной базе данные выглядят примерно так:

id	покупатель	сумма
101	Анна	3 500
102	Илья	890

Здесь orders — таблица, id, покупатель и сумма — столбцы, а каждый заказ — отдельная строка. SQL — язык команд, с помощью которого приложение или человек читает и изменяет эти данные.

#Что станет понятно после урока

Вы разберётесь:

чем PostgreSQL отличается от базы данных, схемы и таблицы;
как клиент подключается к серверу;
как проверить адрес сервера, базу и роль текущего подключения;
зачем PostgreSQL нужны MVCC, VACUUM и WAL;
как запустить безопасную учебную базу PostgreSQL 18.4.

Не пытайтесь сразу запомнить все служебные команды. Цель первого урока — увидеть общую картину и научиться проверять подключение перед работой.

#Сервер, клиент и подключение

PostgreSQL работает по модели «клиент — сервер».

Сервер — запущенная программа postgres. Она хранит файлы базы, принимает подключения и выполняет SQL-команды.
Клиент — программа, которая отправляет команды серверу. Клиентом может быть psql, графическая программа, веб-приложение или ваш код на Python.
Подключение — канал связи между клиентом и сервером.
Сеанс — время от подключения до отключения. Настройки, изменённые только для текущего сеанса, пропадут после выхода.
Роль — учётная запись PostgreSQL и связанный с ней набор прав. Роль определяет, какие данные можно читать и изменять.

Клиент и сервер не обязаны находиться на одном компьютере. Поэтому перед изменением данных важно проверить не только имя базы, но и адрес сервера.

#Как PostgreSQL организует данные

В PostgreSQL есть четыре уровня. Они вложены друг в друга:

кластер PostgreSQL
├── база данных course
│   ├── схема marketplace
│   │   ├── таблица orders
│   │   └── таблица payments
│   └── схема lab_01
└── база данных postgres

Разберём уровни сверху вниз.

Кластер баз данных (database cluster) — все базы, которыми управляет один запущенный сервер PostgreSQL и которые хранятся в одном каталоге данных. Здесь «кластер» не означает несколько компьютеров.
База данных (database) — отдельное пространство данных внутри кластера. При подключении клиент выбирает одну базу. Обычный SQL-запрос не обращается напрямую к таблицам другой базы.
Схема (schema) — именованная папка внутри базы. Схемы помогают разделять объекты разных частей приложения. Например, таблица заказов может называться marketplace.orders, где marketplace — схема.
Таблица (table) — набор строк с одинаковыми столбцами. Кроме таблиц, в схеме могут находиться представления, функции и другие объекты.

Если в запросе написать только orders, PostgreSQL найдёт таблицу по настройке search_path. Это упорядоченный список схем для поиска. Полное имя marketplace.orders не зависит от search_path, поэтому в миграциях и служебных сценариях безопаснее указывать схему явно.

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

#Безопасная учебная база

Для практики нужны Git, Docker и команда make. Если вы пока хотите только прочитать урок, этот раздел можно пропустить и вернуться к нему перед лабораторной работой.

Курс запускает PostgreSQL в контейнере Docker. Контейнер — изолированное окружение для программы. Docker Compose читает описание этого окружения и запускает все нужные части одной командой. Данные хранятся в именованном томе (named volume) — отдельном хранилище Docker, которое не исчезает при обычном перезапуске контейнера.

В терминале выполните команды по порядку:

git clone --branch v1.1.0 \
  https://gitlab.potapov.me/courses/postgresql-labs.git
cd postgresql-labs
make up
make smoke
docker compose exec postgres psql -U course -d course

Что делает каждая команда:

git clone --branch v1.1.0 ... скачивает зафиксированную версию лабораторий;
cd postgresql-labs переходит в скачанную папку;
make up запускает учебный PostgreSQL;
make smoke быстро проверяет, что сервер готов принимать подключения;
последняя команда открывает psql, подключается с ролью course к базе course внутри контейнера postgres.

psql — текстовый клиент PostgreSQL. После успешного подключения появится приглашение вида course=>. SQL-команда заканчивается точкой с запятой, а для выхода из psql используется команда \q.

Учебные имя пользователя и пароль предназначены только для этого локального стенда. Не выполняйте команды курса в рабочей базе компании. Безопасный сброс описан в README лабораторий: он удаляет только именованный том этого учебного проекта.

#Проверьте, куда вы подключились

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

SELECT
    current_setting('server_version') AS server_version,
    current_database() AS database_name,
    session_user,
    current_user,
    inet_server_addr() AS server_address,
    inet_server_port() AS server_port,
    current_setting('TimeZone') AS session_timezone,
    current_setting('search_path') AS search_path;

Запрос ничего не меняет. Он показывает:

версию PostgreSQL;
имя выбранной базы;
роль, которая открыла подключение (session_user);
роль, чьи права действуют сейчас (current_user);
адрес и порт сервера;
часовой пояс и порядок поиска схем.

Обычно session_user и current_user совпадают. После команды SET ROLE app_reader значение current_user станет app_reader, потому что PostgreSQL начнёт проверять права от имени этой роли. session_user не изменится: это исходная роль подключения. Команда RESET ROLE вернёт прежние права.

У psql есть собственные команды, которые начинаются с обратной косой черты. Это не SQL: их выполняет сам клиент.

\conninfo      показать текущее подключение
\dn+           показать схемы и их владельцев
\dt+ *.*       показать таблицы во всех схемах и их размеры
\d+ object     описать столбцы, ограничения и индексы объекта
\du+           показать роли и их свойства
\x auto        удобнее выводить очень широкие строки
\timing on     показывать время выполнения команд на стороне клиента

Перед запуском SQL-файла полезно включить остановку после первой ошибки:

\set ON_ERROR_STOP on

Без этой настройки psql может продолжить сценарий после неудачной команды, и следующие действия выполнятся уже в неожиданном состоянии.

#Что происходит после подключения

Для каждого обычного клиентского подключения PostgreSQL создаёт отдельный серверный процесс. В документации он называется обслуживающим процессом (backend process). Он принимает SQL именно от этого клиента и возвращает результат.

Серверные процессы используют общую память (shared memory). В ней находятся, например, общий кеш страниц данных, буферы WAL и сведения о блокировках. Кроме них работают фоновые процессы: они записывают данные на диск, очищают старые версии строк, обслуживают репликацию и выполняют другие служебные задачи.

Посмотреть процессы, связанные с текущей базой, можно так:

SELECT pid, backend_type, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database();

pid — номер процесса, state — его состояние, query — последняя или текущая команда. Поля wait_event_type и wait_event показывают, чего процесс ждёт, например блокировки или ввода-вывода.

Даже бездействующее подключение расходует память и занимает процесс. Поэтому приложения часто используют пул подключений: посредник держит ограниченное число соединений с PostgreSQL и временно выдаёт их запросам приложения.

#MVCC: как читать данные во время изменений

Транзакция — группа команд, которая завершается целиком через COMMIT или отменяется через ROLLBACK. Но что произойдёт, если одна транзакция читает строку, пока другая её изменяет?

PostgreSQL решает эту задачу с помощью MVCC — многоверсионного управления одновременным доступом. При UPDATE сервер обычно не стирает строку на месте, а создаёт её новую версию. Поэтому разные транзакции некоторое время могут видеть разные версии одной строки.

Снимок данных (snapshot) — правила, по которым PostgreSQL решает, какие версии строк видны конкретной SQL-команде или транзакции. На стандартном уровне изоляции READ COMMITTED каждая SQL-команда получает новый снимок в момент своего начала. Поэтому два SELECT в одной транзакции могут показать разные значения, если между ними другая транзакция успела выполнить COMMIT.

Благодаря MVCC чтение обычно не мешает обычному изменению строки, а изменение — чтению. Это не означает полного отсутствия блокировок: две транзакции, которые изменяют одну строку, могут ждать друг друга или конфликтовать.

Старые версии строк нельзя удалять сразу: возможно, их ещё видит ранее начатый снимок. Команда VACUUM находит версии, которые уже никому не видны, и делает занятое ими место доступным для повторного использования. Обычно VACUUM не уменьшает файл таблицы, а подготавливает свободное место внутри него.

#WAL: как PostgreSQL переживает сбой

WAL расшифровывается как write-ahead log, то есть журнал предварительной записи. Перед тем как записать изменённую страницу таблицы в её основной файл, PostgreSQL надёжно сохраняет в WAL описание изменения.

Страница данных (data page) — небольшой блок файла таблицы, с которым PostgreSQL работает как с единым целым. Надёжно записать (durable write) означает передать данные хранилищу так, чтобы они пережили отказ процесса или операционной системы.

Если сервер аварийно завершится, после запуска он прочитает WAL и повторит необходимые изменения. Этот процесс называют восстановлением после сбоя (crash recovery). Команда COMMIT фиксирует транзакцию, но не обязана ждать, пока каждая изменённая страница таблицы попадёт в основной файл: достаточно, чтобы необходимые записи WAL были надёжно сохранены.

WAL также используется для физической репликации и восстановления базы на нужный момент времени. Но WAL не заменяет резервную копию и не является журналом действий пользователей.

Тонкость для продвинутого уровня: при synchronous_commit=off сервер может сообщить клиенту об успешном COMMIT до надёжной локальной записи WAL. При внезапном отказе можно потерять несколько последних подтверждённых транзакций. Структура базы при этом не должна повредиться.

#Практика и самопроверка

Лаборатория 01 предлагает создать представление connection_profile. Представление (view) — сохранённый запрос, к которому можно обращаться как к таблице. Автоматическая проверка убеждается, что профиль показывает текущее подключение, а не заранее вписанные значения.

Перед тестом попробуйте своими словами ответить на пять вопросов:

Чем PostgreSQL отличается от базы данных?
Где в цепочке «кластер → база → схема → таблица» находится orders?
Чем session_user отличается от current_user?
Зачем PostgreSQL хранит несколько версий строки?
Почему запись в WAL должна произойти раньше записи страницы таблицы?

Если ответы укладываются в одно-два предложения, основная модель уже сложилась.

Официальная документация: архитектура PostgreSQL, клиент psql, управление одновременным доступом, журнал WAL.

Устранение неисправностей

покупатель

сумма

101

Анна

3 500

102

Илья

890

PostgreSQL с нуля: база и первое подключение

PostgreSQL с нуля: как устроена база и куда вы подключаетесь

#Что станет понятно после урока

#Сервер, клиент и подключение

#Как PostgreSQL организует данные

#Безопасная учебная база

#Проверьте, куда вы подключились

#Что происходит после подключения

#MVCC: как читать данные во время изменений

#WAL: как PostgreSQL переживает сбой

#Практика и самопроверка

Устранение неисправностей

Нужно ли устанавливать PostgreSQL прямо в систему?

После команды подключения появилась ошибка connection refused

Как выйти из psql?

PostgreSQL с нуля: база и первое подключение

PostgreSQL с нуля: как устроена база и куда вы подключаетесь

#Что станет понятно после урока

#Сервер, клиент и подключение

#Как PostgreSQL организует данные

#Безопасная учебная база

#Проверьте, куда вы подключились

#Что происходит после подключения

#MVCC: как читать данные во время изменений

#WAL: как PostgreSQL переживает сбой

#Практика и самопроверка

Устранение неисправностей

Нужно ли устанавливать PostgreSQL прямо в систему?

После команды подключения появилась ошибка connection refused

Как выйти из psql?