Guide
PostgreSQL cannot send email and pg_cron cannot attach files. Schedule a query, write a CSV with COPY or psql \copy, and mail it, also on RDS.
PostgreSQL has excellent tools for producing a CSV and no tools for sending one. That split explains most of the answers found online: half of them solve the export, half of them solve the email, and the glue in between is a shell script on whatever machine happened to be available.
There are two commands, and the difference between them is where the file ends up.
COPY runs on the server and writes to the server's filesystem:
COPY (
SELECT order_id, customer_name, total, created_at
FROM orders
WHERE status = 'open'
) TO '/var/exports/open_orders.csv' WITH (FORMAT csv, HEADER);
It needs superuser or membership in pg_write_server_files, and the file belongs to the database server's operating system user. On a managed service there is no filesystem to write to at all.
\copy is a psql meta-command that runs the same query but streams the rows to the client, so the file lands on the machine running psql and needs no special role:
psql "host=db.internal dbname=sales user=reporting" \
-c "\copy (SELECT order_id, customer_name, total, created_at FROM orders WHERE status = 'open') TO '/srv/exports/open_orders.csv' WITH (FORMAT csv, HEADER)"
Both produce real CSV with quoting and a header row. For anything scheduled, \copy from a separate host is almost always the right one.
pg_cron is often suggested because it schedules SQL inside the database. It is great at running a refresh or a cleanup on a timetable. It runs SQL statements and nothing else, so it can call COPY ... TO a server path if the role allows it, but it cannot run psql, cannot reach a mail server and cannot attach anything. On RDS, Cloud SQL and Azure Database for PostgreSQL, where pg_cron is available, COPY to a server file is not, so the combination produces nothing a person can open.
On a Linux host, cron plus a mail client is the classic setup:
# /etc/cron.d/open-orders
0 7 * * 1-5 reports /srv/exports/open_orders.sh
#!/bin/sh
set -e
f=/srv/exports/open_orders_$(date +%F).csv
psql "service=sales_reporting" -c "\copy (SELECT ...) TO '$f' WITH (FORMAT csv, HEADER)"
echo "Open orders attached." | mutt -s "Open orders $(date +%F)" -a "$f" -- finance@example.com
A .pgpass file or a pg_service.conf entry keeps the password out of the script. Mail needs a working relay or an authenticated SMTP setup for mutt or msmtp, which is often the hardest part in a company where outbound SMTP is locked down.
On Amazon RDS and Aurora, aws_s3.query_export_to_s3 writes a query result to an S3 bucket, and a Lambda or an S3 event can mail a link. Cloud SQL can export to Cloud Storage through its API. Each provider has its own path, and in every case the email is a second system wired to the first.
A cron job, a script, a .pgpass, a mail relay configuration and a CSV folder on a host that someone has to patch. It works, and the people who built it can change it. The query itself is invisible to anyone who does not have a shell on that host, and a failure lands in a local mailbox or a log file that no one reads.
PostgreSQL scheduled exports in Taavik run through an agent inside the network that reaches the database the way psql would and only connects outbound to the workspace. The query lives in a shared library the team can read and version, with {{variables}} for the parts that change. The schedule can be daily, weekly, monthly, on an interval or on cron with the Pro plan, each in its own time zone, and the output is CSV or Excel, delivered to an email list or a Slack channel, optionally only when rows came back.
It works the same way against RDS, Aurora, Cloud SQL, Azure Database for PostgreSQL and a self-hosted server, because the agent only needs a network path and a read-only login.
The file is encrypted on the agent before it is uploaded, and recipients open it through a link with a passphrase rather than an attachment. That is one extra step for the reader, and in return the extract does not live forever in mailboxes. The free plan covers one connection and one daily schedule.