domino logo
Tech Ecosystem
Get started with Python
Step 0: Orient yourself to DominoStep 1: Create a projectStep 2: Configure your projectStep 3: Start a workspaceStep 4: Get your files and dataStep 5: Develop your modelStep 6: Clean up WorkspacesStep 7: Deploy your model
Get started with R
Step 0: Orient yourself to Domino (R Tutorial)Step 1: Create a projectStep 2: Configure your projectStep 3: Start a workspaceStep 4: Get your files and dataStep 5: Develop your modelStep 6: Clean up WorkspacesStep 7: Deploy your model
Get Started with MATLAB
Step 1: Orient yourself to DominoStep 2: Create a Domino ProjectStep 3: Configure Your Domino ProjectStep 4: Start a MATLAB WorkspaceStep 5: Fetch and Save Your DataStep 6: Develop Your ModelStep 7: Clean Up Your Workspace
Step 8: Deploy Your Model
Scheduled JobsLaunchers
Step 9: Working with Domino Datasets
Domino Reference
Notifications
On-Demand Open MPI
Configure MPI PrerequisitesFile Sync MPI ClustersValidate MPI VersionWork with your ClusterManage Dependencies
Projects
Projects OverviewProjects PortfolioReference ProjectsProject Goals in Domino 4+
Git Integration
Git Repositories in DominoGit-based Projects with CodeSyncWorking from a Commit ID in Git
Jira Integration in DominoUpload Files to Domino using your BrowserFork and Merge ProjectsSearchSharing and CollaborationCommentsDomino Service FilesystemComparing File RevisionsRevert Projects and Files
Advanced Project Settings
Project DependenciesProject TagsRename a ProjectSet up your Project to Ignore FilesUpload files larger than 550MBExporting Files as a Python or R PackageTransfer Project Ownership
Domino Runs
JobsDiagnostic Statistics with dominostats.jsonNotificationsResultsRun Comparison
Advanced Options for Domino Runs
Run StatesDomino Environment VariablesEnvironment Variables for Secure Credential StorageUse Apache Airflow with Domino
Scheduled Jobs
Domino Workspaces
WorkspacesUse Git in Your WorkspaceRecreate A Workspace From A Previous CommitUse Visual Studio Code in Domino WorkspacesPersist RStudio PreferencesAccess Multiple Hosted Applications in one Workspace Session
Spark on Domino
On-Demand Spark
On-Demand Spark OverviewValidated Spark VersionConfigure PrerequisitesWork with your ClusterManage DependenciesWork with Data
External Hadoop and Spark
Hadoop and Spark OverviewConnecting to a Cloudera CDH5 cluster from DominoConnecting to a Hortonworks cluster from DominoConnect to a MapR cluster from DominoConnect to an Amazon EMR cluster from DominoRunning Local Spark on a Domino ExecutorUsing PySpark in Jupyter WorkspacesKerberos Authentication
On-Demand Ray
On-Demand Ray OverviewValidated Ray VersionConfigure PrerequisitesWork with your ClusterManage DependenciesWork with Data
On-Demand Dask
On-Demand Dask OverviewValidated Dask VersionConfigure PrerequisitesWork with Your ClusterManage DependenciesWork with Data
Customize the Domino Software Environment
Environment ManagementDomino Standard EnvironmentsInstall Packages and DependenciesAdd Workspace IDEsAdding Jupyter KernelsAutomatic Adaptation of Custom Images
Partner Environments for Domino
Use MATLAB as a WorkspaceUse Stata as a WorkspaceUse SAS as a Workspace
Advanced Options for Domino Software Environment
Publish in Domino with Custom ImagesInstall Custom Packages in Domino with Git IntegrationAdd Custom DNS Servers to Your Domino EnvironmentConfigure a Compute Environment to User Private Cran/Conda/PyPi MirrorsUse TensorBoard in Jupyter Workspaces
Publish your Work
Publish a Model API
Model Publishing OverviewModel Invocation SettingsModel Access and CollaborationModel Deployment ConfigurationPromote Projects to ProductionExport Model ImageExport to NVIDIA Fleet Command
Publish a Web Application
App Publishing OverviewGet Started with DashGet Started with ShinyGet Started with FlaskContent Security Policies for Web Apps
Advanced Web Application Settings in Domino
App Scaling and PerformanceHost HTML Pages from DominoHow to Get the Domino Username of an App Viewer
Launchers
Launchers OverviewAdvanced Launcher Editor
Assets Portfolio Overview
Model Monitoring and Remediation
Monitor WorkflowsData Drift and Quality Monitoring
Set up Monitoring for Model APIs
Set up Prediction CaptureSet up Drift DetectionSet up Model Quality MonitoringSet up NotificationsSet Scheduled ChecksSet up Cohort Analysis
Set up Model Monitor
Connect a Data SourceRegister a ModelSet up Drift DetectionSet up Model Quality MonitoringSet up Cohort AnalysisSet up NotificationsSet Scheduled Checks
Use Monitoring
Access the Monitor DashboardAnalyze Data DriftAnalyze Model QualityExclude Features from Scheduled Checks
Remediation
Cohort Analysis
Review the Cohort Analysis
Remediate a Model API
Monitor Settings
API TokenHealth DashboardNotification ChannelsTest Defaults
Monitoring Config JSON
Supported Binning Methods
Model Monitoring APIsTroubleshoot the Model Monitor
Connect to your Data
Data in Domino
Datasets OverviewProject FilesDatasets Best Practices
Connect to Data Sources
External Data VolumesDomino Data Sources
Connect to External Data
Connect Domino to DataRobotConnect to Azure Data Lake StorageConnect to BigQuery from DominoConnect to Google Cloud Storage from DominoConnect to IBM DB2 from DominoConnect to IBM Netezza from DominoConnect to Impala from DominoConnect to MSSQL from DominoConnect to MySQL from DominoConnect to Okera from DominoConnect to Oracle Database from DominoConnect to PostgreSQL from DominoConnect to Redshift from DominoConnect to S3 from DominoConnect to Snowflake from DominoConnect to Teradata from Domino
Work with Data Best Practices
Work with Big Data in DominoWork with Lots of FilesMove Data Over a Network
Advanced User Configuration Settings
User API KeysDomino TokenOrganizations Overview
Use the Domino Command Line Interface (CLI)
Install the Domino Command Line (CLI)Domino CLI ReferenceDownload Files with the CLIForce-Restore a Local ProjectMove a Project Between Domino DeploymentsUse the Domino CLI Behind a Proxy
Browser Support
Get Help with Domino
Additional ResourcesGet Domino VersionContact Domino Technical SupportSupport Bundles
domino logo
About Domino
Domino Data LabKnowledge BaseData Science BlogCommunityTraining
User Guide
>
Domino Reference
>
Connect to your Data
>
Connect to Data Sources
>
Connect to External Data
>
Connect to PostgreSQL from Domino

