Simple PostgreSQL vacuum script

In this tutorial i will explained about how to find out vacuum size of database level and how to write script for vacuum and analyze in postgreSQL open source database.

Ans:
1.Find out the vacuum level 

\c chennai
select count(*) from pg_stat_all_tables where n_dead_tup > 1000;

\c mumbai
select count(*) from pg_stat_all_tables where n_dead_tup > 1000;

\c kolkatta
select count(*) from pg_stat_all_tables where n_dead_tup > 1000;

\c banglore
select count(*) from pg_stat_all_tables where n_dead_tup > 1000;

\c newyork
select count(*) from pg_stat_all_tables where n_dead_tup > 1000;

select schemaname,relname,n_dead_tup,last_vacuum,last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;

2.Script for Analyze and vacuum the postgresql dead tubles

 vi /home/script/vaccum_database.sh

#!/bin/sh
# The script sets environment variables helpful for PostgreSQL

export PATH=/opt/PostgreSQL/9.6/bin:$PATH
export PGDATA=/data/emut_96/
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/opt/PostgreSQL/9.6/share/locale
export MANPATH=$MANPATH:/opt/PostgreSQL/9.6/share/man


vacuumdb --analyze chennai
vacuumdb --analyze mumbai
vacuumdb --analyze kolkatta
vacuumdb --analyze banglore
vacuumdb --analyze newyork

3.vacuum analyze sheduling in crontab on  everyday of 10.pm

befere scheduling crontjob check the current date.
$ date

crontab -e
0 22 * * * sh /home/script/vaccum_database.sh