Create a PostgreSQL Server Data Source

Follow these steps to connect to a PostgreSQL Server and create a PostgreSQL data source:

  1. Configure User Access Rights

  2. Create a PostgreSQL Data Source

Refer to Additional Information for additional performance tips and information about PostgreSQL Server feature support.

Configure User Access Rights

We recommend that you create a dedicated user and user role for integration with the GoodData platform.

Steps:

  1. Create a user role and grant the following access rights to it:

    GRANT CONNECT ON DATABASE {database_name} TO ROLE {role_name};
GRANT USAGE ON SCHEMA {schema_name} TO ROLE {role_name};
GRANT SELECT ON ALL TABLES IN SCHEMA {schema_name} TO ROLE {role_name};

    If you intend to enable pre-aggregation caching, additional usage rights have to be granted for your pre-aggregation schema cache_schema_name:

    GRANT USAGE, CREATE ON SCHEMA {cache_schema_name} TO ROLE {role_name};

  2. Create a user and grant it with the user role:

    GRANT ROLE {role_name} TO USER {user_name};

  3. Make the user role default for the user:

    ALTER USER {user_name} SET DEFAULT_ROLE={role_name};

Create a PostgreSQL Data Source

Once you have configured your PostgreSQL user’s access rights, you can proceed to create a PostgreSQL data source that you can then connect to.

UI
API
Python

Steps:

  1. On the home page switch to Data sources.

    data sources tab

  2. Click Connect data.

    connect data

  3. Select PostgreSQL.

    select data source type

  4. Name your data source and fill in your PostgreSQL credentials and click Connect:

    db cretentials

  5. Input your schema name and click Save:

    db cretentials

    Your data source is created!

    db creation complete

Steps:

  1. Create a PostgreSQL data source with the following API call:

    curl $HOST_URL/api/v1/entities/dataSources \
  -H "Content-Type: application/vnd.gooddata.api+json" \
  -H "Accept: application/vnd.gooddata.api+json" \
  -H "Authorization: Bearer $API_TOKEN" \
  -X POST \
  -d '{
    "data": {
    "type": "dataSource",
    "id": "<unique_id_for_the_data_source>",
    "attributes": {
      "name": "<data_source_display_name>",
      "url": "jdbc:postgresql://<POSTGRES_HOST>:5432/<POSTGRES_DBNAME>",
      "schema": "<POSTGRES_SCHEMA>",
      "type": "POSTGRESQL",
      "username": "<POSTGRES_USER>",
      "password": "<POSTGRES_PASSWORD>"
    }
    }
  }' | jq .

  2. To confirm that the data source has been created, ensure the server returns the following response:

    {
  "data": {
    "type": "dataSource",
    "id": "<unique_id_for_the_data_source>",
    "attributes": {
      "name": "<data_source_display_name>",
      "url": "jdbc:postgresql://<POSTGRES_HOST>:5432/<POSTGRES_DBNAME>",
      "schema": "<POSTGRES_SCHEMA>",
      "type": "POSTGRESQL",
      "username": "<POSTGRES_USER>"
    }
  },
  "links": {
    "self": "$HOST_URL/api/v1/entities/dataSources/<unique_id_for_the_data_source>"
  }
}

Create a PostgreSQL data source with the following API call:

from gooddata_sdk import GoodDataSdk, CatalogDataSource, BasicCredentials

host = "<GOODDATA_URI>"
token = "<API_TOKEN>"
sdk = GoodDataSdk.create(host, token)

sdk.catalog_data_source.create_or_update_data_source(
    CatalogDataSourcePostgres(
        id=data_source_id,
        name=data_source_name,
        db_specific_attributes=PostgresAttributes(
            host=os.environ["POSTGRES_HOST"],
            db_name=os.environ["POSTGRES_DBNAME"]
        ),
        schema=os.environ["POSTGRES_SCHEMA"],
        credentials=BasicCredentials(
            username=os.environ["POSTGRES_USER"],
            password=os.environ["POSTGRES_PASSWORD"],
        ),
    )
)

Additional Information

Ensure you understand the following limitations and recommended practice.

Data Source Details

  • The JDBC URL must be in the following format:

    jdbc:postgresql://<host>:<port>/<databaseName>

  • Basic authentication is supported. Specify user and password.

  • If you use native authentication inside your cloud platform (for example, Google Cloud Platform, Amazon Web Services, or Microsoft Azure), you do not have to provide the username and password.

  • GoodData uses the driver with version 42.3.6.

  • The following database versions are supported:

    • 9.x
    • 10.x
    • 11.x
    • 12.x
    • 13.x
    • 14.x

Performance Tips

If your database holds a large amount of data, consider the following practices:

  • Index the columns that are most frequently used for JOIN and aggregation operations. Those columns may be mapped to attributes, labels, primary and foreign keys.

  • Define partitioning to improve performance of visualizations that use only the recent data.

    This feature strongly relies on the version of your PostgreSQL Version database, so check the official user documentation for your version.

Query Timeout

Query timeout is configurable per application instance. It is a parameter of the sql-executor service, default value is 160 seconds.

Query timeout is closely related to the ACK timeout. Proper configuration of the system requires that ACK timeout is longer than query timeout. Default ACK timeout value is 170 seconds.

Permitted parameters

  • adaptiveFetch
  • adaptiveFetchMaximum
  • adaptiveFetchMinimum
  • allowEncodingChanges
  • ApplicationName
  • assumeMinServerVersion
  • autosave
  • binaryTransferDisable
  • binaryTransferEnable
  • cleanupSavepoints
  • connectTimeout
  • currentSchema
  • defaultRowFetchSize
  • disableColumnSanitiser
  • escapeSyntaxCallMode
  • gssEncMode
  • hostRecheckSeconds
  • loadBalanceHosts
  • loginTimeout
  • logUnclosedConnections
  • options
  • preferQueryMode
  • preparedStatementCacheQueries
  • preparedStatementCacheSizeMiB
  • readOnly
  • reWriteBatchedInserts
  • socketFactory
  • socketTimeout
  • ssl
  • sslcert
  • sslfactory
  • sslmode
  • sslpassword
  • sslpasswordcallback
  • targetServerType
  • tcpKeepAlive
Microsoft SQL Server
Synapse SQL