Back Up and Restore

This article guides you through backing up and restoring your Alteryx Analytics Hub repository database.

Summary

To back up Alteryx Analytics Hub (AAH):

• Use the PostgreSQL PgAdmin4 tool to back up the Alteryx Analytics Hub repository database and store this backup in a remote location.
• Copy and store the assets stored in AAH’s Virtual File System to a remote location.
• Copy and store AAH configuration files and encryption keys to a remote location.

To restore Alteryx Analytics Hub (AAH) on the same machine:

• Restore the AAH repository database.
• Restore Virtual File System Assets.
• If necessary, restore AAH configuration files and keys.

To restore Alteryx Analtyics Hub (AAH) to a different machine:

• Install Alteryx Analytics Hub using the same PostgreSQL username and password for your respository as you did when installing the source AAH deployment.
• Restore the source PostgreSQL repository database on the new target machine.
• Restore/overwrite the target system’s Virutal File System with the backup from the source system.
• Regenerate configuration information on the target machine.

Prerequisites

Now let's prepare and go through the tools you're going to need for the procedure.

Tools

To back up the Alteryx Analytics Hub (AAH) repository database, use the PgAdmin 4 management tool. It is installed by default.

You might want to install the archiving tool of your choice (for example, WinZip, 7zip) in order to easily archive the contents of the Virtual File System stored on your Alteryx Analytics Hub instance.

Because the database back up process requires the platform to be shut down, plan, and perform backups during off-peak times.
For more information about PostgreSQL back up and restore, see https://www.postgresql.org/docs/11/backup.html.

Preparing the Target Server

Your backup must be applied to a functioning copy of Alteryx Analytics Hub.

Once installed, validate that you can sign into AAH running on the target server.

Back Up

Once you are prepared, you can start with backing up your Alteryx Analytics Hub repository database.

Shutdown Services

In order to create a transactionally consistent back up, you must shutdown AAH. To do so, follow these steps:

1. Open Windows Powershell as an Administrator
2. Navigate to the <INSTALL DIR>\Alteryx Analytics Hub folder of your AAH install, for example:
   cd 'C:\Program Files\Alteryx\Alteryx Analytics Hub'
3. Execute .\ayxhub.ps1 -stop

Back Up Configuration and Encryption Files

1. Using Windows Explorer, navigate to the <INSTALL_HOME>\Alteryx Analytics Hub folder (for example, C:\Program Files\Alteryx\Alteryx Analytics Hub).
2. Find the following files:
   Settings.yml
   CutlassSettings.yml
3. Save the files to a safe, remote location on a different computer or storage system.
4. Using Windows Explorer, navigate to the <INSTALL_HOME> \SSLCertificates folder of Alteryx Analytics Hub (for example, C:\Program Files\Alteryx\SSLCertificates).
5. Find and copy the aep.key file to a safe, remote location on a different computer or storage system.

Close Repository Connections

Next, back up the Alteryx Analytics Hub repository after closing all connections to the database. To do so, follow these steps:

1. Select Start, and in the PostgreSQL 11 program group, select pgAdmin 4.
2. When prompted, set or enter the master password which is used to protect passwords to your server connections.
   
3. In the Server Browser, open the Servers node. When you installed Alteryx Analytics Hub, you provided Setup with a username and password to be used when accessing the repository (for example, user='ngp', password='repoPW'). You need that information now. In order to open the PostgreSQL 11 node under Servers, you will need to provide the password. In the screenshot below, enter the password, and optionally select Save Password.
   
4. In Server Browser, open the Databases node. Note the repository database (which has the same name as the repository username you chose during install) is exposed. In the screenshot below the repository database is “ngp”.
   
5. In Server Browser, select the postgres system database.
   
6. From the Tools menu, select Query Tool.
7. Paste the following SQL into the Query Editor. When executed, it will close any remaining connections to the AAH repository database. Make sure to replace the values ‘ngp’ with the name of your repository database:
   UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'ngp';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'ngp';
8. Execute the code above with the F5 key, play button, or lightning bolt button on the toolbar.
9. Paste the SQL below into the editor and execute it with F5 to restore the ability to connect to your repository database. Make sure to replace the value ‘ngp’ with the name of your respository database:
   UPDATE pg_database SET datallowconn = 'true' WHERE datname = 'ngp';
   

Database Backup

1. Select and hold (or right-click) the name of your repository database in the Databases list. Select Backup... in the context menu.
   
2. Enter a filename for your backup in the Filename field. 
   
3. Select Backup.
4. After the backup process completes, store the resulting *.backup file in a safe, remote location.
5. Return to the Windows Powershell console and start your server with the command .\ayxhub -start:
   .\ayxhub.ps1 -start

Restore

The following steps guide you through restoring your Alteryx Analytics Hub repository database.

Restore Repository

Perform the steps below to overwrite the target system with your repository database backup.

1. Copy the *.backup file you created on the source machine to a local folder on the target computer.
2. Launch Windows Powershell as an Administrator.
3. Navigate to the INSTALL_DIR\Alteryx Analytics Hub folder of your AAH install, for example:
   cd 'C:\Program Files\Alteryx\Alteryx Analytics Hub'
4. Execute .\ayxhub.ps1 -stop to stop the target Alteryx Analytics Hub deployment.
5. On the target computer, select Start, and in the PostgreSQL 11 program group, select pgAdmin 4.
6. When prompted, set or enter the master password which is used to protect passwords to your server connections.
   
7. In the Server Browser, open the Servers node. When you installed Alteryx Analytics Hub, you provided Setup with a username and password to be used when accessing the repository (example: user='ngp', password='repoPW'). In order to open the PostgreSQL 11 Server, you will need to provide the PostgreSQL password. In the screenshot below, enter the password for and optionally choose Save Password.
   
