How to Export to PostgreSQL

How to Export to PostgreSQL

Info
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, 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 when your negotiated agreement includes data exports.

How We Store Data in PostgreSQL Exports

Creating a table

Our PostgreSQL export feature will create a table with the same name as the Data Builder data table you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Builder data table being exported.

Appending and updating rows

Unlike BigQuery exports, exports to PostgreSQL are not partitioned by date. Instead, new data from the export is simply appended to the table, while any changed data will be updated in the affected rows.

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.
Alert
Be careful: when dropping a column, historical data for the dropped fields will be deleted.

Requirements

  1. A plan that includes data exports (available as a paid add-on on the Business, Grow, and Pro plans; included with the Custom plan when your agreement includes data exports)
    1. Create the Data Builder data table(s) you want to export
    2. A PostgreSQL server with at least one database
    3. Your PostgreSQL credentials:
      1. Hostname or public IP address of your server
      2. Port (default: 5432)
      3. Username
      4. Password
      5. 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, Amazon RDS, or Azure Database for 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:
    1. 35.188.118.242/32
    2. 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, 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.



    Click Save to save the data table.



    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.
    Info
    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. A table will be created with the same name as your Data Builder data table, with any spaces replaced by underscores (_). 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. 



    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.



    Then click Save.



    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.
    Alert
    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:
    1. 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.
    2. 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:

    1. Check the export logs by opening the three-dot action menu next to your export and selecting View logs.
    2. Verify that your PostgreSQL credentials are correct and that the PMA IP addresses are whitelisted.
    3. Ensure your PostgreSQL server is configured to accept remote connections.
    4. Check that your data table's date range aligns with or exceeds your export schedule to prevent data gaps.
    5. Confirm your PostgreSQL user has sufficient permissions to create and modify tables in the specified database.
    For any persistent issues, please contact our support team for assistance.