How to backup postgresql database

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

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

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

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

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

pg_dump users > /tmp/users.dump

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

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

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

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

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

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 и создаем задание в планировщике:

* мы откроем на редактирование cron для пользователя postgres.

3 0 * * * /scripts/postgresql_dump.sh

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

Права и запуск

Разрешаем запуск скрипта, как исполняемого файла:

chmod +x /scripts/postgresql_dump.sh

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

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

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

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

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

Запускается с опцией -t или —table= :

* где students — таблица; users — база данных.

Если наша таблица находится в определенной схеме, то она указывается вместе с ней, например:

* где public — схема; students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Для определенной схемы

* в данном примере мы заархивируем схему public базы данных peoples.

Только схемы (структуры)

Для резервного копирования без данных (только таблицы и их структуры):

* в данном примере мы создадим дамп структуры базы данных users только для схемы production.

Или полный дамп с данными для схемы внутри базы данных:

Только данные

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

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

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

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

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

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

10 способов сделать резервную копию в PostgreSQL

Если рассматривать резервное копирование как вполне конкретный процесс, то возникает два простых вопроса:
1. откуда запускать резервное копирование?
2. какие инструменты следует использовать для резервного копирования?

На первый вопрос есть два варианта ответа: можно запускать задачу резервного копирования с выделенного backup сервера, на мой взгляд это наиболее подходящий вариант. Либо запускать задачу непосредственно с сервера БД, это в случае если нет выделенного сервера бэкапов.

С инструментами все гораздо интереснее. Здесь я выделяю две группы, основные инструменты и вспомогательные. Основные это те, которые собственно и выполняют резервное копирование. Вспомогательные это те которые добавляют что-то особенное к процессу резервного копирования, например архивирование, шифрование, управление нагрузкой и т.д.

В комплекте PostgreSQL есть 2 утилиты которые позволяют делать резервные копии, это pg_dump/pg_dumpall и pg_basebackup. Кроме того есть возможность использовать утилиты файлового копирования, такие как rsync, tar, cp и т.п.
Итак, каким инструментом запускать бэкап?
pg_dump — подходит для случаев когда нужно сделать резервную копию таблицы, базы, схемы или данных.
pg_basebackup — подходит для случаев когда нужно сделать резервную копию целиком всего кластера БД или настроить hot standby реплику.
rsync/tar/cp — также используются для случаев копирования всего кластера.

Здесь я буду рассматривать pg_basebackup, хотя и pg_dump тоже может использоваться в нижеперечисленных способах.

1. Простое и без изысков резервное копирование с backup сервера в каталог /backup (каталог должен быть предварительно создан):

2. Копирование с пониженным приоритетом IO операций с помощью ionice, для случаев когда нужно уменьшить нагрузку на дисковый ввод-вывод от резервного копирования:

3. Копирование с сжатием в bzip2, для случаев когда нужно использовать нестандартный для pg_basebackup алгоритм сжатия (gzip). Здесь мы передаем данные через стандартный вывод (stdout) на стандартный ввод (stdin) программе bzip2.

4. Копирование с сжатием в несколько потоков (используем lbzip2 и задействуем 6 ядер). При таком раскладе можно задействовать простаивающие ядра и ускорить процесс сжатия.

5. Здесь копирование запускается на сервере БД. Формируемая резервная копия отправляется на удаленный сервер по ssh.

6. Здесь копирование также запускается на сервере БД и выполняется отправка на удаленный сервер, но уже с архивированием в 6 потоков с помощью lbzip2.

7. Копирование на удаленный сервер с ограничением пропускной полосы до 10Мб с помощью pv и последующее архивирование на удаленной стороне. Этот вариант для случаев когда нужно передать не нагружая сеть.

9. Копирование с задействование lbzip2 на обоих узлах, для случаев когда у сети маленькая пропускная способность, сначала поток сжимается, затем передается по сети и затем расжимается на удаленной стороне. Здесь используется tar и требуется выполнение pg_start_backup(‘label_name’) на стороне postgres.

Для расшифровки резервной копии следует выполнить такую команду

Search Results

No Results

Filters

Backing Up a PostgreSQL Database (Database Dump)

