Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Connecting a Self-Hosted Postgres Destination

Important!

  • Stitch only supports Postgres destinations using versions 9.3 and above. While this isn't something that's strictly enforced, we recommend keeping your version current as a best practice.
  • Please note that our Postgres destination is currently in open beta. The information in this article is subject to change.

In this article, we'll walk you through connecting your PostgreSQL destination to Stitch. You'll need some tech expertise to complete the setup, so we recommend looping in a developer or a member of your tech team to help out.

Instructions for installing Postgres and creating an initial database are outside the scope of this tutorial. These instructions assume that you have a Postgres instance up and running. For help installing and getting started with Postgres, refer to the Postgres documentation.

Connecting a PostgreSQL data warehouse is a six-step process:

  1. Configuring the firewall settings
  2. Creating a Postgres database
  3. Retrieve the Stitch public key *
  4. Create a Linux user for Stitch *
  5. Create a Postgres user for Stitch
  6. Enter the database connection info in Stitch

* These steps are required ONLY if you're using an SSH tunnel to connect.

Configuring the Firewall Settings

For the connection from Stitch to your Postgres destination to be successful, you must configure your firewall to allow access from our IP addresses. Whitelist the following IPs before continuing onto the next step:

  • 54.88.76.97/32
  • 52.23.137.21/32
  • 52.204.223.208/32
  • 52.204.228.32/32
  • 52.204.230.227/32

Creating a Postgres Database

Next, you’ll create a database in your Postgres instance for Stitch. This is where data replicated by Stitch will be stored.

Log into your server as a user with createdb permissions and run the following command, replacing [stitch_database] with whatever you want the database name to be:

createdb [stitch_database]

Responses are only returned if the command is not successful. If you don’t receive a response, then the command was successful and the database was created.

If you encounter errors - for example, createdb: command not found - then you’ll need to do some troubleshooting before you can move onto the next step. This Postgres article contains troubleshooting steps for some of the most common issues when creating databases.

Stitch doesn’t require any particular configuration for the database nor do you need to create any tables or schemas. Stitch will take care of that for you after the destination setup is complete and you’ve connected integrations.

Retrieving the Stitch Public Key

If you aren’t using an SSH Tunnel to connect, skip this step.

The Public Key is used to authorize the Stitch Linux user. In the next step, we'll create the user and import the key. To retrieve the key:

  1. Sign into your Stitch account.
  2. Click the User menu (your icon) > Destination Settings.
  3. Click the PostgreSQL icon.
  4. When the credentials page displays, click the Encryption Type menu and select the SSH Tunnel option.
  5. The Public Key will display, along with the other SSH fields.

Leave this page open throughout the tutorial - you'll need it to wrap things up.

Creating a Linux User for Stitch

If you aren’t using an SSH Tunnel to connect, skip this step.

This can be a production or slave machine, as long as it contains real-time (or frequently updated) data. You may restrict this user any way you like as long as it retains the right to connect to the Postgres server.

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

To create the new user, run the following commands as root on your Linux server:

adduser [stitch_username] -p
mkdir /home/[stitch_username]
mkdir /home/[stitch_username]/.ssh

To ensure the user has access to the database, you need to import the Public Key from the last step into authorized_keys. Copy the entire key into the authorized_keys file as follows:

touch /home/[stitch_username]/.ssh/authorized_keys
"< [PASTE KEY HERE] >" >> /home/[stitch_username]/.ssh/authorized_keys

To finish creating the user, alter the permissions on the /home/[stitch_username] directory to allow access via SSH:

chown -R [stitch_username]:[stitch_username] /home/[stitch_username]
chmod -R 700 /home/[stitch_username]/.ssh

In the next step, you'll create a database user for Stitch.

Creating a Postgres User for Stitch

While you can create a Postgres user for us at any time, we highly recommend doing so before any data is replicated into your data warehouse. The process for creating a user for us after replication has taken place is considerably lengthier than what's required for a new connection.

You must be a superuser (or have the CREATEROLE permission) to create a Postgres user. Please contact your technical team if you’re not sure what this means or how to proceed.

Required Postgres Permissions

To successfully pipe your data to your Postgres destination, the Stitch user needs permission to:

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

Restricting the Stitch user to a single schema or revoking public access to tables in the information schema will prevent Stitch from functioning properly. If you'd like guidance or more info on why we need these permissions, please reach out to us.

Creating the Stitch Postgres User

Connect to your Postgres instance using your SQL client. Note that anything inside the square brackets - [like this] - is something you need to define when running the commands yourself.

After connecting, run this command to create the user:

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

Next, you'll assign the CREATE permissions to the user. For [database name], enter the name of the database you created in step 2 of this tutorial:

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

If you restricted access to the information schema in your instance, you'll also need to run this command to grant the Stitch user SELECT permissions:

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

Connecting Stitch to Your Postgres Destination

The last step is to enter the database connection details into Stitch and test the connection.

  1. Sign into your Stitch account, if you haven’t already.
  2. Click the User menu (your icon) > Destination Settings.
  3. Click the PostgreSQL icon.
  4. In the PostgreSQL Destination Settings page, fill in the following fields:
    1. Host:  Enter the host or IP address used by your Postgres instance.
    2. Port: Enter the port used by the Postgres instance.
    3. Database: Enter the name of the database you created for Stitch in step 2.
    4. Username: Enter the Stitch Postgres user’s username.
    5. Password: Enter the Stitch Postgres user’s password.

If you’re using an SSH Tunnel to connect, you’ll also need to complete the following:

  1. Click the Encryption Type menu and select SSH Tunnel.
  2. Fill in the following fields:
    1. Remote Address: The IP address or hostname of the server we will SSH into
    2. SSH Port: The SSH port on your server (22 by default)
    3. Username: The Stitch Linux (SSH) user's username

When finished, click Update PostgreSQL Settings.

Stitch will perform a connection test to the Postgres database; if successful, a Success! message 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 checking out our troubleshooting resources - especially this Troubleshooting Postgres Destination Errors doc - before reaching out to support.

We’ve found that, the majority of the time, these resources can be resolve the most common issues without our assistance.

Learning About Stitch & Postgres

Now that your Postgres destination is up and running, we recommend learning about how Stitch will load and store your data in your data warehouse.

The Postgres Destination Overview doc is a good jumping-off point for learning about how Stitch handles nested data structures, certain data types, and more.

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!