Backup to amazon s3 failing at parsing password !
HI, I am trying to set up vertica backup to s3 and i am following this link
https://my.vertica.com/blog/whats-new-vertica-8-1-1-backup-amazon-s3/
Vertica version:
vertica-8.1.1-3.x86_64
However i am getting an error
Error: When loading config: File contains parsing errors: <???>
[line 58]: ""
Backup FAILED.
I have created a text file in /home/dbadmin/pw.txt and given owner and group same as my vertica.
my pw.txt looks like
somepasswordwithspecialcharacters
i have tried it with double quotes, single quotes and also without any quotes .But not sure how to solve this error. Please help
**my s3bak.ini looks like **
[S3]
; This section replaces the [Mapping] section and is required to back up to S3
; !!Mandatory!! S3 bucket name(no default).
s3_backup_path = s3://bucketname
; !!Mandatory!! directory used to manage locking during a backup (no default). If the directory is mounted on the initiator host, you should use "[]" instead of the local host name. The file system must support POSIX fcntl flock.
s3_backup_file_system_path = []:/vertica/data/
;s3_backup_file_system_path = []:/home/dbadmin/backup_locks_dir/
;s3_backup_file_system_path = /vertica/data
; Specifies SSL encrypted transfer.
; s3_encrypt_transport = True
; Specifies the number of threads for upload/download - backup
; s3_concurrency_backup = 10
; Specifies the number of threads for upload/download - restore
; s3_concurrency_restore = 10
[Misc]
; !!Recommended!! Snapshot name
; Valid values: a-z A-Z 0-9 - _
; snapshotName = backup_snapshot
[Database]
; !!Recommended!! If you have more than one database defined on this Vertica cluster, use this parameter to specify which database to backup/restore.
dbName = bi
; If this parameter is True, vbr prompts the user for the database password every time.
; If False, specify the location of password config file in 'passwordFile' parameter in [Misc] section.
dbPromptForPassword = False
; ------------------------------------------- ;
;;; ADVANCED PARAMETERS ;;;
; ------------------------------------------- ;
[Misc]
; The temp directory location on all database hosts.
; The directory must be readable and writeable by the dbadmin, and must implement POSIX style fcntl lockf locking.
; tempDir = /tmp/vbr
; Specifies the number of historical backups to retain in addition to the most recent backup.
; 1 current + n historical backups
; restorePointLimit = 1
; Full path to the password configuration file
; Store this file in directory readable only by the dbadmin.
; (no default)
passwordFile = /home/dbadmin/pw.txt
[Database]
; Vertica user name for vbr to connect to the database.
; This is rarely needed since dbUser is normally identical to the database administrator
; dbUser = current_username
Comments
why is the password being put in pw.txt. backup uses the boto libraries, which read the aws access key and aws secret key in .aws/config. very similar to aws cli configuration.
is the aws cli working?
yes aws cli is working,my access key and secret access key is .aws/credentials.
i have followed the sample configuration
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/BackupRestore/SampleConfigFiles/s3_backup_restore.htm
In there it is mentioned as a pw.txt file so i have put the database password in a text file.Can you please let me know how i should pass the database password.
can you give me 2 things.
the pw.txt file. change the password such that i can only see the special characters.
for example, if you password is hello$world=, maybe you can send me google$yahoo=. so that i can try with those special characters without knowing you password.
can you run vbr with --debug=3 option and email me the most recently created logs file in /tmp/vbr
I think i may be able to provide the correct quote/escape sequence to get around this error
the password is something aNQ^23PEneST
2017-11-02 21:35:07 localhost vbr VBR log initialized.
2017-11-02 21:35:07 localhost vbr Command line: /opt/vertica/bin/vbr --task backup --config-file /vertica/data/bi/v_bi_node0002_catalog/s3bak.ini
2017-11-02 21:35:07 localhost vbr Getting cluster information.
2017-11-02 21:35:08 localhost vbr Getting information for database: bi.
2017-11-02 21:35:09 localhost vbr Checking node connectivity.
2017-11-02 21:35:10 localhost vbr All nodes reachable.
2017-11-02 21:35:10 localhost vbr Making temp directories.
2017-11-02 21:35:10 localhost vbr Locking nodes.
2017-11-02 21:35:10 localhost vbr Locked nodes.
2017-11-02 21:35:11 localhost vbr Starting backup of database bi.
2017-11-02 21:35:11 localhost vbr Participating nodes: v_bi_node0001, v_bi_node0002, v_bi_node0003.
2017-11-02 21:35:11 localhost vsql /opt/vertica/bin/vsql -q -t -X -p5433 -h.***..**
2017-11-02 21:35:11 localhost vsql Error reading from vsql: vsql: FATAL 3781: Invalid username or password
2017-11-02 21:35:11 localhost vbr Error: SQL command "select name,catalogpath from v_internal.vs_nodes;" failed: vsql: FATAL 3781: Invalid username or password
Backup FAILED.
is the host name starred out for obfuscation or is it like that in the file.
are you running vbr as dbadmin.
remove the password file and just set an env variable like this, that might get you past this
[skeswani@skeswani ~] [@:/]$ export PGPASSWORD='aNQ^23PEneST'
[skeswani@skeswani ~] [@:/]$ vsql -q -t -X -p5433 -h localhost
yes i am running as a dbadmin but , if i set dbPromptForPassword = True
and manually give the password its working,
our main goal is to have it automated like running the backup as a systemctl job.
i did try with export PGPASSWORD='aNQ^23PEneST' and commented the
;dbPromptForPassword = False
;passwordFile = /home/dbadmin/pw.txt
i am getting an error
Error: SQL command "select name,catalogpath from v_internal.vs_nodes;" failed: vsql: FATAL 3781: Invalid username or password
Backup FAILED.
i also tried with without commenting dbPromptForPassword = False
same error.
I put a carret ^ in the password and it worked just fine....
[skeswani@skeswani ~] [@:/]$ /opt/vertica/bin/vbr --task init --config-file backup_snapshot.ini
Initializing backup locations.
Backup locations initialized.
[skeswani@skeswani ~] [@:/]$ /opt/vertica/bin/vbr --task backup --config-file backup_snapshot.ini
Starting backup of database testdb.
Participating nodes: v_testdb_node0001.
Snapshotting database.
Snapshot complete.
Approximate bytes to copy: 296746083 of 296746083 total.
[==================================================] 100%
Copying backup metadata.
Finalizing backup.
Backup complete
[skeswani@skeswani ~] [@:/]$ cat backup_snapshot.ini
[Misc]
snapshotName = backup_snapshot
restorePointLimit = 1
objectRestoreMode = createOrReplace
passwordFile = /home/skeswani/pw.txt
[Database]
dbName = testdb
dbUser = skeswani
;[Mapping]
;v_test1_node0001 = localhost:/vertica/
[S3]
; This section replaces the [Mapping] section and is required to back up to s3
; !!Mandatory!! S3 bucket name(no default).
s3_backup_path = s3://skeswani/database_backup_path/
; !!Mandatory!! directory used to manage locking during a backup (no default). If the directory is mounted on the initiator host, you should use "[]" instead of the local host name. The file system must support POSIX fcntl flock.
s3_backup_file_system_path = []:/home/skeswani
[skeswani@skeswani ~] [@:/]$ cat pw.txt
[Passwords]
dbPassword = let^in
[skeswani@skeswani ~] [@:/]$ aws s3 ls s3://skeswani/database_backup_path/
PRE Objects/
2017-11-06 12:38:20 108 .identity
2017-11-06 12:38:28 2 .index
2017-11-06 12:38:21 22 backup_manifest
@skeswani Thank you so much, Now it works perfect.
the issue was i did not follow the correct was for passing the pw.txt ..
skeswani@skeswani ~] [@:/]$ cat pw.txt
[Passwords]
dbPassword = let^in
i used like this and hence it was not working correctly.
cat pw.txt
let^in
@skeswani suddenly the backups are failing with the below error please help me to solve this, Thanks!
2018-05-14 14:04:35 localhost vbr Old restore points to be removed: 20171106_191224
2018-05-14 14:04:39 localhost vbr Approximate bytes to copy: 350062619 of 67528612898 total.
2018-05-14 14:05:30 localhost vbr Copying backup metadata.
2018-05-14 14:05:33 localhost vbr Finalizing backup.
2018-05-14 14:05:33 localhost vbr Location s3://****************/: putting updated backup manifest.
2018-05-14 14:05:33 localhost vbr Deleting old restore points.
2018-05-14 14:05:35 localhost vbr Deleting restore point metadata.
2018-05-14 14:05:35 localhost vbr Traceback (most recent call last):
File "/opt/vertica/bin/vbr", line 8971, in main
if vbr_task.run():
File "/opt/vertica/bin/vbr", line 4325, in run
return self._run()
File "/opt/vertica/bin/vbr", line 5485, in _run
Parallel.foreach(delete_snap_metadata, old_backup_node_timestamps.items())
File "/opt/vertica/bin/vbr", line 8656, in foreach
cls.map(func, iterable, threads_num=threads_num)
File "/opt/vertica/bin/vbr", line 8635, in map
if not thr.join(Parallel.WAIT_QUANTUM):
File "/opt/vertica/bin/vbr", line 8611, in join
return self._thr.join(*args, **kwargs)
File "/opt/vertica/bin/vbr", line 8565, in run
super(Parallel.CancellableThread, self).run()
File "/opt/vertica/oss/python/lib/python2.7/threading.py", line 763, in run
self.__target(*self.__args, **self.__kwargs)
File "/opt/vertica/bin/vbr", line 8599, in do_run
self._result = func(val)
File "/opt/vertica/bin/vbr", line 5478, in delete_snap_metadata
backup_access.delete_all(metadata_dir)
File "/opt/vertica/bin/vbr", line 3009, in delete_all
self._delete_all(remote_prefix)
File "/opt/vertica/bin/vbr", line 3185, in _delete_all
self._delete_many(remote_prefix, self._list_prefix_recursive(remote_prefix))
File "/opt/vertica/bin/vbr", line 3117, in _list_prefix_recursive
assert basepath.endswith(prefix)
AssertionError