OpenResty Edge Database Backup

# 1. Preamble

We use PostgreSQL as the database for Edge. We usually refer to the database used by Edge Admin as Edge Admin DB. The Edge Admin DB stores all the configuration information of the application, and once the database is corrupted, it cannot be restored, so it is better to make regular backups of the Edge Admin DB.

We also have a database for Edge Log Server, which we call Edge Log Server DB. The Edge Log Server DB stores logs and statistical metrics data, so we also need to perform regular backups of this database.

# 2. Download the Script

We provide database backup and restore scripts. The backup script supports backing up either Edge Admin DB or Edge Log Server DB to a specified local directory. It can also synchronize backup files to a remote machine.

Log in to the database machine you want to back up and execute the following command to download the script to the current directory.

curl -O https://openresty.com/client/oredge/openresty-edge-backup-db.sh
curl -O https://openresty.com/client/oredge/openresty-edge-restore-db.sh

# 3. Execute the Script Manually

The script usage is as follows.

Usage:
    sudo bash openresty-edge-backup-db.sh <component> <backup_directory> [auto_confirm] [[remote_user@]remote_host[:remote_port]] [remote_directory]

    component           admin-db or log-server-db
    backup directory    the local directory for database backup files
    auto confirm        true or false (optional, default: false)
    remote host         the SSH host used for rsync backups (optional)
    remote user         the SSH user (optional)
    remote port         the SSH port (optional, default: 22)
    remote directory    the remote directory for database backup files
                        enclose IPv6 addresses in square brackets

example:
    sudo bash openresty-edge-backup-db.sh admin-db /local/db_backup
    sudo bash openresty-edge-backup-db.sh admin-db /local/db_backup false 192.168.0.2 /remote/db_backup
    sudo bash openresty-edge-backup-db.sh admin-db /local/db_backup false root@192.168.0.2:1022 /remote/db_backup
    sudo bash openresty-edge-backup-db.sh admin-db /local/db_backup false root@[2001:db8::1]:1022 /remote/db_backup
    sudo bash openresty-edge-backup-db.sh log-server-db /local/db_backup_2

There are several points to note.

• The script execution should be done as much as possible when the business is not busy and the execution time is related to the data volume.
• The script creates backup_directory and remote_directory if they do not exist. The backup_directory needs to be on a different disk from the database being backed up. Otherwise, damage to the database disk may also destroy the backup files.
• The script does not automatically remove old backups. Check the backup directory regularly and remove files you no longer need based on the available disk capacity.
• Setting auto_confirm to true makes the script continue automatically when the free space is below the recommended amount but still sufficient for the backup. This is suitable for scheduled tasks.
• We strongly recommend configuring remote_host to synchronize backup files to another machine and protect against the loss of local backup files.
• Remote backups use SSH known_hosts to verify the host identity. Before the first backup, connect to the remote machine manually and verify its host key fingerprint. Scheduled tasks also require passwordless SSH authentication.
• Enclose IPv6 addresses in square brackets, for example, [2001:db8::1] or root@[2001:db8::1]:1022.

After successful execution of the script, the final output will be:

> Backup database or_edge_admin successfully.

# 4. Configure crontab

After executing the script manually and verifying its success, we can configure the backup to be performed regularly via crontab.

Here we provide an example of a daily scheduled configuration.

0 1 * * * sudo bash /path/to/openresty-edge-backup-db.sh admin-db /local/db_backup true 192.168.0.2 /remote/db_backup >> /tmp/backup.log 2>&1
0 1 * * * sudo bash /path/to/openresty-edge-backup-db.sh log-server-db /local/db_backup_2 true 192.168.0.2 /remote/db_backup_2 >> /tmp/backup.log 2>&1
30 1 * * * sudo find /local/db_backup -maxdepth 1 -type f \( -name 'or_edge_admin-*.gz' -o -name 'or_edge_admin-role-*.gz' \) -mtime +9 -delete
30 1 * * * sudo find /local/db_backup_2 -maxdepth 1 -type f \( -name 'or_edge_log_server-*.gz' -o -name 'or_edge_log_server-role-*.gz' \) -mtime +9 -delete

The first two entries run the backups daily. The last two remove local backup files older than 10 days. Adjust the retention period according to the available space, and configure corresponding cleanup tasks on the remote machine.

The scheduled backup output is written to /tmp/backup.log. Check the log and the backup directory’s package.info file to confirm that BACKUP_STATUS is success. When remote backup is enabled, also confirm that REMOTE_BACKUP_STATUS is success.

# 5. Perform database restore

The backup script creates a database backup, a role backup, and package.info. To restore a backup, place package.info and the two .gz files specified by its BACKUP_FILE_NAME and BACKUP_ROLE_FILE_NAME fields in the same directory. The restore script restores missing roles first and then restores the database through psql.

To restore an Edge Admin DB, run the following command on the Edge Admin DB machine:

sudo bash openresty-edge-restore-db.sh /local/db_backup

To restore an Edge Log Server DB, run the following command on the Edge Log Server DB machine:

sudo bash openresty-edge-restore-db.sh /local/db_backup_2

If a database with the same name already exists on the target machine, the restore script prompts you to rename it before continuing. Restoring modifies the database, so verify the database name, backup file names, and backup time in package.info first.