Postgresql backup windows программа — Ваш верный помощник с OS Windows

Уровень сложностиСредний

Время на прочтение7 мин

Количество просмотров864

Базы данных — замечательный инструмент, без которого сложно представить современное приложение. И как бы сильно я ни любил использовать БД, я просто ненавижу писать SQL-запросы. Поэтому однажды задался вопросом, кто мог бы делать это за меня, при этом несильно теряя в качестве. И, конечно же, на ум пришёл мой AI-друг. Тогда остаётся одна проблема, как скормить ему мою БД. Тут на помощь приходит резервное копирование! Выполнив все необходимые задачи, я решил углубиться в тему и поделиться с Хабром, какие вообще есть виды и, конечно, сравнить их между собой.

▍ Инструменты для работы с резервным копированием PostgreSQL

Для начала рассмотрим несколько CLI-инструментов, которые позволят нам работать с резервными копиями

▍ pg_dump

pg_dump <название БД>

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

Подключение к БД: авторизация такая же, как и у обычного клиента, используется библиотека С — libpq.
Чтение схемы: посредством запросов SELECT * FROM ...
Создание структуры: строится дерево объектов, которое затем преобразуется в соответствующий формат.
Сохранение данных: отдельно сохраняются данные из БД.
Запись в конечный файл: в зависимости от типа резервной копии сохраняется либо в бинарник, либо в текстовый файл всю копию, включающую структуру и данные.

Ключи:

-U имя пользователя
-h хост
-p порт
-F формат дампа: p (plain), c (custom), d (directory), t (tar)
-f путь к файлу вывода
-d название базы данных
--table=имя снять дамп только одной таблицы
--schema=имя только определённая схема
-v подробный вывод
--data-only только данные, без схемы
--schema-only только схема, без данных
--inserts использовать INSERT вместо COPY

▍ pg_dumpall

pg_dumpall

По сути тот же pg_dump, но делает дамп сразу всего кластера. Однако отметим сразу, что данная утилита не поддерживает указание форматов, копия создаётся только в формате SQL. Давайте начнём также с реализации:

Подключение к БД: аналогично pg_dump.
Получение имён всех БД: посредством запросов SELECT datname FROM pg_database WHERE datallowconn.
Сохранение всех глобальных объектов.
Получение списка БД: вызывается команда pg_dump для каждой базы.
Создание общего файла дампа: все дампы объединяются в один поток вывода и записываются в файл аналогично pg_dump.

Ключи:

-U имя пользователя
-h хост
-p порт
-f файл вывода
-v подробный вывод
--globals-only только роли и настройки
--roles-only только роли
--data-only только данные

▍ pg_restore

pg_restore <путь к дампу>

Инструмент для восстановления БД из дампа. Под капотом всё просто:

Чтение копии: считывается TOC (table of contents), определяется, в каком порядке необходимо восстанавливать данные.
Фильтрация: происходит по определённым параметрам, которые задаются при запуске утилиты.
Соединение с БД: посредством знакомой нам уже libpq. Также, если выбран многопоточный режим, то для каждого потока будет отдельное соединение.
Восстановление: исполнение SQL-команд.

Ключи:

-U имя пользователя
-h хост
-p порт
-d целевая база данных
-F формат (обычно не нужен — определяется автоматически)
-v подробный вывод
-c удалить объекты перед созданием
-C создать базу перед восстановлением
-j N параллельное восстановление (N — число потоков)
--list показать содержимое дампа
--schema восстановить только указанную схему
--table восстановить только указанную таблицу

Отлично! С инструментами мы познакомились, теперь перейдём к самому интересному.

▍ Форматы дампов

Ради этого мы тут все и собрались. Давайте погрузимся в возможности резервных копий. До этого вы могли наблюдать непонятный ключ -F, который позволяет указать формат. Всего в PostgreSQL есть 4 формата:

plain (стандартный)
custom
tar
dir

Теперь хочется познакомиться с каждым поближе, но перед этим обсудим одну очень важную для понимания разницы между форматами вещь — TOC.

TOC — Table of Contents, оглавление резервной копии, которое отражает, какой контент содержится в дампе, в каком порядке его нужно восстанавливать и какие есть зависимости между объектами.

▍ plain

Дамп представляет собой один текстовый SQL-файл. Данный формат позволяет редактировать копию, а также удобно переходить между СУБД. Реализация представляет собой банальное генерирование SQL-команд.

Преимущества:

Отличный вариант для миграции
Прозрачность дампа

Недостатки:

Невозможно выборочное восстановление
Не поддерживает параллельное восстановление
Отсутствует сжатие

Пример использования:

pg_dump -Fp demo -f demo-plain

Здесь ключ -Fp указывает формат plain. Также можно было вообще не указывать ключ формата, т. к. дефолтно используется plain, но для наглядности прописан.

Давайте посмотрим на то, как отработает наш дамп. Здесь и далее для примера взята БД demo, последняя версия размером 2.5гб:

time pg_dump -Fp demo -f demo-plain

Executed in    8.42 secs        fish                  external
      usr time    1.33 secs    231.00 micros   1.33 secs
      sys time    1.63 secs    120.00 micros   1.63 secs

ls -lh | grep "demo-plain"

-rw-r--r--. 1 net0pyr net0pyr 888M апр 1 21:03 demo-plain

Если посмотреть на формат файла, то это будет обычный текстовый файл:

file demo-plain

demo-plain: Unicode text, UTF-8 text, with very long lines (307)

P.S. Именно этот формат помог мне скормить моему AI-другу БД.

▍ custom

Резервная копия сохраняется в бинарном формате. Дамп содержит TOC.

Преимущества:

Выборочное восстановление, можно использовать фильтры для восстановления только частей дампа
Поддержка параллельного восстановления
Сжатие

Недостатки:

Невозможность читать дамп, пока не восстановишь его

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


pg_dump -Fc demo -f demo.dump

Также давайте аналогично предыдущему варианту соберём информацию по требуемым для создания дампа ресурсам:

time pg_dump -Fc demo -f demo.dump

Executed in    37.00 secs      fish                  external
      usr time    36.40 secs   360.00 micros   36.40 secs
      sys time    0.44 secs     187.00 micros   0.44 secs

ls -lh | grep "demo.dump"

-rw-r--r--. 1 net0pyr net0pyr 233M апр 1 20:50 demo.dump

В этот раз формат файла будет бинарный дамп PostgreSQL:

file demo-plain

demo-plain: Unicode text, UTF-8 text, with very long lines (307)

Вы можете отобразить TOC, воспользовавшись командой pg_restore:

pg_restore --list demo.dump

▍ tar

Дамп сохраняется как tar-архив, который содержит файлы с данными, SQL-файл со структурой и TOC.

Преимущества:

Удобство транспортировки
Есть возможность восстановиться с помощью tar -xvf без использования pg_restore
Выборочное восстановление

