Различия

Здесь показаны различия между двумя версиями данной страницы.

--- postgres:dump [2021/11/13 18:51]
werwolf [Неверная команда \]
+++ postgres:dump [2023/01/12 12:18] (текущий)
@@ Строка 1: / Строка 1: @@
+===== Создание резервных копий =====
+==== Базовая команда ====
+Синтаксис:
+<code>
+pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>
+</code>
+Пример:
+<code bash>
+pg_dump users > /tmp/users.dump
+</code>
+==== Пользователь и пароль ====
+Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию **-U** с указанием пользователя:
+<code bash>
+pg_dump -U dmosk -W users > /tmp/users.dump
+</code>
+* где **dmosk** — имя учетной записи; опция **W** потребует ввода пароля.
+==== Сжатие данных ====
+Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:
+<code bash>
+pg_dump users | gzip > users.dump.gz
+</code>
+==== Скрипт для автоматического резервного копирования ====
+Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.
+Для начала, создадим каталог, в котором разместим скрипт, например:
+<code bash>
+mkdir /scripts
+</code>
+И сам скрипт:
+<code bash>
+vi /scripts/postgresql_dump.sh
+</code>
+**Вариант 1.** Запуск от пользователя root; одна база.
+<code bash>
+#!/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
+</code>
+* где **password** — пароль для подключения к postgresql; **/backup** — каталог, в котором будут храниться резервные копии; **dbuser** — имя учетной записи для подключения к БУБД; **pathB** — путь до каталога, где будут храниться резервные копии.\\ * данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.
+Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:
+<code bash>
+crontab -e
+</code>
+<code>
+0 * * * /scripts/postgresql_dump.sh
+</code>
+* наш скрипт будет запускаться каждый день в 03:00.
+**Вариант 2.** Запуск от пользователя postgres; все базы.
+<code bash>
+#!/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;
+</code>
+* где **/backup** — каталог, в котором будут храниться резервные копии; **pathB** — путь до каталога, где будут храниться резервные копии.\\ * данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.
+Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, **/backup/postgres**.
+Зададим в качестве владельца файла, пользователя postgres:
+chown postgres:postgres /scripts/postgresql_dump.sh
+Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:
+<code bash>
+crontab -e -u postgres
+</code>
+* мы откроем на редактирование cron для пользователя **postgres**.
+0 * * * /scripts/postgresql_dump.sh
+* наш скрипт будет запускаться каждый день в 03:00.
+**Права и запуск**
+Разрешаем запуск скрипта, как исполняемого файла:
+<code bash>
+chmod +x /scripts/postgresql_dump.sh
+</code>
+Единоразово можно запустить задание на выполнение резервной копии:
+<code bash>
+/scripts/postgresql_dump.sh
+</code>
+... или от пользователя postgres:
+<code bash>
+su - postgres -c "/scripts/postgresql_dump.sh"
+</code>
+==== На удаленном сервере ====
+Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:
+pg_dump -h 192.168.0.15 users > /tmp/users.dump
+* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию [[:miniinstruktions|Как настроить удаленное подключение к PostgreSQL]].
+==== Дамп определенной таблицы ====
+Запускается с опцией **-t <table>** или **--table=<table>**:
+<code bash>
+pg_dump -t students users > /tmp/students.dump
+</code>
+* где **students** — таблица; **users** — база данных.
+==== Размещение каждой таблицы в отдельный файл ====
+Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:
+<code bash>
+pg_dump -d customers > /tmp/folder
+</code>
+* где **/tmp/folder** — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.
+==== Только схемы ====
+Для резервного копирования без данных (только таблицы и их структуры):
+<code bash>
+pg_dump --schema-only users > /tmp/users.schema.dump
+</code>
+==== Только данные ====
+<code bash>
+pg_dump --data-only -U parser_wb -d parser_wb > /var/dump/dump.sql
+</code>
+Только комманда insert
+<code bash>
+pg_dump --data-only --inserts -U parser_wb -d parser_wb > /var/dump/dump.sql
+</code>
+==== Использование 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|Дополнительные опции}}После нажимаем **Резервная копия** - ждем окончания процесса и кликаем по **Завершено**.
+==== Не текстовые форматы дампа ====
+Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.
+Бинарный с компрессией:
+<code bash>
+pg_dump -Fc users > users.bak
+</code>
+Тарбол:
+<code bash>
+pg_dump -Ft users > users.tar
+</code>
+Directory-формат:
+<code bash>
+pg_dump -Fd users > users.dir
+</code>
+==== Использование pg_basebackup ====
+pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.
+<code bash>
+pg_basebackup -h node1 -D /backup
+</code>
+* в данном примере создается резервная копия для сервера **node1** с сохранением в каталог **/backup**.
+==== pg_dumpall ====
+Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.
+<code bash>
+pg_dumpall > cluster.bak
+</code>
+Утилиту удобно использовать с ключом -g (--globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).
+Для создание резервного копирования со сжатием:
+<code bash>
+pg_dumpall | gzip > cluster.tar.gz
+</code>
+===== Восстановление =====
+Может понадобиться создать базу данных. Это можно сделать SQL-запросом:
+<code bash>
+=# CREATE DATABASE users WITH ENCODING='UTF-8';
+</code>
+* где **users** — имя базы; **UTF-8** — используемая кодировка.
+Если мы получим ошибку:
+<code bash>
+ERROR:  encoding "UTF8" does not match locale "en_US"\\ DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".
+</code>
+Указываем больше параметров при создании базы:
+<code bash>
+CREATE DATABASE users WITH OWNER 'postgres' ENCODING 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE = 'ru_RU.UTF-8' TEMPLATE = template0;
+</code>
+==== Базовая команда ====
+Синтаксис:
+<code bash>
+psql <имя базы> < <файл с дампом>
+</code>
+Пример:
+<code bash>
+psql parser_wb < /tmp/users.dump
+</code>
+==== С авторизацией ====
+При необходимости авторизоваться при подключении к базе вводим:
+<code bash>
+psql -U parser_wb  -d parser_wb < /var/dump/dump.sql
+</code>
+* где **parser_wb  ** — имя учетной записи; опция **W** потребует ввода пароля.
+<code bash>
+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
+</code>
+==== Из файла gz ====
+Сначала распаковываем файл, затем запускаем восстановление:
+<code bash>
+gunzip users.dump.gz
+psql users < users.dump
+</code>
+Или одной командой:
+<code bash>
+zcat users.dump.gz | psql users
+</code>
+==== Определенную базу ====
+Если резервная копия делалась для определенной базы, запускаем восстановление:
+<code bash>
+psql users < /tmp/database.dump
+</code>
+Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:
+<code bash>
+pg_restore -d users cluster.bak
+</code>
+==== Определенную таблицу ====
+Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление:
+<code bash>
+psql users < /tmp/students.dump
+</code>
+Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:
+<code bash>
+pg_restore -a -t students users.dump
+</code>
+==== С помощью 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 ====
+Данная утилита предназначена для восстановления данных не текстового формата (в одном из примеров создания копий мы тоже делали резервную копию не текстового формата).
+Из бинарника:
+<code bash>
+pg_restore -Fc users.bak
+</code>
+Из тарбола:
+<code bash>
+pg_restore -Ft users.tar
+</code>
+С создание новой базы:
+<code bash>
+pg_restore -Ft -C users.tar
+</code>
+===== Возможные ошибки =====
+==== 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/<version>/bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.
+==== No password supplied ====
+Причина: нет системной переменной PGPASSWORD или она пустая.
+Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).
+==== Неверная команда \ ====
+Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления.
+Решение: запускаем восстановление с опцией **-v ON_ERROR_STOP=1**, например:
+<code bash>
+psql -v ON_ERROR_STOP=1 users < /tmp/users.dump
+</code>
+Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.

Инструменты пользователя

Инструменты сайта

Различия

Инструменты страницы