PostgreSQL. Бэкапирование и восстановление БД

Вариант 1. Командная строка

Бэкапирование

   -U to specify which user will connect to the PostgreSQL database server.

   -W or --password will force pg_dump to prompt for a password before connecting to the server.

   -F is used to specify the format of the output file, which can be one of the following:

   p – plain-text SQL script

   c – custom-format archive

   d – directory-format archive

   t – tar-format archive

   To see a list of all the available options use pg_dump -?

pg_dump -U db_user -W -F t db_name > /path/to/your/file/dump_name.tar

Восстановление

psql -U db_user db_name < dump_name.sql

Вариант 2. Скрипт

Бэкапирование

#!/bin/bash
date=$(date '+%Y%m%d')
PGPASSWORD="12345678"; export PGPASSWORD
pg_dump -h localhost -b -Fc -U 1C BuhIP_2020_M | pigz > /mnt/BKUP/BuhIP_2020_M_20210423.sql.gz

Восстановление

gunzip /mnt/BKUP/BuhIP_2020_M_$date.sql.gz

PGPASSWORD="12345678"; export PGPASSWORD; pg_restore --clean -h localhost -U 1C -d BuhIP_2020_M_copy01 /mnt/BKUP/BuhIP_2020_M_20210423.sql