Backup Restore on Vertica DB (Part 1)

Walkthrough Backup-Restore on Vertica DB cluster (Single Node)

On version 8.0.0-0 version of Vertica DB, we walk through in this section how to set-up and schedule a regular back-Restore process.

In this version Vertica provides /opt/vertica/bin/vbr python script for managing backup & restore process.

-rw-r--r-- 1 dbadmin verticadba 314 Jun 27 19:28 fullbackup01_8000.ini
-rw------- 1 dbadmin verticadba  34 Jun 27 19:28 fullbackup01_8000_pwd
[dbadmin@COENGS-VRTD01 VerticaBR]$ ll /opt/vertica/bin/vbr
lrwxrwxrwx 1 root root 6 Oct 19  2016 /opt/vertica/bin/vbr -> vbr.py
[dbadmin@COENGS-VRTD01 VerticaBR]$

First time we need to setup the backup config file. I’ll demonstrate later how a Full backup and subsequent incremental back work. For now please note, that name of the snapshot is the key to manage a Backup SET.

First invoke /opt/vertica/bin/vbr with --setup-config option to setup the initial config file. Additional documentation on Vertica BR is available https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/BackupRestore/BackingUpAndRestoringTheDatabase.htm%3FTocPath%3DAdministrator’s%2520Guide%7CBacking%2520Up%2520and%2520Restoring%2520the%2520Database%7C_____0.

I setup initial VBR config file:

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr --setupconfig --config-file fullbackup01_8000.ini
Snapshot name (backup_snapshot): fullbackup01_8000
Destination Vertica DB bin directory (only required for object replication) (/opt/vertica/bin):
Number of restore points (1): 3
Specify objects (no default):
Object restore mode (coexist, createOrReplace or create) (createOrReplace):
Vertica user name (dbadmin):
Save password to avoid runtime prompt? (n) [y/n]: y
Database user password to save in vbr password config file (no default):
Node v_comcast_node0001
Backup host name (no default): 172.28.105.199
Backup directory (no default): /vdata/backups/
Change advanced settings? (n) [y/n]: n
Password file name (no default): fullbackup01_8000_pwd
Saved vbr password to fullbackup01_8000_pwd.
Config file name (fullbackup01_8000.ini):
Saved vbr config to fullbackup01_8000.ini.
[dbadmin@COENGS-VRTD01 VerticaBR]$

Please note Backup Host Name must be IP address. I ran into ERROR when I specified HOST name.

Let us take a quick look at the VBR config file, which is a text (.ini) file.

[dbadmin@COENGS-VRTD01 VerticaBR]$ cat fullbackup01_8000.ini
[Misc]
snapshotName = fullbackup01_8000
dest_verticaBinDir = /opt/vertica/bin
restorePointLimit = 3
objects = None
objectRestoreMode = createOrReplace
passwordFile = fullbackup01_8000_pwd

[Database]
dbName = comcast
dbUser = dbadmin

[Transmission]

[Mapping]
v_comcast_node0001 = 172.28.105.199:/vdata/backups/

[dbadmin@COENGS-VRTD01 VerticaBR]$```

With VBR config file setup, I'll initiate a full backup. Plase note with **task backup**, we must specify the __config file name__.
```bash
[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t backup --config-file fullbackup01_8000.ini
Error: Unable to connect to backup host via password-less SSH: 172.28.105.199.
Backup FAILED.
[dbadmin@COENGS-VRTD01 VerticaBR]$

Ran into ERROR, indicating passwrod-less ssh into the backup HOST for dbadmin is required.

I ensured the SSH KEY is properly setup and realised that the user dbadin is NOT allowed to login via SSH.

[dbadmin@COENGS-VRTD01 .ssh]$ ssh dbadmin@COENGS-VRTD01
dbadmin@coengs-vrtd01's password:
Permission denied, please try again.
dbadmin@coengs-vrtd01's password:

[dbadmin@COENGS-VRTD01 .ssh]$

Modified the AllowGroups in /etc/init.d/sshd and restarted sshd.

Now, I am able to password-less ssh in to the same node.

[dbadmin@COENGS-VRTD01 VerticaBR]$ ssh dbadmin@COENGS-VRTD01
Last login: Wed Jun 28 00:22:46 2017 from coengs-vrtd01
[dbadmin@COENGS-VRTD01 ~]$

Now let me run the backup process with following config setup.

-rw-r--r-- 1 dbadmin verticadba 314 Jun 27 21:10 fullbackup01_8000.ini
-rw------- 1 dbadmin verticadba  37 Jun 27 21:10 fullbackup01_8000_pwd
[dbadmin@COENGS-VRTD01 VerticaBR]$ cat fullbackup01_8000.ini
[Misc]
snapshotName = fullbackup01_8000
dest_verticaBinDir = /opt/vertica/bin
restorePointLimit = 3
objects = None
objectRestoreMode = createOrReplace
passwordFile = fullbackup01_8000_pwd

[Database]
dbName = comcast
dbUser = dbadmin

[Transmission]

[Mapping]
v_comcast_node0001 = 172.28.105.199:/vdata/backups/

[dbadmin@COENGS-VRTD01 VerticaBR]$

Run backup script...
```bash
[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t backup --config-file fullbackup01_8000.ini
Error: Manifest not found at rsync://[172.28.105.199]:50000/vdata/backups/ -- is the backup location initialized? Hint: Execute '--task init' first.
Backup FAILED.
[dbadmin@COENGS-VRTD01 VerticaBR]$

Oops… I did not initialize the Backup Location

Let me initialize the backup location:

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t init --config-file fullbackup01_8000.ini
Initializing backup locations.
Backup locations initialized.
[dbadmin@COENGS-VRTD01 VerticaBR]$

NOW, when I tried back script, ran into a BUG!

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t backup --config-file fullbackup01_8000.ini
Starting backup of database comcast.
Objects: ['None']
Participating nodes: v_comcast_node0001.
Snapshotting database.
Error: SQL command "select object_snapshot('fullbackup01_8000','None',false);" failed: ERROR 5211:  Unknown or unsupported object: None
Backup FAILED.
[dbadmin@COENGS-VRTD01 VerticaBR]$

Root cause was setup-config option sets the following values in the ini file, for full backup.

objects = None
objectRestoreMode = createOrReplace

However back up process interprets the object to be backed up as None!!!


I manually removed the two settings from .ini file.

[dbadmin@COENGS-VRTD01 VerticaBR]$ cat fullbackup01_8000.ini
[Misc]
snapshotName = fullbackup01_8000
dest_verticaBinDir = /opt/vertica/bin
restorePointLimit = 3
passwordFile = fullbackup01_8000_pwd

[Database]
dbName = comcast
dbUser = dbadmin

[Transmission]

[Mapping]
v_comcast_node0001 = 172.28.105.199:/vdata/backups/

[dbadmin@COENGS-VRTD01 VerticaBR]$

The backup ran successfully!

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t backup --config-file fullbackup01_8000.ini
Starting backup of database comcast.
Participating nodes: v_comcast_node0001.
Snapshotting database.
Snapshot complete.
Approximate bytes to copy: 488380025 of 488380025 total.
[==================================================] 100%
Copying backup metadata.
Finalizing backup.
Backup complete!
[dbadmin@COENGS-VRTD01 VerticaBR]$

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: