Postgresql scripts

1:-TO base backup weekly :-


#!/bin/bash
#!/bin/bash
#######################################################################
# Name          : To Take Base Backup of weekly
# Usage         : Basebackup.sh
# Purpose       : output for Basebackup - Linux
# Author        : DineshKuamr Asokan
# Version       : 9.5
#
#######################################################################
FILES_PATH="/Backup/base_backup/"
dda=`date +%Y%m%d`

DATE=$(date +%G%m%d)
DATE2=$(date)
WALARCH="/Backup/base_backup/"
WALLOG="/Backup/base_backup/logs/Daily_$DATE.log"
touch $WALLOG
echo -e "::::::::::::::: $DATE ACTDB01  Backup :::::::::::::::" >> $WALLOG

echo $DATE2 >> $WALLOG

psql -U postgres -c "select pg_start_backup ('$DATE');"

cd  $FILES_PATH

tar -cvpzf "pg_backup_Act_D_$DATE.tar.gz" /pg_data/ >> $WALLOG 2>&1 &
tar -cvpzf "pgbackup_Act_I_$DATE.tar.gz" /pg_index/ >> $WALLOG 2>&1 &
tar -cvpzf "pgbackup_Act_Arch_$DATE.tar.gz" /pg_archive/ >> $WALLOG 2>&1 &

wait
echo "TAR completed" >> $WALLOG

psql -U postgres -c "select pg_stop_backup ();"
if [ $? -ne 0 ]
  then
  echo "PSQL pg_stop_backup failed"
  exit 1;
fi
echo "pg_stop_backup done successfully" >> $WALLOG

ls -la $WALARCH/ >> $WALLOG

echo $DATE2 >> $WALLOG

if [ $? -ne 0 ]
  then
  echo "Failed..." >> $WALLOG
  exit 1;
fi





2)TO Moniter client Conenction :-

#!/bin/bash
#######################################################################
# Name          : To Moniter Client Connectivity
# Usage         : Moniterclient.sh
# Purpose       : output for Moniter clinet connection - Linux
# Author        : DineshKuamr Asokan
# Version       : 9.5
#
#######################################################################
# PostgreSQL Client  Moniter


export PATH=/Post_install/9.5/bin:$PATH
export PGDATA=/pg_data/data
export PGDATABASE=actprod
export PGUSER=enterprisedb
export PGPORT=5432
export PGHOST=192.16.1.100

DATE=$(date +%G-%m-%d--%H-%M-%S)
CLIENT_INFO=/Pg_backup/prod/MONITER/$PGDATABASE_$DATE.log

psql \
    -X \
    -U $PGUSER \
    -p $PGPORT \
    -d $PGDATABASE \
    --set AUTOCOMMIT=on \
    --set ON_ERROR_STOP=on \
    -P format=unaligned \
    -P t \
    -q \
    -c "select count(*)*100/(select current_setting('max_connections')::int) as conn_ratio from pg_stat_activity"  >>$CLIENT_INFO




3.House Keeping scripts:-

#!/bin/bash
#######################################################################
# Name          : PostgreSQL House Keeping Activity
# Usage         : HouseKeeping.sh
# Purpose       : output for Housekeeping - Linux
# Author        : DineshKuamr Asokan
# Version       : 9.5
#
#######################################################################
########## PostgreSQL House Keeping Activity#######################


DATE=$(date +%G-%m-%d--%H-%M-%S)

PRO_PATH="/Backup/base_backup/"
PRO_LOG="/Backup/base_backup/logs/CLEAN-LOG_$DATE.log"

touch $CHECK_LOG
echo -e "####################### $DATE HouseKeeping  ############################" >> $CHECK_LOG

echo $DATE >> $CHECK_LOG
cd $PRO_PATH
find /db_data5/base_backup/*.tar.gz -mtime +3 -name 'pgback*.tar.gz' -exec rm {} \; >>$CHECK_LOG

echo $DATE >> $CHECK_LOG

echo "#########################COMPLETED#################################" >>$CHECK_LOG


4.Vacuum The Database in PostgreSQL

#!/bin/bash
#######################################################################
# Name          : PostgreSQL Vacuum Database
# Usage         : Vacuum.sh
# Purpose       : output for Vacuum - Linux
# Author        : DineshKuamr Asokan
# Version       : 9.6
#
#############################Argument Passing##########################################

DATABASENAME=$1

if [ -z $DATABASENAME ]; then
echo "script needs parameter: database name"
exit 1
fi

for tablename in $(psql -U upcload -d $DATABASENAME -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where not exists( select isparent from ( select ns.nspname||'.'||relname as isparent from pg_class c join pg_namespace ns on ns.oid=c.relnamespace where c.oid in ( select i.inhparent from pg_inherits i group by inhparent having count(*)>0) ) a where a.isparent=t.table_schema||'.'||t.table_name ) order by _table"); do
echo $tablename
psql -U upcload -d $DATABASENAME -c "vacuum full analyze verbose ${tablename};"
done


5: Daily Acessed Table Backup scripts:

#!/bin/bash
#######################################################################
# Name          : PostgreSQL Daily Acessed table Backup
# Usage         : Dailyacesstablebkp.sh
# Purpose       : output for dailytablebackup - Linux
# Author        : DineshKuamr Asokan
# Version       : 9.6
#

#######################################################################

export PATH=/Post_install/9.6/bin:$PATH

export PGDATA=/pg_data/data

export PGDATABASE=actprod

export PGUSER=enterprisedb

export PGPORT=5432

export PGHOST=182.168.111.45

PGDUMP_FILENAME="/Pg_backup/Prod/Actprod_db-`date --date="yesterday" +%d-%m-%Y-%H-%M-%S`".dmp

PGDUMP_LOGFILE="/pg_backup/prod/Actprod_db-`date --date="yesterday" +%d-%m-%Y`".log

pg_dump  --no-owner --no-tablespaces -T '(mbl_|dn_)*' -F c -b -v -f $PGDUMP_FILENAME   &>> $PGDUMP_LOGFILE

gzip $PGDUMP_FILENAME

gzip $PGDUMP_LOGFILE

exit