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

    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.


    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.

  • The backup and restore procedure requires a passwordless SSH communication between the Vertica nodes’ servers. Make sure that the ~.ssh/authenticated_keys file contains the public keys for the DBADMIN of that device (or other admin user if used instead of the default DBADMIN). Make sure this file with all necessary keys resides on each node in the same location. This ensures the successful self SSH connection test (when the backup and restore procedures are being run using Vertica script). Learn more.

  • Make sure to share SSH keys among all cluster nodes to enable each node to communicate with others over SSH. Add every node’s server public key to other nodes. Make sure that the ~.ssh/authenticated_keys file on each node contains the public keys for the DBADMIN of each OTHER node’s server (for example, node A DBADMIN user key resides in the node B ~.ssh/authenticated_keys file, and vice verse). Learn more

  • Make sure that in the ~.ssh/sshd_config file on each node, the AllowTcpForwarding is set to yes. Learn more


  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 =


      restorePointLimit =


      dbPromptForPassword =


      dbName =


      snapshotName =


      v_aternity_node0001 = 
      v_aternity_node0002 = 
      v_aternity_node0003 = 
      v_aternity_node0004 = 
      v_aternity_node0005 = 

      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:

    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/ -t init --config-file /home/dbadmin/backup_restore_full_local.ini

    In case of success, see the message:

    Initializing backup locations.    
    Backup locations initialized. 

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


    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/

    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/
    The backup will start daily at 4:00 o'clock.
  10. Step 10 After you backup Vertica database, you can restore it.

    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/ -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/ -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/ -t restore --archive 20190328_125703 --config-file /home/dbadmin/backup_restore_full_local.ini