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
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).
$ 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
$ 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
$ cockroach userfile get test-data.csv --certs-dir=certs
For more information, see cockroach userfile get
.
Delete files
$ 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
We recommend using cockroach import
for quick imports from your client (about 15MB or smaller). For larger imports, use the IMPORT statement.
$ 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:
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 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.
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 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.