PostgreSQL Integration with Edilitics

PostgreSQL, often shortened to Postgres, is a powerful open-source database system known for its reliability and flexibility. Widely used in businesses of all sizes, Postgres can handle complex data and works well for large datasets and high-traffic applications. It is a favorite among developers for its advanced features and ease of use. To explore more about PostgreSQL, visit the official PostgreSQL website.

Within the Edilitics platform, PostgreSQL can be used both as a source and destination for data, making it a versatile tool for data analysis workflows. This enables you to leverage PostgreSQL's strengths for data storage and retrieval within your Edilitics environment.

This step-by-step guide equips you with the knowledge to configure your PostgreSQL Database instance and integrate it with Edilitics for data analysis.

Before You Begin#

Make sure you have the following:

• The IP address and hostname for your PostgreSQL server.

• PostgreSQL version 9.5 or higher.

• Binary logging enabled if using log-based replication.

• Edilitics IP addresses whitelisted on your PostgreSQL platform:

  • 34.131.133.218

  • 34.93.42.224

Setting Up PostgreSQL for Edilitics Integration#

Step 1: Enable Replication#

• Update the PostgreSQL Configuration File#

  • Edit the PostgreSQL configuration file (postgresql.conf), typically located in /etc/postgresql/<version>/main/ on Linux systems. Add or update the following settings:
  
  

  # PostgreSQL Configuration

  max_replication_slots = 10

  max_wal_senders = 10

  wal_level = logical

  wal_sender_timeout = 0

  
  

• Explanation:

  • max_replication_slots: Sets the number of clients that can connect to the server for replication.

  • max_wal_senders: Defines the number of processes that can transmit the write-ahead log (WAL) simultaneously.

  • wal_level: Enables log-based replication when set to logical.

  • wal_sender_timeout: Prevents replication connections from ending due to inactivity when set to 0.

• Update the PostgreSQL Database Configuration File#

  • In the pg_hba.conf file, grant permission to the database user to connect from an external network. Add the following lines, replacing <user> with your actual username:
  
  

  local replication <user> peer

  host replication <user> 127.0.0.1/0 md5

  host replication <user> ::1/0 md5

  
  
  • Restart the PostgreSQL server and grant access to the WAL for the database user:
  
  

  ALTER ROLE <user> WITH REPLICATION;

  
  

Step 2: Whitelist Edilitics’ IP Addresses#

• Grant Edilitics access to your PostgreSQL data port to ensure a real-time connection. Edit the postgresql.conf file to add the Edilitics IP addresses (34.131.133.218 and 34.93.42.224) or use * for the listen_addresses parameter to allow all IPs to connect.

• In the pg_hba.conf file, allow access from the Edilitics IP addresses:

  
  

  host all <user_name> 34.131.133.218/32 md5

  host all <user_name> 34.93.42.224/32 md5

  
  

Step 3: Create a Database User (Optional)#

• Create a database user (replace <password> with your desired password):

  
  

  CREATE USER edilitics_user WITH PASSWORD '<password>';

  
  

• Grant the necessary permissions to the user:

  
  

  GRANT CONNECT ON DATABASE <database_name> TO edilitics_user;

  GRANT USAGE ON SCHEMA <schema_name> TO edilitics_user;

  GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO edilitics_user;

  ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO edilitics_user;

  
  

Note: Replace <database_name> and <schema_name> with the actual database and schema names.

Connecting PostgreSQL to Edilitics#

Step 4: Add the PostgreSQL Connector in Edilitics#

• In Edilitics, navigate to the integrations module and locate the New Integration option.

• Choose the PostgreSQL connector from the list or use the search bar to find it.

Step 5: Configure the PostgreSQL Connection#

To connect, enter the following information on the next screen:

Alternatively, you can connect using a connection string. Replace the placeholders with your details:

Step 6: Test the Connection#

• Once you've entered your credentials, click the Test Connection button. Edilitics will attempt to connect to your PostgreSQL database. You'll see a confirmation message if the connection is successful. If there's a problem, Edilitics will provide details to help you troubleshoot.

Step 7: Verify & Finalize Integration#

Upon successful database connection, you can:

• Preview (Optional): Confirm accuracy by retrieving a sample of tables and data.

• Update (Optional): If the preview deviates from expectations, modify connection details for correct data retrieval.

• Save: Finalize by encrypting and storing connection details within Edilitics for future use.