Edit Page

Archive the PostgreSQL Audit trail

The audit trial can generate a high volume of data, so it is a good practice to periodically archive portions of it. An archive separates a portion of the data from the database and keeps it for long-term storage. This process involves the use of PostgreSQL Table Partitions.

Archiving a partition improves query speed for current data, while providing a cost-effective way to store older.

Prerequisites

Before you start, ensure you have the following:

  • A designated backup location, for example ~/rhize-archives/libre-audit.
  • Access to the Rhize Kubernetes Environment - Optional: kubectx utilities
    • kubectx to manage multiple clusters
    • kubens to switch between and configure namespaces easily
  • Optional: the k8 Lens IDE, if you prefer to use Kubernetes graphically

Also, before you start, confirm you are in the right context and namespace.

## context
kubectl config current-context
## namespace
kubectl get namespace

To change the namespace for all subsequent kubectl commands to libre, run this command:

kubectl config set-context --current --namespace=libre

For a reference of useful kubectl commands, refer to the official kubectl Cheat Sheet.

Steps

To archive the PostgreSQL Audit trail, follow these steps:

  1. Record the <PARTITION_NAME> of the partition you wish to detach and archive. This is based on the retention-period query for the names of the existing partitions:

    kubectl exec -i audit-postgres-0 -- psql -h localhost \ 
  -d audit -U <DB_USER> \
  -c "select * from partman.show_partitions('public.audit_log')"

  2. Detach the target partitions from the main table:

    
kubectl exec -i audit-postgres-0 -- psql -h localhost \
  -d audit -U <DB_USER> \
  -c 'alter table audit_log detach partition <PARTITION_NAME>;'

  3. Backup the partition table:

    pg_dump -U <DB_USER> -h audit-postgres-0 -p5433 \
  --file ./audit-p20240101.sql --table public.audit_log_p20240101 audit

    On success, the backup creates a GZIP file, <PARITION_NAME>.sql. To check that the backup succeeded, unzip the files and inspect the data.

  4. Drop the partition table to remove it from the database:

    kubectl exec -i audit-postgres-0 -- psql -h localhost -d audit \
  -U <DB_USER> -c 'drop table <PARTITION_NAME>;'

Next Steps