Недостатки:

Отсутствует сжатие
Не поддерживает параллельное восстановление

Аналогично предыдущим форматам схема использования:

pg_dump -Ft demo -f demo-tar

И по классике взглянем на результаты создания дампа в tar-формате:

time pg_dump -Ft demo -f demo-tar

Executed in    10.77 secs      fish                  external
      usr time     1.57 secs     77.00 micros    1.57 secs
      sys time     1.53 secs     982.00 micros  1.53 secs

ls -lh | grep "demo-tar"

-rw-r--r--. 1 net0pyr net0pyr 888M апр 1 20:59 demo-tar

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

tar -xvf demo-tar && ls -lh

total 1.8G
-rw------- 1 net0pyr net0pyr  631 Apr 21 16:14 3508.dat
-rw------- 1 net0pyr net0pyr  17K Apr 21 16:14 3509.dat
-rw------- 1 net0pyr net0pyr 204M Apr 21 16:15 3510.dat
-rw------- 1 net0pyr net0pyr  79M Apr 21 16:15 3511.dat
-rw------- 1 net0pyr net0pyr  26M Apr 21 16:15 3512.dat
-rw------- 1 net0pyr net0pyr  21K Apr 21 16:15 3514.dat
-rw------- 1 net0pyr net0pyr 297M Apr 21 16:15 3515.dat
-rw------- 1 net0pyr net0pyr 284M Apr 21 16:15 3516.dat
-rw-r--r-- 1 net0pyr net0pyr 888M Apr 21 16:15 demo-tar
-rw------- 1 net0pyr net0pyr  33K Apr 21 16:15 restore.sql
-rw------- 1 net0pyr net0pyr  41K Apr 21 16:14 toc.dat

Мы можем наблюдать toc.dat — наш TOC, restore.sql — SQL-скрипт для восстановления, 35[08-16].dat — файлы с данными из БД. Таким образом мы можем восстановиться из дампа только с утилитой tar.

▍ dir

Вот мы и дошли до последнего формата. Я специально оставил его на десерт, потому что он позиционирует себя как «не такой как все», предлагая уникальные возможности. Давайте подробнее разберёмся. Сам дамп представляет собой директорию, содержащую сжатые данные и TOC.

Преимущества:

Есть поддержка параллельного дампа и восстановления
Есть сжатие
Быстро работает с большими объёмами данных

Недостатки:

Неудобные хранение и транспортировка

Обычное использование:

pg_dump -Fd demo -f demo-dir-0

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

pg_dump -Fd demo -j 5 -f demo-dir-5

Перейдём к самому интересному — сравнению результатов отработки дампа:

time pg_dump -Fd demo -f demo-dir-0


      Executed in    36.94 secs     fish                  external
      usr time    36.23 secs  380.00 micros  36.22 secs
      sys time    0.36 secs    197.00 micros  0.36 secs

ls -lh demo-dir-0

итого 233M
-rw-r--r--. 1 net0pyr net0pyr 321 апр 1 20:56 4437.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 5,6K апр 1 20:56 4438.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 54M апр 1 20:56 4439.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 20M апр 1 20:56 4440.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,3M апр 1 20:56 4441.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,0K апр 1 20:56 4443.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 69M апр 1 20:56 4444.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 87M апр 1 20:56 4445.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 40K апр 1 20:56 toc.dat

Обратим внимание на время 37 секунд. Также хочется остановиться на структуре каталога. Мы видим `toc.dat`и 8 сжатых файлов с данными, что позволяет сохранить небольшой размер копии. Теперь для сравнения посмотрим на дамп с 5 потоками:

time pg_dump -Fd demo -j 5 -f demo-dir-5


     Executed in   17.69 secs      fish                  external
     usr time     53.16 secs   0.61 millis       53.16 secs
     sys time     0.57 secs     1.24 millis       0.56 secs

ls -lh demo-dir-5

итого 233M
-rw-r--r--. 1 net0pyr net0pyr 321 апр 1 20:55 4437.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 5,6K апр 1 20:55 4438.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 54M апр 1 20:55 4439.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 20M апр 1 20:55 4440.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,3M апр 1 20:55 4441.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 3,0K апр 1 20:55 4443.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 69M апр 1 20:55 4444.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 87M апр 1 20:55 4445.dat.gz
-rw-r--r--. 1 net0pyr net0pyr 40K апр 1 20:55 toc.dat

Мы получили аналогичный результат, но уже за 18 секунд, т. е. в 2 раза быстрее.

▍ Выводы

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

Telegram-канал со скидками, розыгрышами призов и новостями IT 💻

Источник

PostgreSQL is a highly stable, open-source relational database that helps decipher complex queries using JSON and SQL command language. The companies trust the relational database management system over the others due to its excellent features and robust performance.

It is also backed by 20+ years of development by an open-source community and helps solve complex analytical processes and manage dynamic web applications.

It is one of the easy-to-use databases that support multiple authentications and programming languages. Netflix, The Guardian, and Zendesk are a few high-profile companies that use the PostgreSQL database for their operations. It also provides essential services to some popular platforms like Instagram, Skype, and Disqus.

There are many benefits of using the PostgreSQL database, which raises the need for installing backup tools for better security and loss prevention. As most companies rely on PostgreSQL databases for operations and deciphering complex queries, it is a must to have a tool that will save the data and protect it from bigger losses and damages.

Here we have shortlisted some of the top PostgreSQL Backup Tools that will help track and control all your backups in the PostgreSQL database. Further, we have listed their key features to make it easier for you to compare and select the one that suits your need and budget.

Methodology for selecting the Best PostgreSQL Backup Tools

PostgreSQL is a popular open-source database used by businesses, developers, and even system administrators for storing and managing data. In order to protect your data from unexpected losses and uncertain events, it is important to have a reliable backup tool in place. Here are a few methodologies that one must consider when selecting PostgreSQL backup tools:

Check if it fulfills your backup requirements and offers retention period
Check if it is compatible with the PostgreSQL version and operating systems
Make sure to check if you can include database backups in a full server backup
Are on-premises or SaaS packages available?
Check if it supports full and incremental backups as well as disaster recovery
Make sure to cross-check the security features, such as authentication, access control, or encryption
Does it offer proper support and documentation?
Can you create local as well as remote backups through the selected tool?

What is the PostgreSQL backup tool?

PostgreSQL backup tools are beneficial for all businesses that use PostgreSQL and do not wish to suffer heavy losses. These are automated tools that ease the burden of DBA or administrators and save time. All they require is to monitor if the backups are occurring as per the selected frequency or not.

With the help of these backup tools, administrators can focus on other operational areas and improve performance. They will need to pay attention to the backup tool only when a disaster occurs, or when they require to perform restoring tasks.

