Google Cloud PostgreSQL Integration with Edilitics

Leveraging PostgreSQL on Google Cloud Platform (GCP) grants you the benefits of a familiar PostgreSQL experience with the added advantages of scalability, high availability, and automated management offered by GCP. This empowers you to migrate existing PostgreSQL workloads or establish new databases with regional deployments for optimal performance. To explore more about Cloud PostgreSQL, visit the official Google Cloud PostgreSQL documentation.

Edilitics integrates seamlessly with your PostgreSQL GCP database, enabling you to unlock valuable insights from your data for informed decision-making. Within the Edilitics platform, PostgreSQL GCP can be used both as a source and destination for data, making it a versatile tool for data analysis workflows.

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

Before You Begin#

• Make sure you have the following:

  • The IP address or hostname of your PostgreSQL server.

  • PostgreSQL version 9.6 or higher.

  • Whitelisted Edilitics IP addresses:

    • 34.131.133.218

    • 34.93.42.224

  • Database user with SELECT, USAGE, and CONNECT privileges.

• For Change Data Capture Replication Mode:

  • Log-based incremental replication enabled.

  • The PostgreSQL database instance set as a master instance.

Note: PostgreSQL does not support logical replication for read replicas.

Configuring Google Cloud PostgreSQL for Edilitics#

Step 1: Set up Log-based Incremental Replication (Optional)#

Note: Skip this step if you are not using logical replication.

PostgreSQL (version 9.6 and above) supports logical replication by writing additional information to its Write Ahead Logs (WALs). To configure logical replication:

• Log in to Google Cloud SQL to access your database instance.

• Click the More icon next to the PostgreSQL instance and select Edit.

• Scroll down to the Flags section.

• Click the drop-down next to Flags and select Add a Database Flag.

• In the New database flag dialog, type the flag name in the filter bar.

• Select the flag and assign the appropriate value:

  • cloudsql.enable_pglogical: On

    • This enables the pglogical extension, allowing logical replication capabilities.

  • cloudsql.logical_decoding: On

    • Activates logical decoding for capturing row-level changes in the database.

  • max_replication_slots: 10

    • Specifies the maximum number of replication clients that can connect to the server.

  • max_wal_senders: 10

    • Sets the maximum number of processes that can send WAL logs to replication clients simultaneously.

  • wal_sender_timeout: 0

    • Ensures that replication connections are never terminated due to inactivity.

• Click Done after adding each flag.

• Add all required flags and click Save and Restart. Once restarted, you can view the configured settings under the Flags section.

• Once the instance restarts, you can view the configured settings under the Flags section.

Step 2: Whitelist Edilitics’ IP Addresses#

To establish a connection, whitelist Edilitics IP addresses for your Google Cloud PostgreSQL instance:

• Access the Google Cloud SQL Instances page and select the desired instance ID.

• In the left navigation pane, select Connections.

• Navigate to the Networking tab and scroll to the Authorized networks section.

• Add the following networks:

  • Name: Edilitics_1

  • Network: 34.131.133.218

  • Name: Edilitics_2

  • Network: 34.93.42.224

• Save the configuration.

Step 3: Create a Database User and Grant Privileges#

PostgreSQL allows connecting to Edilitics using an existing user or a newly created one. Choose one of the following options:

• Option 1: Configuring the User Account Using Google Cloud Console

  • Select your primary Google Cloud PostgreSQL instance.
  
  
  • In the left navigation pane, select Users, and click + Add User Account.
  
  

  • Choose an authentication method and ensure a strong password is set.

  • Click Add to finalize.

  
  

• Option 2: Create a Replication User and Grant Privileges

  While using logical replication, the user must have the cloudsqlsuperuser role. To configure:

  • Connect to your PostgreSQL database instance as a super admin using a SQL client.

  • Run the following commands:

  
  

  CREATE USER <database_username> WITH REPLICATION

  IN ROLE cloudsqlsuperuser LOGIN PASSWORD '<password>';

  
  

  Alternatively, update an existing user:

  
  

  ALTER USER <existing_user> WITH REPLICATION;

  
  
  • Grant privileges:
  
  

  GRANT CONNECT ON DATABASE <database_name> TO <database_username>;

  GRANT USAGE ON SCHEMA <schema_name> TO <database_username>;

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

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

  
  

  Note: Replace placeholder values as necessary.

Connecting Google Cloud PostgreSQL to Edilitics#

Step 4: Add the PostgreSQL GCP Connector in Edilitics#

• Navigate to the Integrations module in Edilitics and click New Integration.

• Select the PostgreSQL GCP connector from the list or use the search bar.

Step 5: Configure the PostgreSQL GCP Connection#

Enter the following details on the setup screen:

Alternatively, use a connection string:

Step 6: Test the Connection#

Once credentials are entered, click Test Connection. If successful, you'll see a confirmation message. Otherwise, Edilitics provides troubleshooting details.

Step 7: Verify & Finalize Integration#

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

• Update (Optional): Adjust connection details if data retrieval is inaccurate.

• Save: Finalize the setup by encrypting and storing connection details in Edilitics.