PART 4 · LOGICAL BACKUP USING PG_DUMP
pg_dump formats: text-based SQL (-Fp) and compressed archive (-Fc)
Why is the Custom (-Fc) format the standard for pg_dump in engineering practice?
Plain SQL format (-Fp, default)
Custom Format (-Fc, industry standard)
• The output is a standard text file named backup.sql containing CREATE TABLE and COPY ... FROM stdin commands.
• How to restore: using the standard psql client with the -f backup.sql option (the pg_restore utility does not accept this file!).
• Bonus: It’s convenient to view it in a text editor or commit it to Git as a schema reference (--schema-only).
• The output is a compressed binary archive named `backup.dump` containing an internal table of contents listing all objects (a directory).
• How to restore: strictly using the pg_restore utility.
• Power features: automatic compression, table of contents preview (pg_restore -l), selective restoration of a single table (-t purchases), and multithreading (-j 4)!
# 1. Снимаем полный сжатый дамп (-Fc) базы gamedb внутри контейнера:
docker exec itmo_pg_dba pg_dump -U postgres -d gamedb -Fc -f /tmp/gamedb.dump
# 2. Снимаем только схему без данных (--schema-only) в текстовый SQL для Git-репозитория:
docker exec itmo_pg_dba pg_dump -U postgres -d gamedb --schema-only -n game > schema_snapshot.sql
# 3. Смотрим оглавление бинарного архива без подключения к БД:
docker exec itmo_pg_dba pg_restore -l /tmp/gamedb.dump | head -n 20
An important note for PowerShell on Windows: the > operator in the older version of PowerShell 5 may convert the binary stream to UTF-16! It's safer to use -f /tmp/gamedb.dump and copy the data using `docker cp`.