Backup

Overview#

If you define the sourceType property as backup you will be able to create a data image whose content will be what you defined in your backup.

sourceType: backup
name: dev
engine: mysql
version: 5.7
backups:
  - folder: mybackup
    file: backup.sql

In this case we want to create a MySQL data image whose source is backup and is named dev.

The folder is relative to the location of your yaml file.

You can restore multiple backup files by adding multiple entries in the backups section.

sourceType: backup
name: dev
engine: mysql
version: 5.7
backups:
  - folder: mybackup
    file: backup1.sql
  - folder: mybackup
    file: backup2.sql

In this case we want to create a MySQL data image by restoring two backup files: backup1.sql and backup2.sql.

Size limit#

Spawn imposes a 500Gi limit on the size of data images.

Tutorial#

As a prerequisite you should've followed the instructions to install spawnctl

PostgreSQL
MySQL
MariaDB
SQL Server

Tutorial (video)#

Watch this video on how to create data images from a MySQL database via a backup. Or follow the step-by-step instructions below.

Tutorial (step-by-step)#

Create a backup#

Create a subdirectory for the backup file and data image YAML definition file:

mkdir -p ~/spawn/mysql-backup-image/ && cd ~/spawn/mysql-backup-image/

Assuming there is a database called sakila running at prod-sakila.example on the standard port 3306 and is accessible to the user root, you can run the following mysqldump command:

mysqldump --host prod-sakila.example --user root --password --databases sakila > prod-backup.sql

Create a data image yaml file#

Now that we've got a prod-backup.sql file produced by mysqldump we can create our Spawn data image definition YAML file called image.yaml in the current directory with the following contents:

# ~/spawn/mysql-backup-image/image.yaml
sourceType: backup
name: sakila
engine: mysql
version: 5.7
backups:
  - folder: /<YOUR_HOME_DIRECTORY>/spawn/mysql-backup-image/
    file: prod-backup.sql
tags:
  - production

This instructs Spawn to create a data image called sakila on MySQL 5.7. It will upload the file /<YOUR_HOME_DIRECTORY>/spawn/mysql-backup-image/prod-backup.sql to Spawn and restore that backup using mysql, snapshot the state of the restored database and then produce the data image ready for consumption.

The data image will also have a production tag associated with it, so you later know that this data image represents a backup of production.

## Tutorial (step-by-step)

Create a backup#

Create a subdirectory for the backup file and data image YAML definition file:

mkdir -p ~/spawn/mariadb-backup-image/ && cd ~/spawn/mariadb-backup-image/

Assuming there is a database called sakila running at prod-sakila.example on the standard port 3306 and is accessible to the user root, you can run the following mysqldump command:

mysqldump --host prod-sakila.example --user root --password --databases sakila --column-statistics=0 > prod-backup.sql

Note, MariaDB supports multiple mechanisms for creating backups. Spawn currently supports creating MariaDB backups using mysqldump).

Note, new versions of mysqldump (version 8 and above) try to generate histogram statistics for the dumped tables. This is not supported by MariaDB and hence we disable this by --column-statistics=0 in the command above.

Create a data image yaml file#

Now that we've got a prod-backup.sql file produced by mysqldump we can create our Spawn data image definition YAML file called image.yaml in the current directory with the following contents:

# ~/spawn/mysql-backup-image/image.yaml
sourceType: backup
name: sakila
engine: mariadb
version: 10.6
backups:
  - folder: /<YOUR_HOME_DIRECTORY>/spawn/mariadb-backup-image/
    file: prod-backup.sql
tags:
  - production

This instructs Spawn to create a data image called sakila on MariaDB 10.6. It will upload the file /<YOUR_HOME_DIRECTORY>/spawn/mariadb-backup-image/prod-backup.sql to Spawn, restore that backup, snapshot the state of the restored database, and then produce the data image ready for consumption. The data image will also have a production tag associated with it, so you later know that this data image represents a backup of production.

Tutorial (video)#

Watch this video on how to create data images from a SQL server database via a backup. Or follow the step-by-step instructions below.

Tutorial (step-by-step)#

Create a backup#

You may want to skip the "Create a backup" step if you already have the backup file for your database available.

Create a directory which your SQL Server instance can access and will write the backup to:

mkdir C:\LocalBackups

In this example we will be backing up the AdventureWorks2017 database running at a local instance .\SQL2017. To backup the databse to C:\LocalBackups\ you can run the following snippet of SQL:

