Prerequisites
-
An up-and-running Redshift instance. Instructions for creating a Redshift destination are outside the scope of this tutorial; our instructions assume that you have an instance up and running. For help getting started with Redshift, refer to Amazon’s documentation.
Permissions in Amazon Web Services (AWS) that allow you to:
- Create/manage Security Groups, which is required to whitelist Stitch’s IP addresses.
- View database details, which is required for retrieving the database’s connection details.
Superuser privileges, or privileges that allow you to create users and grant privileges. This is required to create a database user for Stitch.
Step 1: Configure database connection settings
In this step, you’ll configure the database server to allow traffic from Stitch to access it. There are two ways to connect your database:
- A direct connection will work if your database is publicly accessible.
- An SSH tunnel is required if your database isn’t publicly accessible. This method uses a publicly accessible instance, or an SSH server, to act as an intermediary between Stitch and your database. The SSH server will forward traffic from Stitch through an encrypted tunnel to the private database.
Click the option you’re using below and follow the instructions.
For Stitch to successfully connect with your database instance, you’ll need to add our IP addresses to the appropriate Security Group via the AWS management console.
Security Groups must reside in the same VPC as the instance. Use the instructions below to create a security group for Stitch and grant access to the VPC.
- Log into your AWS account.
- Navigate to the Security Group Management page, typically Services > Compute > EC2.
- Click the Security Groups option, under Network & Security in the menu on the left side of the page.
- Click Create Security Group.
- In the window that displays, fill in the fields as follows:
- Security group name: Enter a unique name for the Security Group. For example:
Stitch
- Description: Enter a description for the security group.
- VPC: Select the VPC that contains the database you want to connect to Stitch. Note: The Security Group and database must be in the same VPC, or the connection will fail.
- Security group name: Enter a unique name for the Security Group. For example:
- In the Inbound tab, click Add Rule.
- Fill in the fields as follows:
- Type: Select
Custom TCP Rule
- Port Range: Enter the port your database uses. (
5439
by default) -
CIDR, IP or Security Group: Enter one of the IP addresses listed below:
-
52.23.137.21/32
-
52.204.223.208/32
-
52.204.228.32/32
-
52.204.230.227/32
-
- Type: Select
- Click Add Rule to add an additional Inbound rule.
-
Repeat steps 6-8 until all the IP addresses above have been added:
- When finished, click Create to create the Security Group.
- Follow the steps in the Setting up an SSH Tunnel for a database in Amazon Web Services guide to set up an SSH tunnel for Redshift.
- Complete the steps in this guide after the SSH setup is complete.
Step 2: Create a Stitch Redshift database user
In the following tabs are the instructions for creating a Stitch Redshift database user and explanations for the permissions Stitch requires.
-
If you haven’t already, connect to your Redshift instance using your SQL client.
-
After connecting, run this command to create a user named
stitch
. Replace<password>
with a secure password: -
Next, you’ll assign the
CREATE
permissions to the Stitch user. For<database_name>
, enter the name of the database where all Stitch-replicated data should be loaded.Note: This must be a pre-existing database.
-
If you restricted access to the system tables, you’ll also need to run the following commands to grant the Stitch user
SELECT
permissions.
In the table below are the database user privileges Stitch requires to connect to and load data into Redshift.
Privilege name | Reason for requirement |
CREATE ON DATABASE |
Required to create the necessary database objects to load and store your data.
Note: The |
Ownership of all schemas and tables that Stitch will load to |
Stitch occasionally needs to run Note: If the database user you use to connect to Redshift creates the schemas and tables, you will not need to grant explicit ownership permissions. If another user creates the schemas and tables prior to Stitch loading data into them, you will need to alter ownership to the Stitch database user. Failure to do so will result in replication issues. |
SELECT ON ALL TABLES IN information_schema |
Required to select rows from tables in the Note: Stitch will only ever read data from systems tables. |
SELECT ON ALL TABLES IN pg_catalog |
Required to select rows from tables in the Note: Stitch will only ever read data from systems tables. |
Step 3: Connect Stitch
To complete the setup, you need to enter your Redshift connection details into the Destination Settings page in Stitch.
Step 3.1: Locate the Redshift connection details
- Sign into the AWS Console, if needed.
- Navigate to the Redshift option.
-
On the Redshift Dashboard page, click the Clusters option on the left side of the page. This will open the Redshift Clusters page.
-
In the list of clusters, locate and click on the instance you want to connect to Stitch. This will open the Cluster Details page.
- Locate the following fields:
- Endpoint
-
Database Name: This field contains the name of the database used to launch the instance. You’ll only need this info if you want to connect this specific database to Stitch.
You can connect this database to Stitch, or another database within Redshift.
- Port: This is the port used by the database.
Leave this page open for now - you’ll need it to complete the setup.
Step 3.2: Enter connection details into Stitch
- If you aren’t signed into your Stitch account, sign in now.
-
Click the Destination tab.
- Locate and click the Redshift icon.
-
Fill in the fields as follows:
-
Host (Endpoint): Paste the Endpoint address from the Redshift Details page in AWS into this field. Don’t include the port number, if it’s appended to the end of the endpoint string - this will cause errors.
-
Port: Enter the port used by the instance. The default is
5439
. -
Username: Enter the Stitch Redshift database user’s username.
-
Password: Enter the password for the Stitch Redshift database user.
-
Database: Enter the name of the Redshift database you want to connect to Stitch.
-
Step 3.3: Define SSH connection details
If you’re using an SSH tunnel to connect your Redshift database to Stitch, you’ll also need to define the SSH settings. Refer to the Setting up an SSH Tunnel for a database in Amazon Web Services guide for assistance with completing these fields.
- Click the Encryption Type menu.
-
Select SSH to display the SSH fields.
-
Fill in the fields as follows:
-
Remote Address: Paste the Public DNS of the SSH sever (EC2 instance) into this field. Refer to the Amazon SSH guide for instructions on retrieving this info.
-
SSH Port: Enter the SSH port of the SSH server (EC2 instance) into this field. This will usually be
22
. -
SSH User: Enter the Stitch Linux (SSH) user’s username.
-
Step 3.4: Save the destination
When finished, click Check and Save.
Stitch will perform a connection test to the Redshift database; if successful, a Success! mesage will display at the top of the screen. Note: This test may take a few minutes to complete.
Related | Troubleshooting |
Questions? Feedback?
Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.