[PostgreSQL] PostgreSQL 정합성 확인!!

Database를 이관을 하게 되면 이관 이후에 ASIS와 TOBE Database의 크기나 Object Count를 확인하는 절차를 거치게 된다. 아래 스크립트를 돌림으로써 옮겨지지 않은 Object들을 확인하고 옮겨지지 않은 것들이 있다면 재이관을 진행할 수 있다. 

 

PostgreSQL 정합성 스크립트

#!/bin/bash
user=postgres

tablecount=0
indexcount=0
viewcount=0
triggercount=0
procedurecount=0
functioncount=0


echo "*******************************************************"
echo "*                                                     *"
echo "*                  DATABASE  SIZE                     *"
echo "*                                                     *"
echo "*******************************************************"
PGCOMMAND=" psql -U $user -d postgres -At -c \"
           select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by 1
            \""
eval "$PGCOMMAND"


echo ""
echo ""
echo ""
echo "*******************************************************"
echo "*                                                     *"
echo "*            PROCEDURE COUNT                          *"
echo "*                                                     *"
echo "*******************************************************"
PGCOMMAND=" psql -U $user -d postgres -At -c \"
            SELECT   datname
            FROM    pg_database where datname not in ('template0','template1')
            \""


database=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for database in $database; do
  PGCOMMAND=" psql -U $user -d $database -At -c \"
       select current_database(),routine_schema,routine_type,count(*) from information_schema.routines where routine_type in ('PROCEDURE') and routine_schema='public' group by routine_schema,routine_type order by 1;
          \""
    eval "$PGCOMMAND"



var=$(psql -U $user -d $database -At -c "select current_database(),routine_schema,routine_type,count(*) from information_schema.routines where routine_type in ('PROCEDURE') and routine_schema='public' group by routine_schema,routine_type order by 1")
let procedurecount=procedurecount+var


done
echo "PROCEDURECOUNT: " ${procedurecount}




echo ""
echo ""
echo ""
echo "*******************************************************"
echo "*                                                     *"
echo "*            FUNCTION COUNT                           *"
echo "*                                                     *"
echo "*******************************************************"
PGCOMMAND=" psql -U $user -d postgres -At -c \"
            SELECT   datname
            FROM    pg_database where datname not in ('template0','template1')
            \""


database=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for database in $database; do
  PGCOMMAND=" psql -U $user -d $database -At -c \"
       select current_database(),routine_schema,routine_type,count(*) from information_schema.routines where routine_type in ('FUNCTION') and routine_schema='public' group by routine_schema,routine_type order by 1;
          \""
    eval "$PGCOMMAND"




var=$(psql -U $user -d $database -At -c "select current_database(),routine_schema,routine_type,count(*) from information_schema.routines where routine_type in ('FUNCTION') and routine_schema='public' group by routine_schema,routine_type order by 1")
let functioncount=functioncount+var


done
echo "FOUNCTIONCOUNT: " ${functioncount}




echo ""
echo ""
echo ""
echo "*******************************************************"
echo "*                                                     *"
echo "*                   TRIGGER COUNT                     *"
echo "*                                                     *"
echo "*******************************************************"
PGCOMMAND=" psql -U $user -d postgres -At -c \"
            SELECT   datname
            FROM    pg_database where datname not in ('template0','template1')
            \""


database=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for database in $database; do
  PGCOMMAND=" psql -U $user -d $database -At -c \"
          select current_database(),trigger_schema,count(*) from information_schema.triggers
      where trigger_schema='public' group by trigger_schema order by 1;
          \""
    eval "$PGCOMMAND"


var=$(psql -U $user -d $database -At -c "select current_database(),trigger_schema,count(*) from information_schema.triggers where trigger_schema='public' group by trigger_schema order by 1")
let triggercount=triggercount+var


done
echo "TRIGGERCOUNT: " ${triggercount}



echo ""
echo ""
echo ""
echo "*******************************************************"
echo "*                                                     *"
echo "*                   VIEW COUNT                        *"
echo "*                                                     *"
echo "*******************************************************"
PGCOMMAND=" psql -U $user -d postgres -At -c \"
            SELECT   datname
            FROM    pg_database where datname not in ('template0','template1')
            \""

database=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for database in $database; do
  PGCOMMAND=" psql -U $user -d $database -At -c \"
           select current_database(),table_schema,count(*) from information_schema.views
           where table_schema='public' group by table_schema order by 1
          \""
    eval "$PGCOMMAND"


var=$(psql -U $user -d $database -At -c "select current_database(),table_schema,count(*) from information_schema.views  where table_schema='public' group by table_schema order by 1")
let viewcount=viewcount+var


done
echo "VIEWCOUNT: " ${viewcount}


echo ""
echo ""
echo ""
echo "*******************************************************"
echo "*                                                     *"
echo "*                   INDEX COUNT                       *"
echo "*                                                     *"
echo "*******************************************************"
PGCOMMAND=" psql -U $user -d postgres -At -c \"
            SELECT   datname
            FROM    pg_database where datname not in ('template0','template1')
            \""


database=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for database in $database; do
  PGCOMMAND=" psql -U $user -d $database -At -c \"
          select current_database(),schemaname,count(*)
          from pg_indexes where schemaname='public' group by schemaname order by 1
          \""
    eval "$PGCOMMAND"


var=$(psql -U $user -d $database -At -c "select current_database(),schemaname,count(*) from pg_indexes where schemaname='public' group by schemaname order by 1")
let indexcount=indexcount+var


done
echo "INDEXCOUNT: " ${indexcount}


echo ""
echo ""
echo ""
echo "*******************************************************"
echo "*                                                     *"
echo "*                   Table count                       *"
echo "*                                                     *"
echo "*******************************************************"


PGCOMMAND=" psql -U $user -d postgres -At -c \"
            SELECT   datname
            FROM    pg_database where datname not in ('template0','template1')
            \""


database=$(export PGPASSWORD=test; eval "$PGCOMMAND")

for database in $database; do


    PGCOMMAND=" psql -U $user -d $database -At -c \"
                SELECT   current_database(),schemaname,
                         count(*)
                FROM     pg_tables where schemaname='public' group by schemaname order by 1
                \""
    eval "$PGCOMMAND"

var=$(psql -U $user -d $database -At -c "select count(*) FROM     pg_tables where schemaname='public' group by schemaname")
let tablecount=tablecount+var


done
echo "TABLECOUNT: " ${tablecount}



echo ""
echo ""
echo ""
echo "*******************************************************"
echo "*                                                     *"
echo "*                   ROW COUNT                         *"
echo "*                                                     *"
echo "*******************************************************"


PGCOMMAND=" psql -U $user -d postgres -At -c \"
            SELECT   datname
            FROM    pg_database where datname not in ('template0','template1')
            \""


database=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for database in $database; do


PGCOMMAND=" psql -U $user -d $database -At -c \"
            SELECT   table_name
            FROM     information_schema.tables
            WHERE    table_type='BASE TABLE'
            AND      table_schema='public'
            \""


TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for TABLENAME in $TABLENAMES; do
    PGCOMMAND=" psql -U $user -d $database -At -c \"
                SELECT   current_database(),'$TABLENAME',
                         count(*)
                FROM     $TABLENAME order by 1
                \""
    eval "$PGCOMMAND"


done


done