Prerequisites
An up-and-running PostgreSQL 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.
Note: The database must be running version 9.3 or higher.
Database 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 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
- Follow the steps in the Setting up an SSH Tunnel for a database connection guide to set up an SSH tunnel for PostgreSQL.
- Complete the steps in this guide after the SSH setup is complete.
Step 2: Create a PostgreSQL Stitch user
In the following tabs are the instructions for creating a Stitch PostgreSQL database user and explanations for the permissions Stitch requires.
-
If you haven’t already, connect to your PostgreSQL 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 PostgreSQL.
Privilege name | Reason for requirement |
CREATE ON DATABASE |
Required to create the necessary database objects to load and store your data.
Note: The |
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 PostgreSQL connection details into the Destination Settings page in Stitch.
Step 3.1: 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 PostgreSQL icon.
-
Fill in the fields as follows:
-
Host (Endpoint): Enter the host address (endpoint) used by the PostgreSQL instance. For example: This could be a network address such as
192.68.0.1
, or a server endpoint likedbname.hosting-provider.com
. -
Port: Enter the port used by the instance. The default is
5432
. -
Username: Enter the Stitch PostgreSQL database user’s username.
-
Password: Enter the password for the Stitch PostgreSQL database user.
-
Database: Enter the name of the PostgreSQL database you want to connect to Stitch.
-
Step 3.2: Define SSH connection details
If you’re using an SSH tunnel to connect your PostgreSQL database to Stitch, you’ll also need to define the SSH settings. Refer to the Setting up an SSH Tunnel for a database connection 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: Enter the public 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.
-
Step 3.3: Define SSL connection details
-
Check the Connect using SSL checkbox. Note: The database must support and allow SSL connections for this setting to work correctly.
-
Fill in the fields as follows:
-
SSL Certificate: Optional: Provide the certificate (typically a CA or server certificate) Stitch should verify the SSL connection against. The connection will succeed only if the server’s certificate verifies against the certificate provided here.
Note: Providing a certificate isn’t required to use SSL. This is only if Stitch should verify the connection against a specific certificate.
-
Step 3.4: Save the destination
When finished, click Check and Save.
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.
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.