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 a full restore for your data on the same machine you deployed (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.

The procedure uses the Vertica vbr utility.

If you use the Vertica sample file, edit these parameters 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

See Vertica documentation for more details.

Before you begin

  • Ensure you have additional storage location on each node. The default is /backup.

  • Ensure the backup folder is empty.

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

  • 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
      Edit the file in a plain text editor. Enter the values following Aternity's instructions.
      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.
    2. b 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
      If you use a sample file, update the names of all nodes following your deployment settings.
  2. Step 2 Prepare the password file.
    [Passwords]
    dbPassword = admin
    
    1. a 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.
    2. b Change the dbPassword value to the password of your database DBADMIN user.
  3. Step 3 Edit the configuration file.
    1. a In the Mapping section, edit the number of nodes in a cluster and their names (according to your deployment).
    2. b In the Database section, change dbName if changed during Vertica initial setup. The default name is aternity.
    3. c In the Misc section, change the location of passwordFile. The default location is /home/dbadmin/pwdfile.
    4. d 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.
      # 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
  8. Step 8 Perform the first backup.

    Run this command as DBADMIN user.

    $ /opt/vertica/bin/vbr.py -t backup --config-file /home/dbadmin/backup_restore_full_local.ini
  9. Step 9 To create daily data backups, add the backup task to the crontab file of DBADMIN user.
    0 4 * * * /opt/vertica/bin/vbr.py --task backup --config-file /home/dbadmin/backup_restore_full_local.ini
    The backup will start daily at 4:00 o'clock.