PostgreSQL

Connect to your PostgreSQL databases.

Getting Started

Prerequisites to create a PostgreSQL based workflow. You will need

1. A source PostgreSQL connection.

2. (optional) A list of tables OR SQL queries.

3. (optional) A destination PostgreSQL connection.

For the source database connection, we recommend using a backup or clone with read-only permissions, instead of connecting directly to your production database.

Do not use your input database connection as an output connector. This action can result in the unintended overwriting of existing data.

Create a Connection

A postgres connection is created using the following parameters:

Connection Creation Parameters

name	Display name of your choosing used to identify your connection within Gretel.	my-postgres-connection
username	Unique identifier associated with specific account authorized to access database.	john
password	Security credential to authenticate username.	...
host	Fully qualified domain name (FQDN) used to establish connection to database server.	myserver.example.com
port	Port number; If left empty, the default value - 5432- will be used.	5432
database	Name of database to connect to.	mydatabase
(optional) schema	Optional Name of specific schema	public
(optional) params	Optional JDBC URL parameters that can be used for advanced configuration.	TrustServerCertificate=True&useSSL=false

Creating Connections

CLI

First, create a file on your local computer containing the connection credentials. This file should also include type, name , config, and credentials. The config and credentials fields should contain fields that are specific to the connection being created.

Below is an example PostgreSQL connection:

{
    "type": "postgres",
    "name": "my-postgres-connection",
    "config": {
        "username": "john",
        "host": "myserver.example.com",
        "port": 5432,
        "database": "mydatabase",
        "schema": "public",
        "params": "TrustServerCertificate=True&useSSL=false"
    },
    "credentials": {
        "password": "..."
    }
}

Now that you've created the credentials file, use the CLI to create the connection

gretel connections create --project [project id] --from-file [credential_file.json]

Console

• Navigate to the Connections page using the menu item in the left sidebar.

• Click the New Connection button.

• Step 1, choose the Type for the Connection - PostgreSQL.

• Step 2, choose the Project for your Connection.

• Step 3, fill in the credentials and select Add Connection.

Step 1, choose the Type of connection

Step 3, fill in credentials

SDK

from gretel_client import create_or_get_unique_project
from gretel_client.config import get_session_config
from gretel_client.rest_v1.api.connections_api import ConnectionsApi
from gretel_client.rest_v1.models import (
    CreateConnectionRequest,
    UpdateConnectionRequest,
)

session = get_session_config()
connection_api = session.get_v1_api(ConnectionsApi)

project = create_or_get_unique_project(name="postgres-workflow")

connection = connection_api.create_connection(
    CreateConnectionRequest(
        name="my-postgres-connection",
        project_id=project.project_guid,
        type="postgres",
        config={
            "username": "john",
            "host": "myserver.example.com",
            "database": "mydatabase",
            #"port": 5432,
            #"schema": "public",
            #"params": "TrustServerCertificate=True&useSSL=false",
        },
        # note: best practice is to read in credentials from a file
        # or secret instead of directly embedding sensitive values
        # in python code.
        credentials={
            "password": "...",
        },
    )
)