PostgreSQL Setup Requirements

To set up PostgreSQL in Stitch, you need:

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

  • A PostgreSQL instance running on 9.3+ or above. While this isn’t something that Stitch strictly enforces, we recommend keeping your version current as a best practice.

  • createdb permissions in your PostgreSQL instance. This is required to create a database for Stitch.


Step 1: Whitelist Stitch's IP Addresses

For the connection to be successful, you’ll need to configure your firewall to allow access from our IP addresses. Whitelist the following IPs before continuing onto the next step:

  • 52.23.137.21/32

  • 52.204.223.208/32

  • 52.204.228.32/32

  • 52.204.230.227/32


Step 2: Create a Postgres Database

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

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

CREATE DATABASE [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.


Step 3: Retrieve Your Public Key

The Stitch Public Key

The Public Key is used to authorize the Stitch Linux user. If the key isn’t properly installed, Stitch will be unable to access your database.

To retrieve the key:

  1. Sign into your Stitch account.

  2. Click the Destination tab.
  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 for now - you’ll need it to wrap things up at the end.


Step 4: Create a Stitch Linux User

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

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

    adduser --disabled-password [stitch_username]
    mkdir /home/[stitch_username]/.ssh
    
  2. Next, import the Public Key into authorized_keys. This will ensure the Stitch user has access to the database.

    Copy the entire key into the authorized_keys file by:

    "[PASTE KEY HERE]" >> /home/[stitch_username]/.ssh/authorized_keys
    
  3. 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
    

Step 5: Create a Stitch PostgreSQL User

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 6: Connect 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): Enter the host address (endpoint) used by the PostgreSQL instance.

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

If you’re using an SSH Tunnel to connect, you’ll also need to fill in the SSH fields.

  1. Click the Encryption Type menu.
  2. Select SSH Tunnel.
  3. The following fields will display - fill them in as follows:

    • Remote Address: Enter the IP address or hostname of the server Stitch will SSH into.
    • SSH Port: Enter the SSH port on your server. (22 by default)
    • SSH User: Enter the Stitch Linux (SSH) user’s username.

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.