Backups are for cowards!

The other day I heard a rumor that backing up your Alteryx server.next is a good thing. 

That advice sounds fishy to me since bad things never happen to good people. For example, my personal WordPress site didn’t just get totally nuked for reasons unknown to me. But, whatever.

Beta 3 doesn’t yet include a backup utility, so if you want to play it safe you’ll need to do this work manually. Don’t worry, though.  The process is very straight-forward.

The Tools

As you may or may not know, Server.Next uses PostgreSQL as our repository. When we install Postgres on your box, we actually lay down PgAdmin 4 for you, so you pretty much have everything you need to backup and restore the repo, even if you’re not into using tools like pg_dump and/or pg_restore.

The Process

In essence, you’ll follow these steps:

  • Shutdown Server.Next services
  • Save your config files
  • Close connections to PostgreSQL
  • Backup or Restore
  • Update crypto keys if restoring on a different machine

Shutdown Services

Alteryx Backup

Using the Windows Services (services.msc) applet, shutdown the ServerNextBackend, ServerNextFrontend, and AlteryxEngineWorker services.

Backup Config Files

Most of this article focuses on backing up and restoring the repository of Server.Next. That’s all well and good, and will typically save your skin 90% of the time…BUT, what happens if your whole machine just catches on fire and EVERYTHING goes away?

If you’re recreating a box from scratch, you’ll want to have copies of two settings files: 

  • settings.yml: Contains crypto keys (more on this later), information about SSL and other useful info
  • CutlassSettings.yml: Tells Cutlass where your backend is, and where workers are

Both of these files are found in \Program Files\Alteryx\Server.Next. Copy them to a safe place OFF this machine. 

BONUS INFO: 

What’s a Cutlass, you ask? It’s a helper service which is responsible for launching our engine to “do work”. It checks in with the back-end of Server.Next on a regular basis and sees if there are job in our work queue that need to be processed. The “fancy” name of this service (the one you’ll see in the Services applet of Windows) is AlteryxEngineWorker. If this service doesn’t run, none of your jobs will ever execute – your jobs will just queue up instead.

Close Connections

While not (completely and strictly) necessary, it’s always smart to close all connections to the database you want to backup before you begin. You can do so by running the following SQL which will close/disallow connections, and then re-enable them so your backup process can connect.

  • While logged into the console, get into the PostgreSQL 11 program group and launch pgAdmin 4
  • You’ll be prompted to set or enter a master password which is used to protect passwords to your server connections.
  • Open your list of Servers, drill into PostgreSQL 11 and Databases, then click the postgres database. That’s right, you heard me – the postgres database. If / when prompted, login with the PostgreSQL username/password combo you selected during the Server Setup process. 
  • From the Tools menu, choose Query Tool.
  • Execute the following SQL which will kill any remaining connections to the ngp database so you can safely back it up:
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'ngp';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'ngp';
  • You’ll need to re-enable the ability to connect to the database before you can back it up. Do so with like this:
UPDATE pg_database SET datallowconn = 'true' WHERE datname = 'ngp';


Alteryx Backup

Back her up!

  • Right-click the ngp database in The Databases list. Choose Backup in the context menu.
Alteryx Backup
  • You’ll need to give your backup archive a name (use the ... button to do so). In the Select File dialog, make sure you choose the backup format type of .backup instead of the default .sql script.

Alteryx Backup

  • With that, click Backup and wait. 
  • Using Service Manager, restart the ServerNextBackend, ServerNextFrontend, and AlteryxEngineWorker services.
  • You’re not done yet. You need to protect a very important piece of information, otherwise your backup might be useless under certain circumstances. Go to the final section of this article. 

Restore

Restoring is pretty much the same process. Stop your Services. Next,  close connections to ngp, but do NOT allow new connections to be established against the database. Instead, you DROP the existing database and re-create a new one with the script below. If you are using a different username than ngp, make sure to specify it correctly:

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'ngp';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'ngp';

-- Drop
DROP DATABASE ngp;
-- 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;
  • Right-click the ngp database in the Databases List, and choose Restore
  • Use the Filename button to find your backup file (don’t forget to flip the format drop-down to backup instead of sql)
  • Click RESTORE
  • Once complete, restart the ServerNextBackend, ServerNextFrontend, and AlteryxEngineWorker services using Service Manager.

Always backup Crypto Key, optionally restore it.

Remember that we encrypt sensitive information in the PostgreSQL database. If you restore your backup to a new installation of Server.Next (on the same or different machine), much of the information in the database will be unreadable. SO, you have a touch more work to do: 

  • On the “old” machine, open C:\Program Files\Alteryx\Server.Next\settings.ymlAlteryx Backup
  • Copy the aes_crypto_secret value you see in the file and keep it in a safe place
  • Open the same file for the “new” install and update the value.
  • Restart Server.Next Services

Leave a Reply

Old as dirt posts

Contact me.

    %d bloggers like this: