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 a database backup script that 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

# 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

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 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 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.

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

The output of the scheduled backup script is in /tmp/backup.log, you can check /tmp/backup.log or check the file in the backup directory to confirm that the task was executed successfully.

# 5. Perform database restore

If the Edge Admin DB fails and needs to be restored, log in to the Edge Admin DB and execute pg_restore, here’s an example:

# For PostgreSQL 15
export PATH=/usr/local/openresty-postgresql15/bin:$PATH
gzip -dkc /local/db_backup/or_edge_admin-2022-10-01 > /local/db_backup/or_edge_admin-2022-10-01.sql
psql -Upostgres -f /local/db_backup/or_edge_admin-2022-10-01.sql

# For PostgreSQL 12
export PATH=/usr/local/openresty-postgresql12/bin:$PATH
gzip -dkc /local/db_backup/or_edge_admin-2022-10-01 > /local/db_backup/or_edge_admin-2022-10-01.sql
psql -Upostgres -f /local/db_backup/or_edge_admin-2022-10-01.sql

If the Edge Log Server DB fails and needs to be restored, log in to the Edge Log Server DB and execute pg_restore, here’s an example:

# For PostgreSQL 15
export PATH=/usr/local/openresty-postgresql15/bin:$PATH
gzip -dkc /local/db_backup_2/or_edge_log_server-2022-10-01 > /local/db_backup_2/or_edge_log_server-2022-10-01.sql
psql -Upostgres -f /local/db_backup_2/or_edge_log_server-2022-10-01.sql

# For PostgreSQL 12
export PATH=/usr/local/openresty-postgresql12/bin:$PATH
gzip -dkc /local/db_backup_2/or_edge_log_server-2022-10-01 > /local/db_backup_2/or_edge_log_server-2022-10-01.sql
psql -Upostgres -f /local/db_backup_2/or_edge_log_server-2022-10-01.sql