PART 4 · RESTORE DRILL
The DBA's Golden Rule: Verifying a Restore Using pg_restore
A corrupted or incomplete dump file is no different from having no dump file at all—we'll test the restoration into a clean database
# Шаг 1. Снимаем архив внутри контейнера (безопасно для любой ОС и кодировки терминала)
docker exec itmo_pg_dba pg_dump -U postgres -d gamedb -Fc -f /tmp/gamedb.dump
# Шаг 2. Создаём абсолютно чистую проверочную базу gamedb_restore_check
docker exec itmo_pg_dba psql -v ON_ERROR_STOP=1 -U postgres \
-c "DROP DATABASE IF EXISTS gamedb_restore_check;" \
-c "CREATE DATABASE gamedb_restore_check;"
# Шаг 3. Восстанавливаем архив с остановкой на первой же ошибке (--exit-on-error)
docker exec itmo_pg_dba pg_restore --exit-on-error -U postgres -d gamedb_restore_check /tmp/gamedb.dump
# Шаг 4. Сравниваем количество строк в таблицах gamedb и gamedb_restore_check!
Global Cluster Objects (Roles) and pg_dump
Selective Recovery of a Single Table
• Remember: pg_dump dumps only one database, but the roles (game_owner, game_app) belong to the entire PostgreSQL cluster!
• When restoring to a new server, first transfer the roles using `pg_dumpall --roles-only`, or add `--no-owner --no-privileges`.
• If a developer accidentally clears the item table, there’s no need to roll back the entire database to yesterday’s state:
• It’s enough to restore just one table from the archive: pg_restore -U postgres -d gamedb --clean -t items /tmp/gamedb.dump.
In Exercise 5, you will perform this cycle (taking a .dump → loading it into restore_check → running a COUNT(*) comparison) on the database for your command-line project.