Postgres Schema and Table Discovery Issues

Postgres database ingestion is not discovering all schemas or tables, or is missing specific tables that exist in the database. This commonly occurs due to schema filtering, insufficient user permissions, or tables being in non-public schemas.

You Might Be Asking:

• Why are some of my Postgres tables missing?
• How do I ingest from multiple Postgres schemas?
• What permissions does the DataHub user need?

1. Configure Postgres source with schema patterns:

Example for Postgres. This can be adapted for other relational databases.

source:
  type: postgres
  config:
    host_port: "postgres.company.com:5432"
    database: "your_database"
    username: "${POSTGRES_USER}"
    password: "${POSTGRES_PASSWORD}"

    # Include specific schemas
    schema_pattern:
      allow: ["public", "analytics", "staging"]
      deny: ["pg_catalog", "information_schema", "pg_temp.*"]

    # Include specific tables
    table_pattern:
      allow: [".*"]
      deny: [".*_temp$", ".*_backup$"]

2. Grant necessary permissions to DataHub user:

Example for Postgres. This can be adapted for other relational databases.

-- Create dedicated user for DataHub
CREATE USER datahub_user WITH PASSWORD 'secure_password';

-- Grant schema access
GRANT USAGE ON SCHEMA public TO datahub_user;
GRANT USAGE ON SCHEMA analytics TO datahub_user;

-- Grant read access to tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO datahub_user;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO datahub_user;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
  GRANT SELECT ON TABLES TO datahub_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics 
  GRANT SELECT ON TABLES TO datahub_user;

-- For profiling, allow table stats access
GRANT SELECT ON pg_catalog.pg_statistic TO datahub_user;

3. For RDS Postgres, use IAM authentication:

Example for AWS RDS Postgres. This can be adapted for other managed database services.

source:
  type: postgres
  config:
    host_port: "database.region.rds.amazonaws.com:5432"
    database: "your_database"
    username: "datahub_user"
    # Use AWS IAM authentication
    use_iam_auth: true
    aws_region: "us-east-1"

4. Extract table and column lineage from views:

Example for Postgres. This can be adapted for other relational databases.

source:
  config:
    include_views: true
    include_tables: true
    # Parse view definitions for lineage
    parse_view_ddl: true

5. Enable profiling for table statistics:

Example for Postgres. This can be adapted for other relational databases.

source:
  config:
    profiling:
      enabled: true
      # Profile only specific schemas
      allow_deny_patterns:
        allow: ["analytics.*"]
      # Limit row scans
      max_number_of_fields_to_profile: 20
      profile_table_level_only: true

For large Postgres databases, consider excluding system schemas and temporary tables to improve ingestion performance. Use table_pattern filters to focus on relevant tables.

• Postgres Source
• Database Profiling