Backup Restore on Vertica DB (Part 2)

In Part 1 of the write-up, we had walked through steps for configuring and taking a full backup of Vertica database. In this section, I’ll show how to take incremental backups and restore the database from the backup.

Please note, in our test runs, we are storing the backup files in the same server and on the same mount point where the data files reside. This is not recommended for production clusters.

Let us check the backups I have taken so far. I have taken 1 FULL backup and 2 INCREMENTAL backups.

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t listbackup --config-file fullbackup01_8000.ini
backup                              epoch   objects   hosts(nodes)                         file_system_type
fullbackup01_8000_20170628_165513   588               v_comcast_node0001(172.28.105.199)   [Linux]
fullbackup01_8000_20170628_161632   588               v_comcast_node0001(172.28.105.199)   [Linux]
fullbackup01_8000_20170628_114833   587               v_comcast_node0001(172.28.105.199)   [Linux]
[dbadmin@COENGS-VRTD01 VerticaBR]$

The backup file names are already time-stamped YYYYMMDD_HHMISS format. I have taken three backups, oldest being the FULL backup and two subsequent INCREMENTAL backups. Please note, from the EPOCH column value, the epoch did NOT advance between 2nd and 3 backup.

Epoch in Vertica is very similar to SCN in Oracle. However Vertica being a distributed database based on Shared Nothing Architecture, there are some fundamental differences, which we will explore in other write-ups.

Normally we do not have to worry about details of the directory structure and file contents of Vertica BACKUP location for normal operations. I’ll explore these details in future write-ups.

Let us now validate the integrity, if our backups are consistent and complete. In other words, can I use them for recovering and restoring the database, if at all I need to (I hope not).

--quick-check and --full-check are two options supported by vbr.py utility for this purpose.

These tasks outputs an exceptions list that identifies the following inconsistencies:

  • Incomplete restore points
  • Damaged restore points
  • Missing backup files
  • Unreferenced files

A quick check:

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t quick-check --config-file fullbackup01_8000.ini
Checking backup consistency.
List all snapshots in backup location:
Snapshot name and restore point: fullbackup01_8000_20170628_161632, nodes:['v_comcast_node0001'].
Snapshot name and restore point: fullbackup01_8000_20170628_114833, nodes:['v_comcast_node0001'].
Snapshot name and restore point: fullbackup01_8000_20170628_165513, nodes:['v_comcast_node0001'].
Backup consistency check complete.
[dbadmin@COENGS-VRTD01 VerticaBR]$

Now a full check:

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t full-check --config-file fullbackup01_8000.ini
Checking backup consistency.
List all snapshots in backup location:
Snapshot name and restore point: fullbackup01_8000_20170628_161632, nodes:['v_comcast_node0001'].
Snapshot name and restore point: fullbackup01_8000_20170628_114833, nodes:['v_comcast_node0001'].
Snapshot name and restore point: fullbackup01_8000_20170628_165513, nodes:['v_comcast_node0001'].
Snapshots that have missing objects(hint: use 'vbr --task remove' to delete these snapshots):
Backup locations have 0 unreferenced objects
Backup locations have 0 missing objects
Backup consistency check complete.
[dbadmin@COENGS-VRTD01 VerticaBR]$

Backup details are also stored in the DB and can be queried using SQL.

[dbadmin@COENGS-VRTD01 VerticaBR]$ vsql -c "select * from v_monitor.database_backups;"
       backup_timestamp        |     node_name      |   snapshot_name   | backup_epoch | node_count | file_system_type | objects
-------------------------------+--------------------+-------------------+--------------+------------+------------------+---------
 2017-06-28 07:48:46.008013-04 | v_comcast_node0001 | fullbackup01_8000 |          587 |          1 | [Linux]          |
 2017-06-28 12:55:24.185074-04 | v_comcast_node0001 | fullbackup01_8000 |          588 |          1 | [Linux]          |
 2017-06-28 12:16:44.069323-04 | v_comcast_node0001 | fullbackup01_8000 |          588 |          1 | [Linux]          |
(3 rows)
[dbadmin@COENGS-VRTD01 VerticaBR]$