USE AdventureWorks2017;
GO

BACKUP DATABASE AdventureWorks2017
TO DISK = 'C:\LocalBackups\adventureworks.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Backup of AdventureWorks';
GO

Create a data image yaml file#

Now that we've got a adventureworks.bak backup file we can create our Spawn data image definition YAML file called image.yaml in the current directory with the following contents:

name: adventureworks
sourceType: backup
engine: mssql
version: 2017
backups:
  - folder: C:\LocalBackups\
    file: adventureworks.bak
tags:
  - production

This will instruct Spawn to create a data image called adventureworks on SQL Server 2017. It will upload the file C:\LocalBackups\adventureworks.bak to Spawn, restore that backup, snapshot the state of the restored database and then produce the data image ready for consumption.

The data image will also have a production tag associated with it, so you later know that this data image represents a backup of production.

Spawn currently supports two formats of PostgreSQL backups:

Plain
Custom

You can read more about these in the PostgreSQL documentation.

Depending on the type of pg_dump backup you create, your data image definition file will require different configuration.

Plain
Custom

Plain backups are the default type. If you omit the format value in the data image definition file, Spawn will assume the postgres backup is of the "plain" format.

For plain backups, you'll need to specify the following items in the data image definition file:

folder
file

Tutorial (video)#

Watch this video for how to create data images from a PostgreSQL database via a backup. Or follow the step-by-step instructions below.

Tutorial (step-by-step)#

Create a backup#

Create a subdirectory for the backup file and data image YAML definition file:

mkdir -p ~/spawn/postgres-backup-image/ && cd ~/spawn/postgres-backup-image/

Assuming there is a database called pagila running at prod-db.example.com on port 5432 and is accessible to the user admin, you can run the following pg_dump command:

pg_dump -h prod-db.example.com -U admin --create pagila > dump.sql

Create a data image yaml file#

Now that we've got a dump.sql file produced by pg_dump we can create our Spawn data image definition YAML file called image.yaml in the current directory with the following contents:

# ~/spawn/postgres-backup-image/image.yaml
sourceType: backup
name: pagila
engine: postgresql
version: 12.0
backups:
  - folder: /<YOUR_HOME_DIRECTORY>/spawn/postgres-backup-image/
    file: dump.sql
tags:
  - production

This instructs Spawn to create a data image called pagila on Postgres 12. It will upload the file /<YOUR_HOME_DIRECTORY>/spawn/postgres-backup-image/dump.sql to Spawn and restore that backup using psql, snapshot the state of the restored database and then produce the data image ready for consumption.

The data image will also have a production tag associated with it, so you later know that this data image represents a backup of production.

Custom backups are restored with the --no-owner flag enabled. This means all objects restored will be owned by the auto generated spawn admin user. If you need to, you may change the ownership of objects in subsequent data containers created from this image.

For custom backups, there's some additional configuration required. You'll need to specify the following items in the data image definition file:

folder
file
format
initialDatabaseName

Tutorial (step-by-step)#

Create a backup#

Create a subdirectory for the backup file and data image YAML definition file:

mkdir -p ~/spawn/postgres-backup-image/ && cd ~/spawn/postgres-backup-image/

Assuming there is a database called pagila running at prod-db.example.com on port 5432 and is accessible to the user admin, you can run the following pg_dump command:

pg_dump -h prod-db.example.com -U admin --create pagila --format=custom > pagila.dump

Create a data image yaml file#

Now that we've got a pagila.dump file produced by pg_dump we can create our Spawn data image definition YAML file with the following contents:

# ~/spawn/postgres-backup-image/image.yaml
sourceType: backup
name: pagila
engine: postgresql
version: 12.0
backups:
  - folder: /<YOUR_HOME_DIRECTORY>/spawn/postgres-backup-image/
    file: pagila.dump
    format: custom
initialDatabaseName: pagila
tags:
  - production

This instructs Spawn to create a data image called pagila on Postgres 12. It will upload the file /<YOUR_HOME_DIRECTORY>/spawn/postgres-backup-image/pagila.dump to Spawn and restore that custom backup file using pg_restore, snapshot the state of the restored database and then produce the data image ready for consumption.

The data image will also have a production tag associated with it, so you later know that this data image represents a backup of production.

Create the image#

Run

spawnctl create data-image --file=./image.yaml

This will create the data image from the backup defined in the image.yaml file we created.