Веб-стек / Ступень 8 из 9 · шаг 6 из 7

Восстанови базу из дампа

~25 мин

Не запущена Запускается обычно ~20 с

Ситуация

На этой машине работает база интернет-магазина shop в PostgreSQL. После выкладки новой версии из неё пропал заказ, а у части заказов изменились суммы.

Каждую ночь базу копируют командой pg_dump в /var/backups/shop. Команда хочет поднять рядом копию базы на момент до сбоя — отдельной базой shop_restore — и по ней вернуть данные. Саму shop пока не трогают: по ней разбирают, что сделала выкладка. Коллега уже начал восстанавливать копию в shop_restore, но не закончил и не записал, что успел.

Журнал выкладок — /var/log/shop/deploy.log. Команды администратора запускай через sudo; суперпользователем PostgreSQL подключает sudo -u postgres psql. Интернета на машине нет.

Задача

В shop_restore — заказы и настройки из последней копии, снятой до сбоя.
Таблицы shop_restore устроены так же, как в этой копии: столбцы, ключи, ограничения.
Новый заказ в shop_restore получит тот же номер, что получил бы в момент копии.
База shop_restore и всё в ней принадлежит роли shop — как в shop.
База shop и файлы копий в /var/backups/shop остаются как есть.

Цели лабы

0 из 5

В shop_restore заказы и настройки из последней копии, снятой до сбоя — ещё не проверялась
Таблицы shop_restore устроены, как в этой копии, — столбцы, ключи, ограничения — ещё не проверялась
Новый заказ в shop_restore получит тот же номер, что и в момент копии — ещё не проверялась
База shop_restore и всё в ней принадлежит роли shop — ещё не проверялась
База shop и файлы копий в /var/backups/shop не изменены — ещё не проверялась

Подсказки

открыто 0 из 3 · открываются по одной

Подсказка 1

Показать решение после 3 неудачных проверок

Для запуска машины нужен вход

Машина запускается на твой аккаунт, там же хранится прогресс. Читать можно без входа.

После входа вернёшься на эту лабу.

Службы и systemd · подобрано по лабе

PostgreSQL — доступ, pg_hba.conf, дамп и восстановление

Шпаргалка

$_ — вставить в терминал

sudo systemctl start postgresql в лабах служба выключена
pg_lsclusters версия, порт, статус
sudo -u postgres psql консоль суперпользователя
psql -h 127.0.0.1 -U appuser -d appdb вход по TCP с паролем
sudo -u postgres psql -c 'TABLE pg_hba_file_rules' правила доступа
sudo ss -tlnp | grep 5432 на каком адресе слушает
sudo -u postgres pg_dump -Fc -f /tmp/appdb.dump appdb
sudo -u postgres pg_restore -l /tmp/appdb.dump оглавление

Два файла в /etc/postgresql/16/main/: postgresql.conf — параметры сервера, pg_hba.conf — кто и как может войти. Какие файлы и значения сервер прочитал на самом деле — SHOW hba_file;, SHOW listen_addresses;.

pg_hba.conf

pg_hba.conf — правила доступа, по строке на правило. В Ubuntu по умолчанию:

text

# TYPE  DATABASE  USER      ADDRESS        METHOD
local   all       postgres                 peer
local   all       all                      peer
host    all       all       127.0.0.1/32   scram-sha-256
host    all       all       ::1/128        scram-sha-256

Поле	Что значит
TYPE	`local` — через сокет, `host` — по TCP
DATABASE	имя базы или `all`
USER	роль или `all`
ADDRESS	адрес клиента с маской; у `local` пусто
METHOD	как проверять: `peer`, `scram-sha-256`, `reject`

peer пускает пользователя Linux только в роль с тем же именем, поэтому суперпользователь входит как sudo -u postgres psql. scram-sha-256 — пароль. reject — отказ без проверки.

Строки читаются сверху вниз, действует первая подходящая, остальные не смотрятся. Строка reject выше разрешающей закрывает доступ даже с верным паролем. Новое правило для конкретной базы и роли ставь выше общих.