There are three types of backup methods – full backup, incremental backup, and differential backup. Under the full backup, all the database content is copied and stored. This method is generally used by companies with smaller databases.

An incremental backup begins after the full backup, where it copies and extracts records that occurred after the last backup. Lastly, the differential backup maintains the copy of changes that happened after the previous full backup.

By creating a backup, businesses reduce the risk of losing data. Implement these strategies and methods to create a backup for your small or large databases.

Why should you back up your PostgreSQL database?

Most well-known companies use PostgreSQL for their business operations. At current, no company would want to risk its data and lose it all due to a minor mistake. Thus, having a backup will save you from major losses.

Also, PostgreSQL procedures are easy to use, i.e., hackers can easily work out a way and try to steal sensitive data. Thus, any company or web application that is not security conscious is prone to attacks.

Damage to a database can leak all your customer data and devastate your business. Losing such crucial records is something that no company can afford. Thus, to prevent your business from huge losses and protect a PostgreSQL database, one must set up backup tools.

Today, some of the top brands and companies are using PostgreSQL to decipher complex queries. To save their businesses from heavy losses in the future, we have come up with a list of top PostgreSQL Backup Tools that will improve your business performance and offer full data protection.

1. Ottomatik

Ottomatik is a cloud-based service that supports backup and recovery solutions. It uses a point-and-click feature to help restore all the lost information or data.

The automated database backup solution offers 24/7 data protection with cloud Storage options.

Key Features

Allows creating a backup for PostgreSQL databases, MySQL and MongoDB
Supports a full backup method and transfers data to cloud storage
Supports creating backup databases on-premises and cloud platforms
Managed by cloud-based console
Offers 24/7 Database Protection with cloud storage
Supports File and Directory Backup services
Public Key Authentication support
Compliance and security measures
Supports encryption and offers data transmission security
The backup tool is compatible with Google Drive, Amazon S3, Backblaze, Dropbox, etc.
Used for replication and migration
Not restricted to one cloud storage
Requires no hardcore technical knowledge and continuous monitoring
Sensible defaults save configuration time
Automated and compression-enabled backups
Automatic installation of SSH Key, software dependencies, SSH port, and home directory
Quick disaster recovery
Runs backups automatically at regular intervals

Why do we recommend it?

The 24/7 data protection combined with cloud storage options makes Ottomatik a solid choice for businesses that seek continuous data safety. The flexibility in terms of supported databases, integration with multiple cloud storage providers, and ease of setup and operation make it particularly attractive.

In the case of Ottomatik, you do not require to perform server configurations manually as the installation script covers it all. It can back up PostgreSQL databases as well as MySQL and MongoDB. It also supports backup creation for databases on-premises and cloud platforms, including Google Cloud Platform, Amazon Web Services (AWS), and Azure.

Managed from a cloud-based console, Ottomatik is one of the easy-to-use backup tools that extract data and creates a secure data transfer connection with the storage space. All the file movement and recovery solution is protected by encryption.

Once you move all data to a safer place with Ottomatik, admins can also create additional copies and offer better response times. Further, it can be used for migration and replication.

Other features that make it a great choice are it offers database insurance, supports SSH keys, accidental bad database queries, Public Key Authentication, and more.

Who is it recommended for?

Ottomatik is ideal for database administrators, IT teams, and businesses who wish to have an easy-to-use solution that offers security and efficiency without technical overhead.

Pros:

Specializes in creating a backup for PostgreSQL databases as well as the ones controlled by MySQL and MongoDB.
Encrypts all file transfers and maintains security at all times
Does not require in-depth technical expertise and constant monitoring
Not restricted to a single cloud storage option
Allows backup creation for databases on-premises and cloud platforms

Cons:

Ottomatik does not offer an agent for Windows
No other cons found

Website Link: https://ottomatik.io/postgresql-backup-restore/

2. N-able Cove Data Protection

Are you looking for a tool that creates PostgreSQL Backup more frequently? Invest in N-able Cove Data Protection, a popular cloud backup service provider with private cloud storage features. N-able Cove Data Protection is best suitable for managed service providers and compatible with Windows, Mac, Linux, and SaaS applications.

Key Features

Designed for managed service providers
Suitable for replication and migration
Supports file-level and block-level backup features
Offers secure storage space
Offers protection with 256-bit AES encryption
Reduces downtimes
Unlimited scalability
Faster Backup and recovery solutions
Offers automated recovery testing
Cost-effective solution
Enables businesses to restore Microsoft 365 data
N-able Cove Data Protection has a multitenant dashboard
No storage and bandwidth restrictions
Allows storing backup off-site
Allows creation of backup for different service levels
Saves time on monitoring and troubleshooting
Only private key users have the authority to access data
Secures data, files, and folders from threats and attacks

Why do we recommend it?

With its commitment to robust security, N-able Cove Data Protection uses 256-bit AES encryption to safeguard data. Its unlimited scalability and lack of restrictions on storage and bandwidth make it a versatile tool for businesses of varying sizes. The centralized, web-based dashboard facilitates easy monitoring, ensuring administrators can have real-time insights.

With the help of its integrated centralized web-based dashboard, admins can monitor and secure all backup files and folders. Also, you can streamline day-to-day operations and combat threats using N-able Cove Data Protection.

The tool is not specifically designed for PostgreSQL database instances instead, it can be used for replication and migration.

It also supports file-level and block-level backup options and offers secure storage space protected by 256-bit AES encryption.

Another feature of N-able Cove Data Protection is it offers quick recovery features and provides integrations that enable admins to create a backup for Oracle, SQL Server, and MySQL databases. It also supports server backup, file backup, and bare-metal recovery solutions at an affordable pricing plan.

Who is it recommended for?

While this tool is tailored for managed service providers, its features make it relevant for businesses that prioritize data protection, especially those using Windows, Mac, Linux, and various SaaS applications.

Pros:

Monitor and secure backup files with the integrated centralized web-based dashboard
Allows streamlining day-to-day operations
Does not restrict storage and bandwidth
Uses 256-bit AES encryption to protect storage space and maintain security
Supports integration and allows creating backup SQL Server, Oracle, and MySQL databases

Cons:

The recovery option needs to be optimized and improved
Not a good option for enterprises that prefer using a mix of different databases or operating systems

Website Link: https://www.n-able.com/products/cove-data-protection

3. SnapShooter

SnapShooter is a popular backup solution provider that is easy to use and keeps all the data safe and secure in a specific location. Admin can schedule frequent backups for servers, websites, and databases with the help of this tool. It hardly takes any time to create a backup with SnapShooter.

Key Features

