Backup and restore · WorkFusion Documentation

Restore from backup media

At predetermined checkpoints (after key events or time periods) a solution may backup (store) a snapshot of its operational state and the information it has processed. Restoring the solution state and information from backup media enables past information to be reconstructed and the solution to resume operation with a minimum of lost data and time. The Restore from Backup Media section identifies solution checkpoints and the procedures for using backup solution status information to recover from solution failures or degradation.

Primary purpose of the backup is to recover data after its loss. Secondary is to recover data from an earlier time. Backups represent a simple form of Disaster Recovery, and should be included in Disaster Recovery plan. At rare occasions the backups alone are considered as the only procedure of the Disaster Recovery plan.

Steps on defining backup approach

Select an appropriate strategy, tool or method to back up the data.
Ensure there is an appropriate retention policy defined for the data backup.
Ensure there are appropriate security measures in place for the backup data, including encryption and access policies.
Regularly test the recovery of the backup data and the restoration of the system.

Applicability

The Restore from Backup Media approach is used:

As a fallback method in case of a major failure of the main DC with data integrity affected due to the main replication strategy failure.
As a fallback method in case of a major failure of the main HA replication strategy failure.
As a main HA strategy in absence of other higher level HA options or strategies.
As a main DR strategy in absence of other higher level DR options or strategies.

Snapshot backup

A copy of the file system as if it were frozen at a specific point in time.

Backup software packages as a rule may operate on the following level:

Host snapshot

Typically, it is performed on the file system of a logical volume level. The backup software resides on the operating system and makes a point in a time snapshot of data to the disk.

Disk snapshot

Typically, it is tightly coupled with hardware, for example, hardware-based with or without specialized software to do a point in a time disk snapshot.

Virtual Machine or Virtual Host snapshot

Typically, it is performed by Hypervisor or Virtual Machine management tooling. A point in a time copy is performed of the whole Virtual Machine image or host.

Database backup

As an addition to snapshot backup strategy databases data backup may be performed.

Cold database backup

During a cold backup database is stopped or locked and not available to users. It allows taking consistent backup of the data set while it is not being modified.

Hot database backup

A way to make a database backup while it's active and is available to users. As a rule it includes an inconsistent image of the data plus log of changes performed during the hot backup procedure was running.

MySQL backup

All scenarios assume you need to minimize time-to-recovery.

The xtrabackup utility can perform the following actions:

Create a full backup.
Apply logs with commited only transactions to the full backup.
Create an incremental backup.
Apply incremental logs with commited transactions to the full backup.
Prepare the full backup (applying rollbacks to the full backup).

note

Only a prepared full backup is suitable for recovering.
An incremental backup can't be applied to a prepared full backup.
Logs applying and backup preparation may be done without connection to MySQL.
A full backup must be recreated after database recovery.

Scenario 1: Daily full backup

Useful for a daily full backup.

Backup steps:

Create a full backup.
Prepare the backup.

Recovery steps:

Restore the database from the prepared backup.

Pros:

Simple backup procedure.
Fastest recovery.
A backup may be compressed.

Cons:

A full backup may take a lot of time.

Scenario 2: Single incremental backup

Backup steps:

Create a full backup once.
Periodically create an incremental backup and apply it to the full backup.

Recovery steps:

Prepare a backup.
Restore database from the backup.

Pros:

An incremental backup creating is faster then a full backup.

Cons:

A full backup can't be compressed. It must be available during incremental backup.

Scenario 3: Multiple incremental backups

note

The case is not implemented in the backup-mysql.sh script.

Backup steps:

Create a full backup.
Daily create a new incremental backup without log applying.

Recovery steps:

Apply consequently all incremental backups to the full backup.
Prepare the full backup.
Recovery database from the backup.

Pros:

Full and incremental backups may be compressed.
Fastest backup time (no log applying).

Cons:

Long backup preparation time.

Backup script

Download the example backup script: cold-backup.zip.

Main use cases

Create full backup

You may use script from cold-backup.zip for full backup.

Create incremental backup

You need to use external tools to go with incremental backups. WorkFusion does not provide such scripts.

Recover from backup

(Re)install MySQL server software if needed.
Stop the MySQL server if running.
Unpack files from the archive that was created previously and put them into the data folder.

Backup integrity

To check integrity of a backup:

Run the check utility:

mysqlcheck --all-databases -u root -p

Console output sample:

mysql.columns_priv                                 OK              
mysql.db                                           OK              
mysql.event                                        OK              
mysql.func                                         OK              


....
 
test_repl.t4i2                                     OK              
test_repl.t5i3                                     OK

If all tables have the OK status, the database integrity check is passed. If at least one table does not have the OK status, the database is broken:

Sample of output for a broken database backup:

