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.


Postgres-RDS Setup Requirements

To set up Postgres-RDS in Stitch, you need:

  • An Amazon Web Services (AWS) account. Signing up is free - click here or go to https://aws.amazon.com to create an account if you don’t have one already.

  • Some technical know-how and familiarity with AWS.


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.

Selecting a Region in the RDS-AWS console.

  1. 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, as seen in the image to the right.
  2. In the Create Instance section of the page, click the Launch a DB Instance button.
  3. In the Select Engine page, click the PostgreSQL icon and then the Select button.
  4. 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.
  5. Select the option you want and click the Next Step button.

Step 2: Configure the Database: Basic Details

There are two steps to configuring the database: specifying the basic details and configuring more advanced settings. In this step, you’ll specify the basic 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:

Step 2.1: Basic Details: Instance Specifications

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

Setting What it means What you should do
License Model This is the license used by the database engine. Leave as-is - Postgres only has one license.
DB Engine Version This is the version of PostgreSQL the database will run on. Select your version, but keep in mind that Stitch only supports versions 9.3+.
DB Instance Class This indicates the Instance Class your database will run on. Select the Instance Class you want your database to run on.

If you’re not sure what this means or how to select the best option, check out Amazon’s documentation.
Multi-AZ Deployment This indicates whether your instance will use Multi-AZ Deployment. This is only available if you selected Production in the last section. This will default to Yes. Leave this setting as-is if you want Amazon to maintain a synchronous standby replica in a different Availability Zone. Amazon recommends this for Production Instances.
Storage Type This indicates the type of storage your database will use. This is only available if you selected Production in the last section. Select the type of storage you want your database to use.

If you’re not sure what this means or how to select the best option, check out Amazon’s documentation.
Allocated Storage This is the amount of storage for your database. Enter the amount of storage (in GB) you want for your database. If you need help deciding the amount, check out Amazon’s documentation.

Step 2.2: Basic Details: 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.
  • Confirm Password: Enter the master user’s password again to confirm.

When finished, click the Next Step button.


Step 3: Configure the Database: 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:

Step 3.1: Advanced Settings: Network & Security

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

Some fields in this section must be configured a certain way to use Stitch.

Setting What it means What you should do
VPC This setting refers to the Virtual Private Cloud, or the group of servers hosting the instance. If you’re a new AWS customer or don’t want to set up a VPC, you can use the Default option.

Otherwise, select the VPC you want the instance to reside in. Steps for how to create your own VPC are outside the scope of this tutorial, but Amazon has instructions here.
Subnet Group This setting refers to the subnets that the instance can use in the selected VPC. If you’re a new AWS customer or you’re not sure what this means, select the Default option.

You can refer to Amazon’s documentation for more info on subnets and VPCs.
Publicly Accessible This setting determines whether your instance has a publicly accessible IP address, which means it can be accessed from outside the selected VPC. Set this to Yes. This is required to connect the instance to Stitch.
Availability Zone This setting defines what availability zone you want the instance to reside in. If you have a preference, select it.

Otherwise, leave this as No Preference.
Security Groups This setting defines the in- and outbound traffic that can access the instance. If you have a Security Group you’d like to use, select it from the list.

If you’re a new AWS customer, you can use the Default option or Create new Security Group.

If you opt to create a new group, don’t worry too much about the settings for it - you’ll configure them in the next section of this tutorial.

Step 3.2: Advanced Settings: Database Options

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

Setting What it means What you should do
Database Name This is the name of the initial database in the instance. This is the name you’ll enter into Stitch at the end of this tutorial to complete the connection setup. Enter a name for the database.
Database Port This is the port that the database will listen on. Use the default (5432) or enter a port.
DB Parameter Group This is the parameter group for the database. If you’ve created your own parameter group, select it here.

Otherwise use the Default group.
Option Group This is the option group for the database. If you’ve created your own option group, select it here.

Otherwise use the Default group.
Snapshots T his will copy any database instance tags to a database snapshot when you create a snapshot. You can learn more about tagging here. This is your preference; it isn’t required by Stitch.
Encryption This determines if your data will be encrypted with AES-256 encryption in both the instances themselves and at rest. There are additional steps to setting this up, which you can find help for here. This is your preference; it isn’t required by Stitch.

Step 3.3: Advanced Settings: Backup

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

Setting What it means What you should do
Backup Retention Period This defines the number of days you want automatic backups of your database to be retained. This is your preference; it isn’t required by Stitch.
Backup Window This defines the time your database is backed up. Unless you have a preference, use the default of No Preference.

This isn’t required by Stitch.

Step 3.4: Advanced Settings: Maintenance

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

Setting What it means What you should do
Auto Minor Version Upgrade This defines whether your database instance will automatically receive minor database engine upgrades when they’re available. This is your preference; it isn’t required by Stitch.
Maintenance Window This defines the 30 minute window in which pending changes to your database instance are applied. Unless you have a preference, use the default of No Preference.

This is your preference; it isn’t required by Stitch.

Step 4: 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:

Completed provisioning of the PostgreSQL-RDS database.

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


Step 5: 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.