Secure Data Storage
Schedules frequent backups for servers and databases
Allows quick restoring of backup and snapshots
Allows keeping long backup history
Admins have full access to data regardless of your location
Easy to view and manage backups
Allows storing backups in a different data center
Fully Automated Application Backup
Real-Time Logs
Monitors all resources
Alerts and Instant email notifications
Offers Team Support
Data Compliance
Multi-File Backup Support
Unlimited backup retention
MySQL database backups
Stores backup in AWS S3 or DigitalOcean Spaces

Why do we recommend it?

SnapShooter’s strong emphasis on flexibility and security makes it an appealing choice for businesses. Its unlimited backup retention means businesses can keep a long and detailed history, offering peace of mind for data recovery. The tool’s real-time logs, alerts, and instant email notifications ensure that administrators are always in the loop, fostering a proactive approach to potential issues.

With the help of SnapShooter, admins can also create daily, hourly, and monthly backups. Further, it allows users to retain old backups as per the need and demand. Another advantage of using SnapShooter is it supports offsite MySQL backup, AWS S3 support, and DigitalOcean spaces that help add an extra layer of protection.

Try the automated PostgreSQL backup service and its excellent restoring features to save your business from heavy losses. Being a digital business, the assets of the company are most valuable, and backup them correctly is an essential process. Thus, invest in a tool like SnapShooter that offers frequent backups and fast recovery solutions.

Who is it recommended for?

Given its functionalities, SnapShooter seems most fitting for businesses where data plays a pivotal role in operations. Its focus on automated backups and quick restoration processes ensures minimal operational downtime.

Pros:

Allows scheduling backup for servers, websites, as well as databases
Users can create backups in a few minutes with SnapShooter
Users can even maintain a backup for files and folders in a different datacenter
The alert feature instantly updates the administrator by sending email notifications
Offers unlimited backup retention and secure data storage

Cons:

SnapShooter offers less storage space
SnapShooter does not support all devices and platforms

Website Link: https://snapshooter.com/database/postgresql

4. EDB Backup and Recovery

EDB Backup and Recovery software (BART) is another trusted backup tool with a simplified interface that allows continuous creation of backup files and supports point-in-time recovery techniques. With the help of this tool, admins can capture a complete or modified image of a database cluster.

Key Features

Full base and block-level Incremental backups
Stores all the backup data in compressed format
Centralized catalog for backups
Allows quick restoration of backup data
Creates backup of multiple Advanced and PostgreSQL database servers
Supports backup on local or remote hosts
Retention policy support
S3 support
Backups are available in an easy-to-read format
Simplifies data recovery with simple commands
Offers Guaranteed physical backups
Integrates with PostgreSQL architecture
Requires minimal configuration for backup and restoring data
Helps resolve bottleneck problems
Custom scripts are not necessary
Manages data using Postgres Enterprise Manager console
SSH protocol support
WAL archive compression
Parallel backup and restore features

Why do we recommend it?

Its focus on both full base and block-level incremental backups ensures a detailed backup history, which can be invaluable for businesses managing a PostgreSQL environment. The centralized catalog streamlines management and the command-line interface allows for a granular control of backup and restoration processes without being bogged down by a complex GUI.

Further, the tool uses a centralized backup catalog and command-line interface to control and manage necessary operations. It also performs recovery file configuration essential for point-in-time recovery.

The tool also ensures that if the database goes down, EDB BART will help admins manage and recover all the data back with a few clicks. It focuses primarily on sensitive data and is one of the reliable, easy-to-use back-ups and restore solutions.

With the help of EBD Bart, system administrators can create and manage backup and recovery of the database servers on local or remote hosts. The tool comprises a wide range of features that make it a great option to invest in.

Who is it recommended for?

Given its features and target databases, EDB BART is suitable for businesses that heavily rely on PostgreSQL and Advanced server databases. The tool’s point-in-time recovery techniques make it especially appealing to businesses that require precise recovery options.

Pros:

Makes use of a centralized backup catalog and command line interface to coordinate and control essential processes.
Supports EDB BART to recover lost data and manage backup
It’s not essential to use custom scripts
Users can store backup files in compressed format
Facilitates data recovery using straightforward commands

Cons:

Unlike other tools, lacks a few advanced features
Not compatible with other database platforms

Website Link: https://www.enterprisedb.com/products/backup-recovery-postgresql-database-auto-restore-script-tools

5. Bacula

Bacula is an open-source, enterprise-level Backup Software System chosen by millions of people from across the globe. The purpose of designing Bacula software was to automate all backup tasks and reduce the burden on system administrators or computer operators.

Key Features

Open-source backup and recovery tool
Bacula is an efficient and easy-to-use tool
Supports advanced storage management features
Offers full system backup
Allows creation of disaster recovery plans
Offers regular database backup testing
Offers ransomware protection
Eliminates data volume costs
Global Endpoint Deduplication™
Rapid File-Level Recovery
Supports Full, incremental, differential, and ‘virtual-full’ backup
Fast, highly scalable, and stable backup solution
Supports dump and Point In Time Recovery (PITR) techniques
Compatible with many operating systems, including Mac, Linux, and Windows
Plugins for Oracle, PostgreSQL, etc.
Supports storage devices
Windows VSS plugin supports
SAN support
Bare metal restores
Requires less configuration and simplified installation

Why do we recommend it?

With its lineage rooted in the open-source domain, Bacula has evolved to present a potent combination of robust functionality and adaptability. Its support for many operating systems and devices, coupled with its capabilities to backup various databases, containers, and platforms, makes it a versatile tool in a system administrator’s arsenal.

The tool is compatible with all operating platforms, including UNIX, Linux, macOS, and Windows. It also offers backup devices like tape libraries responsible for storing one or more tape drives or cartridges.

Using the command line console, GUI, or web interface, the system administrators can configure the system and manage backup, recovery, and verify computer data. Bacula is one of the easy-to-use, efficient, and free data backup software solutions.

It provides advanced storage features, support services, a special migration plan, and ransomware protection. Bacula is one of the fastest, highly scalable, and stable backup solutions supporting dump and Point In Time Recovery (PITR) techniques.

You can use the tool for creating a backup of PostgreSQL databases, virtual machines, docker containers, Microsoft 365, Active Directory, etc.

Who is it recommended for?

Given its open-source nature and flexibility, Bacula is suitable for businesses with dedicated IT personnel capable of navigating the intricacies of its configuration. It is especially beneficial for enterprises operating on mixed platforms (Linux, UNIX, macOS, Windows) and those that manage a variety of databases and containers.

Pros:

Helps automate backup tasks and reduces the load on system administrators
Bacula works well with UNIX, Linux, macOS, and Windows
Supports using Point In Time Recovery (PITR) techniques
Uses command line console and GUI for system configuration and backup management
Protects your backup files and folders from ransomware and other threats

Cons:

Quite complex to configure and takes time in implementing things properly
Lacks user-friendly interface

Website Link: https://www.bacula.org/postgresql-backup-software/

6. SimpleBackups