I did create and drop a table for testing purpose between 1st FULL backup and 2nd Incremental backup. Now, I’ll restore the database from the FULL backups. Restoring from FULL backup requires the database to be SHUTDOWN. Following are the requirements a FULL DB restore:

  • The database is DOWN. You cannot restore a full backup when the database is running.
  • All of the backup hosts are UP and available.
  • The backup directory exists and contains the backups from which to restore the data.
  • The cluster to which you are restoring the backup has:
  • The same number of hosts as the one used to create the backup
  • Identical node names and IP addresses
  • The target database must already exist on the cluster to which you are restoring data.
  • Database can be completely empty, without any data or schema.
  • The database name must match the name in the backup
  • All of the node names in the database must match the names of the nodes in the configuration file.
  • The user performing the restore is the database administrator.
  • You can use only a full database backup to restore a complete database.
  • If you have saved multiple backup archives, you can restore from either the last backup or a specific archive.

Please Note: Restoring from a full database backup injects the OIDs from each backup into the restored catalog of the full database backup. The catalog also receives all archives. Additionally, the OID generator and current epoch are set to the current epoch.

So Let us proceed…

We have four tables with names containing “faithful”.

[dbadmin@COENGS-VRTD01 VerticaBR]$  vsql -c " select * from v_catalog.all_tables where table_name ilike '%faithful%';"
 schema_name |     table_id      |      table_name       | table_type | remarks
-------------+-------------------+-----------------------+------------+---------
 public      | 45035996275307076 | faithful              | TABLE      |
 public      | 45035996275307108 | faithful_testing      | TABLE      |
 public      | 45035996275307140 | faithful_training     | TABLE      |
 public      | 45035996275387470 | pred_faithful_results | TABLE      |
(4 rows)

[dbadmin@COENGS-VRTD01 VerticaBR]$

If the database is NOT down, the FULL restore fails with an error.

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t restore --config-file fullbackup01_8000.ini
Error: Full database restore requires the nodes be DOWN.
Restore FAILED.
[dbadmin@COENGS-VRTD01 VerticaBR]$

We’ll shut down the DB now. Note if there are user session connected to the DB, we have to use -F option. Beware the option -F will force the sessions off and any active work in those sessions, will be lost.

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/admintools -t stop_db -d comcast
Info: no password specified, using none
        Connecting to database
        Issuing shutdown command to database
Database comcast not stopped successfully for the following reason:
Error: NOTICE 2519:  Cannot shut down while users are connected

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/admintools -t stop_db -d comcast -F
Info: no password specified, using none
        Connecting to database
        Issuing shutdown command to database
Database comcast stopped successfully
[dbadmin@COENGS-VRTD01 VerticaBR]$

The restore now runs correctly and we’ll start the DB. Note that vbr recovered only what needed recovery and not the entire DB (488381583 bytes)

[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/vbr -t restore --config-file fullbackup01_8000.ini
Starting full restore of database comcast.
Participating nodes: v_comcast_node0001.
Restoring from restore point: fullbackup01_8000_20170628_165513
Determining what data to restore from backup.
[==================================================] 100%
Approximate bytes to copy: 191255062 of 488381583 total.
Syncing data from backup to cluster nodes.
[==================================================] 100%
Restoring catalog.
Restore complete!
[dbadmin@COENGS-VRTD01 VerticaBR]$ /opt/vertica/bin/admintools -t start_db -d comcast
Info: no password specified, using none
        Starting nodes:
                v_comcast_node0001 (172.28.105.199)
        Starting Vertica on all nodes. Please wait, databases with large catalog may take a while to initialize.
        Node Status: v_comcast_node0001: (DOWN)
        Node Status: v_comcast_node0001: (DOWN)
        Node Status: v_comcast_node0001: (DOWN)
        Node Status: v_comcast_node0001: (DOWN)
        Node Status: v_comcast_node0001: (DOWN)
        Node Status: v_comcast_node0001: (UP)
Database comcast started successfully
[dbadmin@COENGS-VRTD01 VerticaBR]$

Now, let us check to ensure our table is recovered.

[dbadmin@COENGS-VRTD01 VerticaBR]$  vsql -c " select * from v_catalog.all_tables where table_name ilike '%faithful%';"
 schema_name |     table_id      |      table_name       | table_type | remarks
-------------+-------------------+-----------------------+------------+---------
 public      | 45035996275307076 | faithful              | TABLE      |
 public      | 45035996275307108 | faithful_testing      | TABLE      |
 public      | 45035996275307140 | faithful_training     | TABLE      |
 public      | 45035996275387470 | pred_faithful_results | TABLE      |
 public      | 45035996291019260 | faithful_bkup         | TABLE      |  -- <<<<<<< Recovered Table.
(5 rows)

[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: