Backup and Restore

2020-02-16

Before you begin

In this lab, you'll start with a fresh cluster, so make sure you've stopped and cleaned up the cluster from the previous labs.

Step 1. Start a 3-node cluster

Start and initialize an insecure cluster like you did in previous modules.

  1. Start node 1:

    $ ./cockroach start \
    --insecure \
    --store=node1 \
    --listen-addr=localhost:26257 \
    --http-addr=localhost:8080 \
    --join=localhost:26257,localhost:26258,localhost:26259 \
    --background
    
  2. Start node 2:

    $ ./cockroach start \
    --insecure \
    --store=node2 \
    --listen-addr=localhost:26258 \
    --http-addr=localhost:8081 \
    --join=localhost:26257,localhost:26258,localhost:26259 \
    --background
    
  3. Start node 3:

    $ ./cockroach start \
    --insecure \
    --store=node3 \
    --listen-addr=localhost:26259 \
    --http-addr=localhost:8082 \
    --join=localhost:26257,localhost:26258,localhost:26259 \
    --background
    
  4. Perform a one-time initialization of the cluster:

    $ ./cockroach init --insecure --host=localhost:26257
    

Step 2. Perform a "Core" backup

  1. Use the cockroach gen command to generate an example startrek database:

    $ ./cockroach gen example-data startrek | ./cockroach sql --insecure
    
  2. Check the contents of the startrek database:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="SELECT * FROM startrek.episodes LIMIT 1;" \
    --execute="SELECT * FROM startrek.quotes LIMIT 1;"
    
    +----+--------+-----+--------------+----------+
    | id | season | num |    title     | stardate |
    +----+--------+-----+--------------+----------+
    |  1 |      1 |   1 | The Man Trap |   1531.1 |
    +----+--------+-----+--------------+----------+
    (1 row)
    +----------------------------------------------------------------------+------------------------+----------+---------+
    |                                quote                                 |       characters       | stardate | episode |
    +----------------------------------------------------------------------+------------------------+----------+---------+
    | "... freedom ... is a worship word..." "It is our worship word too." | Cloud William and Kirk | NULL     |      52 |
    +----------------------------------------------------------------------+------------------------+----------+---------+
    (1 row)
    
  3. Use the cockroach dump command to create a SQL dump file for the startrek database:

    $ ./cockroach dump startrek \
    --insecure \
    --host=localhost:26257 > startrek_backup.sql
    
  4. Take a look at the generated startrek_backup.sql file.

    You'll see that it contains the SQL for recreating the two tables in the startrek database and inserting all current rows into those tables.

    CREATE TABLE episodes (
    	id INT NOT NULL,
    	season INT NULL,
    	num INT NULL,
    	title STRING NULL,
    	stardate DECIMAL NULL,
    	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    	FAMILY "primary" (id, season, num, title, stardate)
    );
    
    CREATE TABLE quotes (
    	quote STRING NULL,
    	characters STRING NULL,
    	stardate DECIMAL NULL,
    	episode INT NULL,
    	CONSTRAINT fk_episode_ref_episodes FOREIGN KEY (episode) REFERENCES episodes (id),
    	INDEX quotes_episode_idx (episode ASC),
    	FAMILY "primary" (quote, characters, stardate, episode, rowid)
    );
    
    INSERT INTO episodes (id, season, num, title, stardate) VALUES
    	(1, 1, 1, 'The Man Trap', 1531.1),
    	(2, 1, 2, 'Charlie X', 1533.6),
    	(3, 1, 3, 'Where No Man Has Gone Before', 1312.4),
    	(4, 1, 4, 'The Naked Time', 1704.2),
    	(5, 1, 5, 'The Enemy Within', 1672.1),
      ...
    

Step 3. Perform a "core" restore

Now imagine the tables in the startrek database have changed and you want to restore them to their state at the time of the dump.

  1. Drop the tables in the startrek database:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="DROP TABLE startrek.episodes,startrek.quotes CASCADE;"
    
  2. Confirm that the tables in the startrek database are gone:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="SHOW TABLES FROM startrek;"
    
    +------------+
    | table_name |
    +------------+
    +------------+
    (0 rows)
    
  3. Restore the tables in the startrek database from the dump file:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --database=startrek < startrek_backup.sql
    
  4. Check the contents of the startrek database again:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="SELECT * FROM startrek.episodes LIMIT 1;" \
    --execute="SELECT * FROM startrek.quotes LIMIT 1;"
    
    +----+--------+-----+--------------+----------+
    | id | season | num |    title     | stardate |
    +----+--------+-----+--------------+----------+
    |  1 |      1 |   1 | The Man Trap |   1531.1 |
    +----+--------+-----+--------------+----------+
    (1 row)
    +----------------------------------------------------------------------+------------------------+----------+---------+
    |                                quote                                 |       characters       | stardate | episode |
    +----------------------------------------------------------------------+------------------------+----------+---------+
    | "... freedom ... is a worship word..." "It is our worship word too." | Cloud William and Kirk | NULL     |      52 |
    +----------------------------------------------------------------------+------------------------+----------+---------+
    (1 row)
    

