# How to Export to PostgreSQL

> **Note:** Our PostgreSQL export feature enables flexible access to your data in the form of tables in a PostgreSQL database hosted on the server of your choice. Once you've prepared a data table in [Data Builder](https://knowledge.powermyanalytics.com/application/tools/data-builder-guide/), you're ready to export your data to your PostgreSQL server. With our PostgreSQL export feature, you can run complex queries over your data and integrate your data with a variety of applications. PostgreSQL exports are available as a paid add-on on the Business, Grow, and Pro plans, and are included with the [Custom plan](https://knowledge.powermyanalytics.com/application/administration/custom-plan-guide/) when your negotiated agreement includes data exports.

## How We Store Data in PostgreSQL Exports

### Creating a table

Our PostgreSQL export feature creates tables named after the [Data Builder](https://knowledge.powermyanalytics.com/application/tools/data-builder-guide/) data table you are exporting, with all spaces and periods replaced by underscores (\_). If the data table includes a date field, the export creates one table for each calendar month, with the first day of that month added to the end of the name. For example, a data table named **Search Terms** is exported to tables such as `Search_Terms_20261001` and `Search_Terms_20261101`. If the data table has no date field, the export creates a single table with the data table's name. Each table has columns corresponding to the fields in the Data Builder data table being exported.

### Table and column names are case-sensitive

PostgreSQL reads any name that isn't in double quotes as lowercase. Our export keeps the exact capitalization of your data table name and field names, so when a table or column name contains capital letters, wrap it in double quotes in your queries. For example, the date column of a Microsoft Advertising data table is exported as `bing_TimePeriod`:

```sql
SELECT "bing_TimePeriod"
FROM "Search_Terms_20261101";
```

Without the quotes, PostgreSQL looks for `bing_timeperiod` instead and returns an error such as `column "bing_timeperiod" does not exist`. Names that are all lowercase work with or without quotes.

### Appending and updating rows

Each time the export runs, it refreshes the dates in your data table's date range: it deletes the rows for those dates from the table, then inserts the latest data for the same dates. Rows for dates outside that range stay as they are, so your history builds up over time. Any rows you add or change yourself within the date range are replaced on the next run. If your data table has no date field, the export replaces the whole table each time it runs.

### Adding columns

When you edit the data table in Data Builder to add another field, this field will be added to the table in PostgreSQL as a new column the next time the export runs. This column will be populated with the field's values going forward. Historical rows from before the new column was added will have the new field's value set to null.

### Dropping columns

When you edit the data table in Data Builder to remove an existing field, this field's corresponding column will be dropped from the table in PostgreSQL the next time the export runs.

> **Important:** Be careful: when dropping a column, historical data for the dropped fields will be deleted.

## Requirements

- A plan that includes data exports (available as a paid add-on on the Business, Grow, and Pro plans; included with the [Custom plan](https://knowledge.powermyanalytics.com/application/administration/custom-plan-guide/) when your agreement includes data exports)

- [Create the Data Builder data table(s)](https://knowledge.powermyanalytics.com/application/tools/data-builder-guide/) you want to export
- A PostgreSQL server with at least one database
- Your PostgreSQL credentials:

    - Hostname or public IP address of your server
    - Port (default: 5432)
    - Username
    - Password
    - Name of database to use

If you don't currently have a PostgreSQL server, you can create one using cloud hosting services like [Google Cloud SQL for PostgreSQL](https://cloud.google.com/sql/docs/postgres), Amazon RDS, or [Azure Database for PostgreSQL](https://azure.microsoft.com/en-us/products/postgresql).

### Setting Up Your PostgreSQL Server

When setting up your PostgreSQL server, ensure that you configure it to accept connections from Power My Analytics' IP addresses:

- 35.188.118.242/32
- 35.209.185.69/32

For most PostgreSQL servers, you'll need to:

1. Configure your PostgreSQL server's **pg\_hba.conf** file to allow connections from these IP addresses.
2. Ensure your **postgresql.conf** file has **listen\_addresses** set to allow external connections.
3. Configure any firewall or security groups to allow inbound traffic on port 5432 from PMA's IP addresses.

## Set Up Your Initial Backfill and Rolling Updates

### Initial backfill

The first step is to backfill your historical data to your PostgreSQL server. You can backfill data for up to 2 years for most PMA connectors. In your [data table in Data Builder](https://knowledge.powermyanalytics.com/application/tools/data-builder-guide/), set the date range to the entire range of data you want to export. To backfill your data from a specified start date up to yesterday, choose **Custom start to date** and enter a start date.

![](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-postgresql-1.png)

Click **Save** to save the data table.

![](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-postgresql-2.png)

Go to **Exports > SQL** and click **\+ Create export**. The Create export wizard opens and walks you through four steps: **Destination**, **Data**, **Schedule**, and **Review**.

Under **Destination**, select your PostgreSQL server if it is already connected. If you haven't yet connected your server, click the plus button to add a destination account:

1. For **Type**, select **PostgreSQL**.
2. Enter your PostgreSQL server's **Host** (IP address or host name) and **Port** (default: 5432 for PostgreSQL).
3. Enter your PostgreSQL **Username** and **Password**.
4. Under **Name**, enter a nickname to use for your PostgreSQL server, then click **Connect**. Once the server is added, return to the wizard and restart the export setup.

> **Note:** The Help panel in the Connect SQL dialog lists the IP addresses PMA uses to interact with your database: **35.188.118.242** and **35.209.185.69**. Make sure both are whitelisted on your server.

For **Database name**, enter the name of the database you want to use on your PostgreSQL server. Tables are named after your Data Builder data table, as described in [Creating a table](#creating-a-table). To write the tables into a specific PostgreSQL schema, enter its name under **Schema**; if you leave it blank, the tables are created in the `public` schema. Then click **Next**.

Under **Data table or dataset**, select either the single data table or the entire dataset you want to export (exporting a dataset sends every data table in it). Then click **Next**.

Next, choose a **Refresh period**. Since this export is being used for the initial backfill, select **On demand** and leave **Run as soon as you create the export** enabled. An On demand export has no schedule and runs only when you run it manually from the exports list. (For the scheduled refresh periods, the **Timezone** is required and determines the local wall-clock time at which your export will run.) Click **Next**.

Under **Review**, check the Destination, Data, and Schedule summaries; click **Edit** on any card to make a change. Then click **Create export**. Because **Run as soon as you create the export** was enabled, your initial backfill begins immediately.

To confirm the export was successful, find your new export on the Exports to SQL page, open the three-dot action menu at the end of its row, and select **View logs**. You can run the export manually at any time by selecting **Run export now** from the same menu.

### Rolling Updates

Once your initial backfill has run, it's time to set up rolling updates for your data table. This will keep your PostgreSQL export updated with the latest data from your data table. Go to Data Builder and find your data table. Click the three-dots action menu and select **Edit**.

![](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-postgresql-3.png)

Go to **Date Range** and choose a rolling window for updates to your data table, such as yesterday, last 7 days, or last 30 days. Click **Set Range**. A range of 30 days can be an ideal choice for e-commerce to settle orders that are returned.

![](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-postgresql-4.png)

Then click **Save**.

![](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-postgresql-5.png)

Go back to **Exports > SQL**. Open your export's three-dot action menu and select **Edit export**.

Under **Schedule**, choose how frequently you want your data export to run: Monthly, Weekly, Daily, or Hourly. You will be prompted to choose a day of the month (for Monthly, 1 through 28), day of the week (for Weekly), and hour of the day (for Monthly, Weekly, and Daily). Confirm or update your **Timezone**, which determines the local wall-clock time at which your export will run. Then save your changes.

> **Important:** Ensure your data table's date range in Data Builder is at least as long as the refresh period in the PostgreSQL export in order to avoid missing data. For example, if your data table's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.

Make sure your data table's date range in Data Builder is at least as long as the refresh period in your export. Failing to do so may result in data missing from your backfill. For example:

- If your data table's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.
- If your data table's date range is one week and your export refreshes daily, your export will overwrite the past seven days, every day.

Your export will now run automatically at the scheduled time.

## Troubleshooting

If you encounter any issues with your exports:

- Check the export logs by opening the three-dot action menu next to your export and selecting **View logs**.
- Verify that your PostgreSQL credentials are correct and that the PMA IP addresses are whitelisted.
- Ensure your PostgreSQL server is configured to accept remote connections.
- Check that your data table's date range aligns with or exceeds your export schedule to prevent data gaps.
- Confirm your PostgreSQL user has sufficient permissions to create and modify tables in the specified database.
- If a query returns an error such as `column "bing_timeperiod" does not exist` for a column you can see in the table, the name contains capital letters. Wrap it in double quotes (see [Table and column names are case-sensitive](#table-and-column-names-are-case-sensitive)).

> **Note:** Before November 2, 2026, exports of data tables whose date column has capital letters in its name (such as Microsoft Advertising and Pinterest Ads data tables) could run successfully the first time and then fail on later runs with an error such as `column "bing_timeperiod" does not exist`. This is fixed, and each run now refreshes your data table's date range as usual. If the export was failing for longer than your data table's date range covers, those dates weren't exported: set your data table's date range to cover them, select **Run export now**, then set your rolling date range back (see [Initial backfill](#initial-backfill)).

For any persistent issues, please contact our [support team](https://knowledge.powermyanalytics.com/support/submit-a-ticket/) for assistance.
