Skip to main content

​
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.
FieldDescription
SSH Server HostnameEnter the hostname of the UNIX server to SSH-tunnel through.
SSH UsernameEnter the SSH username for your UNIX account.
SSH PortEnter the port number for your UNIX account. If you leave this field blank, the default port is 22.
SSH Private KeyEnter the SSH private key (either DES or RSA).
Database NameEnter the PostgreSQL database name.
Database HostnameEnter the hostname for the PostgreSQL database. For example: db.company.com.
Database PortEnter your PostgreSQL port number.
Database UsernameEnter your PostgreSQL username.
Database PasswordEnter 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.
MenuDescription
DataSet IDEnter the DataSet ID to retrieve data from.
Select the Schema NameSelect the database schema name.
Table Name SourceChoose a name for the SQL table.
Custom Table NameEnter the SQL table name.
Operation Type
Create a New TableCreates 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 TableUpdates 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 DataAppends 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 TypesMap 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.