If you are using PostgreSQL in a production environment, it is important to take precautions to ensure that your users’ data is not lost. By frequently backing up your database, automating backups with a cron task, you can restore your system when your database is lost or corrupted. Fortunately, PostgreSQL includes tools to make this task simple and easy to manage. Learn how to backup your PostgreSQL database in Linux, in this guide.

Before You Begin

You should have a working installation of PostgreSQL on your system before beginning this guide. Go through our How to Install PostgreSQL on Ubuntu guide to install PostgreSQL and create a sample database.

One-Time SQL Dump

Single Database

PostgreSQL provides the pg_dump utility to simplify backing up a single database. This command must be run as a user with read permissions to the database you intend to back up.

Log in as the postgres user:

Dump the contents of a database to a file by running the following command. Replace dbname with the name of the database to be backed up.

To demonstrate how to load a database in PostgreSQL and restore lost data, first delete your example database and then create an empty database:

Restore the database using psql :

There are several options for the backup format:

Remote Database

All Databases

Create a backup file:

Restore all databases from the backup:

Automate Backups with a Cron Task

In this section, learn how to import a database in PostgreSQL and automate the process. To do this you need to set up a cron job so that your database backs up automatically at regular intervals. The steps in this section sets up a cron task that runs pg_dump once every week.

Make sure you are logged in as the postgres user:

Create a directory in the postgres user’s home to store the automatic backups:

Edit the crontab to create the new cron task:

Add the following line to the end of the crontab:

Save and exit from the editor. Your database is set to back up at midnight every Sunday. To change the time or frequency of the updates, see our Schedule Tasks with Cron guide.

It is always a good idea to export a Postgres database before any major changes in structure or the installation of a new application. This applies to a Postgres import dump from a remote server.

Check PostgreSQL Backup Status

You can set up error logging to check on the status of your PostgreSQL automated backups. If you aren’t creating a log file for your PostgreSQL, create one by adding the following at the end of your cron job.

Make sure you are logged in as the postgres user:

Create a directory in the postgres user’s home to store your error log files:

Edit the crontab to modify the cron task:

Modify the cron task line to output and append any backup errors to a log file:

If your system is capable of sending emails, you can also configure your cron task to send an email alert whenever there is a PostgreSQL backup error. For example, if mailx is installed and enabled for the postgres user, the following cron task sends the last line of your log file whenever an error occurs:

Now, if there are any issues with the backup, you should receive an email containing the error.

Next Steps

PostgreSQL also offers more advanced ways to back up your databases. The official docs describe how to set up continuous archiving and point-in-time recovery. This is a much more complex process, but it can maintain a constant archive of your database. You can replay PostgreSQL’s logs to recover the state of the database at any point in the past.

This method can also be helpful if you have a very large database although continuously archiving a large database consumes resources. Since the process is ongoing, there is no need to make frequent and time consuming full backups.

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

This page was originally published on Monday, December 18, 2017.

How To Backup PostgreSQL Databases on an Ubuntu VPS

What is PostgreSQL?

PostgreSQL is a modern database management system. It is frequently used to store and manipulate information related to websites and applications.

As with any kind of valuable data, it is important to implement a backup scheme to protect against data loss. This guide will cover some practical ways that you can backup your PostgreSQL data.

We will be using an Ubuntu 12.04 VPS with PostgreSQL 9.1. Most modern distributions and recent versions of PostgreSQL will operate in a similar way.

How to Back Up a PostgreSQL Database Using pg_dump

PostgreSQL includes a utility called «pg_dump» that can be used to dump database information into a file for backup purposes.

The pg_dump utility is run from the Linux command line. The basic syntax of the command is:

The command must be run by a user with privileges to read all of the database information, so it is run as the superuser most of the time.

For a real-world example, we can log into the «postgres» user and execute the command on the default database, also called «postgres»:

This command is actually a PostgreSQL client program, so it can be run from a remote system as long as that system has access to the database.

If you wish to backup a remote system, you can pass the «-h» flag for specifying the remote host, and the «-p» flag to give the remote port:

You can also specify a different user using the «-U» option if necessary. The syntax would be:

Keep in mind that the same authentication requirements exist for pg_dump as for any other client program. This means that you must ensure that your log in credentials are valid for the systems you are trying to back up.

How to Restore Data Dumps from pg_dump with PostgreSQL

To restore a backup created by pg_dump, you can redirect the file into psql standard input:

