PostgreSQL SSH Writeback Connector

Intro

This article explains how to use Domo’s PostgreSQL SSH Writeback Connector to export data to a PostgreSQL database in the Data Center via an SSH tunnel, configure connection credentials, and define how data is written to the target table.

---

Prerequisites

To connect to a PostgreSQL database via an SSH tunnel and create a DataSet, you must have the following:

• The username and password you use to log into your PostgreSQL database
• The name of the PostgreSQL database
• The port number for the PostgreSQL database
• The PostgreSQL database hostname or IP address
• The UNIX server to SSH-tunnel through
• The username for the UNIX account
• The SSH port number
• The SSH private key (either DES or RSA)

Before you connect to a PostgreSQL database, you must also allowlist IP addresses on your database server on the port you want to connect to. For the full list of IP addresses, see Allowlisting IP Addresses for Connectors.

Note: The owner of a writeback DataSet must also be an owner or co-owner of the input DataSet.

To learn more about PostgreSQL, visit their website at https://www.postgresql.org/.

Note:

• Domo does not support SSH keys generated using ssh-keygen. The SSH keys must be DES or RSA keys generated by OpenSSL.
• The owner of a writeback DataSet must also be an owner or co-owner of the input DataSet.

Important: You need the following permissions on PostgreSQL:

• CONNECT
• SELECT
• CREATE
• INSERT

This article covers the fields and menus specific to the PostgreSQL SSH Writeback Connector user interface. For general information about adding DataSets, setting update schedules, and editing DataSet information, see Add a DataSet Using a Data Connector.

Configure the Connection

This section describes the options in the Credentials and Details panes on the PostgreSQL SSH Writeback Connector page. The components of the Scheduling and Name & Describe Your DataSet panes are universal across most connector types and are discussed in Add a DataSet Using a Data Connector.

Enter Your Credentials

The Credentials pane contains fields for entering credentials to connect to your database and SSH server. The following table describes what is needed for each field.

Field	Description
SSH Server Hostname	Enter the hostname of the UNIX server to SSH-tunnel through.
SSH Username	Enter the SSH username for your UNIX account.
SSH Port	Enter the port number for your UNIX account. If you leave this field blank, the default port is 22.
SSH Private Key	Enter the SSH private key (either DES or RSA).
Database Name	Enter the PostgreSQL database name.
Database Hostname	Enter the hostname for the PostgreSQL database. For example: db.company.com.
Database Port	Enter your PostgreSQL port number.
Database Username	Enter your PostgreSQL username.
Database Password	Enter your PostgreSQL password.

After you have entered valid credentials, you can use the same account any time you set up a new Domo-PostgreSQL SSH connection. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Manage User Accounts for Connectors.

Configure the Details Pane

In the Details pane, you create an SQL query to pull data from your database.

Menu	Description
DataSet ID	Enter the DataSet ID to retrieve data from.
Select the Schema Name	Select the database schema name.
Table Name Source	Choose a name for the SQL table.
Custom Table Name	Enter the SQL table name.
Operation Type	Create a New Table Creates a new table with the specified name. If a table with this name already exists in the PostgreSQL database, the action fails. To modify an existing table, select Update an Existing Table. Update an Existing Table Updates the table if it exists in the PostgreSQL database. If the table does not exist, the action creates it on the first run and then updates it in subsequent runs based on the selected update method.
Table Update Method	Append Data Appends the retrieved data to the existing table. Overwrite With New Data (Drop and Create Table) Drops and recreates the table, then writes the newly retrieved data to it. Overwrite With New Data (Truncate Table) Overwrites the existing table data by truncating it, and then writes the newly retrieved data. Note: If the table does not exist, the first run creates it. Subsequent runs update the table based on the selected option.
Map Domo DataSet Columns with PostgreSQL Data Types	Map Domo DataSet columns with PostgreSQL data types.

Configure Other Options

For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Add a DataSet Using a Data Connector.