Vacuum Analyze Full Schema in PostgreSQL Database

To analyze full database schema, you can use following shell script :

--------------------------------------------------------------------------------------------------------------------------
#!/bin/sh

rm -rf /tmp/vacuum_analyze_MyData.sql

dbname="mydata"
username="mydatadatamart"
namespace="mydatadatamart"

# Vacuum only those tables which are fragmented over 30%
# Analyze tables only if they are not already
# Process CMF and Groups Master table every time as they are master

Tables=`psql $dbname $username << EOF
SELECT 'VACUUM ANALYZE VERBOSE ' || tablename || ' ;' AS TableList
  FROM (
SELECT *,
      n_dead_tup > av_threshold AS "av_needed",
      CASE WHEN reltuples > 0
     THEN round(100.0 * n_dead_tup / (reltuples))
      ELSE 0
      END AS pct_dead
FROM (SELECT
C.relname AS TableName,
pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
CASE WHEN pg_stat_get_tuples_updated(C.oid) > 0
THEN pg_stat_get_tuples_hot_updated(C.oid)::real / pg_stat_get_tuples_updated(C.oid)
ELSE 0
 END AS HOT_update_ratio,
pg_stat_get_live_tuples(C.oid) AS n_live_tup,
pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
C.reltuples AS reltuples,
round( current_setting('autovacuum_vacuum_threshold')::integer + current_setting('autovacuum_vacuum_scale_factor')::numeric * C.reltuples) AS av_threshold,
date_trunc('minute',greatest(pg_stat_get_last_vacuum_time(C.oid),pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum,
date_trunc('minute',greatest(pg_stat_get_last_analyze_time(C.oid),pg_stat_get_last_analyze_time(C.oid))) AS last_analyze
FROM pg_class C
LEFT JOIN pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE C.relkind IN ('r', 't')
   AND N.nspname = '$namespace'
      ) AS av      
    ) AS final
    WHERE final.pct_dead >= 30
OR final.last_vacuum IS NULL
OR final.last_analyze IS NULL;
EOF`

echo $Tables >> /tmp/vacuum_analyze_MyData.sql
sed -i -r 's/[;]+/;\n/g' /tmp/vacuum_analyze_MyData.sql
sed -i '$ d' /tmp/vacuum_analyze_MyData.sql
sed -i -r 's/tablelist//g' /tmp/vacuum_analyze_MyData.sql
sed -i -r 's/[-]+//g' /tmp/vacuum_analyze_MyData.sql

logfile=$PGDATA'/pg_log/vacuum_analyze_MyData_'`date +"%Y-%m-%d_%H-%M-%S"`

echo 'Starting vacuum : '`date` > $logfile
STARTTIME=$(date +%s)
psql -U mydatadatamart -d mydata -f /tmp/vacuum_analyze_MyData.sql >> $logfile 2>&1
ENDTIME=$(date +%s)
ElapseT=`expr $ENDTIME - $STARTTIME`
echo 'Completed Vacuum : '`date`  >> $logfile
echo "It takes $ElapseT seconds to complete this task..." >> $logfile

--------------------------------------------------------------------------------------------------------------------------