Use Userfile for Bulk Operations

On this page Carat arrow pointing down
Warning:
As of November 24, 2023, CockroachDB v22.1 is no longer supported. For more details, refer to the Release Support Policy.

To put files on your CockroachDB cluster without external servers, use userfile, a per-user bulk file storage. To interact with userfile, use the following commands:

Once a userfile is uploaded, you can run IMPORT.

For PGDUMP and MYSQLDUMP formats, you can use cockroach import to upload a userfile, import its data, and delete the userfile in one command.

Upload a file

Note:

A userfile uses storage space in the cluster, and is replicated with the rest of the cluster's data. We recommend using cockroach userfile upload for quick uploads from your client (about 15MB or smaller).

icon/buttons/copy
$ cockroach userfile upload /Users/maxroach/Desktop/test-data.csv /test-data.csv --certs-dir=certs
successfully uploaded to userfile://defaultdb.public.userfiles_root/test-data.csv

For more information, see cockroach userfile upload.

List files

icon/buttons/copy
$ cockroach userfile list '*.csv' --certs-dir=certs
userfile://defaultdb.public.userfiles_root/test-data-2.csv
userfile://defaultdb.public.userfiles_root/test-data.csv

For more information, see cockroach userfile list.

Get files

icon/buttons/copy
$ cockroach userfile get test-data.csv --certs-dir=certs

For more information, see cockroach userfile get.

Delete files

icon/buttons/copy
$ cockroach userfile delete test-data.csv --certs-dir=certs
deleted userfile://defaultdb.public.userfiles_root/test-data.csv

For more information, see cockroach userfile delete.

Upload and import from a dump file

Note:

We recommend using cockroach import for quick imports from your client (about 15MB or smaller). For larger imports, use the IMPORT statement.

icon/buttons/copy
$ cockroach import db mysqldump /Users/maxroach/Desktop/test-db.sql --certs-dir=certs
successfully imported mysqldump file /Users/maxroach/Desktop/test-db.sql

For more information, see cockroach import.

Import from userfile

To import from userfile, first create the table that you would like to import into:

icon/buttons/copy
CREATE TABLE customers (
  id INT,
  dob DATE,
  first_name STRING,
  last_name STRING,
  joined DATE
);

Then, use IMPORT INTO to import data into the table:

icon/buttons/copy
IMPORT INTO customers (id, dob, first_name, last_name, joined)
   CSV DATA ('userfile:///test-data.csv');

userfile:/// references the default path (userfile://defaultdb.public.userfiles_$user/).

        job_id       |  status   | fraction_completed |  rows  | index_entries |  bytes
---------------------+-----------+--------------------+--------+---------------+-----------
  599865027685613569 | succeeded |                  1 | 300024 |             0 | 13389972
(1 row)

For more import options, see IMPORT INTO.

Backup and restore with userfile

We recommend starting backups from a time at least 10 seconds in the past using AS OF SYSTEM TIME. Read our guidance in the Performance section on the BACKUP page.

Note:

Only database and table-level backups are possible when using userfile as storage. Restoring cluster-level backups will not work because userfile data is stored in the defaultdb database, and you cannot restore a cluster with existing table data.

When working on the same cluster, userfile storage allows for database and table-level backups.

First, run the following statement to backup a database to a directory in the default userfile space:

BACKUP DATABASE bank INTO 'userfile://defaultdb.public.userfiles_$user/bank-backup' AS OF SYSTEM TIME '-10s';

This directory will hold the files that make up a backup; including the manifest file and data files.

Note:

When backing up from a cluster and restoring a database or table that is stored in your userfile space to a different cluster, you can run cockroach userfile get to download the backup files to a local machine and cockroach userfile upload -r <location/of/file> <userfile destination/of/file> --url {CONNECTION STRING} to upload to the userfile of the restoring cluster.

In cases when your database needs to be restored, run the following:

RESTORE DATABASE bank FROM LATEST IN 'userfile://defaultdb.public.userfiles_$user/bank-backup';

It is also possible to run userfile:///bank-backup as userfile:/// refers to the default path userfile://defaultdb.public.userfiles_$user/.

Once the backup data is no longer needed, delete from the userfile storage with the following command:

cockroach userfile delete bank-backup --url {CONNECTION STRING}

If you use cockroach userfile delete {file}, it will take as long as the garbage collection to be removed from disk.

See also


Yes No
On this page

Yes No