Connecting an Amazon PostgreSQL-RDS Data Warehouse to Stitch

Amazon Relational Database Services (RDS) is a managed database service that runs on familiar database engines like PostgreSQL.

In this tutorial, we’ll walk you through how to spin up a Postgres-RDS instance and then connect it to Stitch as a destination, or data warehouse.

Prerequisites

The only requirement for using PostgreSQL with Stitch is an Amazon Web Services (AWS) account.

To sign up for an AWS account, click here or enter this URL in your browser: http://aws.amazon.com/

Follow the instructions on the website to complete setting up your account.

Step 1: Define Data Warehouse Settings

In this step, you’ll sign into your AWS account and configure the basic settings for the Postgres-RDS database.

1. Sign into your AWS account.
2. Once you’re signed into the AWS console, click the Services menu located in the top-left corner of the page.
3. Locate the RDS option. This should be in the Database section of the page.

4. Select the Region you want to launch the instance in. You can do this by clicking the Region drop-down menu in the upper right corner of the console and selecting the appropriate region:

   

5. In the Create Instance section of the page, click the Launch a DB Instance button.
6. In the Select Engine page, click the PostgreSQL icon and then the Select button.
7. In the Production? page, you have two options:
   • Production: The Production option uses Multi-AZ Deployment and Provisioned IOPS Storage, which are features that are intended to guard against downtime and disk I/O performance issues. This option is a good idea if you or your company can’t afford downtime or you anticipate heavy usage of the database.
   • Dev/Test: This option is suitable if this database will operate outside of production, can handle downtime, don’t anticipate heavy usage, or if you simply are giving RDS a try by using the Free Usage Tier.
8. Select the option you want and click the Next Step button.

Step 2: Configure the Database

There are two steps to configuring the database: specifying the basic details and configuring more advanced settings.

Specifying the Database Details

In the Specify DB Details page, you’ll define the basic settings for your Postgres-RDS database. There are two sections on this page:

• Instance Specifications
• Settings

Instance Specifications

In the Instance Specifications section, you can select the licensing model, version, and more.

Settings

In the Settings section, you’ll define the name of the database and the master user credentials.

• DB Instance Identifier: Enter a name for the database instance. This name must be unique for your account in the Region you selected.
• Master Username: Enter a username for the master user. For info on the permissions this user is granted, click here.
• Master Password: Enter a password for the master user.
• Confirm Password: Enter the master user’s password again to confirm.

When finished, click the Next Step button.

Configuring the Advanced Settings

In the Configuring Advanced Settings page, you’ll define the last group of settings the instance needs to run. There are several sections on this page:

• Network & Security
• Database Options
• Backup
• Maintenance

Network & Security

In this section, you’ll define the network security settings for the database.

Database Options

In this section, you’ll define the database’s name and parameter settings.

Backup

In this section, you’ll define the backup settings for the database.

Maintenance

In this section, you’ll define the maintenance settings for the database.

Step 3: Launch the Database

When you’re finished defining the configuration settings, click Launch DB Instance to create and launch the instance.

Note that it may take a few minutes for the instance to complete the provisioning process. The status in the RDS Dashboard page will change to Available when the process completes:

Once the status has changed to Available, you can move onto the next step.

Step 4: Configure the Access Settings

For Stitch to successfully connect with your Postgres-RDS instance, you’ll need to add our IP addresses to the appropriate database Security Group.

1. In the RDS Dashboard page, click the grey selection box (this is the first column in the table) next to the PostgreSQL instance you created.
2. Click the Instance Actions menu.
3. Select the See Details option. This will open the details page for the instance.
4. Locate the Security Groups field in the Security and Network section.

5. Click the Security Group. This will open the Security Groups page.

6. Click the Inbound tab located towards the bottom of the page.
7. Click the Edit button.
8. This is where you’ll add the Stitch IP addresses that can access the instance. Fill out the fields in this screen as follows:
   • Type: Set this to Custom TCP.
   • Protocol: This will default to TCP - leave it as-is.
   • Port Range: Enter the port associated with the instance. This will be the same port you used to create the instance.

   • Source: Paste one of the IP addresses listed below into this field. You need to add them one at a time, meaning each IP address gets its own rule:

     • 52.23.137.21/32

     • 52.204.223.208/32

     • 52.204.228.32/32

     • 52.204.230.227/32

9. Repeat this process until all of the Stitch IP addresses (along with any others you may want to whitelist) have been added.
10. When finished, click Save.

Step 5: Create a Stitch Database User

