最近在使用vertica吼渡，上網(wǎng)找了很多資料都沒有蛤肌，只有自己看官方文檔動(dòng)手搞一下了业簿，今天搞了vertica的備份與恢復(fù) 以下是整理的過程瘤礁，分享給大家，如有問題歡迎大家指正~ 可加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.
備份成功后查看備份的文件內(nèi)容如下：
[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
備份中我選了一個(gè)文件保存密碼：
/home/dbadmin/password
里面存有數(shù)據(jù)路的密碼：
[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.
執(zhí)行備份命令：
[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.
發(fā)現(xiàn)報(bào)錯(cuò)了柜思，根據(jù)提示，必須先初始化路徑（個(gè)人理解的就是先聲明路徑）
[dbadmin@localhost ~]$ /opt/vertica/bin/vbr.py --task init --config-file full_backup20160505.ini
Initializing backup locations.
Backup locations initialized.
路徑聲明成功巷燥！ 查看該備份路徑下多了一個(gè)文件赡盘。
[dbadmin@localhost backup]$ ls -l
total 4
-rw-r--r--. 1 dbadmin verticadba 22 May 5 13:47 backup_manifest
然后再次執(zhí)行備份命令：
/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!
我們可以看到，已經(jīng)完全備份了vertica數(shù)據(jù)庫缰揪。 此時(shí)我們應(yīng)該可以猜到陨享，備份的數(shù)據(jù)存在我們剛剛聲明的路徑/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個(gè)文件夾 /Objects / Snapshots 我們打開Objects文件夾可以看到，我們的vertica數(shù)據(jù)庫文件就在該目錄下钝腺。 /home/dbadmin/backup/Snapshots/full_backup20160505_20160505_060600/v_windy1990_node0001目錄下存放著我們備份的原vertica數(shù)據(jù)庫的一下信息抛姑。
檢查每個(gè)節(jié)點(diǎn)的數(shù)據(jù)流：
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拍屑，在這里我把增量單獨(dú)列了出來途戒。 restorePointLimit控制著增量備份集的數(shù)量 我這只有一個(gè)節(jié)點(diǎn)，剛在配置備份文件的時(shí)候設(shè)置restorePointLimit = 1 再次執(zhí)行1.2中的全備份命令即可實(shí)現(xiàn)增量備份~
[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!
有人會(huì)問僵驰，我怎么知道這樣就是增量備份而不是全量備份的呢喷斋？？ 這個(gè)問題很好蒜茴，我備份的時(shí)候也在想星爪，備份完成后，我再次打開了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目錄的時(shí)間顽腾，因?yàn)樵诖诉^程中我沒有對(duì)vertica數(shù)據(jù)庫進(jìn)行操作，所以vertica數(shù)據(jù)庫文件還是原來的就沒有更新诺核。 所以想要知道上面我們操作的是增量的其實(shí)很簡單抄肖，我在vertica數(shù)據(jù)庫中的任意一張表中插入一條數(shù)據(jù)，然后我再執(zhí)行上面的備份腳本窖杀。 我插入一條手機(jī)號(hào)：
dbadmin=> insert into tb_serv_num values('13488888888','qin**');
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> \q
然后重新執(zhí)行備份語句漓摩，執(zhí)行后查看backup文件下的內(nèi)容：
[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
看文件時(shí)間，是不是說明我們上面的推理是正確的呢~~~ 所以入客，不要懷疑了管毙，我們的增量備份是沒有問題的腿椎！ 1.4 vertica對(duì)象級(jí)備份 官網(wǎng)文檔： 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. 其實(shí)對(duì)象級(jí)備份和全量備份差不多，知識(shí)在生成備份配置文件時(shí)有所不同（注意不同之處）：
[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.
然后在執(zhí)行備份命令：
[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
然后還是執(zhí)行備份命令：
/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 查看備份 查看備份的內(nèi)容：
/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數(shù)據(jù)庫中可以查詢到備份的信息：
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恢復(fù)
2.1 vertica恢復(fù)
為了看到是恢復(fù)是有用的，再次我破壞一下數(shù)據(jù)卓舵，drop一個(gè)表南用，刪除一條數(shù)據(jù)
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
開始執(zhí)行恢復(fù)命令：
/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.
報(bào)錯(cuò)啦~ 提示只有在vertica數(shù)據(jù)庫down的狀態(tài)下才能恢復(fù)……
查看官方文檔：
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.
所以此處，我們先嘗試第二種恢復(fù)方式（對(duì)象級(jí)恢復(fù)）來恢復(fù)數(shù)據(jù)边器，剛剛我們同時(shí)對(duì)tb_serv_num表做了對(duì)象級(jí)備份：
執(zhí)行對(duì)象級(jí)恢復(fù)：
/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~看到上面我們已經(jīng)恢復(fù)成功了训枢，那就登上vertica數(shù)據(jù)庫驗(yàn)證一下數(shù)據(jù)是否恢復(fù)了。
dbadmin=> select * from tb_serv_num ;
serv_num | name
-------------+-------
138***** | windy
186***** | ss
134***** | qin**
(3 rows)
可以看到忘巧，數(shù)據(jù)已恢復(fù)了~恒界，等等，剛才我們還drop了一個(gè) 表砚嘴，這個(gè)表是不是恢復(fù)了呢十酣？
dbadmin=> \d tb_rp_st_user_day;
Did not find any relation.
可以看到，表并沒有恢復(fù)际长，為什么呢耸采？ 要知道我們此次恢復(fù)為對(duì)象級(jí)恢復(fù)，我們對(duì)象級(jí)備份是只備份了tb_serv_num表的數(shù)據(jù)而已工育，隨意恢復(fù)也只能恢復(fù)該表的所有數(shù)據(jù)虾宇。 那么我們用第三種Hard Link Local方式恢復(fù)數(shù)據(jù)：
/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數(shù)據(jù)庫為DOWN
看來要停掉vertica數(shù)據(jù)庫了~（停掉數(shù)據(jù)庫如绸，此處略）
停掉vertica數(shù)據(jù)庫后嘱朽，再試全量恢復(fù)：
[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!
可以看到，恢復(fù)的還不錯(cuò)怔接。
再重新啟動(dòng)vertica搪泳，登上vertica后查看表tb_rp_st_user_day是否恢復(fù)了：
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)
可以看到，恢復(fù)成功了扼脐。
以上就是vertica備份以及簡單的恢復(fù)岸军。
