- When using Control-M/Server and Control-M/Enterprise Manager with Postgresql it is possible to automate the backup process.
- The backup utilities of Postgresql that can be automated are DBUHotBackup and DBUColdBackup. The utilities used for the restore are DBUHotRestore and DBUColdRestore.
- This solution describes how to automate the are DBUHotBackup and DBUColdBackup.
For Unix,
1. Login as the user that owns the Postgresql database
2. Create a customized Hot Backup script file, for example, /apps/controlm/myscripts/hotbackup.ksh
3. If the database is owned by EM , then insert the following line:
<ctm_em_home>/ctm_em/bin//DBUHotBackup -BACKUP_DIRECTORY <Full Path of Backup Directory> -ADMINISTRATOR_PASSWORD <dba_password>
If the database is owned by Control-M/Server , then insert the following line:
<ctm_server_home>/exe_<platform>/DBUHotBackup -BACKUP_DIRECTORY <Full Path of Backup Directory> -ADMINISTRATOR_PASSWORD <dba_password>
4. Save the file
5. Make the file executable using the chmod command: chmod 750 /apps/controlm/myscripts/hotbackup.ksh
6. Call the script /apps/controlm/myscripts/hotbackup.ksh
NOTE: Logs from the execution of DBUHotBackup, like other database utilities, will be found in the following directory:
{Home}/ctm_server/exe_{OS Info}/DBUData/log/
(Example: /home/ctmuser/ctm_server/exe_Linux-x86_64/DBUData/log/DBUHotBackup7761.log)
For Windows,
1. Login to the Windows server running the Postgresql database.
2. Using a text editor, create a file <filename> and insert the following line:
If the database is owned by EM,
<ctm_em_home>\Default\bin\DBUtils\DBUHotBackup -BACKUP_DIRECTORY <Full Path of Backup Directory> -ADMINISTRATOR_PASSWORD <dba_password>
If the database is owned by Control-M/Server,
<ctm_server_home>\ctm_server\exe\DBUtils\DBUHotBackup -BACKUP_DIRECTORY <Full Path of Backup Directory> -ADMINISTRATOR_PASSWORD <dba_password>
3. Save the file
4. Call the script <filename>
In order to perform a DBUHotRestore, you will need 2 things:
1. The directory containing the DBUHotBackup (pointed to by the BACKUP_FILE parameter)
2. The directory containing the Postgresql archive logs
After you perform the DBUHotBackup, you should tar/zip these 2 directories and move them to a location that is backed up.
NOTE:
- If you choose to perform a Hot Backup, you can automate the process by running a job containing the path of the script to execute.
- If you choose to perform a Cold backup, you will have to use the script on the maintenance routine at the OS level because Control-M must shut down before performing a cold backup.
Important Considerations:
1. If both Control-M/Server and Control-M/Enterprise Manager are installed using the same Postgresql database:
- The Postgresql database will be considered dedicated to one and existing to the other.
- The backup can only be done from the dedicated one and it will backup both databases.
- In this case, the backup and recovery will be for both applications and you will only be able to recover both to the same point in time.
- It will not be possible to recover only one application.
- One option it to maintain a sandbox to restore the backup files and then use 'ctm_backup_bcp' or 'em cli export' to export individual databases and then inport that data to inidividual components.
2. Archived Logs:
- The archive logs are not automatically deleted by the Hot Backup.
- Any archive log files created before the Hot Backup can be removed.
- When the Hot Backup is taken, an archive log file with the extension of "*.backup" is created to indicate the backup time.
- All files before this file can be removed or saved to a new location with the Hot Backup.
For example, here is a listing of the archive log directory where:
A DBUHotBackup was taken on Aug 16 at 11:21 and the file with the "*.backup" extension is created to indicate the backup time.
The files older that this file are associated with an earlier backup and are not needed in this directory unless a recovery is needed.
-rw------- 1 em700p controlm 16777216 Aug 15 16:27 0000000300000002000000AB
-rw------- 1 em700p controlm 16777216 Aug 15 23:01 0000000300000002000000AC
-rw------- 1 em700p controlm 16777216 Aug 16 05:45 0000000300000002000000AD
-rw------- 1 em700p controlm 16777216 Aug 16 10:59 0000000300000002000000AE
-rw------- 1 em700p controlm 16777216 Aug 16 11:20 0000000300000002000000AF
-rw------- 1 em700p controlm 16777216 Aug 16 11:21 0000000300000002000000B0
-rw------- 1 em700p controlm 242 Aug 16 11:21 0000000300000002000000B0.000002C0.backup
3. The directory where you perform the Hot Backup to must be empty. If you are running a job to automate this process, you will need to move or delete the previous backup before re-running the Hot Backup.
4. Beginning in Control-M 9.0.18, files in the BACKUP_DIRECTORY will no longer be placed into a .tar archive file. They will be copied into this directory in the same file structure as they appear in the Components /pgsql directory.
More information:- For a complete discussion regarding the CONTROL-M database backup, archive and restore, see the section "Database operation and maintenance > Database backup and restore" of the “CONTROL-M Administrators Guide” - When using the Hot Backup method, the database must be in Archive mode. See the “CONTROL-M Administrators Guide" for details about enabling archiving.
- When Control-M Archive Mode is enabled, the database does not have to be down for the backup to take place, and the database changes between backups are stored in log files.
This allows the recovery of the database to be done to a point it time other that just the time that the Hot Backup is performed, without the need to stop the database to perform backups.
- Note that in a HA environment hotbackup should be run only on the active node, it is not intended for hotbackup to function on the non-active node if an HA system.
- There is an association between the log files generated and the time of the Hot Backup. Included above is an example of the log files and the Hot Backup .backup marker file.
Once a new Hot Backup is performed, all of the log files prior to the time of the .backup file can be moved to a different directory, or compressed into a single file and stored with the Hot Backup.
If multiple sets of logs and Hot Backups are stored, then there will be multiple points in time that the restore can be performed.
The logs before a Hot Backup is taken must be saved with the Hot Backup file to do point in time recovery.
- When performing a restore, the Hot Backup file that covers the time needed, can be moved to the backup directory, and the log files can be placed in the log directory and the utility DBUHotRestore can be used to restore the database.
- To obtain the usage of the backup utilities, logon to the account where the Postgresql database resides(UNIX) or logon to the Windows server where the Postgresql database is installed(Windows) and type the following:
DBUHotBackup -HELP
DBUColdBackup -HELP
This will give you the useage as follows:
Usage: DBUHotBackup
[ -TRACE_LEVEL <error|log|info> ]
[ -HELP ]
[ -BACKUP_DIRECTORY <Full Path of Backup Directory> ]
[ -ADMINISTRATOR_PASSWORD <Administrator Password> ]
Usage: DBUColdBackup
[ -TRACE_LEVEL <error|log|info> ]
[ -HELP ]
[ -BACKUP_FILE <Full Path of Backup File Name> ]
[ -ADMINISTRATOR_PASSWORD <Administrator Password> ]
Note:
- The Hot Backups can be run while the application(EM or Control-M/Server) is running but it requires database archiving to be enabled.
- See the “CONTROL-M Administrators Guide for details about enabling archiving.
- Cold Backups require the application (EM or Control-M/Server) to be shutdown.
- These commands can be used in interactive mode using a script.
Note II:
DBUHotBackup and DBUCold backup utilities are the only mechanism allowed to backup the dedicated Postgresql.
It's possible to backup the Postgresql using another tool at the customers choice but using a existing Postgresql instead.