Backup and Recovery

Backup is an essential measure for ensuring data security. Anyone involved in database management must take backup work seriously. Halo's backup mechanisms primarily include:

• Logical backup
• Physical backup

Logical Backup and Recovery

Logical backup works by dumping database objects and data into corresponding SQL commands. Typically, we use the pg_dump/pg_restore commands for logical backup and recovery.

Backup

Suppose we want to back up a database named halotest:

pg_dump -Fc -v -f halotest.dump halotest

After the command completes successfully, a backup file named halotest.dump will be generated.

Recovery

Suppose we need to restore halotest:

pg_restore -v -C -d halo0root halotest.dump

The halotest database is now restored.

Physical Backup and Recovery

Physical backup works by copying data files and archived logs. When recovery is needed, the backed-up data files are restored first, and then the archived logs are "replayed" to recover to the desired recovery point. Physical backup is generally faster than logical backup, especially for large databases where the performance advantage is more significant. Additionally, physical backup supports point-in-time recovery (PITR), which logical backup cannot achieve.

Here we will use rman2.

Introduction to RMAN2

RMAN2 (Recovery Manager) can be used to back up and restore database files, archived logs, and control files, and to perform complete or incomplete database recovery. The unique advantage of RMAN2 is that it skips unused data blocks. When backing up an RMAN2 backup set, RMAN2 does not back up data blocks that have never been written to. Additionally, RMAN2 uses binary compression mode to maximally compress typical data within data blocks.

Prerequisites

To enable physical backup, the database must be running in archive mode. Setting up archive mode primarily involves two system parameters:

archive_mode — whether to enable archive mode. The default value is off. Set to on to enable it.

archive_command — the archive command, i.e., the command to copy WAL logs. A simple command example:

test ! -f <archive_path>/%f && cp %p <archive_path>/%f

Initializing the Backup Directory

We need to set up a directory to store backup files. This directory must be initialized:

mkdir -p /data/backup
chown halo:halo /data/backup
export BACKUP_PATH=/data/backup
rman2 init -B /data/backup

Add the database instance to be backed up:

rman2 add-instance -B /data/backup -D /data/halo --instance=node1d1

Where:

• /data/backup: backup directory
• /data/halo: data directory
• node1d1: name of the backup instance, which is a directory name under the backup directory

Full Backup

Uncompressed full backup:

rman2 backup -B /data/backup -b FULL --instance=node1d1 --stream --progress --backup-pg-log

Compressed full backup with logs:

rman2 backup -B /data/backup -b FULL --instance=node1d1 --compress --stream --progress --backup-pg-log

Parameter descriptions:

Parameter	Description
-b	Backup type: FULL (full), DELTA (differential), PAGE (page-level)
--compress	Compression parameter
--stream	Enable streaming backup, obtaining WAL files from the database server in real time via the replication protocol
--progress	Display backup progress in real time
--backup-pg-log	Include the log directory in the backup

Viewing Backup Sets

rman2 show -B /data/backup

Validating Backup Sets

rman2 validate -B /data/backup --instance=node1d1 -i SIJP3R --progress -j 4

Incremental Backup

DELTA (differential):

rman2 backup -B /data/backup -b DELTA --instance=node1d1 --stream --progress -j 4

PAGE (page-level):

Prerequisite: configure the following in postgresql.conf:

archive_command = '/u01/app/halo/product/dbms/16/bin/rman2 archive-push -B /data/backup --instance=node1d1 --wal-file-name=%f --wal-file-path=/data/halo/pg_wal'
restore_command = '/u01/app/halo/product/dbms/16/bin/rman2 archive-get -B /data/backup --instance=node1d1 --wal-file-name=%f --wal-file-path=%p'

Then execute:

rman2 backup -B /data/backup -b PAGE --instance=node1d1 --stream --progress -j 4

Full Database Recovery

Before restoring, ensure the data directory is empty:

rman2 restore -B ${backup_dir} -D /home/halo/data --instance=node1d1 --progress --no-validate

Parameter descriptions:

• --no-validate: Skip automatic validation after backup, which can save recovery time
• -B: Backup directory
• -D: Directory to restore to

Point-in-Time Recovery (PITR)

Archive mode must be configured first. Restore to a different local directory:

rman2 restore -B ${backup_dir} -D /home/halo/data --instance=node1d1 --progress --recovery-target-time="2025-08-08 10:00:16"