Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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                                          
          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;     
postgres=# vacuum k ;
9) Complete database Full backup  status( Like RMAN):-
  20 20   * /db_data/dba/BaseBackupLive1.sh
          TAR completed
          pg_stop_backup done successfully 
          total 933934680                                                            
10) To take the configuration table backup
      30 01   * /db_data/dba/nicdelsms_config.sh
           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');                                                       



Popular posts from this blog

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL pgBadger

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Migrating From Oracle to PostgreSQL using ora2pg open source tools

PostgreSQL Pgbadger Installation On Linux