PostgreSQL Requirements

This guide covers the requirements for using PostgreSQL as a CDC source with EZ-CDC.

Version Requirements

Requirement	Minimum	Recommended
PostgreSQL Version	12	14+
Logical Replication	Required	-

Configuration Requirements

1. Enable Logical Replication

PostgreSQL must have wal_level = 'logical' to enable CDC:

-- Check current setting
SHOW wal_level;

If not logical, update postgresql.conf:

# postgresql.conf
wal_level = 'logical'
max_replication_slots = 10
max_wal_senders = 10

Restart PostgreSQL after changing these settings.

warning

Changing wal_level requires a PostgreSQL restart.

2. Replication Slots

Ensure you have available replication slots:

-- Check current usage
SELECT slot_name, active FROM pg_replication_slots;

-- Check max slots
SHOW max_replication_slots;

Each CDC job requires one replication slot.

3. WAL Senders

Ensure you have available WAL senders:

-- Check current usage
SELECT * FROM pg_stat_replication;

-- Check max senders
SHOW max_wal_senders;

User Permissions

Create CDC User

Create a dedicated user for CDC:

-- Create user with replication privilege
CREATE USER ezcdc_user WITH
  REPLICATION
  LOGIN
  PASSWORD 'your_secure_password';

-- Grant connect to database
GRANT CONNECT ON DATABASE mydb TO ezcdc_user;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO ezcdc_user;

-- Grant select on tables (all current tables)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ezcdc_user;

-- Grant select on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO ezcdc_user;

Required Privileges Summary

Privilege	Purpose
REPLICATION	Create replication slots and read WAL
CONNECT	Connect to the database
USAGE	Access the schema
SELECT	Read table data for initial snapshot

Network Requirements

Connectivity

Workers must be able to connect to PostgreSQL:

From	To	Port	Protocol
EZ-CDC Worker	PostgreSQL	5432	TCP

Firewall Rules

If using security groups or firewalls:

# Allow from EZ-CDC worker security group
resource "aws_security_group_rule" "postgres_from_ezcdc" {
  type                     = "ingress"
  from_port                = 5432
  to_port                  = 5432
  protocol                 = "tcp"
  source_security_group_id = var.ezcdc_worker_sg_id
  security_group_id        = var.postgres_sg_id
}

RDS / Aurora Specifics

For AWS managed databases:

1. Ensure the parameter group has rds.logical_replication = 1
2. Verify security group allows worker access
3. Use the correct endpoint (writer for CDC)

-- Check RDS logical replication
SHOW rds.logical_replication;  -- Should be 'on'

Table Requirements

Primary Keys

Tables should have primary keys for proper CDC:

-- Check for tables without primary keys
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints tc
  ON t.table_schema = tc.table_schema
  AND t.table_name = tc.table_name
  AND tc.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema = 'public'
  AND tc.constraint_name IS NULL;

tip

Tables without primary keys can still be replicated, but UPDATE and DELETE operations will include all columns to identify rows.

REPLICA IDENTITY

For tables without primary keys, set REPLICA IDENTITY:

-- Use FULL for tables without PK (includes all columns in updates)
ALTER TABLE my_table REPLICA IDENTITY FULL;

-- Check current setting
SELECT relname, relreplident
FROM pg_class
WHERE relname = 'my_table';
-- 'd' = default, 'f' = full, 'i' = index, 'n' = nothing

Verification Script

Run this script to verify your PostgreSQL is ready:

-- 1. Check WAL level
SELECT
  CASE WHEN current_setting('wal_level') = 'logical'
    THEN '✓ WAL level is logical'
    ELSE '✗ WAL level is ' || current_setting('wal_level')
  END AS wal_check;

-- 2. Check available replication slots
SELECT
  current_setting('max_replication_slots')::int - COUNT(*) AS available_slots
FROM pg_replication_slots;

-- 3. Check available WAL senders
SELECT
  current_setting('max_wal_senders')::int - COUNT(*) AS available_senders
FROM pg_stat_replication;

-- 4. Check user permissions
SELECT
  rolreplication AS has_replication,
  rolcanlogin AS can_login
FROM pg_roles
WHERE rolname = 'ezcdc_user';

-- 5. Check tables without primary keys
SELECT COUNT(*) AS tables_without_pk
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints tc
  ON t.table_schema = tc.table_schema
  AND t.table_name = tc.table_name
  AND tc.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema = 'public'
  AND tc.constraint_name IS NULL;

Common Issues

"cannot create replication slot"

Cause: wal_level is not logical or no available slots.

Solution:

1. Set wal_level = 'logical' and restart
2. Increase max_replication_slots

"permission denied for replication"

Cause: User doesn't have REPLICATION privilege.

Solution:

ALTER USER ezcdc_user WITH REPLICATION;

"could not connect to server"

Cause: Network connectivity issue.

Solution:

1. Check security groups allow port 5432
2. Verify PostgreSQL is listening on correct interface
3. Check pg_hba.conf allows the connection

Next Steps

• PostgreSQL Configuration - Configure the datasource
• Logical Replication Details - Understand how it works