How to build monthly reports from a PostgreSQL database

Before you start

• Agreement with the data owner. Which tables the report reads, how each number is defined, and who approves the access.
• Connection details and SSL. Host, port, database name and the SSL setup. PostgreSQL’s docs recommend sslmode=verify-full for security-sensitive connections. require encrypts the connection but doesn’t check the server’s identity.
• The right date column. Know which column marks when each result happened, whether it’s a timestamptz or a plain timestamp, and the time zone the business reports in.

Build the report query

1. Create a read-only reporting role

   Ask the database owner to create a login that can only read the tables the report needs:

   CREATE ROLE reporting LOGIN PASSWORD 'choose-a-long-password';
   GRANT USAGE ON SCHEMA public TO reporting;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public
     GRANT SELECT ON TABLES TO reporting;

   GRANT ... ON ALL TABLES covers tables that exist now. ALTER DEFAULT PRIVILEGES covers tables created later by the role that runs it, so run it as the role that owns the tables. On version 14 or later, pg_read_all_data grants read access to everything, which is usually more than a report needs.

2. Limit what the role can run

   ALTER ROLE reporting SET statement_timeout = '30s';
   ALTER ROLE reporting SET default_transaction_read_only = on;

   Both take effect at the role’s next login. A user can switch read-only off for a transaction, so the grants above are the real protection; the setting is a safety net.

3. Total the month in the business’s time zone

   Group by month with the time zone the client reports in, and count distinct records:

   SELECT date_trunc('month', created_at AT TIME ZONE 'America/Chicago')::date
            AS month,
          count(DISTINCT id) AS paid_orders,
          sum(total) AS revenue
   FROM orders
   WHERE status = 'paid'
     AND created_at >= '2026-10-01 00:00 America/Chicago'
     AND created_at <  '2026-11-01 00:00 America/Chicago'
   GROUP BY 1;

   Without AT TIME ZONE, date_trunc on a timestamptz column groups by the session’s time zone, which is often UTC. If the column is a plain timestamp, find out which zone it was saved in first.

4. Save it as a view

   Save the query as a view so every report uses the same definition, and grant the reporting role SELECT on it. A view runs its query each time it’s read. A materialized view stores the result until you run REFRESH MATERIALIZED VIEW; refreshing with CONCURRENTLY needs a unique index on the view.

5. Export the result

   From psql, the client-side \copy writes a CSV to your own machine without superuser rights. The whole command has to fit on one line:

   \copy (SELECT * FROM monthly_results) TO 'october.csv' WITH (FORMAT csv, HEADER)

   The server-side COPY ... TO a file needs superuser or the pg_write_server_files role, so use \copy.

What to include each month

Numbers from a database carry more weight than estimates, so define them carefully. A monthly report from PostgreSQL should show these six things.

1. What each number countsOne line per metric, agreed with the client: “Paid orders, excluding test orders and full refunds”, not just “Orders”.
2. The month’s totalsEach metric for the reporting month, totalled in the client’s time zone.
3. The comparisonLast month, and the same month last year for a seasonal business.
4. A breakdown that explains the changeBy service, location or channel, whichever the client makes decisions on.
5. The marketing work beside itCampaigns, pages and changes made that month, so the business result sits next to the activity.
6. A source noteWhich database and table the figures come from and when they were read, so later changes can be explained.

Mistakes to avoid

Grouping by month in the wrong time zone

A timestamptz column grouped in UTC moves late-evening orders into the next day, and month-end orders into the next month. Group in the business’s own time zone.

Counting rows after a join

Joining orders to order items or payments repeats each order. Count DISTINCT order ids, or total before you join.

Reporting from the main database at busy times

A heavy query can slow the client’s live system. Use a read replica if there is one, set a statement timeout on the reporting role, and run reports outside peak hours.

Questions

Sources

Menus, plans and limits change. These are the pages this guide was checked against on 10 November 2026.

• PostgreSQL: Privileges
• PostgreSQL: GRANT
• PostgreSQL: ALTER DEFAULT PRIVILEGES
• PostgreSQL: Predefined roles
• PostgreSQL: Date/time functions (date_trunc)
• PostgreSQL: REFRESH MATERIALIZED VIEW
• PostgreSQL: psql (\copy)
• PostgreSQL: Client connection defaults
• PostgreSQL: SSL support in libpq