Use Userfile Storage

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

• cockroach userfile upload
• cockroach userfile list
• cockroach userfile get
• cockroach userfile delete

Once a userfile is uploaded, you can run IMPORT INTO.

You can create an external connection to represent an external storage or sink URI. This allows you to specify the external connection’s name in statements rather than the provider-specific URI. For detail on using external connections, see the page.

Upload a file

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

$ 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 .

List files

$ 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 .

Get files

$ cockroach userfile get test-data.csv --certs-dir=certs

For more information, see .

Delete files

$ cockroach userfile delete test-data.csv --certs-dir=certs

deleted userfile://defaultdb.public.userfiles_root/test-data.csv

For more information, see .

Import from userfile

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

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:

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 .

Backup and restore with userfile

We recommend starting backups from a time at least 10 seconds in the past using . Read our guidance in the section on the page.

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.

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 to download the backup files to a local machine and 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 to be removed from disk.

See also