Step 4. Perform an "enterprise" backup

Next, you'll use the enterprise BACKUP feature to create a backup of the startrek database on S3.

  1. If you requested and enabled a trial enterprise license in the Geo-Partitioning module, skip to step 2. Otherwise, request a trial enterprise license and then enable your license:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="SET CLUSTER SETTING cluster.organization = '<your organization>';"
    

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="SET CLUSTER SETTING enterprise.license = '<your license key>';"
    
  2. Use the BACKUP SQL statement to generate a backup of the startrek database and store it on S3. To ensure your backup doesn't conflict with anyone else's, prefix the filename with your name:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="BACKUP DATABASE startrek TO 's3://cockroach-training/[name]-training?AWS_ACCESS_KEY_ID={{site.training.aws_access_key}}&AWS_SECRET_ACCESS_KEY={{site.training.aws_secret_access_key}}';"
    
    +--------------------+-----------+--------------------+------+---------------+----------------+-------+
    |       job_id       |  status   | fraction_completed | rows | index_entries | system_records | bytes |
    +--------------------+-----------+--------------------+------+---------------+----------------+-------+
    | 322827820562808833 | succeeded |                  1 |  279 |           200 |             15 | 34673 |
    +--------------------+-----------+--------------------+------+---------------+----------------+-------+
    (1 row)
    

Step 5. Perform an "enterprise" restore

Again, imagine the tables in the startrek database have changed and you want to restore them from the enterprise backup.

  1. Drop the tables in the startrek database:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="DROP TABLE startrek.episodes,startrek.quotes CASCADE;"
    
  2. Confirm that the tables in the startrek database are gone:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="SHOW TABLES FROM startrek;"
    
    +-------+
    | Table |
    +-------+
    +-------+
    (0 rows)
    
  3. Restore the startrek database from the enterprise backup, again making sure to prefix the filename with your name:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="RESTORE startrek.* FROM 's3://cockroach-training/[name]-training?AWS_ACCESS_KEY_ID={{site.training.aws_access_key}}&AWS_SECRET_ACCESS_KEY={{site.training.aws_secret_access_key}}';"
    
    +--------------------+-----------+--------------------+------+---------------+----------------+-------+
    |       job_id       |  status   | fraction_completed | rows | index_entries | system_records | bytes |
    +--------------------+-----------+--------------------+------+---------------+----------------+-------+
    | 322828729641959425 | succeeded |                  1 |  279 |           200 |              0 | 24704 |
    +--------------------+-----------+--------------------+------+---------------+----------------+-------+
    (1 row)
    
  4. Check the contents of the startrek database again:

    $ ./cockroach sql \
    --insecure \
    --host=localhost:26257 \
    --execute="SELECT * FROM startrek.episodes LIMIT 1;" \
    --execute="SELECT * FROM startrek.quotes LIMIT 1;"
    
    +----+--------+-----+--------------+----------+
    | id | season | num |    title     | stardate |
    +----+--------+-----+--------------+----------+
    |  1 |      1 |   1 | The Man Trap |   1531.1 |
    +----+--------+-----+--------------+----------+
    (1 row)
    +----------------------------------------------------------------------+------------------------+----------+---------+
    |                                quote                                 |       characters       | stardate | episode |
    +----------------------------------------------------------------------+------------------------+----------+---------+
    | "... freedom ... is a worship word..." "It is our worship word too." | Cloud William and Kirk | NULL     |      52 |
    +----------------------------------------------------------------------+------------------------+----------+---------+
    (1 row)
    

Step 6. Clean up

In the next module, you'll start a new cluster from scratch, so take a moment to clean things up.

  1. Stop all CockroachDB nodes:

    $ pkill -9 cockroach
    
  2. Remove the nodes' data directories:

    $ rm -rf node1 node2 node3
    

What's next?

Cluster Upgrade