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                                          
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');                                                       

     
   

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

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

vacuumlo - removing large objects orphans from a database PostgreSQL