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 script that supports backing up Edge Admin DB to a specified local directory, and also supports synchronizing backup files to a remote machine.

Login to the Edge Admin DB machine and execute the following command to download the script to the current directory.

curl -O https://openresty.com/client/oredge/edge-admin-database-backup.sh

We also provide a backup database script that allows backing up the Edge Log Server DB to the local directory and also supports synchronizing the backup files to the remote machine.

Login to the Edge Log Server DB machine and execute the following command to download the script to the current directory.

curl -O https://openresty.com/client/oredge/edge-log-server-database-backup.sh

# 3. Execute the Script Manually

The script usage is as follows.

Usage:
    sudo bash edge-admin-database-backup.sh [backup_dir] [backup_count] [remote_user?]@[remote_host?]:[remote_port?] [remote_dir?]

    backup_dir         the local directory for database backup files
    backup_count       number of backup files to keep, old backups will be cleaned up automatically
    remote_host        the ssh host of the remote machine used for rsync synchronization backups (optional)
    remote_user        the ssh user of the remote machine (optional)
    remote_port        the ssh port of the remote machine (optional)
    remote_dir         the remote directory for database backup files (optional)

example:
    sudo bash edge-admin-database-backup.sh /local/db_backup 10
    sudo bash edge-admin-database-backup.sh /local/db_backup 10 192.168.0.2 /remote/db_backup
    sudo bash edge-admin-database-backup.sh /local/db_backup 10 192.168.0.2:1022 /remote/db_backup
    sudo bash edge-admin-database-backup.sh /local/db_backup 10 root@192.168.0.2:1022 /remote/db_backup

Usage:
    sudo bash edge-log-server-database-backup.sh [backup_dir] [backup_count] [remote_user?]@[remote_host?]:[remote_port?] [remote_dir?]

    backup_dir         the local directory for database backup files
    backup_count       number of backup files to keep, old backups will be cleaned up automatically
    remote_host        the ssh host of the remote machine used for rsync synchronization backups (optional)
    remote_user        the ssh user of the remote machine (optional)
    remote_port        the ssh port of the remote machine (optional)
    remote_dir         the remote directory for database backup files (optional)

example:
    sudo bash edge-log-server-database-backup.sh /local/db_backup 10
    sudo bash edge-log-server-database-backup.sh /local/db_backup 10 192.168.0.2 /remote/db_backup
    sudo bash edge-log-server-database-backup.sh /local/db_backup 10 192.168.0.2:1022 /remote/db_backup
    sudo bash edge-log-server-database-backup.sh /local/db_backup 10 root@192.168.0.2:1022 /remote/db_backup

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_dir needs to be on a different disk than the Edge Admin DB, otherwise, when the disk where the database is located is corrupted, the backup file will be corrupted as well.
• The backup_count needs to be adjusted according to the disk capacity, when the count limit is exceeded, the oldest backup file will be cleaned after each backup.
• We strongly recommend configuring remote_host to synchronize backup files to another machine as well.

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

> Edge Admin database backup 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/edge-admin-database-backup.sh  /local/db_backup 10 192.168.0.2 /remote/db_backup >> /tmp/backup.log 2>&1
0 1 * * * sudo bash /path/to/edge-log-server-database-backup.sh  /local/db_backup_2 10 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:

export PATH=/usr/local/openresty/postgresql/bin:/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:

export PATH=/usr/local/openresty/postgresql/bin:/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