Правку применяет sudo systemctl reload postgresql или SELECT pg_reload_conf();. Что сервер прочитал, показывает представление pg_hba_file_rules: номер строки, поля и столбец error. С ошибкой в файле сервер его не принимает и работает по старым правилам, а в журнале — pg_hba.conf was not reloaded.

Адрес и порт

По умолчанию listen_addresses = 'localhost' — только 127.0.0.1 и ::1, порт 5432. Чтобы принимать подключения из сети, нужны три вещи: адрес в listen_addresses ('*' — все), строка host для адреса клиента в pg_hba.conf и открытый порт в файрволе. listen_addresses меняется только через sudo systemctl restart postgresql: после reload параметр помечен pending_restart, а сервер слушает по-старому.

Свою настройку удобно держать отдельным файлом: postgresql.conf читает каталог conf.d рядом с собой (include_dir = 'conf.d'), и обновление пакета такой файл не трогает:

bash

echo "listen_addresses = '127.0.0.1'" \
  | sudo tee /etc/postgresql/16/main/conf.d/listen.conf
sudo systemctl restart postgresql
sudo ss -tlnp | grep 5432

conf.d читается после строки include_dir в конце postgresql.conf, и действует последнее значение. С listen_addresses = '*' в postgresql.conf и '127.0.0.1' в conf.d/listen.conf сервер слушает 127.0.0.1. Откуда взято значение и ждёт ли оно перезапуска, показывает pg_settings:

text

student@ubuntu:~$ sudo -u postgres psql -Atc "SELECT setting,
> sourcefile, sourceline, pending_restart FROM pg_settings
> WHERE name='listen_addresses'"
127.0.0.1|/etc/postgresql/16/main/conf.d/listen.conf|1|f

После правки файла и reload значение прежнее, pending_restart — t, а в журнале /var/log/postgresql/postgresql-16-main.log:

text

LOG:  parameter "listen_addresses" cannot be changed without restarting
  the server
LOG:  configuration file "/etc/postgresql/16/main/conf.d/listen.conf"
  contains errors; unaffected changes were applied

Каждую строку каждого файла с её судьбой показывает pg_file_settings (applied, error — например, setting could not be applied).

Роль и база для приложения

bash

sudo -u postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'apppass'"
sudo -u postgres createdb -O appuser appdb
psql -h 127.0.0.1 -U appuser -d appdb -c 'SELECT current_user'

Новый пароль — ALTER ROLE appuser PASSWORD '…';, список ролей — \du в psql, баз — \l.

Пароль без ввода: ~/.pgpass

bash

echo '127.0.0.1:5432:appdb:appuser:apppass' > ~/.pgpass
chmod 600 ~/.pgpass
psql -h 127.0.0.1 -U appuser -d appdb

Строка — адрес:порт:база:роль:пароль, * подходит к любому значению. Режим — только 600: иначе psql пишет password file … has group or world access и файл пропускает. Переменная PGPASSWORD важнее файла.

Дамп: форматы

Логический дамп снимает pg_dump — команды и данные, из которых база создаётся заново. Роли в него не входят: их сохраняет pg_dumpall --globals-only.

Формат	Команда	Восстанавливает
простой SQL	`pg_dump -f appdb.sql appdb`	`psql -f`
custom	`pg_dump -Fc -f appdb.dump appdb`	`pg_restore`
каталог	`pg_dump -Fd -j 4 -f appdb.dir appdb`	`pg_restore -j 4`

Простой SQL читается глазами: CREATE TABLE, COPY с данными, setval для счётчиков. В начале и в конце — строки \restrict и \unrestrict со случайным ключом: защита psql от чужих команд в данных, так и должно быть. Формат custom сжат, начинается с байтов PGDMP и позволяет посмотреть оглавление и восстановить часть:

bash

sudo -u postgres pg_restore -l /tmp/appdb.dump

text

216; 1259 16387 TABLE public orders appuser
215; 1259 16386 SEQUENCE public orders_id_seq appuser
3396; 0 16387 TABLE DATA public orders appuser
3404; 0 0 SEQUENCE SET public orders_id_seq appuser

Над оглавлением — шапка архива: Archive created at 2026-10-20 10:05:53 UTC, dbname: appdb и версии сервера. По ней видно, какая это копия и откуда. Данные одной таблицы без восстановления печатает pg_restore -f - -t orders /tmp/appdb.dump — определение таблицы и строки COPY.

