Skip to content

Latest commit

 

History

History
143 lines (110 loc) · 6.08 KB

back-up-and-restore-data.md

File metadata and controls

143 lines (110 loc) · 6.08 KB
title summary toc
Back Up and Restore Data
Learn how to back up and restore a CockroachDB cluster.
false

The cockroach dump command outputs the SQL statements required to recreate a specific table and all its rows. This command can be used to back up each table of each database in a cluster. The output should also be suitable for importing into other relational databases, with minimal adjustments.

When cockroach dump is executed:

  • The table data is dumped as it appears at the time that the command is started. Any changes after the command starts will not be included in the dump.
  • If the dump takes longer than the ttlseconds replication setting for the table (24 hours by default), the dump may fail.
  • Reads, writes, and schema changes can happen while the dump is in progress.

{{site.data.alerts.callout_info}}The user must have the SELECT privilege on the target table.{{site.data.alerts.end}}

Synopsis

# Dump a table to stdout:
$ cockroach dump <database> <table> <flags>

# Dump a table to a file:
$ cockroach dump <database> <table> <flags> > dump-file.sql

# View help:
$ cockroach dump --help

Flags

The cockroach dump command supports the following flags as well as logging flags.

Flag Description
--ca-cert The path to the CA certificate. This flag is required if the cluster is secure.

Env Variable: COCKROACH_CA_CERT
--cert The path to the client certificate. This flag is required if the cluster is secure.

Env Variable: COCKROACH_CERT
--database
-d		 Not valid for the dump command. This flag will eventually be removed.
--host The server host to connect to. This can be the address of any node in the cluster.

Env Variable: COCKROACH_HOST
Default: localhost
--insecure Set this only if the cluster is insecure and running on multiple machines.

If the cluster is insecure and local, leave this out. If the cluster is secure, leave this out and set the --ca-cert, --cert, and -key flags.

Env Variable: COCKROACH_INSECURE
--key The path to the client key protecting the client certificate. This flag is required if the cluster is secure.

Env Variable: COCKROACH_KEY
--port
-p		 The server port to connect to.

Env Variable: COCKROACH_PORT
Default: 26257
--pretty Not valid for the dump command. This flag will eventually be removed.
--url The connection URL. If you use this flag, do not set any other connection flags.

For insecure connections, the URL format is:
--url=postgresql://<user>@<host>:<port>/<database>?sslmode=disable

For secure connections, the URL format is:
--url=postgresql://<user>@<host>:<port>/<database>
with the following parameters in the query string:
sslcert=<path-to-client-crt>
sslkey=<path-to-client-key>
sslmode=verify-full
sslrootcert=<path-to-ca-crt>

Env Variable: COCKROACH_URL
--user
-u		 The user executing the dump command. The user must have the SELECT privilege on the target table.

Default: root

Examples

Dump a table to the standard output

In this example, a user that has the SELECT privilege on the accounts table dumps the table to the standard output.

$ cockroach dump bank accounts --user=maxroach
CREATE TABLE accounts (
    id INT NOT NULL DEFAULT unique_rowid(),
    username STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (id),
    FAMILY f1 (id, username)
);

INSERT INTO accounts VALUES
    (160742616544116737, 'aberry'),
    (160742616544149505, 'bcompton'),
    (160742616544182273, 'cdouglas'),
    (160742616544215041, 'dellsinger'),
    (160742616544247809, 'efranklin'),
    (160742616544280577, 'fgoldberg');

Dump a table to a file

In this example, a user that has the SELECT privilege on the accounts table dumps the table to a file.

$ cockroach dump bank accounts --user=maxroach > accounts-backup.sql

$ cat accounts-backup.sql
CREATE TABLE accounts (
    id INT NOT NULL DEFAULT unique_rowid(),
    username STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (id),
    FAMILY f1 (id, username)
);

INSERT INTO accounts VALUES
    (160742616544116737, 'aberry'),
    (160742616544149505, 'bcompton'),
    (160742616544182273, 'cdouglas'),
    (160742616544215041, 'dellsinger'),
    (160742616544247809, 'efranklin'),
    (160742616544280577, 'fgoldberg');

Dump fails (user does not have SELECT privilege)

In this example, the dump command fails for a user that does not have the SELECT privilege on the accounts table.

$ cockroach dump bank accounts --user=leslieroach

CREATE TABLE accounts (
    id INT NOT NULL DEFAULT unique_rowid(),
    username STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (id),
    FAMILY f1 (id, username)
);
Error: pq: user leslieroach does not have SELECT privilege on table accounts
Failed running "dump"

Restore a table from a backup file

In this example, a user that has the CREATE privilege on the bank database uses the cockroach sql command to recreate a table, based on a file created by the dump command.

$ cat accounts-backup.sql
CREATE TABLE accounts (
    id INT NOT NULL DEFAULT unique_rowid(),
    username STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (id),
    FAMILY f1 (id, username)
);

INSERT INTO accounts VALUES
    (160742616544116737, 'aberry'),
    (160742616544149505, 'bcompton'),
    (160742616544182273, 'cdouglas'),
    (160742616544215041, 'dellsinger'),
    (160742616544247809, 'efranklin'),
    (160742616544280577, 'fgoldberg');

$ cockroach sql --database=bank --user=maxroach < accounts-backup.sql
CREATE TABLE
INSERT 6

See Also