SimpleBackups is an all-in-one backup solution that automates all database, server, cloud, and bucket backups without scripts in minutes. It helps in arranging all project backups and stores at a secure location.

Key Features

Automates server and database backups
Offers secure data storage
Helps arrange all projects backups
Quick recovery options
Encrypts using AES-256
Notifications
No need for scripts for creating backups
Saves time and requires no maintenance
Stores backup data on any cloud storage or locally
Flexible backup schedule
Supports all cloud providers, including AWS, DigitalOcean, and Wasabi
100% Database protection
Backup Log
Allows connecting with own storage account
Remote Server Options
Supports Third-Party Plugins and Add-Ons
Supports external integrations
Time-zone support

Why do we recommend it?

The primary allure of SimpleBackups is its user-friendly design. It offers robust backup capabilities without delving deep into scripts and complex configurations. AES-256 encryption, compatibility with popular cloud storage solutions, and flexible backup schedules add to its appeal.

All you require is to set a regular backup schedule to transfer the sensitive files to a safe location.

With the help of Simplebackups, one can create a backup of all databases at once and exclude tables if needed. Also, admins have to no more worry about bash scrips. Just add your backup files to the tool and select the preferred storage, and it automates the whole process. By depending on Simplebackups, admins can save a lot of their time as they do not have to look for its maintenance.

Another benefit of Simplebackups is that it encrypts all your backup files and folders via AES-256 and stores them on the server. It allows you to connect with any cloud storage of your choice and transfer backups for better security.

Who is it recommended for?

This is tailor-made for businesses and individuals who prioritize simplicity and efficiency. For those without the technical expertise or those who simply want a solution that requires minimal oversight, SimpleBackups proves its worth.

Pros:

One can back up all databases simultaneously with SimpleBackups
Encrypts all backup files using AES-256 before storing them on the server
Users can connect with any cloud storage of their choice
For more security, users can even transfer backups
Setting up a regular backup routine will allow you to transfer crucial files to a secure location

Cons:

Limited storage space is available
Offers fewer customization options

Website Link: https://simplebackups.com/postgresql-backup/

Conclusion

PostgreSQL database is used by many high profile companies like Netflix, The Guardian, Zendesk, etc. These platforms store a lot of data in their systems. The company will get destroyed if due to any technical reason or ransomware attack, the company loses its data.

Thus, to protect and save your business from heavy damages, we recommend investing in PostgreSQL backup tools.

PostgreSQL backup tools are more than general-purpose tools. These are automated tools that help reduce the manual work of system administrators of DBA and save time. With the help of the PostgreSQL backup tool, the administrators only need to check on the backup frequency and visit the storage space at the time of disaster recovery.

Since PostgreSQL procedures are easy to use, hackers can easily turn the tables and make their way into the system and steal sensitive data. Thus, to be prepared for future mishaps, it is best to regularly maintain a backup of your data. This way, even if the attackers make a move, you can restore your data and position in the market and save the business from heavy losses.

Check out some of the best backup tools available online for PostgreSQL databases and compare their features before finalizing one for your business.

Ottomatik, N-able Cove Data Protection, Snapshooter, EDB Backup and Recovery, Bacula, and SimpleBackups are the best backup tools that offer storage space and data protection.

N-able Cove Data Protection is a popular cloud backup service provider ideal for managed service providers. Similarly, Ottomatik is an automated database backup solution suitable for databases on-premises and cloud platforms. Each above-listed PostgreSQL backup tool has its benefits and features that make it a great solution for your business-critical data

Go through their robust and excellent features. Compare and choose one that will enable you to use it as a part of your database management setup and improve performance.

Источник

Start SQLBackupAndFTP

You can find the SQLBackupAndFTP shortcut in the Windows Start menu in the SQLBackupAndFTP folder.

Connect to Database Server

The Сonnect to Server form will open automatically or by clicking the gear button.

Select the DBMS type from the Server type list and set the connection parameters for the selected DBMS type.

Click Test Connection to verify the connection. If the connection succeeded, click Save & Close.

Select databases to backup

Click the gear button in the Select databases section and choose databases you want to backup by clicking checkboxes next to them in the list.

Or you can check Backup all non-system databases — this will automatically select all non-system databases.

This feature has an additional benefit — if new databases are created in the future, they will automatically be backed up. To exclude some databases from being automatically backed up add them to the Exclude databases list.

Select destinations to store backups

Click on any destination below for details:

Set up notification e-mails

Turn on Send confirmation.

On success email to (optional): emails will be sent to these recipients when the job is successful.

On failure email to: emails will be sent to these recipients if any error is encountered during the backup job.

Click the gear button for Advanced email settings

Run the job

Click Run Now.

Verify that the appropriate people have received email messages.

Verify the target destinations contain backup files for each database.

If possible, test restoring these databases.

Schedule the backup job

Turn on Schedule backups. Choose a time to do a full backup, usually during the night.

Click the gear button for Advanced schedule settings

Restore

All your backups can be found at the History & restore section. You can open a backup log by clicking Open Log,
download backup file(s) by clicking Download Backup or restore the database from the backup by clicking Restore from Backup.

Web Log

Web log shows the read-only history of backup jobs from your computers. It is available for licenses with active Full-Service subscription.

More about Web log

No hassle, easy and fast installation, email alerts and multiple backup locations, simple and efficient restores — we are finally at peace with our SQL server backups.
It backups my Azure SQL Server database to OneDrive and sends me an email with the details of the success or failure. Thanks for a great software that just works!
- Steve Graddy, Orgbrat Consulting
This is an excellent product and I would recommend it to any user for SQL backups. Easy to configure, fast and robust!
- Rod Jensen, Celoces
I’ve successfully used SQLBackupAndFTP for a long time backing up on-premise databases. Thanks for a great application that just works every time.
- Steve Graddy, Orgbrat Consulting
The latest version is very mature and integrates with all of the most common services, including Azure. We rely on robust backups and have not found a better tool that does exactly what we need.
- Mark Redman, Brighter Tools Ltd
We’ve been using SQLBackupAndFTP for years and will gladly continue to do so in the future. It has everything you’d expect out of a good database backup tool.
- Marcel Contant, VISION3MC
It is is great! It does its stuff in the background — backing up our databases, storing the files and letting us know if anything fails.
- Mark Anderson, Acuity Research & Practice
We use SQLBackupAndFTP to store backups on an off-site Azure-server. The tool does what it promises every day. We are very satisfied!
- Frank van den Essen, Gilde-BT
It gives us peace of mind by sending our SQL databases to a secure external destination nightly and lets us know if something has failed in the process.
- Kristinn Sigurthorsson, NAV.IS
It simply does what it suppose to in a very light and fast way.
- Edhy Rijo, Progytech
I have used and recommended SQLBackupAndFTP for over a decade.
- Mark Redman, Brighter Tools Ltd
The SQL backup software helps us to backup our SQL database daily to protect us in the event of a hard drive failure on our database server which contains thousands of records. Very good software, flexible and easy to use.
- David Jonathan Needham, N-Sat Ltd
Above all we and our customers appreciate how simple it is. And restoring databases is super fast and easy.
- Thomas Wagner, WATO-SOFT AG
SQLBackupAndFTP is a great tool — effortlessly performing scheduled backups of our databases, really easy to use and monitor that everything is running properly.
- Ben Brett, Donorfy
A reliable and easy to use software. Great must-have tool!
- Gregor Varl, Softeh
I had excellent customer service and technical support; when part of our process stopped working — the team worked hard to get it fixed.
- Mark Anderson, Acuity Research & Practice
I’ve never seen a product so perfectly fill a niche as SQLBackupAndFTP. It does exactly what it says it will do, and it does it extremely well.
- Joe Payne, AbleMods Hosting LLC