Note: this redirection operation does not create the database in question. This must be done in a separate step prior to running the command.

For example, we can create a new database called «restored_database» and then redirect a dump called «database.bak» by issuing these commands:

The empty database should be created using «template0» as the base.

Another step that must be performed in order to restore correctly is to recreate any users who own or have grant permissions on objects within the database.

For instance, if your database had a table owned by the user «test_user», you will have to create it on the restoration system prior to importing:

Dealing with Restoration Errors

By default, PostgreSQL will attempt to continue restoring a database, even when it encounters an error along the way.

In many cases, this is undesirable for obvious reasons. It can be painful to try to sort out what operations are needed to restore the database to its proper state.

We can tell PostgreSQL to stop on any error by typing:

This will cause a PostgreSQL restore operation to halt immediately when an error is encountered.

This will still leave you with a crippled database that hasn’t been fully restored, but you can now handle errors as they come up instead of dealing with a list of errors at the end.

A better option in many situations can be the «-1» (the number one) or «—single-transaction» option:

This option performs all of the restoration details in a single transaction.

The difference between this option and the «ON_ERROR_STOP» setting is that this will either succeed completely or not import anything.

This can be a costly trade-off for larger restorations, but in many cases, the benefit of not leaving you with a partially restored database heavily outweighs that cost.

How to Backup & Restore All Databases in PostgreSQL

To save time, if you would like to backup all of the databases in your system, there is a utility called «pg_dumpall«.

They syntax of the command is very similar to the regular pg_dump command, but it does not specify the database. Instead, the command backs up every available database:

You can restore the databases by passing the file to psql, with the default database:

Conclusion

Backups are an essential component in any kind of data storage plan. Fortunately, PostgreSQL gives you the utilities necessary to effectively backup your important information.

As with any kind of backup, it is important to test your backups regularly to ensure the copies that are created can be restored correctly. The backups you create are only useful if they can actually be used to recover your system.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Настройка continuous бекапов PostgreSQL

В данном мануале описывается процесс настройки постоянного (continuous) бекапирования для баз данных PostgreSQL.

В нашей фирме (business to business) под каждого клиента создается свой собственный сервер, на который устанавливается база данных PostgreSQL и наш софт. Таким образом, у нас не единый instance продакшена, а десятки с разными экземплярами базы. Процесс настройки бекапов является частью процесса установки продакшена, а само бекапирование начинается до выхода системы в продакшен и продолжается в течение всего жизненного цикла сотрудничества с клиентом. Спецификацию железа и базового software определяем мы, поэтому все инстансы, как правило, имеют одни и те же версии Linux и PostgreSQL. Изредка этот инвариант нарушается — например, где-то по тем или иным причинам может стоять не Ubuntu, а Debian либо PostgreSQL более старой мажорной версии, чем у остальных. В последнем случае нужно быть особенно аккуратным — при возникновении сбоя следует иметь ввиду, что восстановление базы должно осуществляться на ту же мажорную версию PostgreSQL, на которой был сделан бекап, так как описываемый подход требует бинарной совместимости файлов данных, которая гарантируется только при переходе между минорными версиями PostgreSQL. Как поступить, если этот инвариант нарушен, также описано в конце данной статьи.

Подразумевается, что у читающего может быть довольно мало знаний как в области бекапов PostgreSQL, так и в сфере работы с Linux (а в особенности с инструментами ее командной строки), поэтому tutorial написан как можно более подробно.

Базовая концепция Continuous Archiving and Point-in-Time Recovery

В этом разделе укажем основные идеи, лежащие в основе подхода Continuous Archiving and Point-in-Time Recovery. При необходимости подробные детали можно найти в документации PostgreSQL.

Пожалуй, как и все СУБД, PostgreSQL имеет файлы данных, в которых хранит текущее состояние базы данных. Однако, кроме этого PostgreSQL ведет и сохраняет логи изменений в базе данных. Эти логи представлены в виде так называемых write ahead logs (WAL) файлов, которые сохраняются в подпапке pg_wal директории с данными:

