An article on how CommVault makes PostgreSQL backups.
In this article, we will look at our experience using CommVault for PostgreSQL backup. To do this, we will analyze a small part of one of our past projects, where we set up a backup of the PostgreSQL database from the client.
CommVault is a single, scalable platform that provides integrated data protection and content management. The platform supports software modules with backup and recovery functions, their archiving, deduplication, replication, hierarchical media distribution and encryption. Platform modules work with corporate content from different sources and provide end-to-end information retrieval in the corporate environment and its constant availability even from archives due to the uniform intellectual indexing of documents in virtual storage. The platform is also equipped with advanced analytics tools that generate reports on user and application activity and infrastructure operation.
')
CommVault protects, restores and manages data and access to it in physical and virtual environments.
To perform a PostgreSQL database backup, an agent (iDataAgent) is used, which is installed on the server where this database is running. The agent is designed to effectively manage and protect sensitive business data in PostgreSQL databases. You can use this agent to back up and restore the entire PostgreSQL server or individual databases. If necessary, you can also restore individual tables.
PostgreSQL iDataAgent provides the flexibility to back up databases in various modes and restore them in minimum time. You can perform a full backup or backup of the entire PostgreSQL server, individual databases or archive logs at any time.
Backup and restore capabilities that can be performed in different modes:
Scheme
How does it work:
The CommVault platform is deployed in the network as part of the CommServe managing server and a separate MediaAgent server (it is recommended to use a physical server).
An agent (iDataAgent) is installed on the PostgreSQL database server and its backup policies are configured as required. iDataAgent collects the necessary data, compresses, deduplicates, encrypts, if necessary, and sends them to the MediaAgent.
Further, the data is placed on the storage system, tape library or cloud storage.
For recovery, data is retrieved from the repository and copied to the server with PostgreSQL.
Setup in the CommVault console
Now consider how to do this in the management console.
1. To start database backup at the moment, in the CommCell Browser you need to select:
Client Computers | | PostgreSQL | | DumpBasedBackupSet.
Right click on the default folder in the subclient and select Backup .
2. Select Full as the backup type and select Immediate .
3. Click OK . PostgreSQL backup will start.
4. During the execution of a job, its status can be monitored by their CommCell Job Controller windows.
5. Once the task is completed, it will be possible to see the details of the task performed from the Backup History window. Select the default folder in the subclient and select Backup History .
6. In the Backup History window, you can view the following data on completed tasks:
- Backup errors during the execution of the task;
- Items backed up successfully;
- job details;
- Developments;
- Log files;
- Media on which data is stored.
What can backup be created for?
Backup based dump (Dump Based Backup):
PostgreSQL databases (data and logs) (data and logs):
What is not copied:
Use File System iDataAgent to back up the above components.
The client needed to deploy a CommVault platform to back up its services. One of the services was the PostgreSQL database, which was deployed in a cluster configuration of 2 nodes: Master and Standby. Both worked on physical servers.
PostgreSQL client configuration details
PostgreSQL cluster configuration was selected to provide fault tolerance for the database server.
The PostgreSQL client did backups of the database using pg_dump.
The scheme of work is presented in the figure below:
To unify the backup platform and take advantage of the backup storage advantages, we decided to use CommVault to back up the PostgreSQL database.
Since the client used the PostgreSQL cluster configuration, for backup we decided to use the File System Based Backup file backup option. At the same time, it was necessary to abandon the use of block backup (Block Level Backup), since the version of the Linux kernel used, on which PostgreSQL is deployed, turned out to be higher than the officially supported CommVault. Due to the fact that the service is critical for the organization, the backup schedule was decided according to the table:
The total database size was more than 1.5 TB and, in order to meet the required RTO and RPO, a separate LAN network was used for backup with a speed of 10 Gbit / s.
Backup was performed according to the scheme below:
Backup copies were taken from a PostgreSQL standby server and stored on a server with MediaAgent installed. Further, once a month, full copies were placed in the Amazon cloud with a shelf life of one year.
All necessary settings were made and the backup was successful.
Features of PostgreSQL Backup Configuration
When installing and setting up a backup, we encountered some difficulties, which are listed below. I think it would be useful to take these features into account when performing similar projects and when configuring PostgreSQL DB administrators.
Recovery
Backup is good. Naturally, we are interested not only in the process of its creation, but also in recovery. For what it is all done.
In this situation, the restoration, based on our experience, the client may need in 2 cases:
To restore the database, it is enough to read the documentation at this link: documentation.commvault.com/commvault/v11_sp14/article?p=21502.htm
We would also focus your attention on the following features and steps when restoring:
In this article, we did not take into account the backup of the Linux OS and other systems themselves. It should be done separately. CommVault documentation describes this in detail. If our article is of interest, and there will be many wishes, we will definitely describe how to back up other systems. Write in the comments, what systems would be interesting to you.
We hope our experience will help you in setting up a backup by the PostgreSQL database administrator.
The authors:
Sergey Alexandrov, Backup Team Leader, Softline
Artyom Khmelenko, Lead Engineer, Softline
About CommVault
CommVault is a single, scalable platform that provides integrated data protection and content management. The platform supports software modules with backup and recovery functions, their archiving, deduplication, replication, hierarchical media distribution and encryption. Platform modules work with corporate content from different sources and provide end-to-end information retrieval in the corporate environment and its constant availability even from archives due to the uniform intellectual indexing of documents in virtual storage. The platform is also equipped with advanced analytics tools that generate reports on user and application activity and infrastructure operation.
')
CommVault protects, restores and manages data and access to it in physical and virtual environments.
About PostgreSQL Backup
To perform a PostgreSQL database backup, an agent (iDataAgent) is used, which is installed on the server where this database is running. The agent is designed to effectively manage and protect sensitive business data in PostgreSQL databases. You can use this agent to back up and restore the entire PostgreSQL server or individual databases. If necessary, you can also restore individual tables.
Key features:
PostgreSQL iDataAgent provides the flexibility to back up databases in various modes and restore them in minimum time. You can perform a full backup or backup of the entire PostgreSQL server, individual databases or archive logs at any time.
Backup and restore capabilities that can be performed in different modes:
- iDataAgent provides the ability to restore the entire PostgreSQL server. All databases located on the source server can be restored on the destination server.
- Define a separate database or group of databases as subclient data and perform backup and recovery.
- Back up only logs on the PostgreSQL server. These log files can be used to recover database transactions lost due to an operating system or disk failure.
- Recover the entire PostgreSQL server to a specific point in time for file system based backup (File System Based Backup).
- View and check the status of backup and restore operations from the Job Controller and Event Viewer in the CommCell console. Track the status of work using reports (Reports) that you can save and distribute.
- Use block-level backups as a faster way to back up data, because backups are performed only for extents (or modified parts of the database), and not for the entire PostgreSQL database.
- Block-Level Deduplication. Deduplication provides a smarter way to store data by identifying and removing duplicates in data protection operations.
Architecture
Scheme
How does it work:
The CommVault platform is deployed in the network as part of the CommServe managing server and a separate MediaAgent server (it is recommended to use a physical server).
An agent (iDataAgent) is installed on the PostgreSQL database server and its backup policies are configured as required. iDataAgent collects the necessary data, compresses, deduplicates, encrypts, if necessary, and sends them to the MediaAgent.
Further, the data is placed on the storage system, tape library or cloud storage.
For recovery, data is retrieved from the repository and copied to the server with PostgreSQL.
Setup in the CommVault console
Now consider how to do this in the management console.
1. To start database backup at the moment, in the CommCell Browser you need to select:
Client Computers | | PostgreSQL | | DumpBasedBackupSet.
Right click on the default folder in the subclient and select Backup .
2. Select Full as the backup type and select Immediate .
3. Click OK . PostgreSQL backup will start.
4. During the execution of a job, its status can be monitored by their CommCell Job Controller windows.
5. Once the task is completed, it will be possible to see the details of the task performed from the Backup History window. Select the default folder in the subclient and select Backup History .
6. In the Backup History window, you can view the following data on completed tasks:
- Backup errors during the execution of the task;
- Items backed up successfully;
- job details;
- Developments;
- Log files;
- Media on which data is stored.
What can backup be created for?
Backup based dump (Dump Based Backup):
- PostgreSQL system databases;
- PostgreSQL user databases;
- Backup file system (File System Based Backup).
PostgreSQL databases (data and logs) (data and logs):
- Log files.
What is not copied:
- PostgreSQL application files (application files);
- Operating system data
Use File System iDataAgent to back up the above components.
Task
The client needed to deploy a CommVault platform to back up its services. One of the services was the PostgreSQL database, which was deployed in a cluster configuration of 2 nodes: Master and Standby. Both worked on physical servers.
PostgreSQL client configuration details
PostgreSQL cluster configuration was selected to provide fault tolerance for the database server.
The PostgreSQL client did backups of the database using pg_dump.
The scheme of work is presented in the figure below:
Backup Configuration with CommVault
To unify the backup platform and take advantage of the backup storage advantages, we decided to use CommVault to back up the PostgreSQL database.
Since the client used the PostgreSQL cluster configuration, for backup we decided to use the File System Based Backup file backup option. At the same time, it was necessary to abandon the use of block backup (Block Level Backup), since the version of the Linux kernel used, on which PostgreSQL is deployed, turned out to be higher than the officially supported CommVault. Due to the fact that the service is critical for the organization, the backup schedule was decided according to the table:
| Full copy | Transaction logs | |
|---|---|---|
| Schedule | Once a day, at 23 o'clock | Every hour for 24 hours |
| Copy retention period | 7 days | 1 day |
The total database size was more than 1.5 TB and, in order to meet the required RTO and RPO, a separate LAN network was used for backup with a speed of 10 Gbit / s.
Backup was performed according to the scheme below:
Backup copies were taken from a PostgreSQL standby server and stored on a server with MediaAgent installed. Further, once a month, full copies were placed in the Amazon cloud with a shelf life of one year.
All necessary settings were made and the backup was successful.
Features of PostgreSQL Backup Configuration
When installing and setting up a backup, we encountered some difficulties, which are listed below. I think it would be useful to take these features into account when performing similar projects and when configuring PostgreSQL DB administrators.
- Check that the same PostgreSQL service settings are set on the Master and Standby nodes according to the CommVault documentation:
documentation.commvault.com/commvault/v11_sp14/article?p=21491.htm - Check that the parameters specified in Backup Troubleshooting are consistent with those indicated by the links:
documentation.commvault.com/commvault/v11_sp14/article?p=21723.htm
documentation.commvault.com/commvault/v11_sp14/article?p=21518.htm - Make sure that the access rights to the database server and databases were set according to the following requirements:
documentation.commvault.com/commvault/v11_sp14/article?p=21523.htm
Recovery
Backup is good. Naturally, we are interested not only in the process of its creation, but also in recovery. For what it is all done.
In this situation, the restoration, based on our experience, the client may need in 2 cases:
- To restore the database to a specific point in time in order to gain access to data that, for example, could be deleted from the database;
- In case of loss of the entire cluster of the PostgreSQL database.
To restore the database, it is enough to read the documentation at this link: documentation.commvault.com/commvault/v11_sp14/article?p=21502.htm
We would also focus your attention on the following features and steps when restoring:
- ALWAYS perform the recovery procedure together with the PostgreSQL DBA. This will help you avoid wrong actions and quickly solve problems arising during the recovery process;
- Recovery must be performed on the node with the role of Master;
- When restoring, be sure to check that PostgreSQL services do not start after the end of the operation;
- In the restored node, change the settings for the Master role, since in our case, we backed up the Standby nodes;
- On the Standby node, disable the services, on the Master node enable them, then enable on the Standby node and configure replication again.
Conclusion
In this article, we did not take into account the backup of the Linux OS and other systems themselves. It should be done separately. CommVault documentation describes this in detail. If our article is of interest, and there will be many wishes, we will definitely describe how to back up other systems. Write in the comments, what systems would be interesting to you.
We hope our experience will help you in setting up a backup by the PostgreSQL database administrator.
The authors:
Sergey Alexandrov, Backup Team Leader, Softline
Artyom Khmelenko, Lead Engineer, Softline
Source: https://habr.com/ru/post/442760/
All Articles