Connect to PostgreSQL from Domino

This topic describes how to connect to PostgreSQL from Domino.

PostgreSQL is an open-source relational database that can run on a wide variety of local systems and clouds.

Connect to Postgres with Domino Data Sources

The easiest way to connect to an Postgres instance from Domino is to use a :doc:`Domino Data Source </reference/data/external_data/domino_data_sources>`.

Configuration

To create a Postgres Data Source select Postgres as the Data Source type from the New Data Source wizard and specify the relevant parameters.

image

Authentication

Specify the credentials that will be used to connect to Postgres.

Currently, the only authentication mechanism supported is Username and Password. The credentials will be securely stored in the Domino secret store backed by HashiCorp Vault.

image

Python and psycopg2

Domino recommends the psycopg2 library for interacting with PostgreSQL databases from Python.

Environment setup

Use the following Dockerfile instruction to install psycopg2 in your environment.

This instruction assumes you already have pip installed.

USER root
RUN pip install psycopg2
USER ubuntu

Credential setup

There are several environment variables you must set up to store secure information about your PostgreSQL connection. Set the following as Domino environment variables on your user account:

  • POSTGRES_HOST

    Hostname where your DB is running. Make sure your PostgreSQL DB and network firewall are configured to accept connections from Domino.

  • POSTGRES_USER

    The PostgreSQL user you want to authenticate as.

  • POSTGRES_PASSWORD

    The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the psycopg2 documentation for detailed information about how to use the package. Below is a simple example for connecting to PostgreSQL with psycopg2 where:

  • You have set up environment variables noted above with the hostname, username, and password

  • Your user has access to a database named db1 in the target PostgreSQL instance

  • The database contains a table named metrics

import psycopg2
import os

# fetch values from environment variables and set the target database
hostname = os.environ['POSTGRES_HOST']
username = os.environ['POSTGRES_USER']
password = os.environ['POSTGRES_PASSWORD']
dbname = 'db1'

# set up a connection object with parameters for your database
conn = psycopg2.connect(
  host=hostname,
  port=5432,
  user=username,
  password=password,
  database=dbname, )

# create a cursor in your connection
cur = conn.cursor()

# execute a query on the metrics table and store the response
cur.execute("SELECT * FROM metrics;")
results = cur.fetchall()

# display the contents of the response
print(results)

The results object created in the example above is a Python array of entries from the queried table.

R and RPostgreSQL

Domino recommends the RPostgreSQL library for interacting with PostgreSQL databases from R.

Environment setup

Use the following Dockerfile instruction to add RPostgreSQL to your environment.

USER root
RUN R -e 'install.packages("RPostgreSQL")'
USER ubuntu

Credential setup

There are several environment variables you must set up to store secure information about your PostgreSQL connection. Set the following as Domino environment variables on your user account:

  • POSTGRES_HOST

    Hostname where your DB is running. Make sure your PostgreSQL DB and network firewall are configured to accept connections from Domino.

  • POSTGRES_USER

    The PostgreSQL user you want to authenticate as.

  • POSTGRES_PASSWORD

    The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the RPostgreSQL documentation for detailed information about how to use the package. Below is a simple example for connecting to PostgreSQL with RPostgreSQL where:

  • You have set up environment variables noted above with the hostname, username, and password

  • Your user has access to a database named db1 in the target PostgreSQL instance

  • The database contains a table named metrics`

# load the library
library(RPostgreSQL)

# fetch values from environment variables and set the target database
hostname <- Sys.getenv['POSTGRES_HOST']
username <-  Sys.getenv['POSTGRES_USER']
password <- Sys.getenv['POSTGRES_PASSWORD']
database <- 'db1'

# set up a driver and use it to create a connection to your database
drv <- dbDriver("PostgreSQL")
conn <- dbConnect(
  drv,
  host=hostname,
  port=5432,
  user=username,
  password=password,
  dbname=database )

# run a query and load the response into a dataframe
df_postgres <- dbGetQuery(conn, "SELECT * from metrics;")

# close your connection when finished
dbDisconnect(conn)
Domino Data LabKnowledge BaseData Science BlogCommunityTraining
Copyright © 2022 Domino Data Lab. All rights reserved.