Postgresql backup database linux

Chapter 23. Backup and Restore

As with everything that contains valuable data, PostgreSQL databases should be backed up regularly. While the procedure is essentially simple, it is important to have a basic understanding of the underlying techniques and assumptions.

There are three fundamentally different approaches to backing up PostgreSQL data:

File system level backup

Each has its own strengths and weaknesses.

23.1. SQL Dump

The idea behind the SQL-dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose. The basic usage of this command is:

As you see, pg_dump writes its results to the standard output. We will see below how this can be useful.

pg_dump is a regular PostgreSQL client application (albeit a particularly clever one). This means that you can do this backup procedure from any remote host that has access to the database. But remember that pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in practice you almost always have to run it as a database superuser.

To specify which database server pg_dump should contact, use the command line options -h host and -p port. The default host is the local host or whatever your PGHOST environment variable specifies. Similarly, the default port is indicated by the PGPORT environment variable or, failing that, by the compiled-in default. (Conveniently, the server will normally have the same compiled-in default.)

As any other PostgreSQL client application, pg_dump will by default connect with the database user name that is equal to the current operating system user name. To override this, either specify the -U option or set the environment variable PGUSER. Remember that pg_dump connections are subject to the normal client authentication mechanisms (which are described in Chapter 20).

Dumps created by pg_dump are internally consistent, that is, updates to the database while pg_dump is running will not be in the dump. pg_dump does not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as VACUUM FULL.)

Important: When your database schema relies on OIDs (for instance as foreign keys) you must instruct pg_dump to dump the OIDs as well. To do this, use the -o command line option.

23.1.1. Restoring the dump

The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is

where infile is what you used as outfile for the pg_dump command. The database dbname will not be created by this command, you must create it yourself from template0 before executing psql (e.g., with createdb -T template0 dbname). psql supports options similar to pg_dump for controlling the database server location and the user name. See psql ‘s reference page for more information.

Not only must the target database already exist before starting to run the restore, but so must all the users who own objects in the dumped database or were granted permissions on the objects. If they do not, then the restore will fail to recreate the objects with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is not.)

Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. An easy way to do this is to run vacuumdb -a -z to VACUUM ANALYZE all databases; this is equivalent to running VACUUM ANALYZE manually.

The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database directly from one server to another; for example:

Important: The dumps produced by pg_dump are relative to template0. This means that any languages, procedures, etc. added to template1 will also be dumped by pg_dump . As a result, when restoring, if you are using a customized template1, you must create the empty database from template0, as in the example above.

For advice on how to load large amounts of data into PostgreSQL efficiently, refer to Section 13.4.

23.1.2. Using pg_dumpall

The above mechanism is cumbersome and inappropriate when backing up an entire database cluster. For this reason the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as users and groups. The basic usage of this command is:

The resulting dump can be restored with psql :

(Actually, you can specify any existing database name to start from, but if you are reloading in an empty cluster then postgres should generally be used.) It is always necessary to have database superuser access when restoring a pg_dumpall dump, as that is required to restore the user and group information.

23.1.3. Handling large databases

Since PostgreSQL allows tables larger than the maximum file size on your system, it can be problematic to dump such a table to a file, since the resulting file will likely be larger than the maximum size allowed by your system. Since pg_dump can write to the standard output, you can just use standard Unix tools to work around this possible problem.

Use compressed dumps. You can use your favorite compression program, for example gzip .

Use split. The split command allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:

Use the custom dump format. If PostgreSQL was built on a system with the zlib compression library installed, the custom dump format will compress data as it writes it to the output file. This will produce dump file sizes similar to using gzip, but it has the added advantage that tables can be restored selectively. The following command dumps a database using the custom dump format:

A custom-format dump is not a script for psql , but instead must be restored with pg_restore . See the pg_dump and pg_restore reference pages for details.

Источник

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

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

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

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

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 для резервирования всех баз, созданных в СУБД.

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

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

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

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Вариант 2. Запуск от пользователя 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

Единоразово можно запустить задание на выполнение резервной копии:

. или от пользователя postgres:

su — postgres -c «/scripts/postgresql_dump.sh»

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к 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 users > /tmp/users.data.dump

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

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

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

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

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

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

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

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

Источник

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

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

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

Пример скрипта дампа всех баз postgresSQL

#/bin/sh

START_TIME=`date +%s`


BACKUP_DIR=»/usr/local/backups»
TIMESTAMP=$(date «+%d-%m-%Y_%H-%M-%S»)
DAYS_TO_STORE=7




echo «Starting SQL backup script»
echo «\t PID: $$»
echo «\t Start time: $TIMESTAMP\n»




echo -n «Starting pg_dumpall. «
/usr/bin/pg_dumpall | gzip > $BACKUP_DIR/pg_dumpall_$TIMESTAMP.sql.gz
echo «ok»


echo -n «Computing MD5 hash. «
/usr/bin/md5sum $BACKUP_DIR/pg_dumpall_$TIMESTAMP.sql.gz >> $BACKUP_DIR/md5sums.txt
echo «ok\n»




echo «Deleting backup older than $DAYS_TO_STORE days. «
for i in $(find /usr/local/backups -type f -mtime +14 -name «*.sql.gz» -print); do
echo -n «\tdeleting file $i . «
rm $i
echo «ok»
done


END_TIME=`date +%s`
echo «\n\nScript ended in `expr $END_TIME — $START_TIME` sec.»




# Docs:
# https://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html

Читайте также:  Как удалить все данные с флешки linux

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

Пример (для дампа одной бд зайдите под su postgres):

pg_dump bd > /tmp/bd.dump

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

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

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

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

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

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

pg_dump users | gzip > users.dump.gz

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

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

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к 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 users > /tmp/users.data.dump

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

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

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

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

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

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

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

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

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

pg_dump -Fc users > users.bak

pg_dump -Ft users > users.tar

pg_dump -Fd users > users.dir

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

pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.

pg_basebackup -h node1 -D /backup

* в данном примере создается резервная копия для сервера node1 с сохранением в каталог /backup.

pg_dumpall

Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.

Утилиту удобно использовать с ключом -g (—globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).

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

Может понадобиться создать базу данных. Это можно сделать SQL-запросом:

=# CREATE DATABASE users WITH ENCODING=’UTF-8′;

* где users — имя базы; UTF-8 — используемая кодировка.

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

psql users или выполнив 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 или она пустая.

Источник