PostgreSQL High Availability Cookbook - Second Edition

How to do it...

First, follow these steps to create a static table that stores the top 20 active tables and indexes:

1. Execute the following query as a superuser and ignore any errors:

        DROP TABLE IF EXISTS active_snap; 

2. Next, recreate the snapshot table by running this query as a superuser:

 CREATE TABLE active_snap AS (SELECT t.relid AS objrelid, s.setting || '/' || pg_relation_filepath(t.relid) AS file_path FROM pg_stat_user_tables t, pg_settings s WHERE s.name = 'data_directory' ORDER BY coalesce(idx_scan, 0) DESC LIMIT 20) UNION (SELECT t.indexrelid AS objrelid, s.setting || '/' || pg_relation_filepath(t.indexrelid) AS file_path FROM pg_stat_user_indexes t, pg_settings s WHERE s.name = 'data_directory' ORDER BY coalesce(idx_scan, 0) ...