Selecting the PostgreSQL-RDS instance, then opening the Instance Actions menu on the RDS Dashboard page

  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. It will turn blue when selected.
  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. If you created a new Security Group, click the name of the group that’s in this field.

    If you associated an existing Security Group with the database, click the name of group you selected when you created the database in Step 3.

  6. The Security Group associated with your PostgreSQL instance should automatically open in the Security Groups page. If it doesn’t, click the name of the appropriate group.
  7. The Security Group’s details will display in tabs in the bottom part of the page. Click the Inbound tab:

    The Security Group page in AWS

  8. In the Inbound tab, click the Edit button. This will open the Edit inbound rules window.
  9. This is where you’ll add the IP addresses that can access the instance. Fill out the fields in this screen as follows:
    • Type: Select Custom TCP Rule from the drop-down.
    • 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. (5432 is the default)
    • Source: The drop-down will default to Custom - leave it as-is.

      In the field next to the drop-down, copy and paste one of the following IP addresses. 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

      Below is an example of what a rule should look like:

      Whitelisting Stitch IP addresses through Inbound Security Group rules.

  10. Click Add Rule to add an additional Inbound rule.
  11. Repeat the previous two steps until all of Stitch’s IP addresses (along with any others you may want to whitelist) have been added.
  12. Click Save.

Step 6: Create a Stitch Postgres-RDS 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 (or have the CREATEROLE permission) 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 for Stitch

The Stitch database user requires the following to successfully load data into PostgreSQL:

Action Reason
CREATE schemas, tables, and views Stitch requires these permissions to create the necessary database objects to load and store your data.
SELECT from tables in the pg_catalog and information_schema schemas Stitch uses the data in the systems tables to verify the existence of integration schemas and tables, and gain information about their structure prior to loading data.

Note: Stitch will only ever read data from the systems tables.
Ownership of all integration schemas and tables Stitch occasionally needs to run COMMENT and ALTER TABLE commands to properly insert 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.

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 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.

Create the Database User

Depending on the type of connection this is, the steps for creating a PostgreSQL database user for Stitch will vary a bit:

  • If Stitch hasn’t ever replicated data to your PostgreSQL destination, follow the instructions in the NEW Connections tab.
  • If Stitch has replicated data to your PostgreSQL destination in the past, follow the instructions in the EXISTING Connections tab.

Note: Anything inside square brackets - [like this] - is something you need to define when running the commands yourself.

  1. If you haven’t already, connect to your PostgreSQL instance using your SQL client.2. After connecting, run this command to create the user:

    CREATE USER [stitch_username] WITH PASSWORD '[password]';
    
  2. Next, you’ll assign the CREATE permissions to the 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.

    GRANT CREATE ON DATABASE[database_name] TO [stitch_username];
    
  3. If you restricted access to the system tables, you’ll also need to run the following commands to grant the Stitch user SELECT permissions:

    GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO [stitch_username]
    
    GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO [stitch_username]
    

Note: Anything inside square brackets - [like this] - is something you need to define when running the commands yourself.

  1. If you haven’t already, connect to your PostgreSQL instance using your SQL client.2. After connecting, run this command to create the user:

    CREATE USER [stitch_username] WITH PASSWORD '[password]';
    
  2. Next, you’ll assign the CREATE permissions to the 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.

    GRANT CREATE ON DATABASE[database_name] TO [stitch_username];
    
  3. If you restricted access to the system tables, you’ll also need to run the following commands to grant the Stitch user SELECT permissions:

    GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO [stitch_username]
    
    GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO [stitch_username]
    

Transfer integration schema and table ownership

When an integration is initially connected to Stitch, a schema specific to that connection is created in your data warehouse. Ownership to those schemas and all the tables contained within them must be granted to the Stitch user for data replication to be successful.

  1. Query the catalog tables using this statement to list all tables and their owners:

    SELECT * 
    FROM pg_catalog.pg_tables;
    

    Take note of any integration tables that aren’t owned by the Stitch user - those are the tables you’ll need to update.

  2. Transfer ownership of the integration schema to the Stitch user:

    ALTER SCHEMA [schema_name] OWNER TO [stitch_username];
    
  3. Next, you’ll transfer ownership of all tables in the integration schema to the Stitch user. There are two ways to accomplish this: table-by-table or running a script.

    Table-by-Table

    For every table in the schema not owned by the Stitch user - as outlined in step 1 - use this command to transfer ownership:

    ALTER TABLE [table_name] OWNER TO  [stitch_username];
    
    Script

    To transfer ownership automatically, run this script from the command line:

    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
    

Note: If you need to transfer ownership of more than one schema and its tables, you’ll need to repeat steps 2 and 3 for every integration schema.


Step 7: Connect Stitch

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

Step 7.1: 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
    • 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.

These fields are highlighted in the image below:

PostgreSQL database details page

Step 7.2: Enter connection details into Stitch

  1. If you aren’t signed into your Stitch account, sign in now.
  2. Click the Destination tab.
  3. Click the PostgreSQL icon.
  4. Fill in the fields as follows:

    • Host (Endpoint): Paste the Endpoint address from the PostgreSQL Details page in AWS into this field.

      Don’t include the port number, which is appended to the end of the endpoint string - this will cause errors. For example: if you used the default port, you’d remove the following from the address: :5432

    • Port: Enter the port used by the PostgreSQL instance. The default is 5432.

    • Username: Enter the Stitch PostgreSQL user’s username.

    • Password: Enter the password associated with the Stitch PostgreSQL user.

    • Database: Enter the name of the PostgreSQL database that you created for Stitch.

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: 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 and 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.


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.