Exporting PostHog Event Data to Amazon Redshift at Scale

Introduction

Product analytics platforms like PostHog generate massive volumes of event data. While PostHog stores this in ClickHouse for fast querying, many organizations need this data in a data warehouse like Amazon Redshift for broader analytics, reporting, and integration with BI tools.

PostHog offers a built-in batch export feature for this purpose. However, on self-hosted deployments running via docker-compose, the batch export pipeline does not work out of the box.

The real challenge is not configuring a destination in the UI. It ensures the internal staging pipeline, worker processes, and network paths are correctly wired for a Dockerized self-hosted environment.

To solve this, we built a working pipeline that moves event data from PostHog’s ClickHouse through an internal MinIO staging layer, into an external S3 bucket, and finally into Amazon Redshift using the COPY command.

Problem Statement

In self-hosted PostHog deployments:

• The batch export destination can be configured from the UI
• Events are captured and stored in ClickHouse
• But no data reaches Redshift

This happens because:

• No worker process listens on the required task queue
• The staging code assumes either a cloud environment (with IAM roles) or a local development setup (with localhost)
• ClickHouse cannot reach the internal MinIO bucket using localhost from inside Docker
• Python bytecode caching can override patched source files

The result: exports are scheduled but silently fail, with no data flowing to the warehouse.

Overview of the Solution

The solution connects PostHog’s internal export mechanism with the correct Docker networking and credential configuration.

High-level flow:

1. A dedicated Temporal worker listens on the batch-exports-task-queue
2. The worker queries ClickHouse for event data in the specified time range
3. ClickHouse writes Arrow-format files to the internal MinIO bucket
4. The worker reads staged files and uploads them to an external S3 bucket
5. The worker issues a Redshift COPY command to load data from S3
6. Event data appears in the target Redshift table

At the end, PostHog events flow automatically to Redshift on an hourly schedule.

AWS Services and Components Used

• Amazon EC2
• Amazon Redshift
• Amazon S3
• MinIO
• ClickHouse
• Temporal
• Docker Compose

Architecture and Workflow

Step 1 – Worker Service

The default PostHog docker-compose stack does not include a worker for batch exports. A new service is added that runs:

python manage.py start_temporal_worker –task-queue batch-exports-task-queue

This worker picks up export jobs scheduled by the PostHog UI.

Step 2 – Internal Staging Configuration

The worker requires environment variables to connect to the internal MinIO:

• BATCH_EXPORT_OBJECT_STORAGE_ENDPOINT pointing to MinIO’s Docker hostname
• OBJECT_STORAGE_ACCESS_KEY_ID and OBJECT_STORAGE_SECRET_ACCESS_KEY for MinIO credentials
• OBJECT_STORAGE_BUCKET set to the internal bucket name

Step 3 – Code Patches for Self-Hosted

The PostHog batch export code has three assumptions that break in self-hosted Docker:

1. IAM role detection – The code checks whether to use keyless (IAM) authentication. For self-hosted, this must be forced to use explicit credentials.
2. Environment detection – A function determines if the stack is running locally. For self-hosted Docker, this must return true to use the MinIO staging path.
3. Endpoint URL – When running “locally,” the code uses localhost:19000. But ClickHouse runs in its own container and needs the Docker hostname objectstorage:19000.

These patches are applied to extracted source files and mounted as volumes into the container.

Step 4 – Bytecode Cache Prevention

Python caches compiled .pyc files. When mounting patched .py files, the cached bytecode from the original image takes precedence. Setting PYTHONDONTWRITEBYTECODE=1 ensures Python always reads the patched source files.

Step 5 – Redshift Connection

The worker connects to Redshift using psycopg with SSL required. Key configuration points:

• Use port 5439 (Redshift default), not the PostgreSQL default
• Ensure the EC2 security group allows outbound access to Redshift
• Redshift and PostHog should be in the same VPC or have VPC peering

Step 6 – Data in Redshift

Once the pipeline runs, PostHog creates the target table and loads event data. The table includes all standard PostHog event fields: event name, timestamp, distinct_id, properties, and more.

Key Implementation Details

Volume Mounts for Persistence

Editing files inside a running container is temporary. Container restarts lose all changes. The solution uses Docker volume mounts to overlay patched files onto the container filesystem. This ensures patches survive restarts and upgrades only require re-extracting and re-patching.

ClickHouse to MinIO Connectivity

The most subtle issue is that ClickHouse executes the S3 write query inside its own container. It cannot reach localhost:19000 because MinIO is in a different container. The Docker service hostname objectstorage resolves correctly across the Docker network.

Workflow Management

Failed exports can leave Temporal workflows stuck. The batch export may auto-pause after repeated failures. Recovery requires:

• Terminating stuck workflows via the Temporal client
• Unpausing the batch export via Django shell
• Creating new backfills after fixing the root cause

Operational Impact

After implementing this pipeline:

• Event data flows automatically to Redshift every hour
• No manual intervention required after initial setup
• BI tools can query PostHog data alongside other business data
• Historical backfills can be triggered from the UI
• The setup survives container restarts

Important Considerations

1. Version Compatibility

The patches are specific to the PostHog version running at the time of deployment. After upgrading PostHog, the original files should be re-extracted and patches reapplied.

2. Monitoring

Monitor the batch export worker logs for failures. PostHog will auto-pause exports after repeated failures, requiring manual intervention to unpause.

3. S3 Bucket Lifecycle

The staging bucket accumulates temporary Arrow files. Configure S3 lifecycle rules to clean up old files in the staging prefix.

4. Security Groups

Ensure the EC2 instance hosting PostHog can reach:

• Redshift on port 5439
• S3 endpoints (via NAT gateway or VPC endpoint if in a private subnet)

Conclusion

Getting batch exports working on self-hosted PostHog requires understanding the internal architecture: how Temporal workers process jobs, how ClickHouse stages data to object storage, and how Docker networking affects service-to-service communication.

The core issues are not bugs, they are design decisions optimized for PostHog CloudThat need adjustment for self-hosted Docker deployments.

By adding a dedicated worker, patching the staging logic, and configuring the correct network paths, we get a reliable pipeline that moves product analytics data into a proper data warehouse.

This enables teams to combine PostHog event data with other business data in Redshift, unlocking analytics use cases that go beyond what the PostHog UI alone can provide.

Drop a query if you have any questions regarding PostHog, and we will get back to you quickly.