Vertica的这些事<十六>—— vertica备份与恢复

简介: 最近在使用vertica,上网找了很多资料都没有,只有自己看官方文档动手搞一下了,今天搞了vertica的备份与恢复 以下是整理的过程,分享给大家,如有问题欢迎大家指正~ 可以发邮件到 windyqinchaofeng@163.com 也可加QQ群交流:4121914941、vertica备份1.1 vertica备份配置:[dbadmin@local

最近在使用vertica,上网找了很多资料都没有,只有自己看官方文档动手搞一下了,今天搞了vertica的备份与恢复 以下是整理的过程,分享给大家,如有问题欢迎大家指正~ 可以发邮件到 windyqinchaofeng@163.com 也可加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备份以及简单的恢复。

通过此次vertica恢复可以看到,很多问题可以在官方文档中找到解决方法,在停掉vertica和启动时,遇到了一点问题,不过根据错误提示可以看出解决方法的。

目录
相关文章
|
SQL 分布式计算 运维
【大数据开发运维解决方案】Sqoop增量同步mysql/oracle数据到hive(merge-key/append)测试文档
上一篇文章介绍了sqoop全量同步数据到hive, 本片文章将通过实验详细介绍如何增量同步数据到hive,以及sqoop job与crontab定时结合无密码登录的增量同步实现方法。
【大数据开发运维解决方案】Sqoop增量同步mysql/oracle数据到hive(merge-key/append)测试文档
|
SQL 运维 Oracle
【大数据开发运维解决方案】Sqoop增量同步Oracle数据到hive:merge-key再次详解
这篇文章是基于上面连接的文章继续做的拓展,上篇文章结尾说了如果一个表很大。我第一次初始化一部分最新的数据到hive表,如果没初始化进来的历史数据今天发生了变更,那merge-key的增量方式会不会报错呢?之所以会提出这个问题,是因为笔者真的有这个测试需求,接下来先对oracle端的库表数据做下修改,来模拟这种场景。
【大数据开发运维解决方案】Sqoop增量同步Oracle数据到hive:merge-key再次详解
|
SQL 分布式计算 运维
【大数据开发运维解决方案】sqoop增量导入oracle/mysql数据到hive时时间字段为null处理
前面几篇文章详细介绍了sqoop全量增量导入数据到hive,大家可以看到我导入的数据如果有时间字段的话我都是在hive指定成了string类型,虽然这样可以处理掉时间字段在hive为空的问题,但是在kylin创建增量cube时需要指定一个时间字段来做增量,所以上面那种方式不行,这里的处理方式为把string改成timestamp类型,看实验:
【大数据开发运维解决方案】sqoop增量导入oracle/mysql数据到hive时时间字段为null处理
|
编解码 Oracle 关系型数据库
【大数据实时数据同步】GoldenGate实时同步异常:OGG-03533:character ‘c2 a0‘ at offset 0 that is not available报错解决
博主所在单位目前使用Oracle GoldenGate将各个业务生产库汇聚到一起做数仓实时ODS平台,源端库可能涉及Oracle、Mysql、达梦、Guassdb库。 最近遇到了这个错误:`OGG-03533:character 'c2 a0' at offset 0 that is not available。` 导致报错的原因是源端库有业务人员手动用excel往数据库导入数据,源端库字符集:`AMERICAN_AMERICA.AL32UTF8` ,目标库ODS字符集:`AMERICAN_AMERICA.ZHS16GBK`。
【大数据实时数据同步】GoldenGate实时同步异常:OGG-03533:character ‘c2 a0‘ at offset 0 that is not available报错解决
|
SQL NoSQL Oracle
Flink CDC 2.3 发布,持续优化性能,更多连接器支持增量快照,新增 Db2 支持
Flink CDC 2.3 发布,新增 Db2 数据源,MongoDB CDC 和 Oracle CDC支持增量快照,MySQL CDC 性能大幅提升
Flink CDC 2.3 发布,持续优化性能,更多连接器支持增量快照,新增 Db2 支持
Vertica的这些事<十五>—— Vertica备份元数据信息
—备份资源池 SELECT 'CREATE RESOURCE POOL ' || name || CASE WHEN memorysize IS NULL THEN ' ' ELSE ' MEMORYSIZE ' || '''' || memorysize
1481 0

热门文章

最新文章