PostgreSQL Daily Activity

1)Daily check Excel for database details and mail checking ,
2)Check the status of last backup
3)Hand over the job:

• When one person leaves a job and another takes his place, it's helpful to have job handover guidelines in place to help with a smooth job transition period. 
• Ideally, the worker who is leaving should have time to discuss the incoming employee's new job guidelines and to give him some pointers to help make the transition easier. If this time period isn't possible, written documentation should be in place to make the transition period easier.

4) Daily do basic health check database servers
5) To check the alert log file for the below location.

$cd /db_data/data/pg_log
$tail -f  postgresql-Tue.log                                          

6) LINUX Top COMMAND :- 
          The Normal load is between  8 to 10.

7) To check long running queries:-

postgres=# select datname,pid,query_start,state,query from pg_stat_activity ;
postgres=# select datname,pid,query_start,state,query from pg_stat_activity where query like '%copy%';                                   

8) TO check that table whether having any index or not  and check the index status
--to checking table index:

postgres=# select relname,indexrelname,indexrelid from pg_stat_all_indexes where indexrelid=24700;
 relname | indexrelname | indexrelid 
---------+--------------+------------
 k       | production   |      24700 

--check the index is valid or not and check the index is alive or not

postgres=# select indexrelid,indislive,indisvalid from pg_index where indexrelid=24700;
 indexrelid | indislive | indisvalid 
------------+-----------+------------
      24700 | t         | t
(1 row)                                       

--Checking the tables dead tubles and analyze 

postgres=# select relname,last_vacuum,n_dead_tup,last_analyze 
from pg_stat_all_tables where relname='k';

 relname | last_vacuum | n_dead_tup | last_analyze 
---------+-------------+------------+--------------
 k       |             |       8192 |                                   

--analyze and vaccum the table

postgres=# analyze  k;     
ANALYZE
postgres=# vacuum k ;
VACUUM                                       

9) Complete database Full backup  status( Like RMAN):-

  20 20   * /db_data/dba/BaseBackupLive1.sh
          /db_backup3/base_backup/log
          TAR completed
          pg_stop_backup done successfully 
          total 933934680                                                            

10) To take the configuration table backup

      30 01   * /db_data/dba/nicdelsms_config.sh
           /db_backup/backup/config/                                                       
           To keep 7 days backup only
 
-rw-r--r-- 1 postgres postgres 24689184177 Jul 14 07:19 nicdelsms_db-13-07-2016.dmp.gz
-rw-r--r-- 1 postgres postgres       94023 Jul 15 07:32 nicdelsms_db-14-07-2016.log
-rw-r--r-- 1 postgres postgres 24709698844 Jul 15 07:32 nicdelsms_db-14-07-2016.dmp.gz
-rw-r--r-- 1 postgres postgres       94023 Jul 16 07:27 nicdelsms_db-15-07-2016.log
-rw-r--r-- 1 postgres postgres 24734385734 Jul 16 07:27 nicdelsms_db-15-07-2016.dmp.gz
-rw-r--r-- 1 postgres postgres       94023 Jul 17 07:22 nicdelsms_db-16-07-2016.log
-rw-r--r-- 1 postgres postgres 24753028394 Jul 17 07:22 nicdelsms_db-16-07-2016.dmp.gz
-rw-r--r-- 1 postgres postgres       94023 Jul 18 07:19 nicdelsms_db-17-07-2016.log
-rw-r--r-- 1 postgres postgres 24763924552 Jul 18 07:19 nicdelsms_db-17-07-2016.dmp.gz
-rw-r--r-- 1 postgres postgres       94023 Jul 19 07:25 nicdelsms_db-18-07-2016.log
-rw-r--r-- 1 postgres postgres 24782807003 Jul 19 07:25 nicdelsms_db-18-07-2016.dmp.gz

11) How to Kill  query:-

select pg_cancel_backend('PID');