Skip to main content

PostgreSQL Requirements

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

Version Requirements​

RequirementMinimumRecommended
PostgreSQL Version1214+
Logical ReplicationRequired-

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​

PrivilegePurpose
REPLICATIONCreate replication slots and read WAL
CONNECTConnect to the database
USAGEAccess the schema
SELECTRead table data for initial snapshot

Network Requirements​

Connectivity​

Workers must be able to connect to PostgreSQL:

FromToPortProtocol
EZ-CDC WorkerPostgreSQL5432TCP

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
}

AWS RDS / Aurora​

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'

For RDS, grant replication via the rds_replication role:

GRANT rds_replication TO ezcdc_user;

GCP Cloud SQL​

For Google Cloud SQL for PostgreSQL:

  1. Enable logical replication: In the Cloud SQL instance settings, enable the cloudsql.logical_decoding database flag and set it to on. This sets wal_level = 'logical' internally. The instance will restart.

  2. Grant REPLICATION privilege: The default postgres user in Cloud SQL does not have the REPLICATION privilege by default. Connect as postgres and run:

-- If using a dedicated CDC user
CREATE USER ezcdc_user WITH REPLICATION LOGIN PASSWORD 'your_secure_password';

-- Or if using the default postgres user
ALTER ROLE postgres WITH REPLICATION;

  1. Network access: Cloud SQL instances with private IP only are accessible from within the VPC. Ensure your EZ-CDC workers are deployed in the same VPC or a peered network. If using Cloud SQL Auth Proxy from outside the VPC, enable a public IP temporarily or use IAP tunneling.

  2. Verify configuration:

-- Check WAL level (should be 'logical')
SHOW wal_level;

-- Check replication privilege
SELECT rolname, rolreplication FROM pg_roles WHERE rolname = current_user;

-- Check available replication slots
SHOW max_replication_slots;
tip

Cloud SQL sets max_replication_slots = 10 and max_wal_senders = 10 by default when logical decoding is enabled. You can adjust these via database flags if needed.

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​