Creating Checkpoint Locations
Checkpoint locations are created using the 'with CLOUD_STORAGE=(<api>,<bucket>,<config>)' clause in the 'create location' SQL statement. CLOUD_STORAGE values:
Area and usage clauses are also needed in this statement, see
Setting up a Cloud Checkpoint Location for correct checkpoint location creation.
The parameters need to be divided by semicolons in the <config> string. See the list of used parameters below:
S3 parameters:
• endpoint - The location where the Cloud storage is accessed, written in URL format.
• provider - s3 provider name.
• region - Region of the bucket being accessed (if necessary).
Examples:
CLOUD_STORAGE=('s3', 'backup-bucket', 'provider=AWS;endpoint=http://s3.us-east-1.amazonaws.com;region=us-east-1')
CLOUD_STORAGE=('s3', 'backup-bucket', 'provider=Minio;endpoint=http://11.111.11.11:1111/')
CLOUD_STORAGE=('s3', 'backup-bucket', 'provider=GCS;endpoint=https://storage.googleapis.com;region=us-east1')
Azure Blob Storage parameters:
type - set to 'azureblob'.
endpoint - set to: https://{account}.blob.core.windows.net/ where {account} is your azure account name.
Example:
CLOUD_STORAGE=('abs','backup-bucket','type=azureblob;endpoint=https://azureaccountname.blob.core.windows.net/')
bucket_namespace:
Acts as a suffix to your bucket name, which allows you to separate items in the cloud provider. If the bucket_namespace set to "area1" for the location, all the objects for this location would be stored (assuming s3) in:s3ckp:<bucket name>/area1
You can add this as a parameter to both S3 and ABS <config> strings.
To read or write data into the cloud provider's bucket, rclone must have the storage credentials for the bucket. The "access_key" and "secret_access_key" authorization strings are used to access the bucket.
To avoid exposing these strings in process parameters or database utilities, they are set using the following environment variables:
S3:
RCLONE_CONFIG_S3CKP_ACCESS_KEY_ID={ACCESS_KEY}
RCLONE_CONFIG_S3CKP_SECRET_ACCESS_KEY={SECRET_KEY}
VW_AWS_BACKUP_ACCESS_KEY={ACCESS_KEY}
VW_AWS_BACKUP_SECRET_KEY={SECRET_KEY}
VW_AWS_BACKUP_ENDPOINT={ENDPOINT}
Note: The access keys and secret keys must be consistent between this configuration and the <config> above, and verify that the endpoint is the same.
Note: Environment variables referencing AWS refer to the S3 protocol. These variables can be applied to any S3 compatible storage provider.
Azure Blob Storage:
Azure OAuth 2.0 Authorization:
VW_AZURE_BACKUP_CREDENTIAL_TYPE=AZURE_OAUTH2_CLIENT
VW_AZURE_BACKUP_CLIENT_ENDPOINT=https://login.microsoftonline.com/{TENANT}/oauth2/token
VW_AZURE_BACKUP_CLIENT_ID={CLIENT_ID}
VW_AZURE_BACKUP_CLIENT_SECRET={SECRET}
RCLONE_AZUREBLOB_TENANT={TENANT}
RCLONE_AZUREBLOB_CLIENT_ID={CLIENT_ID}
RCLONE_AZUREBLOB_CLIENT_SECRET={SECRET}
Azure Shared Key Authorization:
VW_AZURE_BACKUP_CREDENTIAL_TYPE=AZURE_SHARED_KEY
VW_AZURE_BACKUP_CLIENT_ID={ACCOUNT}
VW_AZURE_BACKUP_CLIENT_SECRET={SECRET}
RCLONE_AZUREBLOB_CLIENT_ID={ACCOUNT}
RCLONE_AZUREBLOB_KEY={SECRET}
Note: The environment variables must be set in the local environment when you start Vector so running Vector processes will have the values stored in memory.
Checkpoint and rollforwarddb run rclone to copy the data.
Note: The environment variables must be set before running the checkpoint or rollforwarddb.
When a rollforwardb or ckpdb is run against a database, a new file (rclone.<database>.conf) is created in the II_SYSTEM/ingres/files directory. This file contains the CLOUD_STORAGE parameters for the database cloud checkpoint location (except the bucket).
The rclone configuration file is used for all the rclone operations for the database. You can use this file for debugging purposes and can be used with rclone with the --config=<file> option.
Note: The recovery process from cloud backups relies on streaming data through rclone. On GCP and AWS this is a multi-threaded operation, but on Azure this is a single-threaded operation. This makes a recovery from cloud backups on Azure more time consuming than on GCP and AWS. Rclone support is aware of this performance limitation.
Setting up a Cloud Checkpoint Location
Follow these steps for setting up a cloud checkpoint location:
1. Install rclone.
Linux:
rclone must exist in the path used by the Vector server, and in the path used for running checkpoints or rollforwards.
Windows:
On Windows, rclone.exe must be in %II_SYSTEM%\ingres\bin.
Note: Cloud checkpoints use rclone to copy files to the cloud location. It is recommended to first run rclone with the intended configuration to make sure it runs without errors. Also, the version of rclone on the machine might need to update because rclone will require the use of the --s3-no-check-bucket flag.
2. Set the following storage credential variables of the bucket of your provider:
S3:
RCLONE_CONFIG_S3CKP_ACCESS_KEY_ID={ACCESS_KEY}
RCLONE_CONFIG_S3CKP_SECRET_ACCESS_KEY={SECRET_KEY}
VW_AWS_BACKUP_ACCESS_KEY={ACCESS_KEY}
VW_AWS_BACKUP_SECRET_KEY={SECRET_KEY}
VW_AWS_BACKUP_ENDPOINT={ENDPOINT}
Azure OAuth 2.0 Authorization:
VW_AZURE_BACKUP_CREDENTIAL_TYPE=AZURE_OAUTH2_CLIENT
VW_AZURE_BACKUP_CLIENT_ENDPOINT=https://login.microsoftonline.com/{TENANT}/oauth2/token
VW_AZURE_BACKUP_CLIENT_ID={CLIENT_ID}
VW_AZURE_BACKUP_CLIENT_SECRET={SECRET}
RCLONE_AZUREBLOB_TENANT={TENANT}
RCLONE_AZUREBLOB_CLIENT_ID={CLIENT_ID}
RCLONE_AZUREBLOB_CLIENT_SECRET={SECRET}
Azure Shared Key Authorization:
VW_AZURE_BACKUP_CREDENTIAL_TYPE=AZURE_SHARED_KEY
VW_AZURE_BACKUP_CLIENT_ID={ACCOUNT}
VW_AZURE_BACKUP_CLIENT_SECRET={SECRET}
RCLONE_AZUREBLOB_CLIENT_ID={ACCOUNT}
RCLONE_AZUREBLOB_KEY={SECRET}
For example:
When using MinIO as an s3 server with the default passwords:
Linux:
export RCLONE_CONFIG_S3CKP_ACCESS_KEY_ID=minioadmin
export RCLONE_CONFIG_S3CKP_SECRET_ACCESS_KEY=minioadmin
export VW_AWS_BACKUP_ACCESS_KEY=minioadmin
export VW_AWS_BACKUP_SECRET_KEY=minioadmin
export VW_AWS_BACKUP_ENDPOINT=http://11.111.11.11:1111/
Windows:
set RCLONE_CONFIG_S3CKP_ACCESS_KEY_ID=minioadmin
set RCLONE_CONFIG_S3CKP_SECRET_ACCESS_KEY=minioadmin
set VW_AWS_BACKUP_ACCESS_KEY=minioadmin
set VW_AWS_BACKUP_SECRET_KEY=minioadmin
set VW_AWS_BACKUP_ENDPOINT=http://11.111.11.11:1111/
When using Azure Blob Storage with sample authentication data:
Linux:
export VW_AZURE_BACKUP_CREDENTIAL_TYPE=AZURE_OAUTH2_CLIENT
export VW_AZURE_BACKUP_CLIENT_ENDPOINT=https://login.microsoftonline.com/11111111-2222-3333-4444-555555555555/oauth2/token
export VW_AZURE_BACKUP_CLIENT_ID=55555555-6666-7777-8888-999999999999
export VW_AZURE_BACKUP_CLIENT_SECRET=DDDDddd-B-654863GgggAAAA.jjj4.9.7p
export RCLONE_AZUREBLOB_TENANT=11111111-2222-3333-4444-555555555555
export RCLONE_AZUREBLOB_CLIENT_ID=55555555-6666-7777-8888-999999999999
export RCLONE_AZUREBLOB_CLIENT_SECRET=DDDDddd-B-654863GgggAAAA.jjj4.9.7p
Windows:
set VW_AZURE_BACKUP_CREDENTIAL_TYPE=AZURE_OAUTH2_CLIENT
set VW_AZURE_BACKUP_CLIENT_ENDPOINT=https://login.microsoftonline.com/11111111-2222-3333-4444-555555555555/oauth2/token
set VW_AZURE_BACKUP_CLIENT_ID=55555555-6666-7777-8888-999999999999
set VW_AZURE_BACKUP_CLIENT_SECRET=DDDDddd-B-654863GgggAAAA.jjj4.9.7p
set RCLONE_AZUREBLOB_TENANT=11111111-2222-3333-4444-555555555555
set RCLONE_AZUREBLOB_CLIENT_ID=55555555-6666-7777-8888-999999999999
set RCLONE_AZUREBLOB_CLIENT_SECRET=DDDDddd-B-654863GgggAAAA.jjj4.9.7p
Note: The authentication data DDDDddd-B-654863GgggAAAA.jjj4.9.7p, 55555555-6666-7777-8888-999999999999, 11111111-2222-3333-4444-555555555555 specified here are for example purposes and must be replaced with the correct values.
3. Set the RCLONE environment variables for your Windows service:
a. In the Command Prompt, type regedit and press Enter to open the Windows registry.
b. Open: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
c. Select Services from the dropdown, open the key for your Vector Installation. (The key will be named 'Actian Vector_Database_XX' where XX is the installation code of your Vector installation).
d. In the right panel, select New, Multi-String Value. Then use the following:
• Name: Environment. Then double-click on the new Multi-String Value.
For Value Data, add the credential variables set in Step 2. For example, for an S3 provider set:
• RCLONE_CONFIG_S3CKP_ACCESS_KEY_ID={ACCESS_KEY}
• RCLONE_CONFIG_S3CKP_SECRET_ACCESS_KEY={SECRET_KEY}
• VW_AWS_BACKUP_ACCESS_KEY={ACCESS_KEY}
• VW_AWS_BACKUP_SECRET_KEY={SECRET_KEY}
• VW_AWS_BACKUP_ENDPOINT={ENDPOINT}
4. Confirm if the following rclone settings are set to access the cloud bucket:
a. Create an rclone configuration file with appropriate rclone parameters.
Example configuration sets: note endpoints/regions must be set appropriately.
MinIO:
[s3ckp]
type = s3
provider = Minio
endpoint = http://127.0.0.1:9999/
Google Cloud Services:
[s3ckp]
type = s3
provider = GCS
endpoint = https://storage.googleapis.com
region = us-east1
Amazon Web Services:
[s3ckp]
type = s3
provider = AWS
endpoint = http://s3.us-east-1.amazonaws.com
region = us-east-1
Azure Blob Storage:
[absckp]
type = azureblob
account = azureaccountname
b. Test rclone:
Assume the test configuration is named ‘test.conf’ and check that every parameter in ‘test.config’ (except ‘type=’) is in the csconfig of your cloud location. Run the following commands:
S3:
rclone ls s3ckp:<bucket> --config=test.conf
Azure Blob Storage:
rclone ls absckp:<bucket> --config=test.conf
where <bucket> is the name of the storage bucket.
Example:
rclone ls s3ckp:backupbucket --config=test.conf
Verify your access keys for any authorization errors. For any other status errors, verify the status of your bucket with your cloud provider.
5. Start Vector.
IMPORTANT! To avoid any authorization errors in the Vector processes while running rclone, the storage credential environment variables must be set correctly.
6. Create a Cloud checkpoint location by running the following query in iidbdb:
create location <location name> with area = 'II_CHECKPOINT', usage=(CHECKPOINT), CLOUD_STORAGE=(<api>, <bucket>, <config>)
Note: The values for <api>, <bucket>, and <config> should be set to the appropriate values for your cloud provider.
7. Assign the checkpoint location to an existing database using relocatedb, or create a database using this checkpoint location:
• createdb -cnewckp testdb or
• createdb -cnewckp testdb -no_x100 to enable journaling.
8. Checkpoint the database.
Note: The RCLONE storage credential variables must be set in the environment.
When you checkpoint the database, the checkpoint uses the rclone rcat command to store a compressed tar file of a database location. Settings in the II_SYSTEM/ingres/files/cktmpl.def entries will cause rclone to use a chunk-size of 220 MB. Therefore, rclone can copy a compressed tar file of up to 2.1 TB. You can lower the chunk-size, which will reduce the amount of memory rclone uses, but it might cause the rclone to fail if the tar file is too big. If you need to checkpoint a tar file larger than 2 TB, you can increase the chunk-sizing, but this increases the amount of memory used.