PostgreSQL Backup in 2 min

Just a single form to automate backups: select databases, backup, encrypt, compress, send to a folder, FTP or cloud service:

schedule backups, receive confirmation emails and restore when needed.

Features & Prices

Starts from $0

free— backup 2 databases to a network or FTP on schedule
$39+ backup 5 databases, Google Drive or Dropbox
$89+ unlimited database backups, OneDrive Personal & Box
$129+ Amazon S3, Windows Azure, OneDrive for Business & AES encryption
$499+ lifetime updates

Buy now

What can SQLBackupAndFTP do?

There is a lot more to SQLBackupAndFTP than meets the eye. Below is a feature list for the tool.

Run scheduled backup of PostgreSQL databases as well as local files and folders
Compress and encrypt backups
Store backups on a network folder, FTP server or in the cloud (Amazon S3, Google Drive, Dropbox, Box, OneDrive)
Remove old backups
Send an email notification to confirm backup success or failure
Restore any backup directly from within the program
Make backup history available for viewing online
Notify you if your PostgreSQL server is down

What’s unique about this PostgreSQL backup tool?

You can view PostgreSQL backup results for multiple servers. The results are available through the web log
This tool has a free version that is fully functional. It is the ideal solution if all you need are ad hoc PostgreSQL backups or scheduled backups for 1 or 2 databases

How does it backup PostgreSql databases?

SQLBackupAndFTP runs on Windows machine and backups remote PostgreSQL databases via TCP/IP connection. It creates PostgreSQL backup script that later can be run to restore the PostgreSQL database. When TCP/IP connection is used the backup is accomplished using pg_dump utility.

How does this tool relate to PostgreSQLBackupFTP?

Previously, SQLBackupAndFTP and PostgreSQLBackupFTP were two different products. PostgreSQLBackupFTP has since been deprecated. Now SQLBackupAndFTP includes all the functionality of PostgreSQLBackupFTP and more. SQLBackupAndFTP can restore PostgreSQL backups. In addition, it can also notify you if something goes wrong with your PostgreSQL servers.

Источник

pg_probackup

pg_probackup is a utility to manage backup and recovery of PostgreSQL database clusters. It is designed to perform periodic backups of the PostgreSQL instance that enable you to restore the server in case of a failure.

The utility is compatible with:

PostgreSQL 11, 12, 13, 14, 15, 16

As compared to other backup solutions, pg_probackup offers the following benefits that can help you implement different backup strategies and deal with large amounts of data:

Incremental backup: page-level incremental backup allows you to save disk space, speed up backup and restore. With three different incremental modes, you can plan the backup strategy in accordance with your data flow.
Incremental restore: page-level incremental restore allows you dramatically speed up restore by reusing valid unchanged pages in destination directory.
Merge: using this feature allows you to implement «incrementally updated backups» strategy, eliminating the need to do periodical full backups.
Validation: automatic data consistency checks and on-demand backup validation without actual data recovery
Verification: on-demand verification of PostgreSQL instance with the checkdb command.
Retention: managing WAL archive and backups in accordance with retention policy. You can configure retention policy based on recovery time or the number of backups to keep, as well as specify time to live (TTL) for a particular backup. Expired backups can be merged or deleted.
Parallelization: running backup, restore, merge, delete, verificaton and validation processes on multiple parallel threads
Compression: storing backup data in a compressed state to save disk space
Deduplication: saving disk space by not copying unchanged non-data files, such as _vm or _fsm
Remote operations: backing up PostgreSQL instance located on a remote system or restoring a backup remotely
Backup from standby: avoid extra load on master by taking backups from a standby server
External directories: backing up files and directories located outside of the PostgreSQL data directory (PGDATA), such as scripts, configuration files, logs, or SQL dump files.
Backup Catalog: get list of backups and corresponding meta information in plain text or JSON formats
Archive catalog: getting the list of all WAL timelines and the corresponding meta information in plain text or JSON formats
Partial Restore: restore only the specified databases or exclude the specified databases from restore.

To manage backup data, pg_probackup creates a backup catalog. This directory stores all backup files with additional meta information, as well as WAL archives required for point-in-time recovery. You can store backups for different instances in separate subdirectories of a single backup catalog.

Using pg_probackup, you can take full or incremental backups:

Full backups contain all the data files required to restore the database cluster from scratch.
Incremental backups only store the data that has changed since the previous backup. It allows to decrease the backup size and speed up backup operations. pg_probackup supports the following modes of incremental backups:
- PAGE backup. In this mode, pg_probackup scans all WAL files in the archive from the moment the previous full or incremental backup was taken. Newly created backups contain only the pages that were mentioned in WAL records. This requires all the WAL files since the previous backup to be present in the WAL archive. If the size of these files is comparable to the total size of the database cluster files, speedup is smaller, but the backup still takes less space.
- DELTA backup. In this mode, pg_probackup read all data files in PGDATA directory and only those pages, that where changed since previous backup, are copied. Continuous archiving is not necessary for it to operate. Also this mode could impose read-only I/O pressure equal to Full backup.
- PTRACK backup. In this mode, PostgreSQL tracks page changes on the fly. Continuous archiving is not necessary for it to operate. Each time a relation page is updated, this page is marked in a special PTRACK bitmap for this relation. As one page requires just one bit in the PTRACK fork, such bitmaps are quite small. Tracking implies some minor overhead on the database server operation, but speeds up incremental backups significantly.

Regardless of the chosen backup type, all backups taken with pg_probackup support the following strategies of WAL delivery:

Autonomous backups streams via replication protocol all the WAL files required to restore the cluster to a consistent state at the time the backup was taken. Even if continuous archiving is not set up, the required WAL segments are included into the backup.
Archive backups rely on continuous archiving.

ptrack support

PTRACK backup support provided via following options:

