Skip to main content

PostgreSQL Configuration

Welcome to your detailed guide on configuring PostgreSQL as a source database for Boltic Pipes! This document will take you through each crucial step required to integrate PostgreSQL seamlessly with Boltic’s powerful data pipeline framework.

Boltic Pipes excels in synchronizing and transferring data across various platforms. To set up PostgreSQL as a source, you’ll need to fine-tune several settings to ensure optimal operation and data integrity. This guide will provide you with the essential configurations and best practices to make your PostgreSQL setup both robust and efficient.

Suggested User Creation​

We recommend creating a user named boltic_cdc_user for handling replication and login tasks. Follow the steps below for setting up the user and configuring necessary permissions and privileges.

1. Permissions​

Assign the following permissions to the user:

-- Grant replication permission to the user
ALTER ROLE <user> WITH REPLICATION;

-- Grant login permission to the user
ALTER ROLE <user> WITH LOGIN;

2. Privileges​

  • Replication privileges in the database to add the table to a publication.
  • CREATE privileges on the database to add publications.
  • SELECT privileges on the tables to copy the initial table data. Table owners automatically have SELECT permission for the table.
  • Configure the necessary privileges for the user mentioned below:
-- Create a user with replication role and login capability
-- Replace '[MY_PASSWORD]' with the desired password for the user
CREATE USER boltic_cdc_user WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD '[MY_PASSWORD]';

-- Grant CREATE privilege on the database to allow adding publications
GRANT CREATE ON DATABASE <YOUR_DB_NAME> TO boltic_cdc_user;

-- Grant USAGE privilege on the schema to allow access to schema objects
GRANT USAGE ON SCHEMA <YOUR_SCHEMA_NAME> TO boltic_cdc_user;

-- Grant SELECT privilege on all tables in the schema to allow reading table data
GRANT SELECT ON ALL TABLES IN SCHEMA <YOUR_SCHEMA_NAME> TO boltic_cdc_user;

-- Alter default privileges to ensure new tables will have SELECT privilege
ALTER DEFAULT PRIVILEGES IN SCHEMA <YOUR_SCHEMA_NAME> GRANT SELECT ON TABLES TO boltic_cdc_user;

-- Create a heartbeat table to track changes and grant necessary privileges
CREATE TABLE <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.__boltic_heartbeat();

-- Grant SELECT, INSERT, UPDATE, and DELETE privileges on the heartbeat table
GRANT SELECT, INSERT, UPDATE, DELETE ON <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.__boltic_heartbeat TO boltic_cdc_user;

-- Alter the heartbeat table to include additional columns for tracking
ALTER TABLE <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.__boltic_heartbeat
ADD COLUMN IF NOT EXISTS id SERIAL PRIMARY KEY,
ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

3. Database Configuration​

Configure your PostgreSQL instance with the settings below to ensure proper replication:

  • Set wal_level to logical or higher: Ensure that logical replication is enabled.
  • Set max_replication_slots to a value greater than 0: Configure replication slots to handle replication.
  • Ensure Free Replication Slots are Greater Than 0: Verify that there are available replication slots.
  • Set max_wal_senders to a value greater than 0: Configure WAL senders to manage replication connections.

Configure the PostgreSQL settings with the following SQL commands:

Set wal_level to logical​

-- Set wal_level to logical to support logical replication
-- This command enables logical replication by setting the wal_level parameter.
-- Logical replication requires this setting to be at least 'logical'.
ALTER SYSTEM SET wal_level = 'logical';

Set max_replication_slots to a value greater than 0​

-- Configure replication slots to handle replication
-- This command sets the max_replication_slots parameter to a value greater than 0.
-- Replication slots are used to manage the replication process.
ALTER SYSTEM SET max_replication_slots = 4;

Ensure Free Replication Slots are Greater Than 0​

-- Check available replication slots
-- This command retrieves information about the current replication slots.
-- Ensure that there are free replication slots available for use.
SELECT * FROM pg_replication_slots;

Set max_wal_senders to a value greater than 0​

-- Configure WAL senders to manage replication connections
-- This command sets the max_wal_senders parameter to a value greater than 0.
-- WAL senders are required for managing the replication connections.
ALTER SYSTEM SET max_wal_senders = 4;

🤓 Have Questions?​

We're here to help! Feel free to reach out via email at 📧 [email protected].