Back Up and Restore

Version:
Current
Last modified: November 24, 2020

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.

Uninstall and reinstall AAH as necessary, making sure you use the same PostgreSQL Admin repository database username and password you did when you originally installed the platform.

Login to PostgreSQL Admin

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.
    Set the Master Password
  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.
    Connect to the Server
  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”.
    Choose the Database
  5. In Server Browser, select the postgres system database.
    Choose Postgres
  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';
    Update the Database

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.
    Backup the Database
  2. Enter a filename for your backup in the Filename field. 
    Name the Database File
  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.
    Set the Master Password
  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.
    Connect to the Server
  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”.
    Choose the Database FIle
  9. In Server Browser, select the postgres system database.
    Choose Postgres
  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.

    Update the Database

  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.)
    Restore the Database
  18. Select Restore.
  19. Wait for the restore to complete. This could take moments-to-hours depending on the size of your backup.
    Restore the Database
  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:
    Shut Down the pgAdmin service

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.
    Pack the Storage folder

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.
    Restore the SSL Certificates
  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.
    Rewrite Settings
  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:
    Rewrite the Path to the Secret
  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.
    Rewrite the Location
  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
    Open Windows Poweshell
    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.

    Was This Helpful?

    Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.