vanilla PostgreSQL 11, 12, 13, 14, 15, 16 with ptrack extension
Postgres Pro Standard 11, 12, 13, 14, 15, 16
Postgres Pro Enterprise 11, 12, 13, 14, 15, 16

Limitations

pg_probackup currently has the following limitations:

The server from which the backup was taken and the restored server must be compatible by the block_size and wal_block_size parameters and have the same major release number.
Remote backup via ssh on Windows currently is not supported.
When running remote operations via ssh, remote and local pg_probackup versions must be the same.

Documentation

Documentation can be found at github and Postgres Professional documentation

Development

Stable version state can be found under the respective release tag.
master branch contains minor fixes that are planned to the nearest minor release.
Upcoming major release is developed in a release branch i.e. release_2_6.

For detailed release plans check Milestones

Installation and Setup

Windows Installation

Installers are available in release assets. Latests.

Linux Installation

See the Installation section in the documentation.

Once you have pg_probackup installed, complete the setup.

For users of Postgres Pro products, commercial editions of pg_probackup are available for installation from the corresponding Postgres Pro product repository.

Building from source

Linux

To compile pg_probackup, you must have a PostgreSQL installation and raw source tree. Execute this in the module’s directory:

make USE_PGXS=1 PG_CONFIG=<path_to_pg_config> top_srcdir=<path_to_PostgreSQL_source_tree>

The alternative way, without using the PGXS infrastructure, is to place pg_probackup source directory into contrib directory and build it there. Example:

cd <path_to_PostgreSQL_source_tree> && git clone https://github.com/postgrespro/pg_probackup contrib/pg_probackup && cd contrib/pg_probackup && make

Windows

Currently pg_probackup can be build using only MSVC 2013.
Build PostgreSQL using pgwininstall or PostgreSQL instruction with MSVC 2013.
If zlib support is needed, src/tools/msvc/config.pl must contain path to directory with compiled zlib. Example

CALL "C:\Program Files (x86)\Microsoft Visual Studio 12.0\VC\vcvarsall" amd64
SET PATH=%PATH%;C:\Perl64\bin
SET PATH=%PATH%;C:\msys64\usr\bin
gen_probackup_project.pl C:\path_to_postgresql_source_tree

License

This module available under the license similar to PostgreSQL.

Feedback

Do not hesitate to post your issues, questions and new ideas at the issues page.

Authors

Postgres Professional, Moscow, Russia.

Credits

pg_probackup utility is based on pg_arman, that was originally written by NTT and then developed and maintained by Michael Paquier.

Localization files (*.po)

Description of how to add new translation languages.

Add a flag —enable-nls in configure.
Build postgres.
Adding to nls.mk in folder pg_probackup required files in GETTEXT_FILES.
In folder pg_probackup do ‘make update-po’.
As a result, the progname.pot file will be created. Copy the content and add it to the file with the desired language.
Adding to nls.mk in folder pg_probackup required language in AVAIL_LANGUAGES.

For more information, follow the link below:
https://postgrespro.ru/docs/postgresql/12/nls-translator

Источник

Резервное копирование — одна из важнейших задач системного администратора. Хорошо если копии вам никогда не пригодятся, но они должны быть. Сегодня мы рассмотрим некоторые аспекты резервного копирования популярной СУБД PostgreSQL, в частности при ее применении совместно с 1С:Предприятие. Начнем с самого простого и понятного способа — использования утилиты pg_dump, которая, кстати, может использоваться не только для резервного копирования, но и для переноса баз между различными серверами.

Онлайн-курс по устройству компьютерных сетей
На углубленном курсе «Архитектура современных компьютерных сетей» вы с нуля научитесь работать с Wireshark и «под микроскопом» изучите работу сетевых протоколов. На протяжении курса надо будет выполнить более пятидесяти лабораторных работ в Wireshark.

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

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

В тоже время pg_dump работает на уровне базы данных и позволяет копировать и откатывать именно определенную базу, не затрагивая соседей по кластеру. Это несомненный плюс.

Также pg_dump может использоваться для переноса баз данных, она кроссплатформенна и кроссверсионна, т.е. позволяет переносить базы между разными платформами и разными версиями PostgreSQL. При этом следует помнить, что версии PostgreSQL совместимы снизу вверх, совместимость сверху вниз не поддерживается, либо поддерживается на ограниченное число версий. Т.е. вы всегда сможете загрузить дамп из PostgreSQL 9.6 в PostgreSQL 15, но не наоборот.

Подготовка сервера

Чтобы удобно работать с утилитами PostgreSQL добавим путь к ним в переменную окружения PATH. Для этого перейдем в Свойства системы — Дополнительно — Переменные окружения.

Затем найдем в списке переменную PATH и изменим ее, добавив новой строкой путь к папке bin вашего экземпляра PostgreSQL, в нашем случае это C:\Program Files\PostgreSQL\15.5-10.1C\bin:

Теперь вы можете обращаться к утилитам PostgreSQL просто по имени. Однако при написании скриптов всегда указывайте полный путь, что позволит избежать ошибок, если у запустившего пользователя скрипт в переменной PATH не окажется указанного выше пути.

Следующий вопрос — пароль суперпользователя СУБД, либо другого пользователя, имеющего нужные права для выгрузки и загрузки базы. В подавляющем большинстве случаев с пользователями никто не заморачивается и все работают от суперпользователя postgres. Для этого можно пойти несколькими путями, один из них создание специального файла паролей, для этого создайте в указанные ниже директорию и файл:

%APPDATA%\postgresql\pgpass.conf

После чего внесите в него следующие строки:

#имя_узла:порт:база_данных:имя_пользователя:парольlocalhost:*:*:postgres:MyPa$$Word_1

Первая строка — это подсказка-комментарий, чтобы вам потом не приходилось вспоминать синтаксис. А ниже мы указали, что при подключении к инстансу расположенному на локальном узле localhost, использующему любой порт, для любой базы данных, при подключении пользователем postgres использовать указанный пароль. Первые четыре поля могут использовать подстановочный знак * указывающий на любое значение. Например, если у вас множество серверов с одинаковым паролем, то можете указать так:

*:*:*:postgres:MyPa$$Word_1

Данный способ является рекомендуемым, но у него есть недостаток в виде хранения пароля в открытом виде. Поэтому можно использовать альтернативу, разрешить локальные подключения без пароля. Для этого найдите файл pg_hba.conf который находится в директории кластера data, в нашем случае он расположен по умолчанию в C:\Program Files\PostgreSQL\15.5-10.1C\data. В данный файл добавьте строку:

host all all localhost trust

Сохраните файл и перезапустите службу PostgreSQL. После чего вы можете без пароля подключаться к базе через localhost, обратите внимание, что в данном случае localhost не равнозначен 127.0.0.1 и если вы укажете адрес, то подключиться не получится.

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

Утилита pg_dump умеет создавать копии в разных форматах, каждый из которых имеет свои достоинства и недостатки.

