Script Hooks: How to Protect PostgreSQL with Retrospect

Hooks

PostgreSQL is an free open source relational database management system (DBMS).PostgreSQL supports transactions, subselects, triggers, views, foreign key referential integrity, and sophisticated locking. PostgreSQL is the first database management system that implements multi-version concurrency control (MVCC) feature, even before Oracle. The MMVC feature is known as snapshot isolation in Oracle. PostgreSQL requires very minimum maintained efforts because of its stability. Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems.

With Retrospect, you can protect your live, running PostgreSQL instance with no downtime. We’ll walk through how to do it.

By default, PostgreSQL stores data at /var/lib/postgresql/main/ on Linux and Mac systems and at C:\Program Files\PostgreSQL\9.5\data\ on Windows systems. Alternatively, you can specify the location in postgresql.conf and view it using query show data_directory;.

To protect the underlying data while your PostgreSQL instance is running, we will use Retrospect’s Script Hooks to mark the database fore backup before the backup using the StartSource trigger and unmark the database after the backup completes using the EndSource trigger. The database is still readable and writable during this time. You can selectively protect only the PostgreSQL data store on the system by using a rule/selector to only include the data store path.

Backup Steps

To protect PostgreSQL, Retrospect or Retrospect Client needs to be running on the same system.

1. Download Retrospect_for_Windows_External_Scripts.zip and place the PostgreSQL script hook at the appropriate location:
   • Retrospect for Windows: C:\ProgramData\Retrospect
   • Retrospect for Mac: /Library/Application Support/Retrospect
   • Retrospect Client for Windows: C:\Program Files\Retrospect\Retrospect Client
   • Retrospect Client for Mac: /Library/Application Support/Retrospect Client/retroeventhandler
2. In the RetroEventHandler file, replace "postgres" with the appropriate username for your environment. Below is the relevant section:
3. function StartSource {
4. sudo -u postgres /usr/local/bin/psql -c "select pg_start_backup('retrospect_backup', true);" -q &>/dev/null
5. echo
6. }
7. function EndSource {
8. sudo -u postgres /usr/local/bin/psql -c "select pg_stop_backup();" -q &>/dev/null
9. echo

}

1. Start a backup of the system where PostgreSQL is running. You do not need to restart Retrospect for the updated file to be used.

Restore Steps

1. Stop your PostgreSQL instance.
2. Restore the Retrospect backup containing the PostgreSQL data store.
3. Start your PostgreSQL instance. The instance will utilize the restored data.

Last Update: September 5, 2017