As we mentioned when you created the instance, the credentials used to launch belong to the master user, or admin.

If you want to control the permission settings, you’ll need to create an additional database user for Stitch. An added benefit is that Stitch will be easily distinguishable in any logs or audits.

Requirements for Creating a PostgreSQL User

You must be a superuser to create a PostgreSQL user. If you’re not a superuser or you’re not sure what this means, please contact someone on your technical team before proceeding.

Required PostgreSQL Permissions

To successfully pipe your data to your PostgreSQL data warehouse, the Stitch user must:

• Be able to CREATE schemas, tables, and views
• Be able to SELECT from systems tables
• Be the owner of all integration schemas and tables. We occasionally need to run COMMENT and ALTER TABLE commands to properly insert your data.

While you technically can manually create integration schemas in PostgreSQL and then grant Stitch access to those schemas, we don’t recommend it. We understand that security is a high priority and permissions should be carefully granted, but this approach will likely lead to headaches for you.

What do we mean? If you take this approach, you’ll need to first create the schema in your data warehouse and then grant the Stitch user the appropriate permissions to access it every single time a new integration is added in Stitch. If this isn’t done, Stitch won’t be able to load data into your data warehouse.

Additionally, if there are any differences between the name of the schema you create in your data warehouse and the schema name you enter in Stitch when setting up an integration, Stitch won’t be able to load data into your data warehouse.

Creating the Stitch User

• Connection Type
• NEW Connections
• EXISTING Connections

create user [stitch_username] with password '[password]';

grant create on database [database_name] to [stitch_username];

grant select on all tables in schema information_schema to [stitch_username];

create user [stitch_username] with password '[password]';

grant create on database [database_name] to [stitch_username];

grant select on all tables in schema information_schema to [stitch_username];

select * from [stitch_username] pg_tables;

alter schema [schema_name] owner to [stitch_username];

alter table [table_name] owner to [stitch_username];

for table in `psql -qAt --host [your.postgres.amazonaws.com] --port [port] --user [admin_user] -c "select tablename from pg_tables where schemaname = '[schema_name]';" [database_name]` ; 
do psql -qAt --host [your.postgres.amazonaws.com] --port [port] --user [admin_user] -c "alter table \"[schema_name]\".\"$table\" owner to [stitch_username];" [database_name] ; done

Step 6: Connect Stitch

To complete the setup, you need to locate your PostgreSQL connection details and enter them into the Destination Settings page in Stitch.

Locate the PostgreSQL Connection Details

1. In the RDS Dashboard page, click the grey selection box (this is the first column in the table) next to the PostgreSQL instance you created.
2. Click the Instance Actions menu.

3. Select the See Details option. This will open the details page for the instance.

4. Locate the following fields:
   • Endpoint
   • Database (DB) Name: This is the name of the database you want to connect to Stitch. This must be a pre-existing database - Stitch won’t create one for you. It could be:
     • The name of the database you used to launch the instance.
     • The name of another database within PostgreSQL.
   • Username: You’ll only need this if you’re using the master user’s credentials to connect to Stitch.
   • Port: This is the port used by the database.

Entering the Connection Details into Stitch

To wrap things up, you need to enter the PostgreSQL database connection and user info into Stitch.

1. Click the Destination tab.
2. Click the PostgreSQL icon.
3. Fill in the fields as follows:
   • Host: Enter the host address (endpoint) used by the PostgreSQL instance.
   • Port: Enter the port used by the PostgreSQL instance.
   • Username: Enter the Stitch PostgreSQL user’s username.
   • Password: Enter the password for the Stitch PostgreSQL user.
   • Database: Enter the name of the PostgreSQL database that you created for Stitch.
4. When finished, click Update PostgreSQL Settings.

Stitch will perform a connection test to the PostgreSQL database; if successful, a Success! mesage will display at the top of the screen. Note that this test may take a few minutes to complete.

Troubleshooting Connection Errors

If you receive an error message, we recommend trying these troubleshooting steps for common destination connection problems before reaching out to support.

We’ve found that the majority of the time, these resources can resolve most issues.

Learning About Stitch & PostgreSQL

Now that your PostgreSQL data warehouse is up and running, we recommend learning about how Stitch will load your data into it.

• PostgreSQL Destination Overview: This overview will give you the high-level basics for working with your PostgreSQL data warehouse.
• Destination Data Loading Guide: This article covers many of the common scenarios Stitch will encounter when loading data into your PostgreSQL destination and the expected behavior for each scenario.

Did this article help? If you have questions or feedback, please reach out to us.