plain — выгрузка в текстовом SQL формате. Наиболее универсальна, а при необходимости позволяет вручную откорректировать дамп или выполнить частичную загрузку или восстановление, например отдельной таблицы. Не сжимается, имеет большой размер.
custom — собственный формат pg_dump, предусматривает сжатие данных и возможность многопоточной загрузки, выгружается всегда однопоточно
directory — выгрузка в виде директории, на каждую таблицу выгружается отдельный сжатый файл, позволяет многопоточную выгрузку и загрузку.
tar — представляет, по сути, выгрузку в виде директории, но упакованной в tar-архив, сжатие не предусмотрено, поэтому размер выгрузки будет больше, чем у директории, однопоточен.

Таким образом, наиболее удобными с практической точки зрения является формат custom, либо directory — если вам требуется многопоточная выгрузка. При переносе между разными системами предпочтительно использовать plain, так как он представляет набор SQL команд и может быть легко отредактирован вручную.

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

psql -h localhost -U postgres -l

В выводе мы увидим список баз и их параметры:

В нашем случае мы будем бекапить базу данных bkp1 и местом хранения резервных копий определим D:\Backup. Начнем с текстового формата, он используется по умолчанию и отдельно указывать его не нужно:

pg_dump -h localhost -U postgres -f D:\Backup\bkp1.sql bkp1

Общий синтаксис команды такой: сначала указываем все используемые ключи, первый аргумент без ключа считается именем базы данных, и оно должно быть последним в команде. Указывать ключи после имени базы не следует. В команде мы использовали ключи:

-h сервер — указывает имя или адрес компьютера, на котором работает сервер СУБД
-U имя_пользователя — имя пользователя, под которым производится подключение
-f файл — файл, в который производится выгрузка

Также ниже мы будем использовать ключи:

-F формат — формат выгрузки
-j число_заданий — количество потоков

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

В некоторых источниках можно встретить команду в виде:

pg_dump -h localhost -U postgres bkp1 > D:\Backup\bkp1.sql

Важно! В среде Windows не используйте перенаправление для выгрузки и загрузки резервных копий!

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

Теперь создадим выгрузку в формате custom:

pg_dump -h localhost -U postgres -Fc -f D:\Backup\bkp1.dump bkp1

Здесь у нас добавился еще один ключ, указывающий на формат выгрузки, если вы хотите выгрузить в формате tar, просто замените -Fc на -Ft.

И, наконец, в формате директории:

pg_dump -h localhost -U postgres -Fd -f D:\Backup\bkp1_dir -j 4 bkp1

Для данного формата у нас появился еще один ключ, указывающий число потоков выгрузки. Число потоков не должно превышать количество ядер процессора, но не все так просто: pg_dump спокойно нагрузит каждый поток, создав 100% загрузку ядер процессора, но сможет ли его принять устройство хранения? Может получиться так, что скорость записи на накопитель окажется бутылочным горлышком и вместо ускорения вы получите замедление как выгрузки дампа, так и всей системы вообще.

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

Еще одна тонкость, что будет если указанный файл выгрузки существует? Форматы, custom и tar молча перезапишут его. При выгрузке в формате directory вы получите сообщение, что целевая директория не пуста и выгрузка выполнена не будет. Это следует учитывать при написании скриптов, потому как при ошибке вы либо останетесь без старых копий, либо не будут создаваться новые. Хотя визуально все будет нормально.

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

Восстановление базы данных из резервной копии

Начнем с того, что восстановить базу данных PostgreSQL можно только в новую, пустую базу. Если мы хотим восстановить ее в существующую, то ее придется сначала удалить, а потом создать новую с таким же именем. Исключение — формат plain, это просто набор SQL-команд, которые мы можем выполнить на рабочей базе, выборочно туда что-то подгрузив. Но это требует определенных знаний и квалификации, что выходит за рамки нашей статьи.

Итак, прежде всего удалим старую базу:

dropdb -h localhost -U postgres -i bkp1

В приведенной команде мы использовали ключ -i который запросит интерактивное подтверждение действия:

База данных "bkp1" будет удалена навсегда.
Продолжить? (y/n)

Почему мы это сделали и советуем вам поступать также? Как показывает практика, данные команды часто вводятся методом копирования, неважно откуда, из статьи, документации, собственных записей, истории команд. В этом случае интерактивный запрос послужит предохранителем, который позволит остановиться и задуматься что вы делаете.

А теперь создадим новую:

createdb -h localhost -U postgres -T template0 bkp1

Ключ -T указывает использовать при создании базы полностью пустой шаблон template0. Если вы хотите выполнить восстановление в отдельную новую базу, то просто создайте ее приведенной выше командой.

Начнем восстановление с формата plain, так как это не дамп, а набор SQL команд, то для их исполнения мы будем использовать утилиту psql:

psql -h localhost -U postgres -d bkp1 D:\Backup\bkp1.sql

Формат команды здесь такой же, сначала ключи, потом файл или директория, из которой идет восстановление. Ключ -d указывает имя базы данных, в которую мы загружаем выгрузку.

Для остальных форматов следует использовать утилиту pg_restore, например восстановим дамп формата custom, в два потока:

pg_restore -h localhost -U postgres -d bkp1 -j 2 D:\Backup\bkp1.dump

Синтаксис тот же самый, просто указываем базу и файл, с форматом утилита разберется самостоятельно. Если же вы попытаетесь подсунуть ей формат plain, то утилита откажется делать загрузку и любезно посоветует вам использовать для этого psql.

Что касается выбора количества потоков, то исходим из тех же соображений: один поток — одно ядро и обязательно тестируем, чтобы производительность диска не стала узким горлышком. Если вы выполняете восстановление в рабочее время, то учтите также влияние на работу пользователей, чтобы не вышло что вы запустили восстановление, а у всех остальных все стало.

При восстановлении из формата directory вместо файла укажите путь к папке:

pg_restore -h localhost -U postgres -d bkp1 -j 2 D:\Backup\bkp1_dir

Напоминаем, что формат tar многопоточную загрузку не поддерживает, но вы можете его распаковать и загрузить многопоточно в формате directory.

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

pg_restore -h localhost -U postgres -d postgres -C -c  D:\Backup\bkp1.dump

Ключи -C -c предписывают перед загрузкой удалить и создать заново базу имя которой записано в дампе, в ключе -d при этом потребуется указать любую существующую базу, обычно указывается стандартная postgres. В результате выполнения данной команды вы можете получать некоторые безвредные сообщения об ошибках.

Однако использовать эту команду следует очень осторожно и только на том сервере, откуда был сделан дамп. Также убедитесь, что вы взяли именно тот дамп, что надо. Но мы не рекомендуем так делать, особенно на продуктовых серверах, лучше осознанно удалите базу руками.

Источник