Backing Up the Database
  • 03 Nov 2022
  • 7 Minutes to read
  • Dark
    Light
  • PDF

Backing Up the Database

  • Dark
    Light
  • PDF

CTERA Portal uses PostgreSQL to store metadata. This database must be backed up to ensure continued use of CTERA Portal in order to ensure data and metadata persistence and consistency on the CTERA platform, and to keep Recovery Time Objective (RTO) and Recovery Point Objective (RPO) values to a minimum.

Calculating the Minimum Space Required for the Database Backup

The database storage used for backup does not have SSD storage. You require double the storage for the backup. For example, if the primary database uses 1TB storage and a secondary, replication, database uses another 1TB, then each of these databases require 2TB for backup. The storage pool must be at least 6TB: 1TB for the primary database, 1TB for the secondary database and 4TB for the primary and secondary backup (2TB for each backup).

Note

You must define a database archive pool, whether using PostgreSQL continuous archiving or PostgreSQL Streaming Replication. Both Using PostgreSQL Continuous Archiving and Using PostgreSQL Streaming Replication procedures describe how to create an archive pool.

Using PostgreSQL Continuous Archiving

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:
image.png
Using WAL logs has the following major benefits:

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

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: portal-storage-util.sh create_db_archive_pool device
    Where device is the name of the disk on which the database archive pool is created.
    For example: portal-storage-util.sh create_db_archive_pool sdd
    Note

    This command creates both a logical volume and an LVM volume group using the specified device. Multiple devices can be specified. For example:
    portal-storage-util.sh 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.
    Note

    When using NFS storage, use the following command to create the database archive pool: portal-storage-util.sh 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: portal.sh 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: portal.sh 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 scheduled base 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.
    Warning

    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
Done

You can roll back to any older version of the database up until the previous base backup.

Note

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

Rolling Back PostgreSQL Continuous Archiving to a Previous Point-in-Time

After continuous archiving has been set up, you can roll back to an older version of the portal database.

To roll back PostgreSQL continuous archiving to a previous point in time:

  1. Using SSH, log in as root to your CTERA Portal primary database server.
  2. In the command line, enter the following command to view the oldest time possible to roll back to: portal.sh db-rollback -p
  3. Enter the following command to roll back to a point in time within the available backup range: portal.sh db-rollback -r "point-in-time"
    Where point-in-time is the desired point in time, in the format YYYY-MM-DD hh:mm:ss.
    For example: portal.sh db-rollback -r "2020-06-10 00:23:21"
  4. When rolling back a primary database server, from which a replication server is streaming, described in [Using PostgreSQL Streaming Replication], restart the CTERA Portal on the secondary database server, the replicating server, by running: ‘portal-manage.sh restart‘

Using PostgreSQL Streaming Replication

Streaming replication enables the continuous streaming and replication of WAL segments from the WAL directory and archived WAL segments from a primary database server to a secondary database server.

The following diagram illustrates streaming replication with continuous archiving and point-in-time recovery:
image.png

Note

Syncing archived WAL segments is done only on the first setup of the replica.

This process is complementary to configuring continuous archiving, described in Using PostgreSQL Continuous Archiving. It protects against failures occurring on the primary database server, by streaming the WAL directory and synchronizing the database's archived WAL logs to a secondary database server.

Note

You can also configure the replication so that the archive storage pool is on the passive, secondary, database and not the active database server with point-in-time recovery on the secondary.
image.png

Configuring PostgreSQL Streaming Replication

The secondary server continuously streams the primary server’s WAL segments, and synchronizes the primary database server’s archived WAL segments every 10 minutes. Streaming replication traffic runs on TCP port 5432, which means you must open these ports for communication between the primary and secondary database servers. The secondary CTERA database server acts as a standby/passive server and cannot be used for load balancing purposes.

Before setting up streaming replication, the following conditions must be met:

  • A CTERA Portal storage pool has to be created, with the same hardware configuration as the primary database server.
  • Add dedicated disks to the host for the PostgreSQL database archive in order to create a separate storage pool that contains the database backup archive.
  • The allocated disk space for the PostgreSQL archive storage pool should be at least twice the size of the storage space allocated for the CTERA Portal storage pool, as described in Calculating the Minimum Space Required for the Database Backup.
Note

The locations of the streaming and archived WAL segments are $pgdatadir/pg_wal and $DBarchive respectively.

To configure streaming replication:

  1. Create an archive pool on the primary database server with the following command: portal-storage-util.sh create_db_archive_pool device
    Where device is the name of the disk on which the database archive pool should be created. For example: portal-storage-util.sh create_db_archive_pool sdd

  2. Using SSH, log in as root to your CTERA Portal secondary, replication, server.

  3. In the command line, enter the following command to create the database archive pool:
    portal-storage-util.sh create_db_archive_pool device
    Where device is the name of the disk on which the database archive pool should be created. For example: portal-storage-util.sh create_db_archive_pool sdd

    Note

    This command creates both a logical volume and an LVM volume group using the specified device. Multiple devices can be specified. For example:
    portal-storage-util.sh create_db_archive_pool sdd sde sdf

    The logical volume size can be extended at a later time, if needed. See Extending the Database Archive Pool.

    Note

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

  4. Do one of the following:

    • If the secondary server has not been initialized, browse to the server's IP address or public DNS.
    • If the secondary server has been initialized but without replication, and you want to set it up as a replication server, open an SSH session to the CTERA Portal secondary, replication, server, by running the following command: portal-manage.sh resetdb
      Warning

      You must not run portal-manage.sh resetdb on the primary database server as this will deleet all the data from the database.

    The Setup wizard opens, displaying the Select Server operation mode window.

  5. Set the server as a replication of the desired database server.

Note

After completing the setup wizard on an already initialized server, a new server entry is created representing the newly configured server. This makes the old server entry obsolete. You can remove the obsolete server entry by doing the following:

  1. Log in to the CTERA Portal as a global administrator.
  2. In Main > Servers locate the obsolete server entry, displayed as Not Connected.
  3. Select the server and click Delete.

Was this article helpful?