CockroachDB Setup Guide Private Preview

Follow these instructions to replicate your CockroachDB database to your destination using Fivetran.

Prerequisites

To connect your CockroachDB database to Fivetran, you need:

• CockroachDB version 22.1.0 - 23.2.3.
• The IP (e.g., 1.2.3.4) or domain (your.server.com) of one node in your CockroachDB cluster.
• Your node's port (usually 26257).
• TLS 1.3 enabled on your database. Learn more in CockroachDB's Transport Layer Security documentation.
• Changefeeds enabled on your CockroachDB cluster. Learn how to enable changefeeds in CockroachDB's Create and Configure Changefeeds documentation.

Setup instructions

Choose connection method

IMPORTANT: Do not perform this step if you want to use the Hybrid Deployment model for your data pipeline.

First, decide whether to connect Fivetran to your CockroachDB database directly or using an SSH tunnel.

Create user

Create a database user for Fivetran's exclusive use.

1. Open a connection to your CockroachDB database.

2. Create a user for Fivetran by executing one of the following SQL commands, depending on your CockroachDB version. Replace <username> and some-password with a username and password of your choice.

NOTE: For both versions, Fivetran uses the VIEWCLUSTERSETTING privilege to verify that necessary settings are enabled for replication.

CREATE USER <username> WITH LOGIN PASSWORD 'some-password' CONTROLCHANGEFEED VIEWCLUSTERSETTING;

CREATE USER <username> WITH LOGIN PASSWORD 'some-password' VIEWCLUSTERSETTING;

Grant read-only access

Grant the Fivetran user read-only access to all tables by running the following commands. Each of the commands in the following example grants access to two schemas, public and some_schema, respectively, but you can grant access to one or more schemas in each command by making the corresponding schema name changes.

GRANT USAGE ON SCHEMA "public", "some_schema" TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA "public", "some_schema" TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public", "some_schema" GRANT SELECT ON TABLES TO <username>;

NOTE: The last command of the three provided above makes sure that any future tables will be accessible to Fivetran.

(Optional) Restrict access to tables

If you want to limit Fivetran's access to your tables, you can revoke the Fivetran user's access to tables that you do not want to sync.

REVOKE SELECT ON TABLE "public"."table", "some_schema"."table" FROM <username>;

Enable changefeeds on your cluster

Fivetran uses changefeeds for the incremental sync method. Run the following commands to ensure that your cluster has changefeeds enabled:

SET CLUSTER SETTING kv.rangefeed.enabled = true;
SET CLUSTER SETTING feature.changefeed.enabled = true;

Finish Fivetran configuration

1. In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.

2. In the host field, enter your cluster node's IP (for example, 1.2.3.4) or domain (for example, your.server.com).

3. Enter your node's port number. The port number is usually 26257.

4. Enter the Fivetran-specific user that you created in the Create User step.

5. Enter the password you created in the Create User step.

6. Enter the name of your database (for example, your_database).

7. (Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected in the Select an existing agent drop-down menu. To use a different agent, select the agent of your choice, and then select the same agent for your destination.

8. (Optional for Hybrid Deployment) If you want to use a TLS connection between the connector and your source database in your own environment, set the Require TLS toggle to ON.

   IMPORTANT: We do not support TLS 1.0.

9. (Not applicable to Hybrid Deployment) Choose your connection method. If you selected Connect via an SSH tunnel, copy or make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel, and provide the following information:

   • SSH hostname (do not use a load balancer's IP address/hostname)
   • SSH port
   • SSH user
   • If you enabled TLS on your database in the Choose connection method step, set the Require TLS through tunnel toggle to ON.

10. Click Save & Test. Fivetran tests and validates our connection to your CockroachDB cluster. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your CockroachDB cluster and that it is properly configured:

• The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It generates a pop-up window where you must verify the SSH fingerprint. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you are connecting directly.)
• The Validating Host Test validates that the value you provided in the setup form is valid as a host name.
• The Connection Test validates that Fivetran can connect to your cluster using the host information and database credentials you provided in the setup form. It also generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that Fivetran can connect to your cluster using TLS.
• The Verifying CockroachDB Version Test verifies that the version of your cluster is within the range that Fivetran supports. If Fivetran does not support your version of CockroachDB, a warning is displayed indicating that your connector may not function as expected.
• The VIEWCLUSTERSETTING Role Option Test verifies that the Fivetran user you provided in the setup form has the VIEWCLUSTERSETTING role option. Fivetran uses this privilege to validate that your cluster settings are correctly configured for your connector.
• The CONTROLCHANGEFEED Role Option Test verifies that the Fivetran user has the CONTROLCHANGEFEED role option. CockroachDB requires this privilege to create a CHANGEFEED on a table in major version 22. This test is skipped if your CockroachDB cluster is major version 23 or higher.
• The kv.rangefeed.enabled Cluster Setting Test verifies that the kv.rangefeed.enabled cluster setting is enabled. CockroachDB requires this setting to create a CHANGEFEED on a table.
• The feature.changefeed.enabled Cluster Setting Test verifies that the feature.changefeed.enabled cluster setting is enabled. CockroachDB requires this setting to create a CHANGEFEED on a table.

Related articles

description Connector Overview

account_tree Schema Information

home Documentation Home