Aurora Postgres 11.8 I have to segregate pg_stat_statements on an hourly basis on Reader instance. On Writer instance it's simple, create a table and backup pg_stat_statements on an hourly basis and then clean up pg_stat_statements using pg_stat_statements_reset().
Since I can't create a table on Reader so need experts remarks for that, One thought is to create FDW table on Writer and copy data on an hourly basis but how to sync pg_stat_statements_reset() along with that on Reader? not look like a robust solution to have two separate cronjobs for copy and pg_stat_statements_reset().
Being from Oracle background I am tunned up for hourly ADDM reports. pg_stat_statements is found to be a somewhat satisfactory alternate to ADDM. I want to divide pg_stat_statements on an hourly basis to get a more in-depth analysis since a single summary of all busy and off-hours is less meaningful.
Thanks
Thete are many possible soutions, choose one based on simplicity and how well it suits your setup.
My idea would be not to run pg_stat_statements_reset() at all, but calculate the difference between subsequent snapshots of the data. That saves you the trouble of synchronizing the two activities.
For the snapshot, my idea would be a (possibly NFS-mounted) directory on the standby into which you write a hourly snapshot with COPY, scheduled by a cron job.