Using PostgreSQL Continuous Archiving
  • 04 Apr 2022
  • 2 Minutes to read
  • Dark
  • PDF

Using PostgreSQL Continuous Archiving

  • Dark
  • PDF

Article Summary

CTERA Portal uses PostgreSQL's built-in continuous archiving mechanism, known as Point-In-Time Recovery (PITR).

After backing up the database for the first time, the base backup , Incremental backups of the database are performed using Write Ahead Log (WAL) files, which include all the database transactions and changes. Running a base backup and incremental backups is non-disruptive: the database continues running, without losing any data.

The following diagram illustrates continuous archiving and point-in-time recovery:
DBRep1Using WAL logs has the following major benefits:

  • Ensures data integrity
  • Significantly reduces the number of disk writes

The CTERA Portal database WAL files are located in $pgdatadir/pg_wal, which is commonly called the WAL directory.
The WAL file size is 16MB. Once a WAL file reaches 16MB, a new WAL file is created.
The maximum number of WAL files stored in the WAL directory is 128. This means that the WAL directory size can reach 16 (MB) * 128 (files) = 2GB.
When the 128-file threshold is reached, WAL files are recycled.

The database base backup and WAL logs are compressed upon backup:

  • The base backup is typically 20%-30% of the size of the original database size. By default, there are always two base backups in the archive storage pool so the archived base backups typically consume around 40%-60% of the original database size.
  • An archived WAL file size is typically 20% the size of the original WAL file.

Configuring PostgreSQL Continuous Archiving

To configure PostgreSQL continuous archiving:

  1. Using SSH, log in as root to your CTERA Portal primary database server.
  2. In the command line, enter the following command to create the database archive pool: create_db_archive_pool device
    Where device is the name of the disk on which the database archive pool is created.
    For example: create_db_archive_pool sdd

    This command creates both a logical volume and an LVM volume group using the specified device. Multiple devices can be specified.
    For create_db_archive_pool sdd sde sdf
    The logical volume size can be extended at a later time, as described in Extending the Database Archive Pool.


    When using NFS storage, use the following command to create the database archive pool: create_db_archive_pool -nfs < NFS_IP >:/export/db_archive_dir
    where NFS_IP is the IP address of the NFS mount point.

  3. In the command line, enter the following command to configure the maximum number of days to keep the backups: configure-db-recovery backup-history-days
    Where backup-history-days is the number of days you want to retain a base backup archive before a new one is created. For example, to retain an archive for seven days, run: configure-db-recovery 7
    An initial base backup of the database is created and the next backup is scheduled based on the backup-history-days parameter. Starting from the second base backup, the first scheduledbase backup, there is always two base backups in the archive storage pool. WAL files are created after the first base backup. When a scheduled base backup is performed, the new base backup replaces both the old base backup that exceeded the backup-history-days , as well as the WAL files created in the period of time between the old base backup and the new base backup.

    The minimum retention period recommended by CTERA is 7 days. If you set the retention period to less than 7 days, you must also have a secondary backup method in order to protect the portal from disasters.

When the command finishes successfully a message is displayed, similar to the following:
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
You can roll back to any older version of the database up until the previous base backup.

You can change the backup-history-days parameter at a later date using the command set_archive_history_days history-days.

Was this article helpful?