> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Use Userfile Storage

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

To put files on your CockroachDB cluster without external servers, use `userfile`, a per-user file storage. To interact with `userfile`, use the following <InternalLink path="cockroach-commands">commands</InternalLink>:

* [`cockroach userfile upload`](#upload-a-file)
* [`cockroach userfile list`](#list-files)
* [`cockroach userfile get`](#get-files)
* [`cockroach userfile delete`](#delete-files)

Once a userfile is uploaded, you can run [`IMPORT`](#import-from-userfile).

For `PGDUMP` and `MYSQLDUMP` formats, you can use [`cockroach import`](#upload-and-import-from-a-dump-file) to upload a userfile, import its data, and delete the userfile in one command.

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 <InternalLink path="create-external-connection">`CREATE EXTERNAL CONNECTION`</InternalLink> page.

## 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 <InternalLink path="cockroach-userfile-upload">`cockroach userfile upload`</InternalLink> for quick uploads from your client (about 15MB or smaller).
</Note>

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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 <InternalLink path="cockroach-userfile-upload">`cockroach userfile upload`</InternalLink>.

## List files

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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 <InternalLink path="cockroach-userfile-list">`cockroach userfile list`</InternalLink>.

## Get files

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach userfile get test-data.csv --certs-dir=certs
```

For more information, see <InternalLink path="cockroach-userfile-get">`cockroach userfile get`</InternalLink>.

## Delete files

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach userfile delete test-data.csv --certs-dir=certs
```

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

For more information, see <InternalLink path="cockroach-userfile-delete">`cockroach userfile delete`</InternalLink>.

## Upload and import from a dump file

<Note>
  We recommend using <InternalLink path="cockroach-import">`cockroach import`</InternalLink> for quick imports from your client (about 15MB or smaller). For larger imports, use the <InternalLink path="import">IMPORT</InternalLink> statement.
</Note>

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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 <InternalLink path="cockroach-import">`cockroach import`</InternalLink>.

## Import from `userfile`

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="import-into">`IMPORT INTO`</InternalLink>.

## Backup and restore with `userfile`

We recommend starting backups from a time at least 10 seconds in the past using <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink>. Read our guidance in the <InternalLink path="backup#performance">Performance</InternalLink> section on the <InternalLink path="backup">`BACKUP`</InternalLink> 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.
</Note>

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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="cockroach-userfile-get">`cockroach userfile get`</InternalLink> to download the backup files to a local machine and <InternalLink path="cockroach-userfile-upload#upload-a-directory-recursively">`cockroach userfile upload -r <location/of/file> <userfile destination/of/file> --url {CONNECTION STRING}`</InternalLink> to upload to the `userfile` of the restoring cluster.
</Note>

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach userfile delete bank-backup --url {CONNECTION STRING}
```

If you use `cockroach userfile delete {file}`, it will take as long as the <InternalLink path="configure-replication-zones">garbage collection</InternalLink> to be removed from disk.

## See also

* <InternalLink path="cockroach-userfile-upload">`cockroach userfile upload`</InternalLink>
* <InternalLink path="cockroach-userfile-list">`cockroach userfile list`</InternalLink>
* <InternalLink path="cockroach-userfile-get">`cockroach userfile get`</InternalLink>
* <InternalLink path="cockroach-userfile-delete">`cockroach userfile delete`</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>
* <InternalLink path="import">`IMPORT`</InternalLink>
* <InternalLink path="import-into">`IMPORT INTO`</InternalLink>