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

# CREATE SCHEDULE FOR CHANGEFEED

`CREATE SCHEDULE FOR CHANGEFEED` allows you to create a scheduled changefeed to export data out of CockroachDB. Scheduled changefeeds have the scale, observability, and endpoint sink options that changefeed jobs include, with the convenience of setting a regular schedule. A changefeed job created with `CREATE SCHEDULE FOR CHANGEFEED` performs a one-time table scan using the [initial scan](https://docs.cockroachlabs.com/docs/stable/create-changefeed.md#initial-scan) functionality to create an export of your table data.

For more detail on using changefeeds to create an export of your table data, see [Export Data with Changefeeds](https://docs.cockroachlabs.com/docs/stable/export-data-with-changefeeds.md).

## Required privileges

> [!CAUTION]
>
> As of v25.1, **viewing and managing** a changefeed job by users with the [`CHANGEFEED` privilege](#changefeed-privilege) is **deprecated**. This functionality of the `CHANGEFEED` privilege will be removed in a future release.
>
> We recommend transitioning users that need to view and manage running changefeed jobs to [roles](https://docs.cockroachlabs.com/docs/stable/create-role.md) that own the [jobs](https://docs.cockroachlabs.com/docs/stable/show-jobs.md) or [granting](https://docs.cockroachlabs.com/docs/stable/grant.md) them the `VIEWJOB` or `CONTROLJOB` privilege. For more details, refer to [View and manage changefeed jobs](#view-and-manage-changefeed-jobs).

### Privilege model

> [!TIP]
>
> For fine-grained access control, we recommend using the system-level privileges [`CHANGEFEED`](#changefeed-privilege) and [`CONTROLJOB` / `VIEWJOB`](#view-and-manage-changefeed-jobs).

The following summarizes the operations users can run depending on whether the assigned privileges are at the job or table level:

| Granted privileges | Usage |
| - | - |
| `CHANGEFEED` | Create changefeeds on tables. For details, refer to [`CHANGEFEED` privilege](#changefeed-privilege). **Deprecated**: View and manage changefeed jobs on tables. Instead, transition users that need to view and manage running changefeed jobs to [roles](https://docs.cockroachlabs.com/docs/stable/create-role.md) that own the [jobs](https://docs.cockroachlabs.com/docs/stable/show-jobs.md) or [granting](https://docs.cockroachlabs.com/docs/stable/grant.md) them the `VIEWJOB` or `CONTROLJOB` privilege. For more details, refer to [View and manage changefeed jobs](#view-and-manage-changefeed-jobs). |
| `CHANGEFEED` + [`USAGE`](https://docs.cockroachlabs.com/docs/stable/create-external-connection#required-privileges) on external connection | Create changefeeds on tables to an external connection URI. For details, refer to [`CHANGEFEED` privilege](#changefeed-privilege). **Deprecated**: View and manage changefeed jobs on tables. Instead, transition users that need to view and manage running changefeed jobs to [roles](https://docs.cockroachlabs.com/docs/stable/create-role.md) that own the [jobs](https://docs.cockroachlabs.com/docs/stable/show-jobs.md) or [granting](https://docs.cockroachlabs.com/docs/stable/grant.md) them the `VIEWJOB` or `CONTROLJOB` privilege. For more details, refer to [View and manage changefeed jobs](#view-and-manage-changefeed-jobs). **Note:** If you need to manage access to changefeed sink URIs, set the `changefeed.permissions.require_external_connection_sink.enabled=true` cluster setting. This will mean that users with these privileges can **only** create changefeeds on external connections. |
| Job ownership | [View](https://docs.cockroachlabs.com/docs/stable/show-jobs.md#show-changefeed-jobs) and manage changefeed jobs ([pause](https://docs.cockroachlabs.com/docs/stable/pause-job.md), [resume](https://docs.cockroachlabs.com/docs/stable/resume-job.md), and [cancel](https://docs.cockroachlabs.com/docs/stable/cancel-job.md)). For details, refer to [View and manage changefeed jobs](#view-and-manage-changefeed-jobs). |
| `CONTROLJOB` | Manage changefeed jobs ([pause](https://docs.cockroachlabs.com/docs/stable/pause-job.md), [resume](https://docs.cockroachlabs.com/docs/stable/resume-job.md), and [cancel](https://docs.cockroachlabs.com/docs/stable/cancel-job.md)). For details, refer to [View and manage changefeed jobs](#view-and-manage-changefeed-jobs). |
| `VIEWJOB` | [View](https://docs.cockroachlabs.com/docs/stable/show-jobs.md#show-changefeed-jobs) changefeed jobs. For details, refer to [View and manage changefeed jobs](#view-and-manage-changefeed-jobs). |
| `SELECT` | Create a sinkless changefeed that emits messages to a SQL client. |
| **Deprecated** `CONTROLCHANGEFEED` role option + `SELECT` | Create changefeeds on tables. Users with the `CONTROLCHANGEFEED` role option must have `SELECT` on each table, even if they are also granted the `CHANGEFEED` privilege. The `CONTROLCHANGEFEED` role option will be removed in a future release. We recommend using the system-level privileges [`CHANGEFEED`](#changefeed-privilege) and [`CONTROLJOB`/ `VIEWJOB`](#view-and-manage-changefeed-jobs) for fine-grained access control. |
| `admin` | Create, view, and manage changefeed jobs. |

#### `CHANGEFEED` privilege

> [!NOTE]
>
> Viewing and managing changefeed jobs with the `CHANGEFEED` privilege is **deprecated** as of v25.1. Instead, transition users that need to view and manage running changefeed jobs to [roles](https://docs.cockroachlabs.com/docs/stable/create-role.md) that own the [jobs](https://docs.cockroachlabs.com/docs/stable/show-jobs.md) or [granting](https://docs.cockroachlabs.com/docs/stable/grant.md) them the `VIEWJOB` or `CONTROLJOB` privilege. For more details, refer to [View and manage changefeed jobs](#view-and-manage-changefeed-jobs).

You can [grant](https://docs.cockroachlabs.com/docs/stable/grant.md#grant-privileges-on-specific-tables-in-a-database) a user the `CHANGEFEED` privilege to allow them to create changefeeds on a specific table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT CHANGEFEED ON TABLE example_table TO user;
```

When you grant a user the `CHANGEFEED` privilege on a set of tables, they can create changefeeds on the target tables even if the user does **not** have the [`CONTROLCHANGEFEED` role option](https://docs.cockroachlabs.com/docs/stable/alter-role#role-options) or the `SELECT` privilege on the tables.

These users will be able to create changefeeds, but they will not be able to run a `SELECT` query on that data directly. However, they could still read this data indirectly if they have read access to the [sink](https://docs.cockroachlabs.com/docs/stable/changefeed-sinks.md).

You can add `CHANGEFEED` to the user or role's [default privileges](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#default-privileges) with [`ALTER DEFAULT PRIVILEGES`](https://docs.cockroachlabs.com/docs/stable/alter-default-privileges#grant-default-privileges-to-a-specific-role):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DEFAULT PRIVILEGES GRANT CHANGEFEED ON TABLES TO user;
```

To restrict a user's access to changefeed data and sink credentials, enable the `changefeed.permissions.require_external_connection_sink.enabled` cluster setting. When you enable this setting, users with the [`CHANGEFEED` privilege](https://docs.cockroachlabs.com/docs/stable/create-changefeed#required-privileges) on a set of tables can only create changefeeds into [external connections](https://docs.cockroachlabs.com/docs/stable/create-external-connection.md).

#### View and manage changefeed jobs

Users can [view](https://docs.cockroachlabs.com/docs/stable/show-jobs.md#show-changefeed-jobs) and manage changefeed jobs when one of the following are met:

* **Job ownership**: They own the job, or are a member of a role that owns a job.
* **Global privileges**: They are assigned [`CONTROLJOB` or `VIEWJOB`](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization#supported-privileges).

To give a set of users access to a specific job, or set of jobs, assign them to a [role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#users-and-roles) that owns the job(s).

You can transfer ownership of a job to a role or user using the [`ALTER JOB`](https://docs.cockroachlabs.com/docs/stable/alter-job) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER JOB job_ID OWNER TO role_name;
```

## Synopsis

![create\_schedule\_for\_changefeed syntax diagram](https://mintcdn.com/cockroachlabs/R40d76ku6ZWAJ59v/images/sql-diagrams/v26.3/create_schedule_for_changefeed.svg?fit=max\&auto=format\&n=R40d76ku6ZWAJ59v\&q=85\&s=763dbdb3125728cfde44e6e7cfaeb639)

### Parameters

| Parameter | Description |
| - | - |
| `IF NOT EXISTS` | A scheduled changefeed should not be created if the `schedule_label` already exists. You will receive an error if the schedule label already exists, or if `schedule_label` is not defined when using `IF NOT EXISTS`. |
| `schedule_label` | The name for the scheduled changefeed. This is optional and does not need to be unique. If you do not define a name, the label will default to `CHANGEFEED` with the timestamp of when you created the schedule. |
| `changefeed_targets` | The tables to target with the changefeed. For example, `movr.users, movr.rides`. |
| `changefeed_sink` | The [changefeed sink URI](https://docs.cockroachlabs.com/docs/stable/changefeed-sinks.md). |
| `changefeed_option` | The [options](https://docs.cockroachlabs.com/docs/stable/create-changefeed.md#options) to control the behavior of your changefeed. For example, `WITH format = csv, full_table_name`. See [Changefeed options](#changefeed-options) for a list of available options. |
| `target_list` | The columns to emit data from if you're using a [CDC query](https://docs.cockroachlabs.com/docs/stable/cdc-queries.md) expression. |
| `insert_target` | The target tables for the changefeed if you're using a [CDC query](https://docs.cockroachlabs.com/docs/stable/cdc-queries.md) expression. |
| `where_clause` | An optional `WHERE` clause to apply filters to the table if you're using a [CDC query](https://docs.cockroachlabs.com/docs/stable/cdc-queries.md) expression. |
| `crontab` | The frequency of the changefeed. The schedule is specified as a `STRING` in [crontab format](https://wikipedia.org/wiki/Cron). All times in UTC. For example, `'@daily'`, `'@hourly'`, `'1 0 * * *'`. |
| `schedule_option` | The schedule options to control the schedule's behavior. For example, `first_run = now`. See [Schedule options](#schedule-options). |

## Changefeed options

You can include the changefeed options listed on the [`CREATE CHANGEFEED`](https://docs.cockroachlabs.com/docs/stable/create-changefeed#options) page to modify the behavior of your changefeed. The following options are **not** compatible with scheduled changefeeds:

* `diff`
* `end_time`
* `mvcc_timestamp`
* `resolved`
* `updated`

Scheduled changefeeds have the [`initial_scan = 'only'`](https://docs.cockroachlabs.com/docs/stable/create-changefeed#initial-scan) option included implicitly. You cannot specify `initial_scan` as `'yes'` or `'no'`.

## Schedule options

| Option | Value | Description |
| - | - | - |
| `first_run` | [`TIMESTAMP`](https://docs.cockroachlabs.com/docs/stable/timestamp) / `now` | Execute the first run of the schedule at this time. If you do not specify `first_run`, the schedule will execute based on the next `RECURRING` time set by the crontab. |
| `on_execution_failure` | `retry` / `reschedule` / `pause` | Determine how the schedule handles an error.  `retry`: Retry the changefeed immediately. `reschedule`: Reschedule the changefeed based on the `RECURRING` expression. `pause`: Pause the schedule. This requires that you [resume the schedule](https://docs.cockroachlabs.com/docs/stable/resume-schedules.md) manually. **Default:** `reschedule` |
|  `on_previous_running` | `start` / `skip` / `wait` | Control whether the changefeed schedule should start a changefeed if the previous scheduled changefeed is still running. `start`: Start the new changefeed anyway, even if the previous one is running. `skip`: Skip the new changefeed and run the next changefeed based on the `RECURRING` expression. `wait`: Wait for the previous changefeed to complete. **Default:** `wait` |

> [!NOTE]
>
> To avoid multiple clusters running the same schedule concurrently, changefeed schedules will [pause](https://docs.cockroachlabs.com/docs/stable/pause-schedules.md) when [restored](https://docs.cockroachlabs.com/docs/stable/restore.md) onto a different cluster or after [physical cluster replication](https://docs.cockroachlabs.com/docs/stable/failover-replication.md) has completed.

## Examples

Before running any of the examples in this section, it is necessary to enable the `kv.rangefeed.enabled` cluster setting. If you are working on a CockroachDB Standard or Basic cluster, this cluster setting is enabled by default.

The [Changefeed Sinks](https://docs.cockroachlabs.com/docs/stable/changefeed-sinks.md) page provides detail on the available sinks for your change data messages and connection URIs. We recommend using [external connections](https://docs.cockroachlabs.com/docs/stable/create-external-connection.md) to interact with external sinks. The examples in this section use an external connection URI for the changefeed sink.

### Create a scheduled changefeed

The following statement sets up a scheduled changefeed named `users_rides_nightly` that will send changefeed messages in `CSV` format 1 minute past midnight every night. As soon as the statement is run, the first changefeed run will execute immediately:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEDULE users_rides_nightly FOR CHANGEFEED users, rides INTO 'external://kafka-sink' WITH format=csv RECURRING '1 0 * * *' WITH SCHEDULE OPTIONS first_run=now, on_execution_failure=retry, on_previous_running=skip;
```

The [schedule options](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-changefeed.md#schedule-options) control the schedule's behavior:

* If it runs into an error, `on_execution_failure=retry` will ensure that the schedule retries the changefeed immediately.
* If the previous scheduled changefeed is still running, `on_previous_running=skip` will skip a new changefeed at the next scheduled time.

The output will confirm that the changefeed has added the `initial_scan = 'only'` option implicitly:

```
     schedule_id     |     label     | status |           first_run           | schedule  |                                                      changefeed_stmt
---------------------+---------------+--------+-------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------
  947257854259855361 | users_nightly | ACTIVE | 2024-02-28 20:02:35.716699+00 | 1 0 * * * | CREATE CHANGEFEED FOR TABLE movr.public.users, TABLE movr.public.rides INTO 'external://kafka-sink' WITH OPTIONS (format = 'csv', initial_scan = 'only')
(1 row)

NOTICE: added missing initial_scan='only' option to schedule changefeed
```

### Rotate the sink credentials of a scheduled changefeed

A scheduled changefeed stores only the name of its external connection. Each scheduled run creates a new changefeed job that resolves the external connection when the run starts. To rotate the credentials in the sink URI, for example an expiring cloud storage access key, update the external connection instead of the schedule. You do not need to drop and recreate the schedule.

This example creates an external connection for an Amazon S3 sink and a nightly scheduled changefeed that uses it:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE EXTERNAL CONNECTION nightly_export AS 's3://{bucket name}/{path}?AWS_ACCESS_KEY_ID={access key}&AWS_SECRET_ACCESS_KEY={secret access key}';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEDULE nightly_export FOR CHANGEFEED users INTO 'external://nightly_export' WITH format=csv RECURRING '0 20 * * *' WITH SCHEDULE OPTIONS on_execution_failure=retry, on_previous_running=skip;
```

When the credentials change, update the external connection with [`ALTER EXTERNAL CONNECTION`](https://docs.cockroachlabs.com/docs/stable/alter-external-connection). CockroachDB validates the new URI when you run the statement, and the next scheduled run uses the updated connection:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER EXTERNAL CONNECTION nightly_export AS 's3://{bucket name}/{path}?AWS_ACCESS_KEY_ID={new access key}&AWS_SECRET_ACCESS_KEY={new secret access key}';
```

Update the external connection between scheduled runs. To avoid rotating credentials in the URI, you can use [`AUTH=implicit` with `ASSUME_ROLE`](https://docs.cockroachlabs.com/docs/stable/cloud-storage-authentication) in the connection URI instead of access keys.

### Create a scheduled changefeed with CDC queries

You can use CDC queries with scheduled changefeeds to define expression syntax that selects columns and applies filters to further restrict or transform the data in your changefeed messages. When you add this expression syntax to your changefeed statement, you can only target one table.

For guidance on syntax and more example use cases, see [Change Data Capture Queries](https://docs.cockroachlabs.com/docs/stable/cdc-queries.md).

This scheduled changefeed filters for the usage of promotion codes in the [`movr`](https://docs.cockroachlabs.com/docs/stable/movr) database and sends the changefeed messages on a daily basis:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEDULE promo_code FOR CHANGEFEED INTO 'external://kafka-sink' AS SELECT user_id, usage_count FROM movr.user_promo_codes WHERE usage_count > 1 RECURRING '@daily' WITH SCHEDULE OPTIONS first_run=now, on_execution_failure=reschedule, on_previous_running=skip;
```

### View scheduled changefeed details

To [show](https://docs.cockroachlabs.com/docs/stable/show-schedules.md) all scheduled changefeeds:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW SCHEDULES FOR CHANGEFEED;
```

To view the details of only running scheduled changefeeds:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW RUNNING SCHEDULES FOR CHANGEFEED;
```

To view the details of only paused scheduled changefeeds:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW PAUSED SCHEDULES FOR CHANGEFEED;
```

To view the details of a specific scheduled changefeed:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW SCHEDULE {schedule ID};
```

To [pause](https://docs.cockroachlabs.com/docs/stable/pause-schedules.md) a scheduled changefeed:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PAUSE SCHEDULE {schedule ID};
```

To [resume](https://docs.cockroachlabs.com/docs/stable/resume-schedules.md) a scheduled changefeed:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESUME SCHEDULE {schedule ID};
```

To [delete](https://docs.cockroachlabs.com/docs/stable/drop-schedules.md) a scheduled changefeed:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP SCHEDULE {schedule ID};
```

To see the full [`CREATE SCHEDULE` statement](https://docs.cockroachlabs.com/docs/stable/show-create-schedule) for the scheduled changefeed:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE SCHEDULE {schedule ID};
```

## See also

* [`CREATE CHANGEFEED`](https://docs.cockroachlabs.com/docs/stable/create-changefeed#initial-scan)
* [Change Data Capture Queries](https://docs.cockroachlabs.com/docs/stable/cdc-queries.md)
* [Export Data with Changefeeds](https://docs.cockroachlabs.com/docs/stable/export-data-with-changefeeds.md)
* [Changefeed Sinks](https://docs.cockroachlabs.com/docs/stable/changefeed-sinks.md)