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
First, decide whether to connect Fivetran to your CockroachDB database directly or using an SSH tunnel.
Connect directly (TLS required)
IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran. Learn more in CockroachDB's Transport Layer Security documentation.
Fivetran connects directly to your CockroachDB database. This is the simplest method.
If you connect directly, configure your firewall and/or other access control systems to allow incoming connections to your CockroachDB port (usually 26257
) from Fivetran's IPs.
Connect using SSH (TLS optional)
Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet.
To connect using SSH, configure your firewall and/or other access control systems to allow incoming connections from your SSH tunnel server's IP address to your CockroachDB port (usually 26257
).
Before you proceed to the next step, you must follow our SSH connection instructions. If you want Fivetran to tunnel SSH over TLS, you must first enable TLS on your database. Learn more in CockroachDB's Transport Layer Security documentation.
Create user
Create a database user for Fivetran's exclusive use.
Open a connection to your CockroachDB database.
Create a user for Fivetran by executing one of the following SQL commands, depending on your CockroachDB version. Replace
<username>
andsome-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.
Major version 22
CREATE USER <username> WITH LOGIN PASSWORD 'some-password' CONTROLCHANGEFEED VIEWCLUSTERSETTING;
NOTE: CockroachDB requires the CONTROLCHANGEFEED privilege to create a CHANGEFEED on a table in major version 22. This privilege is deprecated in later versions.
Major version 23 or higher
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
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.
In the host field, enter your cluster node's IP (for example,
1.2.3.4
) or domain (for example,your.server.com
).Enter your node's port number. The port number is usually
26257
.Enter the Fivetran-specific user that you created in the Create User step.
Enter the password you created in the Create User step.
Enter the name of your database (for example,
your_database
).Choose your connection method. If you selected Connect via an SSH tunnel, 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.
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