Vertica的这些事（十二）——-vertica备份与恢复
- 2020 年 4 月 9 日
- 筆記
最近在使用vertica，上网找了很多资料都没有，只有自己看官方文档动手搞一下了，今天搞了vertica的备份与恢复 以下是整理的过程，分享给大家，如有问题欢迎大家指正~ 可加QQ群交流：412191494
1、vertica备份
1.1 vertica备份配置:
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --setupconfig Snapshot name (backup_snapshot): full_backup20160505 Number of restore points (1): 1 Specify objects (no default): Object restore mode (coexist, createOrReplace or create) (createOrReplace): Vertica user name (dbadmin): 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_windy1990_node0001 Backup host name (no default): 192.168.231.110 Backup directory (no default): /home/dbadmin/backup Change advanced settings? (n) [y/n]: y Temp directory (/tmp/vbr): Number of times to retry (2): Seconds between retry attempts (1): Encrypt data during transmission? (n) [y/n]: Use checksum for data integrity (not file data and size)? (n) [y/n]: Port number for rsync daemon (50000): User name to access rsync daemon (no default): Password of the user who accesses rsync daemon: Backup transfer bandwidth limit in KBps or 0 for unlimited (0): Number of concurrency for backup (1): Restore transfer bandwidth limit in KBps or 0 for unlimited (0): Number of concurrency for restore (1): Password file name (no default): password Saved vbr password to password. Config file name (full_backup20160505.ini): Saved vbr config to full_backup20160505.ini. 备份成功后查看备份的文件内容如下：
[dbadmin@localhost ~]$ more full_backup20160505.ini [Misc] snapshotName = full_backup20160505 restorePointLimit = 1 objectRestoreMode = createOrReplace tempDir = /tmp/vbr retryCount = 2 retryDelay = 1 passwordFile = password [Database] dbName = Windy1990 dbUser = dbadmin [Transmission] encrypt = False checksum = False port_rsync = 50000 serviceAccessUser = None total_bwlimit_backup = 0 concurrency_backup = 1 total_bwlimit_restore = 0 concurrency_restore = 1 [Mapping] v_windy1990_node0001 = 192.168.231.110:/home/dbadmin/backup 备份中我选了一个文件保存密码：
/home/dbadmin/password 里面存有数据路的密码：
[dbadmin@localhost ~]$ more password [Passwords] dbPassword = dbadmin 1.2 vertica 全备份 官方文档： A full backup is a complete copy of the database catalog, its schemas, tables, and other objects. It is a consistent image of the database at the time the backup occurred. You can use a full backup for disaster recovery to restore a damaged or incomplete database. 执行备份命令：
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini Error: Manifest not found at rsync://[192.168.231.110]:50000/home/dbadmin/backup/ -- is the backup location initialized? Hint: Execute '--task init' first. Backup FAILED. 发现报错了，根据提示，必须先初始化路径（个人理解的就是先声明路径）
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task init --config-file full_backup20160505.ini Initializing backup locations. Backup locations initialized. 路径声明成功！ 查看该备份路径下多了一个文件。
[dbadmin@localhost backup]$ ls -l total 4 -rw-r--r--. 1 dbadmin verticadba 22 May 5 13:47 backup_manifest 然后再次执行备份命令：
/opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini 可以看到这次可以备份成功了：
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini Starting backup of database Windy1990. Participating nodes: v_windy1990_node0001. Snapshotting database. Snapshot complete. Approximate bytes to copy: 60269241 of 60269241 total. [==================================================] 100% Copying backup metadata. Finalizing backup. Backup complete! 我们可以看到，已经完全备份了vertica数据库。 此时我们应该可以猜到，备份的数据存在我们刚刚声明的路径/home/dbamin/backup下：
[dbadmin@localhost backup]$ ll total 12 -rw-r--r--. 1 dbadmin verticadba 1044 May 5 14:08 backup_manifest drwxr-xr-x. 8 dbadmin verticadba 4096 May 5 14:07 Objects drwxr-xr-x. 3 dbadmin verticadba 4096 May 5 14:08 Snapshots 可以看到多了2个文件夹 /Objects / Snapshots 我们打开Objects文件夹可以看到，我们的vertica数据库文件就在该目录下。 /home/dbadmin/backup/Snapshots/full_backup20160505_20160505_060600/v_windy1990_node0001目录下存放着我们备份的原vertica数据库的一下信息。
检查每个节点的数据流：
select node_name,sum(used_bytes) as size_in_bytes from v_monitor.storage_containers group by node_name; 1.3 vertica的增量备份 官方文档上是把全量和增量一起介绍的，称为Full and Incremental Backups，在这里我把增量单独列了出来。 restorePointLimit控制着增量备份集的数量 我这只有一个节点，刚在配置备份文件的时候设置restorePointLimit = 1 再次执行1.2中的全备份命令即可实现增量备份~
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file full_backup20160505.ini Starting backup of database Windy1990. Participating nodes: v_windy1990_node0001. Snapshotting database. Snapshot complete. Approximate bytes to copy: 0 of 60269241 total. [==================================================] 100% Copying backup metadata. Finalizing backup. Backup complete! 有人会问，我怎么知道这样就是增量备份而不是全量备份的呢？？ 这个问题很好，我备份的时候也在想，备份完成后，我再次打开了backup文件夹，看到：
[dbadmin@localhost ~]$ cd backup/ [dbadmin@localhost backup]$ ll total 12 -rw-r--r--. 1 dbadmin verticadba 1103 May 5 14:36 backup_manifest drwxr-xr-x. 8 dbadmin verticadba 4096 May 5 14:07 Objects drwxr-xr-x. 4 dbadmin verticadba 4096 May 5 14:36 Snapshots 大家看Objects目录的时间，因为在此过程中我没有对vertica数据库进行操作，所以vertica数据库文件还是原来的就没有更新。 所以想要知道上面我们操作的是增量的其实很简单，我在vertica数据库中的任意一张表中插入一条数据，然后我再执行上面的备份脚本。 我插入一条手机号：
dbadmin=> insert into tb_serv_num values('13488888888','qin**'); OUTPUT -------- 1 (1 row) dbadmin=> commit; COMMIT dbadmin=> q 然后重新执行备份语句，执行后查看backup文件下的内容：
[dbadmin@localhost ~]$ cd backup/ [dbadmin@localhost backup]$ ll total 12 -rw-r--r--. 1 dbadmin verticadba 1171 May 5 14:46 backup_manifest drwxr-xr-x. 9 dbadmin verticadba 4096 May 5 14:46 Objects drwxr-xr-x. 4 dbadmin verticadba 4096 May 5 14:46 Snapshots 看文件时间，是不是说明我们上面的推理是正确的呢~~~ 所以，不要怀疑了，我们的增量备份是没有问题的！ 1.4 vertica对象级备份 官网文档： The database administrator user can create object-level backups consisting of one or more schemas and tables. Object-level backups are especially useful for multi-tenanted database sites. 其实对象级备份和全量备份差不多，知识在生成备份配置文件时有所不同（注意不同之处）：
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --setupconfigSnapshot name (backup_snapshot): backup_object_tb_serv Number of restore points (1): Specify objects (no default): tb_serv_num 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_windy1990_node0001 Backup host name (no default): 192.168.231.110 Backup directory (no default): /home/dbadmin/backup Change advanced settings? (n) [y/n]: Password file name (no default): password Saved vbr password to password. Config file name (backup_object_tb_serv.ini): Saved vbr config to backup_object_tb_serv.ini. 然后在执行备份命令：
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file backup_object_tb_serv.ini Starting backup of database Windy1990. Objects: ['tb_serv_num'] Participating nodes: v_windy1990_node0001. Snapshotting database. Snapshot complete. Approximate bytes to copy: 0 of 244 total. [==================================================] 100% Copying backup metadata. Finalizing backup. Backup complete! 已备份完成~~ 1.5 vertica Hard Link Local备份 官方文档： A hard link local backup is a full or object-level backup consisting of a complete copy of the database catalog, and a set of hard file links to corresponding data files. 直接修改全备份的配置文件即可，加上一行：
[Transmission] encrypt = False checksum = False port_rsync = 50000 serviceAccessUser = None total_bwlimit_backup = 0 concurrency_backup = 1 total_bwlimit_restore = 0 concurrency_restore = 1 hardLinkLocal = True 然后还是执行备份命令：
/opt/vertica/bin/vbr.py --task backup --config-file HardLinkLocal_backup20160505.ini [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task backup --config-file HardLinkLocal_backup20160505.ini Starting backup of database Windy1990. Participating nodes: v_windy1990_node0001. Snapshotting database. Snapshot complete. Approximate bytes to copy: 0 of 60269355 total. [==================================================] 100% Copying backup metadata. Finalizing backup. Backup complete! 1.6 查看备份 查看备份的内容：
/opt/vertica/bin/vbr.py --task listbackup --config-file HardLinkLocal_backup20160505.ini [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task listbackup --config-file HardLinkLocal_backup20160505.ini backup epoch objects hosts(nodes) file_system_type full_backup20160505_20160505_074850 16 v_windy1990_node0001(192.168.231.110) [Linux] full_backup20160505_20160505_064640 16 v_windy1990_node0001(192.168.231.110) [Linux] 在vertica数据库中可以查询到备份的信息：
dbadmin=> select * from database_backups; backup_timestamp | node_name | snapshot_name | backup_epoch | node_count | file_system_type | objects -------------------------------+----------------------+-----------------------+--------------+------------+------------------+------------- 2016-05-05 14:08:03.369797+08 | v_windy1990_node0001 | full_backup20160505 | 15 | 1 | [Linux] | 2016-05-05 14:36:39.496688+08 | v_windy1990_node0001 | full_backup20160505 | 15 | 1 | [Linux] | 2016-05-05 14:42:35.517465+08 | v_windy1990_node0001 | full_backup20160505 | 15 | 1 | [Linux] | 2016-05-05 14:44:43.043536+08 | v_windy1990_node0001 | full_backup20160505 | 15 | 1 | [Linux] | 2016-05-05 14:46:57.958863+08 | v_windy1990_node0001 | full_backup20160505 | 16 | 1 | [Linux] | 2016-05-05 15:03:19.580159+08 | v_windy1990_node0001 | backup_object_tb_serv | 16 | 1 | [Linux] | tb_serv_num 2016-05-05 15:50:53.143446+08 | v_windy1990_node0001 | full_backup20160505 | 16 | 1 | [Linux] | (7 rows) 2、vertica恢复
2.1 vertica恢复
为了看到是恢复是有用的，再次我破坏一下数据，drop一个表，删除一条数据
dbadmin=> select * from tb_rp_st_user_day ; serv_number | user_name -------------+----------- (0 rows) dbadmin=> drop table tb_rp_st_user_day; DROP TABLE dbadmin=> select * from tb_serv_num where name='ss'; serv_num | name -------------+------ 186371***** | ss (1 row) dbadmin=> delete from tb_serv_num where name='ss'; OUTPUT -------- 1 (1 row) dbadmin=> commit; COMMIT 开始执行恢复命令：
/opt/vertica/bin/vbr.py --task restore --config-file full_backup20160505.ini [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file full_backup20160505.ini Error: Full database restore requires the nodes be DOWN. Restore FAILED. 报错啦~ 提示只有在vertica数据库down的状态下才能恢复……
查看官方文档：
To restore a full database backup, you must ensure that: • 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 cluster to which you are restoring the backup has the same number of hosts as the one used to create the backup. The node names and the IP addresses must also be identical. • The database you are restoring must already exist on the cluster to which you are restoring data. The database can be completely empty without any data or schema. As long as the database name matches the name in the backup, and all of the node names match the names of the nodes in the configuration file, you can restore to it. • The user performing the restore is the database administrator. 所以此处，我们先尝试第二种恢复方式（对象级恢复）来恢复数据，刚刚我们同时对tb_serv_num表做了对象级备份：
执行对象级恢复：
/opt/vertica/bin/vbr.py --task restore --config-file backup_object_tb_serv.ini [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file backup_object_tb_serv.ini Starting object restore of database Windy1990. Participating nodes: v_windy1990_node0001. Objects to restore: tb_serv_num. Restoring from restore point: backup_object_tb_serv_20160505_070249 Loading snapshot catalog from backup. Syncing data from backup to cluster nodes. [==================================================] 100% Finalizing restore. Restore complete! OK~看到上面我们已经恢复成功了，那就登上vertica数据库验证一下数据是否恢复了。
dbadmin=> select * from tb_serv_num ; serv_num | name -------------+------- 138***** | windy 186***** | ss 134***** | qin** (3 rows) 可以看到，数据已恢复了~，等等，刚才我们还drop了一个 表，这个表是不是恢复了呢？
dbadmin=> d tb_rp_st_user_day; Did not find any relation. 可以看到，表并没有恢复，为什么呢？ 要知道我们此次恢复为对象级恢复，我们对象级备份是只备份了tb_serv_num表的数据而已，随意恢复也只能恢复该表的所有数据。 那么我们用第三种Hard Link Local方式恢复数据：
/opt/vertica/bin/vbr.py --task restore --config-file HardLinkLocal_backup20160505.ini [dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file HardLinkLocal_backup20160505.ini Error: Full database restore requires the nodes be DOWN. Restore FAILED. 同样看到，也需要vertica数据库为DOWN
看来要停掉vertica数据库了~（停掉数据库，此处略）
停掉vertica数据库后，再试全量恢复：
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task restore --config-file full_backup20160505.ini Starting full restore of database Windy1990. Participating nodes: v_windy1990_node0001. Restoring from restore point: full_backup20160505_20160505_074850 Computing the size of data to be synced from backup to cluster nodes. Syncing data from backup to cluster nodes. [==================================================] 100% Restoring catalog. Restore complete! 可以看到，恢复的还不错。
再重新启动vertica，登上vertica后查看表tb_rp_st_user_day是否恢复了：
dbadmin=> d tb_rp_st_user_day; List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+-------------------+-------------+-------------+------+---------+----------+-------------+------------- public | tb_rp_st_user_day | serv_number | varchar(13) | 13 | | f | f | public | tb_rp_st_user_day | user_name | varchar(50) | 50 | | f | f | (2 rows) 可以看到，恢复成功了。
以上就是vertica备份以及简单的恢复。
