Connect to a PostgreSQL database

MOSTLY AI can use a PostgreSQL database as a source for original data and deliver synthetic data to another PostgreSQL database. You can create separate connectors for the source and delivery of synthetic data.

Prerequisites

Obtain the PostgreSQL database connection details.

• hostname
• port
• database credentials
• database name
• schema name

To use an SSL connection, you need the following files:

• root certificate (.crt)
• client certificate (.crt)
• client private key (.key)

Steps

1. From the Connectors tab, click Create connector. The Create connector drawer appears on the right.
2. On the Connect to database tab, select PostgreSQL.
3. On the Create PostgreSQL connector page, configure the connector.

   1. For Connector name, enter a name that you can distinguish from other connectors.

      A combination of PostgreSQL + _DATABASE_ + _SCHEMA_ might help you identify this connector among other PostgreSQL connectors.

   2. For Connection type, select whether you want to use the connector as a source or destination.

      You can select only data source connectors when you create a new catalog.

      Similarly, you can select only data destination connectors when you configure a destination for the new synthetic dataset.

   3. For Hostname, enter the database hostname.

   4. For Port, enter the database port.

      By default, the port for PostgreSQL databases is 5432.

   5. For Username and Password, enter your PostgreSQL database credentials.

   6. For Database, enter the name of the database.

   7. For Schema, enter the schema of the database.

      
4. (Optional) Configure an encrypted SSL connection to the database.
   1. Enable Use SSL.
   2. For Root certificate, upload your server root certificate.
   3. For SSL certificate, upload your client certificate.
   4. For SSL certificate key, upload your client private key.
5. Click Test connection to make sure the configuration is correct. If you see a successful connection message, then MOSTLY AI connected to your PostgreSQL database successfully and found the database in the provided schema.
6. Click Save to save your new PostgreSQL connector.

What's next

You can now use the PostgreSQL connector as a data source when you create a new catalog.

You can also use the PostgreSQL connector as a destination.

You can use different types of data sources and destinations for a synthetic dataset. For example, if your data source is an PostgreSQL database, you can deliver the generated synthetic to any of the supported databases or cloud storage providers.