Return
-
Title
SQL PI Infobright Postgresql Repository Cold Backup Procedure -
Description
What is the process for a cold backup of the Infobright SQL PI Postgres repository database?
-
Cause
-
Resolution
The Infobright based version of SQL Performance Investigator (SQL PI) is out of support and has been replaced with a SQL Server based version of the PI repository in the 5.9.5.20 and higher releases of the database cartridges. Please upgrade the database cartridges to the most current release for continued full support of the product.
To perform a cold backup of the SQL PI Repository (PostgreSQL database):
- Deactivate the SPIRepository, DBSS-SPIRepository, and DBO-SPIRepository agents
- This can be done from Foglight console by navigating to "Administration | Agents | Agent Status" and ticking on "SPIRepository" agent and clicking the "Deactivate" button. Do the same for DBSS-SPIRepository and the DBO-SPIRepository agents.
or using the Foglight command line on the FMS server:
fglcmd -cmd agent:deactivate -name agent_name
While the agent is deactivated infobright can be stopped using infobright-stop and the directory can be copied to backup. The agent can then be re-activated (as shown in the example above) which also restarts Infobright. - Connect to the SQL PI repository host
- Stop the Infobright process by running:
On Windows: $IB_INSTALL_DIR\Infobright\Infobright-stop.bat (start command prompt - cmd with "Run As Administrator" option)
On Unix: $IB_INSTALL_DIR/Infobright/Infobright-stop.sh
(Note: this could differ if you select another installation path during PI installation.) - Backup the $IB_INSTALL_DIR/Infobright/ib_data & pg_data directories
- Once the backup is done, restart the PI repository by activating the SPIRepository, DBSS-SPIRepository, and DBO-SPIRepository agents
- Verify that ibengine and postgres processes are running using the ps -ef (in Linux) or Task Manager (in Windows)
To restore from a cold backup of the SQL PI Repository (PostgreSQL database):
Restoring from backup of the Postgresql-based SQL PI repository follows a similar procedure to the backup, with the difference being that the backed up Infobright folders are restored to the PI server.
- Deactivate the SPIRepository, DBSS-SPIRepository, and DBO-SPIRepository agents
- This can be done from Foglight console by navigating to "Administration | Agents | Agent Status" and ticking on "SPIRepository" agent and clicking the "Deactivate" button. Do the same for DBSS-SPIRepository and the DBO-SPIRepository agents.
or using the Foglight command line on the FMS server:
fglcmd -cmd agent:deactivate -name agent_name
While the agent is deactivated infobright can be stopped using infobright-stop and the directory can be renamed, then the backup be restored. The agent can then be re-activated (as shown in the example above) which also restarts Infobright. - Connect to the SQL PI repository host
- Stop the Infobright process by running:
On Windows: $IB_INSTALL_DIR\Infobright\Infobright-stop.bat (start command prompt - cmd with "Run As Administrator" option)
On Unix: $IB_INSTALL_DIR/Infobright/Infobright-stop.sh
(Note: could differ if you select another installation path during PI installation.) - Rename the current $IB_INSTALL_DIR/Infobright/ib_data folder to ib_data_old
- Rename the current $IB_INSTALL_DIR/Infobright/pg_data folder to pg_data_old
- Restore the previously backed up Infobright/ib_data & pg_data directories.
- Once the restore is complete, restart the PI repository by activating the SPIRepository, DBSS-SPIRepository, and DBO-SPIRepository agents
- Verify that ibengine and postgres processes are running using the ps -ef (in Linux) or Task Manager (in Windows)
After a few days of using the restored Postgresql - Infobright SQL PI installation successfully, the $IB_INSTALL_DIR/Infobright/ib_data_old and $IB_INSTALL_DIR/Infobright/pg_data_old folders can be deleted from the PI repository server.
Issues with the shutdown of SQL PI on PostgreSQL
KB articles 232383 and 232387 provide additional details manually shutting down and restarting the Infobright PI repository for Windows and Linux (respectively).
KB article 267280 provides details on shutting down and restarting SQL PI using the command line.