WAL-файлы используются PostgreSQL для защиты от сбоев. Упрощенно говоря, при коммите транзакции PostgreSQL убеждается, что именно изменения в соответствующем WAL-файле гарантированно сохранились на диск, но, вообще говоря, может не делать такую же проверку по отношению к файлам с данными, например, кешируя их изменения до определенного момента в оперативной памяти. Если после последнего гарантированного сохранения файлов данных на диск (checkpoint) произошел сбой и текущее состояние еще не было сохранено, то после восстановления работы PostgreSQL возьмет последний checkpoint файлов данных (назовем его base backup) и последовательно применит к нему изменения, сохраненные в WAL-файлах (replay log entries).

Вышеописанную модель также можно использовать и для бекапирования с последующим восстановлением (например) на другом сервере. Для этого, очевидно, требуется реализовать следующую схему:

Модель восстановления после сбоя, таким образом, тоже становится очевидной:

Стоит иметь ввиду следующие особенности рассматриваемой модели бекапов:

В следующих разделах описываются технические детали описанной схемы.

Для этой цели будут использоваться следующие наименования: целевой сервер — текущий рабочий сервер, с которого делаем бекапы, сервер бекапов — сервер на который отправляются бекапы, новый сервер — сервер, на котором восстанавливается бекап целевого сервера после сбоя.

Оффтоп: несколько слов по безопасности

Ввиду особой ценности бекапов для бизнеса как с точки зрения восстановления после сбоев, так и с точки зрения возможных утечек информации нелишним будет пройтись по базовой безопасности Linux серверов. В целом, это не относится напрямую к рассматриваемой теме, поэтому, можно просто пробежаться глазами по этому разделу и, если у вас все устроено так же, спокойно пойти дальше. В случае если ваши решения по безопасности лучше описанных — просьба отписаться в комментариях и рассказать о своем опыте. Если же вы считаете, что с безопасностью у вас хуже, то описанные ниже решения стоит, вероятно, как можно скорее применить и к вашим продакшенам. Рассматриваться будет Ubuntu 18.04, на других версиях Linux инструкция может отличаться.

Настройка сервера бекапов

Создание папок для бекапа

Поскольку у нас множество инсталляций, с которых будут собираться бекапы, структура папок будет следующей. Папка для хранения бекапов — /var/lib/postgresql/backups. Под каждую инсталляцию в папке для хранения бекапов создается подпапка по имени клиента [clientName]. В каждой такой подпапке будут лежать 2 папки: base для базового бекапа и wal для continious archiving WAL-файлов с целевого сервера. Таким образом, при настройке бекапирования для клиента [clientName] выполняем следующие команды по созданию соответствующих директорий:

Разумеется, структура папок может быть и любой другой, которую вы находите более удобой для вас. Например, если хранить бекапы в home директориях соответствующих пользователей (см. ниже), то в следующем пункте можно сэкономить пару команд по раздаче прав на чтение и запись в эти папки.

Создание пользователя

В целях безопасности каждый продакшен (целевой сервер), с которого идут бекапы, будет иметь своего собственного пользователя на сервере бекапов. Это — обычный пользователь с ограниченными правами (не sudoer), который должен иметь права на чтение и запись в свою папку для бекапа, но не иметь возможность ни читать, ни писать в папки чужих продакшенов. Таким образом, даже если данный целевой сервер будет скомпрометирован, это не приведет к утечке данных других продакшенов через сервер бекапов.
Подробнее процесс выглядит следующим образом. Допустим, мы настраиваем бекап для проекта с именем foo. Для настройки сервера бекапов изначально заходим на него от пользователя из sudo. Добавляем пользователя с ограниченными правами foobackup:

Выдача прав пользователю на папки бекапа

Разрешаем foobackup читать и писать в свою папку, но запрещаем это делать всем остальным:

Настройка публичных ключей

Для завершения настройки сервера бекапов нашему новому пользователю foobackup необходимо дать удаленный доступ, чтобы он имел возможность отсылать бекапы на сервер. Как и ранее, доступ будет осуществляться по ключам доступа и только по ним.

На целевом сервере бекапы делаются от имени пользователя postgres, соответственно генерируем (если этого не было сделано ранее) приватный и публичные ключи для него:

Копируем содержимое публичного ключа из указанного при генерации системой места (Your public key has been saved in. ), в нашем случае это /var/lib/postgresql/.ssh/id_rsa.pub:

Сохраняем публичный ключ на сервер бекапов для пользователя foobackup:

