PostgreSQL Configuration

This guide explains how to configure a PostgreSQL datasource in EZ-CDC.

Add PostgreSQL Source

Via Portal

1. Navigate to Datasources → New Datasource
2. Select PostgreSQL as the type
3. Fill in the connection details:

Field	Description	Example
Name	Unique identifier	production-orders
Host	PostgreSQL hostname	postgres.example.com
Port	PostgreSQL port	5432
Database	Database name	orders_db
Username	CDC user	ezcdc_user
Password	User password	********
SSL Mode	TLS connection mode	require

4. Click Test Connection
5. Click Save

Connection Options

SSL Modes

Mode	Description	Use Case
disable	No SSL	Development only
allow	Try SSL, fallback to plain	Not recommended
prefer	Try SSL, fallback to plain	Default
require	Require SSL, skip verification	Production
verify-ca	Require SSL, verify CA	High security
verify-full	Require SSL, verify CA and hostname	Maximum security

tip

Always use require or higher in production.

SSL Certificates

For verify-ca or verify-full, provide certificates:

{
  "ssl_mode": "verify-full",
  "ssl_root_cert": "-----BEGIN CERTIFICATE-----\n...",
  "ssl_client_cert": "-----BEGIN CERTIFICATE-----\n...",
  "ssl_client_key": "-----BEGIN PRIVATE KEY-----\n..."
}

Replication Settings

When creating a job, you'll configure replication-specific settings:

Replication Slot

The replication slot name tracks position in the WAL:

Field	Description	Default
Slot Name	Unique slot identifier	ezcdc_slot_{job_id}

-- Slot is created automatically when the job starts
-- View existing slots:
SELECT slot_name, active, restart_lsn
FROM pg_replication_slots;

Publication

The publication defines which tables to replicate:

Field	Description	Default
Publication Name	Publication identifier	ezcdc_pub_{job_id}

-- Publication is created automatically
-- View existing publications:
SELECT pubname, puballtables
FROM pg_publication;

-- View tables in publication:
SELECT * FROM pg_publication_tables
WHERE pubname = 'ezcdc_pub_123';

Advanced Options

Metadata Schema

EZ-CDC stores tracking metadata in your PostgreSQL database:

{
  "metadata_schema": "ezcdc_metadata"
}

This schema contains:

• Checkpoint position tracking
• Schema version information

Connection Pool

Configure connection pooling (future feature):

{
  "pool_min": 1,
  "pool_max": 5,
  "connection_timeout_ms": 5000
}

Test Connection

From Portal

Click Test Connection to verify:

1. Network connectivity
2. Authentication
3. Database access
4. Required permissions

From CLI

Use psql to test manually:

psql "postgresql://ezcdc_user:password@postgres.example.com:5432/orders_db?sslmode=require"

# Check replication permission
SELECT * FROM pg_roles WHERE rolname = 'ezcdc_user';

# Check logical replication
SHOW wal_level;

Connection Troubleshooting

"Connection refused"

Error: could not connect to server: Connection refused

Causes:

• PostgreSQL not running
• Wrong host/port
• Firewall blocking connection

Solutions:

1. Verify PostgreSQL is running: systemctl status postgresql
2. Check host and port are correct
3. Check security groups/firewall rules

"Password authentication failed"

Error: password authentication failed for user "ezcdc_user"

Solutions:

1. Verify password is correct
2. Check pg_hba.conf allows the connection method
3. Try connecting with psql to isolate the issue

"SSL connection required"

Error: SSL connection required

Solutions:

1. Set ssl_mode: require in configuration
2. Or enable SSL on PostgreSQL server

"Permission denied"

Error: permission denied for table orders

Solutions:

GRANT SELECT ON TABLE orders TO ezcdc_user;
-- Or for all tables:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ezcdc_user;

Best Practices

1. Use Environment-Specific Datasources

Create separate datasources for each environment:

• production-orders-source
• staging-orders-source
• development-orders-source

2. Rotate Credentials Regularly

Update passwords periodically:

1. Create new password in PostgreSQL
2. Update datasource in EZ-CDC portal
3. Verify connection works
4. Remove old password

3. Monitor Connection Health

Set up alerts for:

• Connection failures
• High latency
• Replication slot inactive

Next Steps

• Logical Replication Details
• Create a Job