How to Export to PostgreSQL
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 report in Data Explorer, 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 in the Enterprise plan or the Exports Addon.
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 Explorer report you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Explorer report 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 report in Data Explorer 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 report in Data Explorer to remove an existing field, this field's corresponding column will be dropped from the table in PostgreSQL the next time the export runs.
Be careful: when dropping a column, historical data for the dropped fields will be deleted.
Requirements
- An Enterprise subscription, or the Exports Addon
- Create the Data Explorer report(s) 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, 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:
- 35.188.118.242/32
- 35.209.185.69/32
For most PostgreSQL servers, you'll need to:
- Configure your PostgreSQL server's pg_hba.conf file to allow connections from these IP addresses.
- Ensure your postgresql.conf file has listen_addresses set to allow external connections.
- 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 report in Data Explorer, 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 report.
Go to Exports > SQL and click the Create Export button.
Click the blue + to add a PostgreSQL destination.
For Type, select PostgreSQL.
Enter your PostgreSQL server's hostname, port, and your username and password.
Next, enter a nickname to use for your PostgreSQL server.
Then click the Connect button.
Under Destination, select your PostgreSQL server.
For database, enter the name of the database you want to use on your PostgreSQL server. A data table will be created with the same name as your Data Explorer report, with any spaces replaced by underscores (_). Then click Next.
For Report or Report Group, choose whether to report a single Data Explorer report or a group of reports.
Then choose the report(s) you want to export, and click Next.
Next, choose how frequently you want your data export to run. Since this export is being used for the initial backfill, and the export will be run manually in the next step, this can be set to monthly. You can select any day and hour.
Then click Add. Your new export will appear under Exports.
Click the play icon to run export now.
Your export will begin running, as indicated by the three dots icon. When the three dots change back to a play button icon, the export has finished running. You can confirm the export was successful by clicking on the paper icon to view logs.
Rolling Updates
Once your initial backfill has run, it's time to set up rolling updates for your report. This will keep your PostgreSQL export updated with the latest data from your report. Go to Data Explorer and find your report. Click the pencil icon to Edit Report.
Go to Date Range and choose a rolling window for updates to your report, 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. Find your export and click the pencil icon to Edit Export.
Go to Schedule and 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), day of the week (for weekly), and hour of the day (for monthly, weekly, and daily). Then click Update.
Important: Ensure your report's date range in Data Explorer is at least as long as the refresh period in the PostgreSQL export in order to avoid missing data. For example, if your report'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 report's date range in Data Explorer 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 report'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 report'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 clicking the paper icon next to your export.
- 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 report'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.
For any persistent issues, please contact our support team for assistance.
Still having issues?
Send us a ticket and we will get back to you.
Related Articles
How to Export to Azure SQL Database
Exporting your marketing data to Azure SQL allows you to integrate it with various applications and run complex data queries. This guide will walk you through the process of setting up and managing your Azure SQL exports using Power My Analytics. An ...How to Use the Exports Add-On
Power My Analytics offers the option to export data from your reports into destinations like BigQuery, MySQL, Azure SQL Database, PostgreSQL, and FTP. With our easy-to-use Exports Add-On, you can set up a report in PMA's Data Explorer and schedule ...How to Export to MySQL
Our MySQL export feature enables flexible access to your data in the form of tables in a MySQL database hosted on the server of your choice. Once you've prepared a report in Data Explorer, you're ready to export your data to your MySQL server. With ...Use the GA4 Exports Template in Data Explorer
Power My Analytics offers pre-made report templates in our Data Explorer for use with your Google Analytics 4 data. By using our GA4 report templates, you can configure your reports in just a few minutes and prepare your data for export to ...How to Create an FTP Export
FTP can be a valuable way for Enterprise users to export their Data Explorer reports. In this article, we will walk you through creating an FTP export, including selecting an FTP destination, scheduling data refreshes, and managing your exports to ...