Проверка работоспособности копирования

Теперь проверим, что мы все сделали правильно. В нашей схеме за копирование бекапов с целевого сервера на сервер бекапов будет отвечать scp — утилита для копирования файлов на удаленный сервер с синтаксисом, аналогичным локальному аналогу — cp. В принципе, можно использовать и другие средства доставки файлов, например, rsync и т.п.

После копирования заходим на сервер бекапов и убеждаемся, что файл действительно скопировался, например, так:

По окончании проверок файл test.txt следует удалить из соответствующих папок сервера бекапов.

Настройка целевого сервера

Конфигурация PostgreSQL

Архивирование без сжатия

В файле postgresql.conf (его расположение можно получить выполнив из psql команду «SHOW config_file;») сделать следующие изменения (с подстановкой [backupServerIp] = IP адрес сервера бекапов, [clientName] = foo):

Каждый WAL-файл занимает 16Mb и его архивирование (в нашем случае «архивирование» — это отправка на сервер бекапов) происходит только после того, как он заполнен. Таким образом, если данный клиент генерирует мало трафика БД, то бекап текущего WAL-файла может не происходить недетерменированно долго. Чтобы иметь возможность при сбое восстановить версию базы, например, не более часовой давности, необходимо в archive_timeout задать время форсированного архивирования (промежуток, через который даже неполный WAL-файл архивируется) в 1 час — в секундах это 3600. Не следует устанавливать слишком малые значения, потому что даже неполные WAL-файлы занимают 16Mb — таким образом, в заданный промежуток времени не менее 16Мб данных будет уходить на сервер бекапов. Например, при archive_timeout равном одному часу, в сутки на сервер бекапов будет уходить не менее 384Мб данных, в неделю это больше 2Gb.

Архивирование со сжатием

Учитывая возможные проблемы с разрастанием размера бекапов можно сразу делать сжатие и на сервер бекапов отправлять уже сжатые WAL-файлы (на своих продакшенах мы делаем именно так). В таком случае команда archive_command будет выглядеть так:

После сделанных в postgresql.conf изменений необходимо перезапустить PostgreSQL:

Создание базового бекапа

Создание базового бекапа делается утилитой pg_basebackup, которая была установлена на целевом сервере вместе с PostgreSQL. Предполагается, что в PostgreSQL целевого сервера создан некий trusted пользователь с привилегиями, достаточными для осуществления бекапа всех баз данных на текущей инсталляции PostgreSQL (к примеру, это может быть администраторский аккаунт, используемый для обслуживания баз данных, или отдельный пользователь, специально созданный для создания базовых бекапов). Имя этого пользователя должно быть использовано в подстановке [trusted db user]. В процессе выполнения команды будет запрошен пароль этого пользователя. После создания бекапа сразу отправляем его на сервер бекапов.

Ключи —progress и —verbose не являются обязательными и используются для наглядности наблюдения процесса создания бекапа — при наличии этих ключей PostgreSQL выдает некоторую дополнительную информацию в консоль в удобочитаемом виде.

Восстановление бекапа

Проверка работоспособности бекапирования

Перед тем, как выводить нашу инсталляцию foo в продакшен, необходимо убедиться в работоспособности настроенной системы бекапов. Проверка, очевидно, должна состоять из двух частей:

П.1 проверяется так: на сервере бекапов заходим в папку /var/lib/postgresql/backups/foo/base и убеждаемся, что она содержит файлы base.tar.gz и pg_wal.tar.gz:

Чтобы быстро — без долгого времени наблюдения за системой — проверить п.2, вернемся в пункт настройки PostgreSQL и поменяем таймаут архивирования (archive_timeout) на 60 секунд, затем рестартуем PostgreSQL. Теперь при наличии изменений в базе WAL-файлы будут архивироваться не реже, чем раз в минуту. Далее в течение некоторого времени (3-5 минут) будем любым (безопасным) образом генерировать изменения в базе — например, мы делаем это просто через наш фронт, вручную создавая активность тестовыми пользователями.
Параллельно нужно наблюдать за папкой /var/lib/postgresql/backups/foo/wal сервера бекапов, где примерно раз в минуту будет появляться новый файл:

После проверки очень важно вернуть archive_timeout в продакшен значение (у нас в зависимости от клиента это минимум 1 час, т.е. 3600, максимум — сутки, т.е. 86400).

