• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Blog

      • Home
      • Blog
      • Blog
      • Script To Listing Postgresql dead tubles

      Script To Listing Postgresql dead tubles

      • Posted by 2ndnijam
      • Categories Blog
      • Date January 4, 2019
      • Comments 0 comment

      In this Shell script will displays schema name and table name in which table having more than dead tubles in a postgresql server

      How to create & run shell script file ?
      create  a shell script file here created tables_dead_tubles.sh using vim editor and change the environment variable as well as db name as per your environment, i – for inserting mode then paste the following script then pressEsc button then enter :wq for saving script then run the script using sh tables_dead_tubles.sh

      cat tables_dead_tubles.sh
      echo " "
      echo " "
      HOSTNAME=`hostname`
      PSQL="/opt/PostgreSQL/9.3/bin/psql"
      PORT=5432
      HOST="localhost"
      DB="template1"
      USER="postgres"
      echo "Enter the dead tuble count for example if you give 5000 means the script will dispays the schema name as well as table name How many table having more than 5000 dead tubles in database ";
      read count
      echo "------***WHAT ARE TABLES HAVING MORE THAN $count DEAD TUBLES  PARTICULAR DATABASE***------"
      
      echo " "
      
      $PSQL -d $DB -U $USER -p $PORT <<EOF
      \c Your_database_name
      select schemaname,relname from pg_stat_all_tables where n_dead_tup > $count;
      EOF
      
      echo " "
      # skip following 4 lines (untill EOF) if your postgresql server having single database
      
      $PSQL -d $DB -U $USER -p $PORT <<EOF
      \c your_2nd_db_name
      select schemaname,relname from pg_stat_all_tables where n_dead_tup > $count;
      EOF
      
      echo " "

      After running this script for example If you get 100 of table name in which table having more than 5000 dead tubles
      then schedule vacuum for set of tables table using crontjob or schedule autovacuum for that particular tables then only you will get good performance of your postgresql server,This is one of the most important concept in postgresql tuning

      • Share:
      2ndnijam

      Previous post

      PostgreSQL Backup & Restore
      January 4, 2019

      Next post

      Script To Take The PostgreSQL Physical Backup
      January 4, 2019

      Leave A Reply Cancel reply

      You must be logged in to post a comment.

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


      Search

      ADVERTISEMENT

      Latest Posts

      PostgreSQL Patching version 9, 10,11
      10Oct2019
      Tools for PostgreSQL
      16Sep2019
      Postgres user creation and restrict DDL & database access
      13Sep2019
      PostgreSQL SSL Setup
      07Sep2019
      How to DELETE current XLOG / WAL LOG in postgresql database ?
      19Aug2019

      Latest Courses

      PostgreSQL Database

      PostgreSQL Database

      $600.00 $500.00
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • thought behind whiteboard activity
      • Are you going to take your first ste
      • How to start working on an application?
      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now