You can import data from PostgreSQL to create source datasets in Anaplan Data Orchestrator (ADO). To import data, you need a connection to PostgreSQL and either a pipeline or a connection extract.

You must first create a connection to PostgreSQL with the PostgreSQL connector in Data Orchestrator. Then use that connection to either create a pipeline or use a connection extract for data import.

Connectivity prerequisites

Before you can create a connection to PostgreSQL, you need to meet these prerequisites.

ItemsNotes
PostgreSQL instanceA PostgreSQL instance hosted on a cloud platform, such as Amazon Web Services (AWS), Azure, Google Cloud Platform (GCP), or any other cloud provider.
Certificate Authority (CA) certificate

An unencrypted CA certificate in Privacy-Enhanced Mail (PEM) format.

For the connector to use the CA certificate, you must first decode the Base64-encoded CA certificate in the PEM format. For example:

-----BEGIN CERTIFICATE-----
MIIDBzCCAe+....................
.......multiple line...........
-----END CERTIFICATE-----

PostgreSQL database user

You must be a PostgreSQL database user with a password, and have at least read permissions. 

For example:

CREATE USER <user_name> PASSWORD 'your_password_here';

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

Create a connection with PostgreSQL

You need your PostgreSQL credentials to connect the PostgreSQL data with Data Orchestrator. See the PostgreSQL documentation for more information.

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Connections on the left-side panel.
  4. Select Create connection.
  5. Select PostgreSQL and then select Next.
    If you can't find the connector, enter a search term in the Find... field.
  6. Enter these details on the Connection details screen, and then select Next:
    • Name: Create a name for your connection. The name can contain alphanumeric characters and underscores.
    • Description: Enter a description about your connection.
  7. Enter your PostgreSQL credentials on the Connection credentials screen, and then select Next:
    • Username: The PostgreSQL user name required to authenticate.
    • Password: The PostgreSQL password required to authenticate.
    • Host: The PostgreSQL host domain. The host is provided by the administrator of your PostgreSQL instance.
    • Port: The PostgreSQL port number. The port number is provided by the administrator of your PostgreSQL instance.
    • Database: The PostgreSQL database name.
    • CA Certificate: The PostgreSQL connector supports TLS/SSL encryption. TLS/SSL encryption enables you to establish a secure, encrypted connection to PostgreSQL instances. It protects data in transit from being intercepted or read by unauthorized parties. The connection is secured using a digital certificate installed on the server. This provides an essential layer of data protection.
  8. After the connection test is complete, select Done.

Note: If you need to edit the connection, you must enter the CA certificate again.

Import data

You can import data from PostgreSQL with a pipeline or a connection extract. Use the connection you created to configure the pipeline or connection extract.