Postgres destination for batch exports

Batch exports can be used to export data to a Postgres table.

Setting up Postgres access

1. Make sure PostHog can access your Postgres database.

Notes:

• We only support connections using SSL/TLS. This provides protection against various types of attacks.

2. Create a Postgres user with table creation privileges.

When executing a batch export, if the destination table doesn't exist, it will be created. CREATE TABLE and USAGE permissions are required for this reason. The other permissions that are required on the destination table are INSERT, SELECT, and UPDATE. You can and should block PostHog from doing anything else on any other tables. In particular, we recommend creating a new schema and only granting PostHog CREATE TABLE and USAGE access limited to that schema:

CREATE USER posthog WITH PASSWORD 'insert-a-strong-password-here';
CREATE SCHEMA posthog_exports;
GRANT CREATE ON SCHEMA posthog_exports TO posthog;
GRANT USAGE ON SCHEMA posthog_exports TO posthog;

Models

Note: New fields may be added to these models over time. To maintain consistency, these fields are not automatically added to the destination tables. If a particular field is missing in your Postgres tables, you can manually add the field, and it will be populated in future exports.

Events model

This is the default model for Postgres batch exports. The schema of the model as created in Postgres is:

Persons model

The schema of the model as created in Postgres is:

The Postgres table will contain one row per (team_id, distinct_id) pair, and each pair is mapped to their corresponding person_id and latest properties.

Note: The persons model only includes persons that have a person profile in PostHog. If your project has person profile processing disabled (via person_profiles: 'identified_only', person_profiles: 'never', or by sending events with $process_person_profile: false), anonymous users who have never been identified will not appear in the persons export. To count unique users including those without person profiles, you can fall back to distinct_id from the events model. See the example queries in each destination's documentation for details.

Sessions model

You can view the schema for the sessions model in the configuration form when creating a batch export (there are a few too many fields to display here!).

Creating the batch export

1. Click Data management > Destinations in the left sidebar.
2. Click + New destination in the top-right corner.
3. Select Postgres as the batch export destination.
4. Set up a PostgreSQL connection by clicking Configure new PostgreSQL connection in the Connection dropdown. This opens an integration setup modal where you provide your host, port, user, password, and SSL/TLS verification settings.
5. Fill in the remaining configuration details.
6. Finalize the creation by clicking on Create.
7. Done! The batch export will schedule its first run on the start of the next period.

Postgres configuration

Configuring a batch export targeting Postgres requires the following Postgres-specific configuration values:

• Connection - A PostgreSQL integration that stores your database credentials (host, port, user, password, and SSL settings). Click Configure new PostgreSQL connection to create one.
• Table name - The name of a Postgres table where to export the data.
• Database - The name of the Postgres database where the table provided to insert data is located.
• Schema - The name of the Postgres database schema where the table provided to insert data is located.

Note: Existing batch exports created before the integration setup was available continue to work with their inline credentials (user, password, host, port). When editing these exports, the original fields are shown instead of the Connection dropdown.

SSL/TLS verification

All PostgreSQL connections are encrypted using TLS. You can optionally verify the server's identity during the integration setup to guard against man-in-the-middle attacks.

The Verify server identity? setting in the integration setup modal has three options:

When using either verification option, you can:

• Upload a root certificate - Provide a custom CA certificate file (.crt, .pem, .cer, or .ca-bundle).
• Use the system certificate authorities - Use the default system CA bundle instead of uploading a custom certificate.