SQL DB Retention Management

Introduction

This feature is specific to PostgreSQL and relies on the pg_dump and pg_restore utilities.

Parameters

The following parameters have been added to the system.xml configuration file. These parameters improve functionality by enabling efficient data archival and restoration, reducing storage overhead, and allowing for customizable configurations:

1. Set Archival Threshold (sendRecordsToColdStoreDays)

   • Defines the number of days after which records are archived (default: 90 days). This helps maintain an optimized database by moving older records to an archive, improving performance.

2. Set Compression Level (coldStoreCompressionLevel)

   • Specifies the compression level for archived files (default: 4; range: 0 for no compression to 9 for the highest compression). Compression reduces the size of archive files, saving storage space.

3. Define Archive Path (coldStorePath)

   • Indicates the directory path for storing archived files (default: /tmp; can also be an external NFS export mount). This provides flexibility to store archives locally or on an external system.

Example:

<sendRecordsToColdStoreDays>90</sendRecordsToColdStoreDays>
<coldStoreCompressionLevel>4</coldStoreCompressionLevel>
<coldStorePath>/tmp</coldStorePath>

Commands

Archival Command

igls adv sqldbarchive

Restoration Command

igls adv sqldbrestore set --startDate=2024_06_04 --endDate=2024_07_06

Tasks

A new entry has been added to sync.xml for automating the archival process:

<EasyAuditorColdStoreOldRecordsTask 
    IsEnabled="false" 
    IsConfigurable="true" 
    Label="EA Cold Store Old Reports" 
    combine.self="OVERRIDABLE">
    0 1 * * *
</EasyAuditorColdStoreOldRecordsTask>

Task Attributes

• IsEnabled: Determines if the task is active (default: false).
• IsConfigurable: Allows customization of the task.
• Label: Provides a descriptive label for the task.
• Schedule: Specifies the task’s execution cadence (default: daily at 1:00 AM).

Prerequisites

Add the Database

To use this feature, you need to add the PostgreSQL database to Eyeglass. This step ensures that Eyeglass can interact with the database for archival and restoration tasks.

Command:

igls adv sqldb add …

How It Works

Archival Process

The sqldbarchive command archives records from the userEvents table that are older than the threshold defined by sendRecordsToColdStoreDays (default: 90 days). The archived records are stored in a daily archive format, e.g., userEvents_2024_06_11 (userEvents_yyyy_MM_dd), in the directory defined by coldStorePath.

Steps:

1. Move Records to Temporary Table

   • Records older than the threshold are moved to a temporary table.

2. Compress Data

   • The data is compressed based on the coldStoreCompressionLevel and saved as an archive file.

3. Delete Archived Records

   • Records are deleted from both the userEvents table and the temporary table.

4. Automate the Process

   • Automation is configured through the sync.xml entry to run the archival process on a schedule.

Restoration Process

The sqldbrestore command restores archived files from the specified coldStorePath within the provided date range.

Steps:

1. Retrieve Archived Data

   • Data is retrieved from archive files into temporary tables.

2. Copy Data to Main Table

   • Data from the temporary tables is copied back to the userEvents table.

3. Clean Up

   • Temporary tables and archive files are deleted upon successful restoration.

Logging

Logs for both archival and restoration processes are stored in the following directories:

• Archival logs: /opt/data/SQLarchive_logs
• Restoration logs: /opt/data/SQLrestore_logs

The logs provide detailed information on the success or failure of each operation, including the dates processed, specific records archived or restored, and any errors encountered during the process. This helps users troubleshoot issues and verify task completion.

Job Outcomes

• SUCCESS: All records archived/restored successfully or no data found to process.
• WARNING: Archive files could not be deleted.
• FAILURE: Issues like insufficient permissions on the cold store folder, database, or log directory.