Perform a Backup of Data Stored in Vertica Database Server

Valuable data is important to you and you might be interested to keep it safe. You can create a full backup of data that stored in Vertica cluster and restore it if required. This article explains how to create a full backup and restore for your data on the same machine you deployed database (let's call it node 1). It does not assume backing up on a different (target) machine.

A full backup stores a copy of all files and occurs automatically according to a predefined schedule. An incremental backup saves space by backing up only the files that have been created or changed since the last backup. Aternity stores one full and one incremental backup.

Perform the backup procedure only once, on one machine (node 1 in this example). The process sends a command to each node to create a local backup. No need to repeat the procedure on each node separately.

Aternity keeps two backup files named backup_snapshot_<data_<time>. When restoring, you can either restore to the latest backup snapshot or go back to a specific file.

Before you begin

  • Ensure you have additional storage location on each node. The default folder for backups is /backup. If this folder does not exist, create it on ALL nodes.

  • Ensure the backup folder is empty.

  • Note that DBADMIN user must be the owner of a backup folder.

  • You should be familiar with the Vertica vbr.py tool.

  • Vertica setup creates an encrypted password file. However, to backup and to restore database, you need the password file that is not encrypted. For security reasons, you should not keep such file. So, you have to create pwdfile each time you run backup or restore commands and to delete it after that.

  • Ensure that partition size of the mounted backup folder has the same size as /vertica/data.
    Note

    Aternity retains one historical backup in addition to the most recent backup. So that you always have two backup snapshots: the first is a full backup and the next one is an incremental. If you need more than that, ensure you have enough free disk space in the Vertica /backup folder.

    Tip

    The sample ini file resides on Vertica node in the following destination: /opt/vertica/share/vbr/example_configs/backup_restore_full_local.ini. You can edit this file according to the below instructions.

Procedure

  1. Step 1 Prepare a backup configuration file.
    1. a Save backup_restore_full_local.ini to the DBADMIN home folder. The default folder is /home/dbadmin).

      Alternatively, use the file that resides in Vertica: /opt/vertica/share/vbr/example_configs/backup_restore_full_local.ini

      If you use the Vertica sample file, edit it in a plain text editor and enter the values as specified in the table below.
      Parameters Value
      passwordFile =

      /home/dbadmin/pwdfile

      restorePointLimit =

      1

      dbPromptForPassword =

      False

      dbName =

      aternity

      snapshotName =

      backup_snapshot

      v_aternity_node0001 = 
      v_aternity_node0002 = 
      v_aternity_node0003 = 
      v_aternity_node0004 = 
      v_aternity_node0005 = 
      []:/backup
      []:/backup
      []:/backup
      []:/backup
      []:/backup

      You can save the file to a Windows machine, edit it and copy it to the Vertica server, or edit it directly on the Vertica server.

      See Vertica documentation for more details.

    2. b Update the list of nodes to reflect your deployment (total amount of nodes and their names).
    3. c Check the names of nodes by running the command $ admintools -t list_allnodes

      Run this command as DBADMIN user.

      Check the names of Vertica nodes - Example
  2. Step 2 Prepare the password file (not encrypted):

    Create or download the pwdfile file and save it in the DBADMIN home folder. The default folder is /home/dbadmin).

    Edit the file in a plain text editor:

    [Passwords]
    dbPassword = admin
    

    Change the dbPassword value to the password of your database DBADMIN user.

  3. Step 3 Edit the following parameters in the configuration file:
    • In the Mapping section, edit the number of nodes in a cluster and their names (according to your deployment).

    • In the Database section, change dbName if changed during Vertica initial setup. The default name is aternity.

    • In the Misc section, change the location of passwordFile. The default location is /home/dbadmin/pwdfile.

    • In the Database section, change dbUser to the Vertica database DBADMIN user. By default, it is dbadmin.

  4. Step 4 Update permissions.
    1. a For both files, change the owner to DBADMIN user.
      Run the command as a user with root or sudo root privileges on the computer.
      # sudo chown dbadmin /home/dbadmin/backup_restore_full_local.ini /home/dbadmin/pwdfile
    2. b For both files, change permissions to 600 (meaning read&write permissions only to the owner of this file).

      Run this command as DBADMIN user.

      $ chmod 600 /home/dbadmin/backup_restore_full_local.ini /home/dbadmin/pwdfile
  5. Step 5 Uncomment the lines you edited.

    Comments in a configuration file help users in understanding the file. The commented lines are ignored by the system while running commands. Remove the ; character at the beginning of the line.

  6. Step 6 Verify that you edited all necessary parameters as described in the above table.
  7. Step 7 Initialize the backup process.

    Run this command as DBADMIN user.

    This command verifies permissions and other settings before performing backups:
    $ /opt/vertica/bin/vbr.py -t init --config-file /home/dbadmin/backup_restore_full_local.ini

    In case of success, see the message:

    Initializing backup locations.    
    Backup locations initialized. 
    Note

    This command fails if you did not create the password file in advance.

    Tip

    If you stop here and do not continue with the procedure, delete the password file you created because it is insecure to keep unencrypted passwords. Remember to create pwdfile each time you run backup or restore commands and to delete it after that.

  8. Step 8 Perform the first backup.

    Run this command as DBADMIN user.

    $ /home/dbadmin/vertica_backup.sh

    In case of success, see the message:

    Backup completed successfully
  9. Step 9 To create daily data backups, add the backup task to the crontab file of DBADMIN user.
    0 4 * * * /home/dbadmin/vertica_backup.sh
    The backup will start daily at 4:00 o'clock.
  10. Step 10 After you backup Vertica database, you can restore it.
    Important

    Remember to create pwdfile each time you run backup or restore commands and to DELETE it after that.

    Restore data from the latest backup file of Aternity Vertica Database Server or return to a specific point in time (to any of the two saved backups).

    Run this command as DBADMIN user.

    1. a Get the list of all backup files by running the following command:
      $ /opt/vertica/bin/vbr.py -t listbackup --config-file /home/dbadmin/backup_restore_full_local.ini
      For example, you get two backup snapshots:
      • backup_snapshot_20190328_125703
      • backup_snapshot_20190328_125404
    2. b Stop Vertica database using admintools.
      admintools -t stop_db -F -d <DB_NAME> -p '<DBA_USER_PSWD>'
    3. c Execute the following task to restore the latest backup snapshot.
      $ /opt/vertica/bin/vbr.py -t restore --config-file /home/dbadmin/backup_restore_full_local.ini
    4. d Alternatively, execute the following task to restore a specific backup snapshot (use its name in the command): $ /opt/vertica/bin/vbr.py -t restore --archive 20190328_125703 --config-file /home/dbadmin/backup_restore_full_local.ini