centos 6, centos7, error/ошибки, mysql

Резервное копирование PostgreSQL

Thank you for reading this post, don't forget to subscribe!

Создание резервных копий

Базовая команда

Син­так­сис:

pg_dump <пара­мет­ры> <имя базы> > <файл, куда сохра­нить дамп>

При­мер:

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резерв­ная копия выпол­ня­ет­ся не от учет­ной запи­си postgres, необ­хо­ди­мо доба­вить опцию -U с ука­за­ни­ем пользователя:

pg_dump -U test -W users > /tmp/users.dump

* где test — имя учет­ной запи­си; опция W потре­бу­ет вво­да пароля.

Сжатие данных

Для эко­но­мии дис­ко­во­го про­стран­ства или более быст­рой пере­да­чи по сети мож­но сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

Рас­смот­рим 2 вари­ан­та напи­са­ния скрип­та для резер­ви­ро­ва­ния баз PostgreSQL. Пер­вый вари­ант — запуск скрип­та от поль­зо­ва­те­ля root для резер­ви­ро­ва­ния одной базы. Вто­рой — запуск от поль­зо­ва­те­ля postgres для резер­ви­ро­ва­ния всех баз, создан­ных в СУБД.

Для нача­ла, созда­дим ката­лог, в кото­ром раз­ме­стим скрипт, например:

mkdir /scripts

И сам скрипт:

vi /scripts/postgresql_dump.sh

Вари­ант 1. Запуск от поль­зо­ва­те­ля root; одна база.

* где 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; все базы.

* где /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

* необ­хо­ди­мо убе­дить­ся, что сама СУБД раз­ре­ша­ет уда­лен­ное подключение

Дамп определенной таблицы

Запус­ка­ет­ся с опци­ей -t <table> или --table=<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 users > /tmp/users.data.dump

Использование pgAdmin

Дан­ный метод хоро­шо подой­дет для ком­пью­те­ров с Windows и для быст­ро­го созда­ния резерв­ных копий из гра­фи­че­ско­го интерфейса.

Запус­ка­ем pgAdmin - под­клю­ча­ем­ся к сер­ве­ру - кли­ка­ем пра­вой кноп­кой мыши по базе, для кото­рой хотим сде­лать дамп - выби­ра­ем Резерв­ная копия:

В открыв­шем­ся окне выби­ра­ем путь для сохра­не­ния дан­ных и настра­и­ва­е­мый формат:

При жела­нии, мож­но изу­чить допол­ни­тель­ные пара­мет­ры для резерв­но­го копирования:

После нажи­ма­ем Резерв­ная копия - ждем окон­ча­ния про­цес­са и кли­ка­ем по Завер­ше­но.

Не текстовые форматы дампа

Дру­гие фор­ма­ты поз­во­ля­ют делать частич­ное вос­ста­нов­ле­ние, рабо­тать в несколь­ко пото­ков и сжи­мать данные.

Бинар­ный с компрессией:

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 — исполь­зу­е­мая кодировка.

Базовая команда

Син­так­сис:

psql <имя базы> < <файл с дампом>

При­мер:

psql users < /tmp/users.dump

С авторизацией

При необ­хо­ди­мо­сти авто­ри­зо­вать­ся при под­клю­че­нии к базе вводим:

psql -U test -W users < /tmp/users.dump

* где test — имя учет­ной запи­си; опция W потре­бу­ет вво­да пароля.

Из файла 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 - под­клю­ча­ем­ся к сер­ве­ру - кли­ка­ем пра­вой кноп­кой мыши по базе, для кото­рой хотим вос­ста­но­вить дан­ные - выби­ра­ем Вос­ста­но­вить:

Выби­ра­ем наш файл с дампом: 

И кли­ка­ем по Вос­ста­но­вить:

Использование 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/<version>/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

Теперь, когда воз­ник­нет ошиб­ка, систе­ма пре­кра­тит выпол­нять опе­ра­цию и выве­дет сооб­ще­ние на экран.