...
trepl2.t2                                          OK
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '

PostgreSQL backup

Backup utility pg_basebackup provides a full backup mode. For incremental backup, archive logs may be applied to the last full backup. If archive_mode is on, postgres runs archive_command for every completed archive log.

As pg_basebackup doesn't include all required WALs into stream

postgresql.conf need to be configured to copy WAL files to the Backup server.

Cold full backup

You may use your own tools or examples provided below.

The cold-backup.zip script
Your own scripts and tools

You can use the reserve DB server to create a cold full backup.

host: Reserve DB, user: postgres

$ pg_ctl stop
$ tar -xcvf /backups/pgsql/arch/postgres-$(date +"%Y-%m%d-%H%M).tar.gz $PGDATA/*
$ pg_ctl start

Hot full backup

host: Main

BACKUP_POSTGRES_DIR=/backups/pgsql/arch
 
pg_basebackup -D - -Ft -P | gzip > $BACKUP_POSTGRES_DIR/postgres-$(date +"%Y-%m%d-%H%M").tar.gz

Incremental backup

Increment backup requires a preliminary created full backup.

Set up WAL copying to the Backup server.

host: Main, file: postgresql.conf

archive_mode = on
### archive_command = 'cd .'
archive_command = 'test ! -f /backups/pgsql/xlog/%f && cp %p /backups/pgsql/xlog/%f'

Recovery from hot or incremental backup

(Re)install the postgreSQL software if needed.
Stop database. User: root.
```
service postgresql-9.3 stop
```
caution

Ensure that $PGDATA is defined.
Clear old data. User: root.
```
rm -rf $PGDATA/*
```
note

Specify a correct filename for BACKUP_POSTGRES_FILE.

Extract files from a full backup archive. User: postgres.

BACKUP_POSTGRES_DIR=/backups/pgsql/arch
BACKUP_POSTGRES_FILE=postgres-<backup-date>.tar.gz
tar -zxvf $BACKUP_POSTGRES_DIR/$BACKUP_POSTGRES_FILE -C $PGDATA

Restore file permissions. User: root.
```
chown postgres:postgres $PGDATA -R
chmod go-rwx $PGDATA -R
```
If there are no WAL files, but a full backup, perform the run action.
Clear transaction logs.
```
pg_resetxlog -f /var/lib/pgsql/9.3/data"
pg_ctl start
```
Otherwise, do as follows:
1. Find out the last WAL filename in the backup_label file:
```
$ cat $PGDATA/backup_label | grep file
START WAL LOCATION: 0/40000028 (file 000000010000000000000040)
```
1. Copy all WAL files started with 000000010000000000000040 to $PGDATA/pg_xlog.

Create $PGDATA/recovery.conf if needed. User: postgres.

standby_mode = 'on'
primary_conninfo = 'port=5432 host=DBHOSTNAME_MASTER user=repmgr'
recovery_target_timeline = 'latest'

note

Replace DBHOSTNAME_MASTER with a proper value.

Start the database. User: postgres.
```
pg_ctl start
```

Recovery from cold backup

User: postgres.

$ pg_ctl stop
$ tar -zxvf /backups/pgsql/arch/postgres-2016-0311-1456.tar.gz -C $PGDATA
$ pg_ctl start

Backup integrity

To check integrity of a backup just recovery database from this backup and create a full hot backup of the recovered database with the pg_basebackup utility.

Assume that backups/pgsql/last contains an unpacked full cold backup, /backups/pgsql/test is empty, and PostgreSQL is installed and run.

User: root; file: check_backup.sh.

#!/bin/bash
export BACKUP_FILE_DIR=/backups/pgsql/last
export BACKUP_TEST_DIR=/backups/pgsql/test
export PGSQL_DATA_DIR=/var/lib/pgsql/9.4/data

echo Stop PostgreSQL...
service postgresql-9.4 stop

echo Clear pgsql data...
rm -rf $PGSQL_DATA_DIR/*

echo Copy backup files...
cp -R $BACKUP_FILE_DIR/* $PGSQL_DATA_DIR/

echo Restore permissions...
chown postgres:postgres $PGSQL_DATA_DIR -R

echo Remove backup label...
rm -f $PGSQL_DATA_DIR/backup_label

echo Switch to postgres to reset transaction log...
su postgres -c '/usr/pgsql-9.4/bin/pg_resetxlog $PGSQL_DATA_DIR/ -f'

echo Start PostgreSQL...
service postgresql-9.4 start

echo Wait 2 secs...
sleep 2

echo Switch to postgres to create test backup...
su postgres -c 'pg_basebackup --pgdata=$BACKUP_TEST_DIR -P'

If pg_basebackup is completed successfully, the source backup is valid.

note

pg_basebackup may issue the following message:

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

S3 backup

Hot backup

This approach is to traverse the S3 resources and download them as regular files.

As a result it allows restoring individual files.

It's recommended to perform this backup during no or low S3 load hours.

Check how much space your S3 storage takes.

s3cmd -c /etc/s3cfgfasttrack du -H | grep Total

Create a backup folder.

# Please make sure you have enough free space on backup disk, see S3 storage size above
mkdir -p /backups/wf-s3-content

Perform the backup of the S3 storage.

BUCKET_LIST=$(s3cmd -c /etc/s3cfgfasttrack ls | cut -d'/' -f3); \
for i in $BUCKET_LIST; do mkdir -p /backup/wf-s3-content/$i && \
s3cmd -c /etc/s3cfgfasttrack get -f -r s3://$i /backup/wf-s3-content/$i; done

The procedure may take some time (the overall progress is displayed). It depends on how many files you have in your s3 storage

Create an archive with S3 content.

cd /backup
tar czvf wf-s3-content.tar.gz wf-s3-content

To restore, do as follows:

Copy and replace the archive to the package installation directory.

cp /tmp/wf-s3-content.tar.gz PATH_TO/app-all/
# where PATH_TO is the path where 'app-all' package installation directory is located.

Restore the S3 content backup.
```
cd PATH_TO/app-all/
# where PATH_TO is the path where 'app-all' package installation directory is located.
./workfusion_setup_full.sh -i wf_s3_content
```
The procedure may take some time (the process progress is indicated in the console). It depends on how many files you have in your S3 storage.

Cold backup

Backups can be accomplished using standard system utilities such as cp, rsync, and tar, as well as any other including the previously mentioned backup strategies.

Refer to the Riak KV instructions

You can also use cold-backup.zip to provide this functionality for you.

Analytics backup

Back up Tableau data

A backup of your Tableau Server installation saves all configuration information, user information, and content in a single file. This can use this file to restore your server to the same condition it was in when you took the backup.

To create a backup of your Tableau Server configuration and data:

Click Start or press the Windows key.
Type cmd. Results are listed, including cmd.exe, the command prompt.
Right-click cmd.exe and select Run as administrator.
Go to the Tableau Server bin folder: C:/Program Files/Tableau/Tableau Server/<version>/bin, where <version> is your version of Tableau Server. For example, go to the Tableau Server 10.1 bin directory:
```
cd C:/Program Files/Tableau/Tableau Server/10.1/bin
```
Type tabadmin backup <filename> -v and press Enter:
```
tabadmin backup tabserver -d -v
```
The example creates a backup file in the bin folder named tabserver-2016-02-10.tsbak. The -d option adds the datestamp, and the -v switch verifies the state of the database for backup and restore.

Restore Tableau Server

On the Tableau Server computer, open a command prompt as Administrator:

Click Start or press the Windows key.
Type cmd. Results are listed, including cmd.exe, the command prompt.
Right-click cmd.exe and select Run as administrator.
Go to the Tableau Server bin folder: C:/Program/Files/Tableau/Tableau Server/<version>/bin, where <version> is your version of Tableau Server. For example, go to the Tableau Server 10.3 bin:
```
cd C:/Program/Files/Tableau/Tableau Server/10.3/bin
```
Stop the server:
```
tabadmin stop
```
Restore from a backup file:
```
tabadmin restore <filename>
```
Replace <filename> with the name of the backup file you want to restore from.
Start the server:
```
tabadmin start
```

For more details, refer to the Tableau Online help.

Mongo backup

Mongo DB keeps only temporal inflight data. It does not require a backup.

Solr backup

Solr is a deprecated component and should not be used as a data storage solution.

Artifactory backup

Nexus backup is done by a simple file copy of the data directory. It would have handled by the host or disk backup if that is in place.

Vault backup

Vault backup is done by a simple file copy of the data directory. It would have handled by the host or disk backup if that is in place.

Restore from backup media

Steps on defining backup approach

Applicability

Snapshot backup

Host snapshot

Disk snapshot

Virtual Machine or Virtual Host snapshot

Database backup

Cold database backup

Hot database backup

MySQL backup

note

Scenario 1: Daily full backup

Scenario 2: Single incremental backup

Scenario 3: Multiple incremental backups

note

Backup script

Main use cases

Create full backup

Create incremental backup

Recover from backup

Backup integrity

PostgreSQL backup

Cold full backup

Hot full backup

Incremental backup

Recovery from hot or incremental backup

caution

note

note

Recovery from cold backup

Backup integrity

note

S3 backup

Hot backup

Cold backup

Analytics backup

Back up Tableau data

Restore Tableau Server

Mongo backup

Solr backup

Artifactory backup

Vault backup