PostgreSQL Scheduled Email Reports: A Step-by-Step Guide
How to set up automated PostgreSQL reports that run on a schedule and land in stakeholders' inboxes as formatted Excel files — without maintaining any infrastructure.
You have a PostgreSQL database. Someone needs a weekly report from it. They want an Excel file in their inbox every Monday morning. They don't want to log in anywhere. They just want the file.
This guide covers the practical ways to make that happen — including which approach is worth your time depending on your constraints.
What "scheduled PostgreSQL report" actually means
The workflow has three parts:
- Trigger: something decides it's time to run the report (a cron schedule)
- Execution: a PostgreSQL connection runs the SQL query and returns rows
- Delivery: the rows are formatted as Excel and sent to an email address
Simple in concept. Where complexity creeps in is in the plumbing between these three parts — handling connection errors, formatting the Excel file correctly, managing SMTP credentials, and keeping all of this running without attention.
Option A: pg_cron + a custom email script
PostgreSQL has a pg_cron extension that can schedule SQL functions to run on a cron schedule directly inside the database. You can write a function that runs the query and uses pg_sendmail (via a wrapper) to deliver results.
Pros: Everything lives in the database. No external dependencies for scheduling.
Cons: pg_cron is not available on all managed PostgreSQL providers. Formatting output as a proper Excel file inside PostgreSQL is painful. Email delivery from inside a database is fragile and limited. Not recommended for production reporting pipelines.
Option B: A Python script on a cron job
The most common DIY approach. A Python script uses psycopg2 to connect to PostgreSQL, runs the query, writes results to an Excel file with openpyxl or pandas + xlsxwriter, and sends it via smtplib or a transactional email API.
Basic structure
# Simplified structure
import psycopg2, openpyxl, smtplib
conn = psycopg2.connect(DATABASE_URL)
cursor = conn.cursor()
cursor.execute("SELECT ...")
# Write rows to Excel
# Send via SMTP
# Handle errors, retries, logging...
What it costs you: A few hours to build correctly. Then ongoing maintenance when dependencies update, SMTP credentials rotate, the PostgreSQL host moves, or column names in the query change. This script will need attention over its lifetime.
When to use it: When you need custom Excel formatting, multi-sheet workbooks, conditional send logic, or transformations that go beyond SQL.
Option C: A scheduled SQL report delivery tool
Tools like Query2Mail are purpose-built for this workflow. You connect your PostgreSQL database, write the SQL query, configure a schedule and recipient list, and the tool handles execution, Excel generation, and email delivery — on managed infrastructure.
What this looks like in practice
1. Connect your database. Provide the host, port, database name, username, and password. Use a read-only role — you only need SELECT access for reporting. Credentials are encrypted immediately.
2. Write your query. Any SELECT statement that runs in psql will work here. CTEs, window functions, JSON aggregation — whatever your query needs.
-- Weekly revenue by region
SELECT region,
SUM(revenue) AS total_revenue,
COUNT(*) AS order_count,
AVG(revenue) AS avg_order_value
FROM orders
WHERE created_at >= date_trunc('week', now())
GROUP BY region
ORDER BY total_revenue DESC;
3. Set a schedule. Daily at 7 AM, weekly on Mondays, monthly on the first. The tool runs it at the configured time regardless of whether you're around.
4. Add recipients. Email addresses of anyone who should receive the report. They receive an Excel file attachment. No login required, no account needed.
Security considerations for PostgreSQL connections
Before connecting any external tool to a production PostgreSQL database, create a dedicated read-only role:
CREATE ROLE reporting_user WITH LOGIN PASSWORD 'strong-password';
GRANT CONNECT ON DATABASE your_db TO reporting_user;
GRANT USAGE ON SCHEMA public TO reporting_user;
GRANT SELECT ON specific_table TO reporting_user;
Only grant SELECT access to the tables your report queries need. Use a dedicated reporting schema if possible. This limits the blast radius if credentials are ever compromised.
Which approach is right for your situation
Use a Python script if you need: custom Excel formatting (colours, merged cells, charts), complex business logic beyond SQL, multi-sheet workbooks, or conditional send logic.
Use a scheduled delivery tool if you need: a SELECT query formatted as Excel, delivered to an inbox on a schedule, with no infrastructure to maintain. This covers the majority of stakeholder reporting requests.
Try Query2Mail with your PostgreSQL database.
Connect in minutes. Write your query. Set a schedule. Excel lands in any inbox automatically. No infrastructure required.
Learn about the PostgreSQL integration →