Восстановить в новую базу

bash

sudo -u postgres createdb -O appuser appdb_copy
sudo -u postgres pg_restore -d appdb_copy /tmp/appdb.dump
sudo -u postgres psql -d appdb_copy -c 'SELECT count(*) FROM orders'

Новая пустая база — без конфликтов: код 0. Счётчик id дамп восстанавливает строкой setval('public.orders_id_seq', 3, true) — следующая вставка получит id 4.

Владельцы. createdb -O appuser задаёт владельца самой базы. Владельцев таблиц pg_restore от postgres берёт из архива — строками ALTER TABLE … OWNER TO appuser, и такая роль должна существовать. С --no-owner владельцем станет тот, кто восстанавливает, а с --no-owner --role=r — роль r.

Ключ pg_restore	Что делает
`-l`	только оглавление архива
`-L список`	восстановить строки оглавления из файла
`-f -`	напечатать SQL вместо записи в базу
`--exit-on-error`	остановиться на первой ошибке
`--single-transaction`	всё или ничего: при ошибке откат
`--clean --if-exists`	удалить объекты перед созданием
`--create -d postgres`	создать базу с именем из дампа
`--no-owner --role=r`	владельцем объектов станет роль r

Сверить копию с источником

sql

SELECT count(*) FROM orders;
SELECT md5(string_agg(t::text, ',' ORDER BY id)) FROM orders t;
SELECT last_value FROM orders_id_seq;

Одинаковые число строк, сумма выборки и значение счётчика в источнике и копии — данные совпадают. Владельца таблиц покажет \dt.

Connection refused

connection to server at "127.0.0.1", port 5432 failed: Connection refused и Is the server running on that host and accepting TCP/IP connections?. Служба не запущена (pg_lsclusters, systemctl status postgresql) или не слушает этот адрес: sudo ss -tlnp | grep 5432, SHOW listen_addresses;. Через сокет то же выглядит как No such file or directory и Is the server running locally….

no pg_hba.conf entry

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "appuser", database "appdb", no encryption — ни одна строка не подошла к этому адресу, базе и роли. Сообщение приходит дважды: psql пробует подключиться с SSL, потом без. Добавь строку host appdb appuser <адрес>/32 scram-sha-256, reload и проверь pg_hba_file_rules.

pg_hba.conf rejects connection for host … — подошла строка reject, она стоит выше разрешающей.

password authentication failed

Правило нашлось, пароль не подошёл. Для роли, которой нет, сервер пишет то же самое — проверь \du. Пароль задаёт ALTER ROLE … PASSWORD; помни, что PGPASSWORD перекрывает ~/.pgpass. fe_sendauth: no password supplied — пароль спросили, а взять его неоткуда.

Peer authentication failed

psql -U appuser без -h идёт через сокет, а там правило peer: пользователь Linux должен называться как роль. Подключайся с -h 127.0.0.1 и паролем или sudo -u postgres psql. role "student" does not exist — psql без -U берёт роль по имени пользователя Linux.

Правка pg_hba.conf не действует

Файл не перечитан или перечитан с ошибкой — тогда работают старые правила, а pg_reload_conf() всё равно возвращает t. Ошибку покажет SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;, в журнале — pg_hba.conf was not reloaded.

pg_restore: relation "…" already exists

Восстановление в базу, где объекты уже есть: ошибка на каждом, в конце pg_restore: warning: errors ignored on restore: 4, код 1, и часть данных могла лечь поверх. Восстанавливай в новую пустую базу. --single-transaction при первой ошибке откатывает всё. --create -d postgres при живой базе с тем же именем — database "appdb" already exists.

input file appears to be a text format dump

pg_restore получил простой SQL — восстанавливай его psql -f. Обратная ошибка: psql -f на custom-дампе — The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client….

trust в pg_hba.conf

trust пускает без пароля любого, кто подходит под строку. host all all 0.0.0.0/0 trust открывает все базы каждому, кто дотянется до порта.

pg_restore --clean в рабочую базу

--clean удаляет объекты перед созданием. В рабочей базе это замена живых данных старыми из дампа. Сначала восстанови в отдельную базу и сверь.