With IBM Netezza, there are many ways to pull data from a backup, but a relatively new feature has been included which allows the use also an AWS stack.
Introduction
With the increase of cloud computing, more and more users are adopting secure, scalable, and redundant services for data storage. One of the most popular is Amazon S3 service. S3 stands for Simple Scalable Storage, and with broad accompanying services available from AWS it is easy to understand its popularity.
With IBM’s new flagship product called IBM CP4D NPS (Cloud Pak for Data – Netezza Performance Server), we can use the newly acquired connector as part of the nzrestore utility. With Netezza, there are many ways to pull data from a backup, but a relatively new feature has been included which allows the use of an AWS stack. Currently, it is possible to move data from a remote backup directory using an S3 bucket. Depending on your preferences, network speeds, size of data, and availability, there are multiple options. For example, if the target system is airgapped (meaning it is not connected to internet and can only be accessed via VPN), the process is a bit more complicated. In that case, you need to have the capability to save data to a networked drive that has external access. Pull the data using the AWS CLI commands which we will describe shortly. Once you have the backups saved on the local network, mounting the drive to NPS and standard nzload / nzrestore with the known features is smooth sailing. In our case today we will cover the more complex nzrestore directly from Amazon S3 bucket to a given database in Netezza Performance Server (NPS).
To summarize, the process can be performed in two ways:
- Move backups from S3 to a mounted NPS drive using the AWS CLI utilityPerform the ingestion via nzrestore
- Perform the ingestion via nzrestore
- Move the data from S3 bucket and directly ingest via nzrestore utility using the S3 connector.
So, let’s dig in!
Setup Amazon S3 Bucket for Connection
If you don’t already have the bucket with the backups that you want to restore on the target environment, you will have to create it. If you already have it setup, you can proceed to the next step. In order to access the S3 bucket, you will first need to create it. Login to your AWS web console and navigate to S3 service and create a new bucket:
From there, you will be directed to the wizard where you can select various options. For the purposes of this tutorial we will leave everything as it is and only modify the bucket name.
Restoring Database from S3 Bucket
On your NPS environment, execute the following command:
nzrestore -db DummyDB -sourcedb DummyDB \-connector s3 \-connectorArgs UNIQUE_ID=dummyBackupFolder:ACCESS_KEY_ID=asdf1234321fdsa:SECRET_ACCESS_KEY=1234shfdskfdjds3dsakdjda33s:DEFAULT_REGION=eu-central-1:BUCKET_URL=s3bucket-dummy \-npshost ipshost
Some context is needed to explain how to properly configure the connector arguments. The connector consists of the following elements:
- UNIQUE_ID
- Necessary to identify the folder where the nzrestore script will look for the Netezza backups.
- ACCESS_KEY_ID
- The previously acquired unique id tied to your user and used to authenticate your access privileges to the S3 bucket
- SECRET_ACCESS_KEY
- The previously acquired Secret Key ID tied to your user and used to authenticate your access privileges to the S3 bucket
- DEFAULT_REGION
- Region where the S3 bucket is hosted
- BUCKET_URL
- Unique address of the S3 bucket
Remaining elements of the command are standard ones when using the nzrestore command.
Pro tip: Use the nohup
Restore Monitoring
In case our session gets terminated and command “jobs” does not return our restore command, we can use a query to list the 10 most recent restores.
SELECT seqno, optype, status, dbname, starttime, '/nz/kit/log/restoresvr/' || logfile AS log_file_pathFROM _v_restore_historyORDER BY starttime DESCLIMIT 10;
Column “STATUS” will give us information if restore is still in progress or if it has finished (successful/failed). By issuing tail –f <log_file_path> we can monitor the progress of our command in real time.
Conclusion
Today, we learned how to use the nzrestore utility in conjunction with Amazon S3 service to perform database restores on the fly. We hope this article helps you streamline your usual DBA processes and if you have any questions, feel free to reach out to us.
Let's collaborate
Partner with us
Let’s work together to create smarter, more effective solutions for your business.
Related blogs
When it comes to business intelligence (BI) tools, Power BI and Tableau are two of the most popular options, each offering unique capabilities that can help transform data into valuable…
A comparison of two automated backup solutions. One is through Amazon EC2 service and the other one is using AWS Systems Manager.
For years, third-party cookies have been a cornerstone of digital advertising, enabling businesses to track user behaviour across websites, deliver targeted ads, and measure campaign effectiveness. However, until recently their…
Who we are
Explore how our culture and expertise fuel digital innovation
Join us