===== Создание резервных копий ===== ==== Базовая команда ==== Синтаксис: pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп> Пример: pg_dump users > /tmp/users.dump ==== Пользователь и пароль ==== Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию **-U** с указанием пользователя: pg_dump -U dmosk -W users > /tmp/users.dump * где **dmosk** — имя учетной записи; опция **W** потребует ввода пароля. ==== Сжатие данных ==== Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив: pg_dump users | gzip > users.dump.gz ==== Скрипт для автоматического резервного копирования ==== Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД. Для начала, создадим каталог, в котором разместим скрипт, например: mkdir /scripts И сам скрипт:  vi /scripts/postgresql_dump.sh **Вариант 1.** Запуск от пользователя root; одна база. #!/bin/sh PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin PGPASSWORD=password export PGPASSWORD pathB=/backup dbUser=dbuser database=db find $pathB \( -name "*-1[^5].*" -o -name "*-[023]?.*" \) -ctime +61 -delete pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz unset PGPASSWORD * где **password** — пароль для подключения к postgresql; **/backup** — каталог, в котором будут храниться резервные копии; **dbuser** — имя учетной записи для подключения к БУБД; **pathB** — путь до каталога, где будут храниться резервные копии.\\ * данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи. Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике: crontab -e 3 0 * * * /scripts/postgresql_dump.sh * наш скрипт будет запускаться каждый день в 03:00. **Вариант 2.** Запуск от пользователя postgres; все базы. #!/bin/bash PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin pathB=/backup/postgres find $pathB \( -name "*-1[^5].*" -o -name "*-[023]?.*" \) -ctime +61 -delete for dbname in `echo "SELECT datname FROM pg_database;" | psql | tail -n +3 | head -n -2 | egrep -v 'template0|template1|postgres'`; do     pg_dump $dbname | gzip > $pathB/$dbname-$(date "+%Y-%m-%d").sql.gz done; * где **/backup** — каталог, в котором будут храниться резервные копии; **pathB** — путь до каталога, где будут храниться резервные копии.\\ * данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля. Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, **/backup/postgres**. Зададим в качестве владельца файла, пользователя postgres: chown postgres:postgres /scripts/postgresql_dump.sh Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике: crontab -e -u postgres * мы откроем на редактирование cron для пользователя **postgres**. 3 0 * * * /scripts/postgresql_dump.sh * наш скрипт будет запускаться каждый день в 03:00. **Права и запуск** Разрешаем запуск скрипта, как исполняемого файла: chmod +x /scripts/postgresql_dump.sh Единоразово можно запустить задание на выполнение резервной копии: /scripts/postgresql_dump.sh ... или от пользователя postgres: su - postgres -c "/scripts/postgresql_dump.sh" ==== На удаленном сервере ==== Если сервер баз данных находится на другом сервере, просто добавляем опцию -h: pg_dump -h 192.168.0.15 users > /tmp/users.dump * необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию [[:miniinstruktions|Как настроить удаленное подключение к PostgreSQL]]. ==== Дамп определенной таблицы ==== Запускается с опцией **-t ** или **--table=
**: pg_dump -t students users > /tmp/students.dump * где **students** — таблица; **users** — база данных. ==== Размещение каждой таблицы в отдельный файл ==== Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d: pg_dump -d customers > /tmp/folder * где **/tmp/folder** — путь до каталога, в котором разместяться файлы дампа для каждой таблицы. ==== Только схемы ==== Для резервного копирования без данных (только таблицы и их структуры): pg_dump --schema-only users > /tmp/users.schema.dump ==== Только данные ==== pg_dump --data-only -U parser_wb -d parser_wb > /var/dump/dump.sql Только комманда insert pg_dump --data-only --inserts -U parser_wb -d parser_wb > /var/dump/dump.sql ==== Использование pgAdmin ==== Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса. Запускаем pgAdmin - подключаемся к серверу - кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп - выбираем **Резервная копия**: {{https://www.dmosk.ru/img/miniinstruktions/postgresql-dump-01.jpg|Выбираем операцию резервного копирования для базы Postgresql}}В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат: {{https://www.dmosk.ru/img/miniinstruktions/postgresql-dump-02.jpg|Настраиваем путь для резервного копирования}}При желании, можно изучить дополнительные параметры для резервного копирования: {{https://www.dmosk.ru/img/miniinstruktions/postgresql-dump-03.jpg|Дополнительные опции}}После нажимаем **Резервная копия** - ждем окончания процесса и кликаем по **Завершено**. ==== Не текстовые форматы дампа ==== Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные. Бинарный с компрессией: pg_dump -Fc users > users.bak Тарбол: pg_dump -Ft users > users.tar Directory-формат: pg_dump -Fd users > users.dir ==== Использование pg_basebackup ==== pg_basebackup позволяет создать резервную копию для кластера PostgreSQL. pg_basebackup -h node1 -D /backup * в данном примере создается резервная копия для сервера **node1** с сохранением в каталог **/backup**. ==== pg_dumpall ==== Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта. pg_dumpall > cluster.bak Утилиту удобно использовать с ключом -g (--globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств). Для создание резервного копирования со сжатием: pg_dumpall | gzip > cluster.tar.gz ===== Восстановление ===== Может понадобиться создать базу данных. Это можно сделать SQL-запросом: =# CREATE DATABASE users WITH ENCODING='UTF-8'; * где **users** — имя базы; **UTF-8** — используемая кодировка. Если мы получим ошибку: ERROR:  encoding "UTF8" does not match locale "en_US"\\ DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1". Указываем больше параметров при создании базы: CREATE DATABASE users WITH OWNER 'postgres' ENCODING 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE = 'ru_RU.UTF-8' TEMPLATE = template0; ==== Базовая команда ==== Синтаксис: psql <имя базы> < <файл с дампом> Пример: psql parser_wb < /tmp/users.dump ==== С авторизацией ==== При необходимости авторизоваться при подключении к базе вводим: psql -U parser_wb -d parser_wb < /var/dump/dump.sql * где **parser_wb ** — имя учетной записи; опция **W** потребует ввода пароля. cat /var/dump/dump.sql | grep 'INSERT INTO public.brands' > /var/dump/brands.sql cat /var/dump/dump.sql | grep 'INSERT INTO public.card_products' > /var/dump/card_products.sql cat /var/dump/dump.sql | grep 'INSERT INTO public.stocks_jsons' > /var/dump/table.sql cat /var/dump/dump.sql | grep 'INSERT INTO public.shippings' > /var/dump/table.sql cat /var/dump/dump.sql | grep 'INSERT INTO public.suppliers' > /var/dump/table.sql cat /var/dump/dump.sql | grep 'INSERT INTO public.servers' > /var/dump/table.sql cat /var/dump/dump.sql | grep 'INSERT INTO public.server_proxies' > /var/dump/table.sql ==== Из файла gz ==== Сначала распаковываем файл, затем запускаем восстановление: gunzip users.dump.gz psql users < users.dump Или одной командой: zcat users.dump.gz | psql users ==== Определенную базу ==== Если резервная копия делалась для определенной базы, запускаем восстановление: psql users < /tmp/database.dump Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d: pg_restore -d users cluster.bak ==== Определенную таблицу ==== Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление: psql users < /tmp/students.dump Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t: pg_restore -a -t students users.dump ==== С помощью pgAdmin ==== Запускаем pgAdmin - подключаемся к серверу - кликаем правой кнопкой мыши по базе, для которой хотим восстановить данные - выбираем **Восстановить**: {{https://www.dmosk.ru/img/miniinstruktions/postgresql-dump-04.jpg|Выбираем восстановление базы данных}}Выбираем наш файл с дампом: {{https://www.dmosk.ru/img/miniinstruktions/postgresql-dump-05.jpg|Выбираем файл с дампом}}И кликаем по **Восстановить**: {{https://www.dmosk.ru/img/miniinstruktions/postgresql-dump-06.jpg|Восстанавливаем данные}} ==== Использование pg_restore ==== Данная утилита предназначена для восстановления данных не текстового формата (в одном из примеров создания копий мы тоже делали резервную копию не текстового формата). Из бинарника: pg_restore -Fc users.bak Из тарбола: pg_restore -Ft users.tar С создание новой базы: pg_restore -Ft -C users.tar ===== Возможные ошибки ===== ==== Input file appears to be a text format dump. please use psql. ==== Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore. Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор. ==== No matching tables were found ==== Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру. Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов. ==== Too many command-line arguments ==== Причина: Утилита pg_dump чувствительна к лишним пробелам. Решение: проверьте, что нет лишних пробелов. ==== Aborting because of server version mismatch ==== Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли. Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql//bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить. ==== No password supplied ==== Причина: нет системной переменной PGPASSWORD или она пустая. Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD). ==== Неверная команда \ ==== Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления. Решение: запускаем восстановление с опцией **-v ON_ERROR_STOP=1**, например: psql -v ON_ERROR_STOP=1 users < /tmp/users.dump Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.