8. In Server Browser, open the Databases node, so that databases appear. Note the repository database (which has the same name as the repository username you chose during install) is exposed. In the screenshot below the repository database is “ngp”.
   
9. In Server Browser, select the postgres system database.
   
10. From the Tools menu, select Query Tool.
11. Copy and paste the following SQL into the Query Editor. When executed, it will close any remaining connections to the target AAH repository database you are about to delete. Make sure to replace the value ‘ngp’ with the name of your repository database:
    UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'ngp';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'ngp';
12. Execute the code above with the F5 key or the play button on the toolbar.
13. Paste the SQL below into the Query Editor and execute with F5 to delete the repository database. Make sure to replace the value ‘ngp’ with the name of your repository database.
    -- Drop
DROP DATABASE ngp;
14. Paste the SQL below into the Query Editor and execute with F5 to recreate an empty repository database. Make sure to replace the value ‘ngp’ with the (user)name of your repository database.
    -- Create
CREATE DATABASE ngp
WITH
OWNER = ngp
ENCODING = 'UTF8'
LC_COLLATE = 'en-US'
LC_CTYPE = 'en-US'
TABLESPACE = pg_default
template 'template0'
CONNECTION LIMIT = -1;

    Do not run the three blocks of code above at the same time. The UPDATE/SELECT, DROP statement and CREATE DATABASE statements must each be run in isolation.

    

15. In the Server Browser, right-click the Databases node and select Refresh.

16. Right-click the empty repository database in the Databases list, and select Restore.
17. In the Restore dialog, select the Filename (...) button to find your backup file (make sure you select the backup format type of .backup.)
    
18. Select Restore.
19. Wait for the restore to complete. This could take moments-to-hours depending on the size of your backup.
    
20. Close the browser window hosting pgAdmin4.
21. In the system tray of your computer, find the PgAdmin4 Server icon and choose to shut down the server:
    

Restore Storage

If your system uses File System storage, follow the steps below to restore assets to the target server. If you used Database Storage, you can skip these tasks as assets are stored in the repository database you’ve already restored.

1. On the target computer, Navigate to the INSTALL_HOME\vfs folder (for example, C:\Program Files\Alteryx\vfs).
2. Using the mechanism of your choice, copy, winzip, or 7zip your backed-up \storage folder and all its contents into the \vfs folder of the target machine.
   

Restore Encryption Key

Copying the aep.key file from your source machine to the target machine is necessary to decrypt information in the repository database and elsewhere.

1. On the target computer, use Windows Explorer to navigate to INSTALL_LOCATION\Alteryx Analytics Hub (For example, C:\Program Files\Alteryx\Alteryx Analytics Hub).
2. Inside \Alteryx Analytics Hub, create a new folder with a name of your choosing. This folder will hold the key. In the screenshot below, the folder NewSSLCerts has been added.
   
3. Copy the backed-up aep.key file from your old machine into the new folder (example: \NewSSLCerts).

Update Configuration Files

During installation, setup generates several configuration files on your target system. These files must be updated with information on your source (backed up) system configuration files. Follow the steps below to update the files in question.

1. On the target computer, use Windows Explorer to navigate to <Install_Home>\Alteryx Analytics Hub (For example, C:\Program Files\Alteryx\Alteryx Analytics Hub).
2. Create a backup of auto-generated settings files in case something goes wrong. Do this by copying the auto-generated Settings.yml and CutlassSettings.yml files into a separate folder. You might need these later if you make an editing error in the steps that follow.
3. Use a text editor to open the auto-generated Settings.yml configuration file, located in \Alteryx Analytics Hub.
4. Use another copy of your text editor to open the saved Settings.yml file from your source computer. This copy of Settings.yml should contain information from the system you backed up previously.
5. In the backed-up Settings.yml file which came from your source computer, find the jwt_secret setting and copy the entire value onto your clipboard.
   
6. Paste the value into the auto-generated Settings.yml file on your target machine, replacing the original value completely. Make sure that you do not change the level of indentation while pasting.
7. Repeat the same process with the session_secret setting:
   
8. In the updated Settings.yml file, find and update the aes_cypto.key_location setting. Point to the restored aep.key encryption key you added to the system in the previous set of steps.
   
9. Save your changes.

Regenerate Self-signed Certificates and Repository Password

Your base Alteryx Analytics Hub leverages self-signed digital certificates to guarantee safety and privacy. You must regenerate them and then store the username and password used to access your PostgreSQL repository.

1. On the target computer, use Windows Explorer to navigate to INSTALL_LOCATION\Alteryx Analytics Hub (For example, C:\Program Files\Alteryx\Alteryx Analytics Hub).
2. Execute .\ayxhub.ps1 -https generate-selfsigned [location] [hostname] [password], passing in the following values:[location]: the location where new SSL certs will be stored.
   [hostname]: the fully qualified domain name (FQDN) of your computer.
   [password]: a password for the certificates about to be generated.
   Example:
   .\ayxhub.ps1 -https generate-selfsigned 'C:\Program Files\Alteryx\Alteryx Analytics Hub\NewSSLCerts' aah2.russch.com unsafepassword
   
   Executing the command above populates the \NewSSLCerts folder with digital certificates to keep the pre-existing aep.key company.
3. Next, run .\ayxhub.ps1 -db <username> <password> in order to properly encrypt the repository username and password, passing the following values:
   <username>: username of the database admin account.
   <password>: password of the database admin account.
   Example:
   .\ayxhub.ps1 -db ngp repoPW
4. Apply these settings by executing .\ayxhub -start.
   .\ayxhub.ps1 -start

Your Alteryx Analytics Hub is now fully restored.