March 6, 2019 at 15:35An article about how CommVault backups PostgreSQL
In this article, we will review our experience using CommVault for PostgreSQL backups. 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 the functions of backup and recovery of data, their archiving, deduplication, replication, hierarchical distribution of media and encryption. The platform modules work with corporate content from various sources and provide end-to-end information search in the corporate environment and its constant availability even from archives thanks to a single intellectual indexing of documents in a virtual repository. The platform is also equipped with advanced analytics tools that generate reports on the actions of users and applications and on the functioning of the infrastructure.
CommVault protects, restores, and manages data and access to it in physical and virtual environments.
To perform a backup of the PostgreSQL database, an agent (iDataAgent) is used, which is installed on the server where the database is running. The agent is designed to effectively manage and protect important business data in PostgreSQL databases. You can use this agent to backup 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 minimal time. You can back up or back up the entire PostgreSQL server, individual databases or archived logs at any time.
The backup and recovery features that can be performed in different modes:
Scheme
How it works:
The CommVault platform is deployed on the network as part of the CommServe management server and a separate MediaAgent server (it is recommended to use a physical server).
An agent (iDataAgent) is installed on the server with the PostgreSQL database and its backup policies are configured in accordance with the requirements. iDataAgent collects the necessary data, compresses, deduplicates, if necessary, encrypts it and transfers it to MediaAgent.
Next, the data is placed on the storage system, on a tape library or on cloud storage.
For recovery, data is extracted from the storage and copied to the server with PostgreSQL.
Configuration in the CommVault Console
Now we’ll look at how to do this in the management console.
1. To start database backup at the moment, select in the CommCell Browser console:
Client Computers | | PostgreSQL | | DumpBasedBackupSet.
Right-click on the default folder in subclient and select Backup .
2. Select Full as the backup type and select Immediate .
3. Click OK . PostgreSQL backup starts .
4. During the execution of the job, its status can be monitored by their CommCell console Job window .
5. Once the task is completed, you can see the details of the completed task from the Backup History window . Select the default folder in subclient and select Backup History .
6. In the Backup History window, you can see the following data on completed tasks:
- Backup errors when completing a task;
- Items that were backed up successfully;
- Details of the assignment;
- Events;
- Log files;
- Media on which data is stored.
Why you can back up
Dump Based Backups:
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 the 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.
Client PostgreSQL configuration features The PostgreSQL
cluster configuration was chosen to ensure the fault tolerance of the database server.
The client did a backup of the PostgreSQL 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, we decided to use CommVault to back up the PostgreSQL database.
Because the client used the PostgreSQL cluster configuration; for backup, we decided to use the File System Based Backup option for file backup. At the same time, I had to abandon the use of block backup (Block Level Backup), because The version of the Linux kernel used on which PostgreSQL is deployed is higher than the officially supported CommVault. Due to the fact that the service is critical for the organization, they decided to make a backup schedule according to the table:
The total volume of the database was more than 1.5 Tb and in order to keep within the required RTO and RPO, a separate LAN network was used for backup at a speed of 10 Gb / s.
The backup was performed according to the scheme below:
The backups were taken from the PostgreSQL Standby server and stored on the server with MediaAgent installed. Next, once a month, full copies were uploaded to the Amazon cloud for a one-year shelf life.
All necessary settings were made, and the backup was successful.
PostgreSQL Backup Configuration Features
When installing and configuring the backup, we encountered some difficulties, which are listed below. I think it will be useful to take these features into account when performing similar projects and when setting up the PostgreSQL database administrators.
Recovery
Backup is good. Naturally, we are interested not only in the process of its creation, but also in restoration. For what it's all done.
In this situation, the restoration, based on our experience, the client may need in 2 cases:
To restore the database, just read the documentation at this link: documentation.commvault.com/commvault/v11_sp14/article?p=21502.htm
We would also focus on the following features and steps during recovery:
In this article, we did not take into account the backup of the Linux OS itself and other systems. It should be done separately. The CommVault documentation describes this in detail. If our article is of interest, and there are many wishes, then we will definitely describe how to back up other systems. Write in the comments which systems you would be interested in.
We hope our experience will help you in setting up a backup by the PostgreSQL DBA.
Authors:
Sergey Alexandrov, Head of Backup Group, 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 the functions of backup and recovery of data, their archiving, deduplication, replication, hierarchical distribution of media and encryption. The platform modules work with corporate content from various sources and provide end-to-end information search in the corporate environment and its constant availability even from archives thanks to a single intellectual indexing of documents in a virtual repository. The platform is also equipped with advanced analytics tools that generate reports on the actions of users and applications and on the functioning of the infrastructure.
CommVault protects, restores, and manages data and access to it in physical and virtual environments.
About PostgreSQL backup
To perform a backup of the PostgreSQL database, an agent (iDataAgent) is used, which is installed on the server where the database is running. The agent is designed to effectively manage and protect important business data in PostgreSQL databases. You can use this agent to backup 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 minimal time. You can back up or back up the entire PostgreSQL server, individual databases or archived logs at any time.
The backup and recovery features 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 single database or group of databases as subclient data and back up and restore it.
- Back up only the logs on the PostgreSQL server. These log files can be used to recover database transactions lost due to a failure of the operating system or disk.
- Restore your entire PostgreSQL server at a specific point in time for File System Based Backup.
- View and verify the status of backup and recovery operations from Job Controller and Event Viewer in the CommCell console. Track the status of your work with reports that you can save and distribute.
- Use block-level backups as a faster way to back up data, because backups are 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 it works:
The CommVault platform is deployed on the network as part of the CommServe management server and a separate MediaAgent server (it is recommended to use a physical server).
An agent (iDataAgent) is installed on the server with the PostgreSQL database and its backup policies are configured in accordance with the requirements. iDataAgent collects the necessary data, compresses, deduplicates, if necessary, encrypts it and transfers it to MediaAgent.
Next, the data is placed on the storage system, on a tape library or on cloud storage.
For recovery, data is extracted from the storage and copied to the server with PostgreSQL.
Configuration in the CommVault Console
Now we’ll look at how to do this in the management console.
1. To start database backup at the moment, select in the CommCell Browser console:
Client Computers | | PostgreSQL | | DumpBasedBackupSet.
Right-click on the default folder in subclient and select Backup .
2. Select Full as the backup type and select Immediate .
3. Click OK . PostgreSQL backup starts .
4. During the execution of the job, its status can be monitored by their CommCell console Job window .
5. Once the task is completed, you can see the details of the completed task from the Backup History window . Select the default folder in subclient and select Backup History .
6. In the Backup History window, you can see the following data on completed tasks:
- Backup errors when completing a task;
- Items that were backed up successfully;
- Details of the assignment;
- Events;
- Log files;
- Media on which data is stored.
Why you can back up
Dump Based Backups:
- PostgreSQL system databases;
- PostgreSQL user databases
- Backing up a 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 the 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.
Client PostgreSQL configuration features The PostgreSQL
cluster configuration was chosen to ensure the fault tolerance of the database server.
The client did a backup of the PostgreSQL database using pg_dump.
The scheme of work is presented in the figure below:
Configure backup with CommVault
To unify the backup platform and take advantage of the backup storage, we decided to use CommVault to back up the PostgreSQL database.
Because the client used the PostgreSQL cluster configuration; for backup, we decided to use the File System Based Backup option for file backup. At the same time, I had to abandon the use of block backup (Block Level Backup), because The version of the Linux kernel used on which PostgreSQL is deployed is higher than the officially supported CommVault. Due to the fact that the service is critical for the organization, they decided to make a backup schedule according to the table:
| Full copy | Transaction logs | |
|---|---|---|
| Schedule | Once a day, at 23 hours | Every hour for 24 hours |
| Period of storage of copies | 7 days | 1 day |
The total volume of the database was more than 1.5 Tb and in order to keep within the required RTO and RPO, a separate LAN network was used for backup at a speed of 10 Gb / s.
The backup was performed according to the scheme below:
The backups were taken from the PostgreSQL Standby server and stored on the server with MediaAgent installed. Next, once a month, full copies were uploaded to the Amazon cloud for a one-year shelf life.
All necessary settings were made, and the backup was successful.
PostgreSQL Backup Configuration Features
When installing and configuring the backup, we encountered some difficulties, which are listed below. I think it will be useful to take these features into account when performing similar projects and when setting up the PostgreSQL database administrators.
- Verify that the PostgreSQL service settings are the same 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 specified 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 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 restoration. For what it's all done.
In this situation, the restoration, based on our experience, the client may need in 2 cases:
- To restore the database at a specific point in time in order to access data that, for example, could be deleted from the database;
- In case of loss of the entire PostgreSQL database cluster.
To restore the database, just read the documentation at this link: documentation.commvault.com/commvault/v11_sp14/article?p=21502.htm
We would also focus on the following features and steps during recovery:
- ALWAYS perform the recovery procedure with the PostgreSQL DBA. This will help you avoid incorrect actions and quickly solve problems that arise during the recovery process;
- Recovery must be performed on a node with the role of Master;
- When restoring, be sure to check that PostgreSQL services do not start after the operation is completed;
- In the restored node, change the settings to the Master role, because in our case, we backed up Standby nodes;
- Disable services on the Standby node, enable them on the Master node, then enable the services on the Standby node and configure replication again.
Conclusion
In this article, we did not take into account the backup of the Linux OS itself and other systems. It should be done separately. The CommVault documentation describes this in detail. If our article is of interest, and there are many wishes, then we will definitely describe how to back up other systems. Write in the comments which systems you would be interested in.
We hope our experience will help you in setting up a backup by the PostgreSQL DBA.
Authors:
Sergey Alexandrov, Head of Backup Group, Softline
Artyom Khmelenko, Lead Engineer, Softline