Gridly connectors will enable you to connect PostgreSQL with Gridly and easily sync your data.

If you are using Gridly connectors for the first time, click here to learn more: Gridly connector overview. 

Open Gridly connectors

There are two ways to access Gridly connectors: 

Open Integration settings

1. On the project page, click next to the company name. 
2. Select Integration settings from the dropdown. 
3. The Gridly connectors page will be displayed by default.

Open Integration popup 

You can also open Gridly connectors anywhere in Gridly.

1. Click in the header to open the Integration pop-up. 
2. Select Gridly connectors from the side panel. 

Create a new source in Gridly 

1. In Gridly connectors, switch to the Sources tab.
2. In the top-right corner, click + New source. 
3. On the Set up the source page, enter a name for your source.
4. From the Source type dropdown, select PostgreSQL as the data source.
5. Complete Database information
   • Host: URL of the database.
   • Port: Port to use for connecting to the database.
   • Database: database name.
   • Schemas: input the list of schemas to sync from and press Enter.
6. Enter Database credentials 
   • Username
   • Password 
   • Replication Method: The method used for extracting data from the database. STANDARD replication requires no setup on the DB side but will not be able to represent deletions incrementally. CDC uses the Binlog to detect inserts, updates, and deletes. This needs to be configured on the source database itself. 
7. Select SSH Tunnel method: Select whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use.
8. Click Save & check connection.

CDC Replication Method

We use logical replication of the Postgres write-ahead log (WAL) to incrementally capture deletes using a replication plugin. You need to set up CDC on the source database (PostgreSQL) itself. 

After finishing set up, on the Gridly setting source page: 

• Select Replication Method as Logical replication CDC. 
• Select Plugin: We recommend using a pgoutput plugin as it is the standard logical decoding plugin in Postgres. In case the replication table contains a lot of big JSON blobs and table size exceeds 1 GB, we recommend using a wal2json instead. Please note that wal2json may require additional installation for Bare Metal, VMs (EC2/GCE/etc), Docker, etc. 
• Enter Replication slot: Enter the Replication slot you’ve created.
• Enter Publication: enter the Postgres publication used for consuming changes you’ve created. 

Connection via SSH Tunnel 

If it is not possible (or against your security policy) to connect to the database directly (e.g. it does not have a public IP address), Gridly allows connecting to a PostgreSQL instance via an SSH Tunnel.

When using an SSH tunnel, you are configuring Gridly to connect to an intermediate server (a.k.a. a bastion server) that does have direct access to the database. Gridly connects to the bastion and then asks the bastion to connect directly to the server.

In the SSH Tunnel Method field, there are two options for you: No tunnel (a direct connection) and SSH Key Authentication. 

No tunnel is selected by default, if you want to use SSH Tunnel, select SSH Key. Authentication or Password Authentication.

• Choose Key Authentication if you will be using an RSA private key as your secret for establishing the SSH Tunnel.
• Choose Password Authentication if you will be using a password as your secret for establishing the SSH Tunnel.

SSH Key Authentication

Fill in all the information: 

• SSH Tunnel jump server host: The intermediate (bastion) server that Gridly will connect to. This should be a hostname or an IP Address.
• SSH Connection port: The port on the bastion server with which to make the SSH connection. The default port for SSH connections is 22, so unless you have explicitly changed something, go with the default.
• SSH Login username: The username that Gridly should use when connecting to the bastion server. This is NOT the PostgreSQL username.
• SSH Private key: Enter the RSA Private Key that you are using to create the SSH connection. This should be the full contents of the key file starting with -----BEGIN RSA PRIVATE KEY----- and ending with -----END RSA PRIVATE KEY-----.

Password Authentication

Fill in all the information: 

• SSH Tunnel jump server host: The intermediate (bastion) server that Gridly will connect to. This should be a hostname or an IP Address.
• SSH Connection port: The port on the bastion server with which to make the SSH connection. The default port for SSH connections is 22, so unless you have explicitly changed something, go with the default.
• SSH Login username: The username that Gridly should use when connecting to the bastion server. This is NOT the PostgreSQL username.
• Password: password for the OS-user.

Set up a connection 

1. On the Gridly Connectors page, open the Connections tab.
2. Click + New connection. 

Set up source

1. Select the PostgreSQL source from the Source type dropdown.
2. Select a source and a table to connect with Gridly and click Next. 

Set up a destination Grid

1. Select the Grid from a Database from within the Project you want to transfer data with. 
2. Click Next. 

Set up connection

1. Map the columns of the table in PostgreSQL with the columns of your selected Grid. 
2. Click Sync now.

Sync data

1. Open Gridly Connectors, then open the Connections tab.
2. Click on in a connection to sync data.

Learn how to edit, rename, delete sources or connections in Gridly connectors: Manage Gridly connector.