> AI agents: this is one page from PostHog's docs. Full index of Markdown docs for LLMs: https://posthog.com/llms.txt

# Postgres destination for batch exports

Canonical URL: https://posthog.com/docs/data-pipelines/batch-exports/postgres

> **Cloud region:** This page uses US Cloud hosts. For an EU Cloud project, use `https://eu.posthog.com` instead of `https://app.posthog.com` (app host for links).

## On this page

- [Setting up Postgres access](#setting-up-postgres-access)
- [Models](#models)
- [Creating the batch export](#creating-the-batch-export)
- [Postgres configuration](#postgres-configuration)

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](https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-PROTECTION).

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:

```sql
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:

| Field | Type | Description |
| --- | --- | --- |
| uuid | `VARCHAR(200)` | The unique ID of the event within PostHog |
| event | `VARCHAR(200)` | The name of the event that was sent |
| properties | `JSONB` | All the properties sent along with an event |
| elements | `JSONB` | This field is present for backwards compatibility but has been deprecated |
| set | `JSONB` | Deprecated. Person update payloads aren't retained on stored events |
| set\_once | `JSONB` | Deprecated. Person update payloads aren't retained on stored events |
| person\_properties | `JSONB` | All the person properties at the time the event was ingested. Later changes to the person do not update exported rows |
| person\_id | `VARCHAR(200)` | The ID of the person at the time the event was ingested. Rows are never updated regardless of underlying person changes |
| distinct\_id | `VARCHAR(200)` | The `distinct_id` of the user who sent the event |
| team\_id | `INTEGER` | The `team_id` for the event |
| ip | `VARCHAR(200)` | The IP address that was sent with the event |
| site\_url | `VARCHAR(200)` | This field is present for backwards compatibility but has been deprecated |
| timestamp | `TIMESTAMP WITH TIME ZONE` | The timestamp associated with an event |

### Persons model

The schema of the model as created in Postgres is:

| Field | Type | Description |
| --- | --- | --- |
| team\_id | `INTEGER` | The id of the project (team) the person belongs to |
| distinct\_id | `TEXT` | A `distinct_id` associated with the person |
| person\_id | `TEXT` | The id of the person associated to this (`team_id`, `distinct_id`) pair |
| properties | `JSONB` | A JSON object with all the latest properties of the person |
| person\_distinct\_id\_version | `INTEGER` | Internal version of the person to `distinct_id` mapping associated with a (`team_id`, `distinct_id`) pair, used by batch export in merge operation |
| person\_version | `INTEGER` | Internal version of the person properties associated with a (`team_id`, `distinct_id`) pair, used by batch export in merge operation |
| created\_at | `TIMESTAMP WITH TIME ZONE` | The timestamp when the person was created |

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](https://posthog.com/docs/data/anonymous-vs-identified-events.md) (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](https://app.posthog.com/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](#ssltls-verification) settings.
5.  Fill in the remaining [configuration details](#postgres-configuration).
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:

| Option | Description |
| --- | --- |
| **No** | The connection is encrypted, but the server's certificate is not verified. |
| **Verify certificate authority** | Verifies that the server's certificate is signed by a trusted certificate authority (CA). |
| **Verify certificate authority and server hostname** | Verifies both the CA signature and that the certificate's hostname matches the host you entered. |

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.