Если видно, что файлы не отправляются на сервер бекапов, то исследование проблемы можно начать с анализа логов PostgreSQL, лежащих здесь /var/log/postgresql. Например, если пара приватный-публичный ключ была настроена неверно, то можно увидеть подобную запись в файле postgresql-10-main.log (название лог-файла зависит от устанавливаемой версии):

Восстановление из бекапа

Пусть у нас заранее подготовлен новый сервер, где установлен PostgreSQL той же мажорной версии, что и на целевом сервере. Также предположим, что с сервера бекапов мы предварительно скопировали папку бекапа /var/lib/postgresql/backups/foo на новый сервер по тому же пути.
Далее по шагам описана процедура развертывания этого бекапа на новом сервере.

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

В зависимости от версии PostgreSQL выдастся что-то подобное: /var/lib/postgresql/10/main

Удаляем все содержимое папки с данными:

Останавливаем инстанс PostgreSQL на ЦЕЛЕВОМ сервере:

Если после сбоя доступ к целевом серверу сохранился, то останавливаем там инстанс PostgreSQL, чтобы потерять как можно меньшее количество новых данных, которые не войдут в бекап.

Распаковываем файлы из бекапа в папку данных PostgreSQL:

Здесь и далее снова работаем с новым сервером.

Ожидается, что команда tar, запущенная от sudo, сохранит group и ownership распакованных файлов за пользователем postgres — это важно, поскольку далее их будет использовать PostgreSQL, работающий именно от этого пользователя.

В папке с данными создаем конфиг восстановления:

Если сжатия на шаге архивирования WAL-файлов не было:

Если сжатие на шаге архивирования WAL-файлов было, то restore_command должен выглядеть следующим образом (все остальное не меняется):

Обнаружив в папке с данными конфиг восстановления, PostgreSQL входит в режим восстановления и начинает применять (replay) WAL-файлы из архива. После окончания восстановления recovery.conf будет переименован в recovery.done (поэтому очень важно на предыдущем шаге дать пользователю postgres права на изменение файла). После этого шага сервер PostgreSQL готов к работе. Если по внешним признакам видно, что база не восстановилась либо восстановилась только до уровня базового бекапа, то исследование проблемы можно начать с анализа логов PostgreSQL, лежащих здесь /var/log/postgresql. Например, если на предыдущем шаге не были даны права пользователю postgres на папку с бекапом WAL-файлов, то можно увидеть подобную запись в файле postgresql-10-main.log (название лог-файла зависит от устанавливаемой версии):

Перенос PostgreSQL базы данных между разными мажорными версиями

В этом параграфе, как и было обещано в начале статьи, рассмотрим случай, когда требуется перенести базу с одной мажорной версии на другую. Поскольку основным сценарием бекапирования для нас является подход, описанный выше, то здесь мы будем предполагать, что целевой сервер доступен и работоспособен — то есть, перенос базы нужно осуществить не из-за сбоя, а по другим (организационным) причинам.

Будем использовать утилиту pg_dump или pg_dumpall. Обе они генерируют набор SQL-команд. Первая используется для создания бекапа конкретной базы данных, а вторая — при бекапе всего кластера. В последнем случае, кроме баз данных кластера копируются еще и его глобальные объекты — например, роли, что позволяет затем после развертывания бекапа не делать дополнительных действий в виде создания недостающих ролей, раздачи привилегий и т.п.

Создание дампа

На целевом сервере выполнить (желательно, чтобы на этот момент потребители его баз данных были остановлены и/или не проявляли активность по отношению к БД):

Накатывание дампа

Будем считать, что на новый сервер по тому же пути (/tmp/backups/foo.dump.gz) скопирован сделанный на предыдущем шаге дамп.

Находясь на новом сервере:

После окончания восстановления бекапа нужно просмотреть лог восстановления. По умолчанию psql работает с выключенным флагом ON_ERROR_STOP, поэтому при возникновении ошибок скрипт не прерывается, а продолжает работу. Ниже перечислены те ошибки, которые не являются проблемными, при появлении же в логе других ошибок следует считать восстановление базы не успешным.

Включение сервисов

После окончания накатывания дампа нужно:

После этого перенос можно считать завершенным.