How to compare two PostgreSQL databases to find differences in tables, indexes, functions?
Tags: postgres, database, devops, programming
Published: 2023-03-15 12:38:51

The following script compares two PostgreSQL databases to find differences in tables, indexes, functions?
```
#!/bin/bash

##################################################################################################################################
#
# Name: Compare 2 Postgres DBs
#
# Description: Compare 2 Postgres DBs Tables, Indexes, and Functions. In case no matching, print out the details.
#
# Author: Dmitry
#
# Date Created: 26-Dec-2020
#
# Usage Example: 
#
#     ./compare_2_dbs_postgres.sh -h localhost -p 5432 -d some_db_1 -l user_db_1 -g localhost -q 5433 -e some_db_2 -m user_db_2 -w pwdSrc -z pwdDest
#     ./compare_2_dbs_postgres.sh -h localhost -p 5432 -d some_db_1 -l user_db_1 -g localhost -q 5433 -e some_db_2 -m user_db_2 -w pwdSrc -z pwdDest -v
#
####################################################################################################################################


helpFunction()
{
   echo ""
   echo "Usage: $0 -h hostname1 -p port1 -d dbname1 -l dbuser1 -g hostname2 -q port2 -e dbname2 -m dbuser2 -w srcPwd -z destPwd"
   echo -e "\t-h Postgres hostname1"
   echo -e "\t-p Postgers port1"
   echo -e "\t-d Postgres db1 to compare"
   echo -e "\t-l Postgres dbuser1"
   echo -e "\t-g Postgres hostname2"
   echo -e "\t-q Postgers port2"
   echo -e "\t-e Postgres db2 to compare"
   echo -e "\t-m Postgres dbuser2"
   echo -e "\t-w Postgres SrcDB pwd"
   echo -e "\t-z Postgres DestDB pwd"
   echo -e "\t-v Verbose"
   exit 1 # Exit script after printing help
}

echo " --- start of compare 2 DBs script ---"
echo " "

inpVerbose=0

while getopts "h:p:d:l:g:q:e:m:w:z:v" opt
do
   case "$opt" in
      h ) inpHost1="$OPTARG" ;;
      p ) inpPort1="$OPTARG" ;;
      d ) inpDB1="$OPTARG" ;;
      l ) inpUser1="$OPTARG" ;;
      g ) inpHost2="$OPTARG" ;;
      q ) inpPort2="$OPTARG" ;;
      e ) inpDB2="$OPTARG" ;;
      m ) inpUser2="$OPTARG" ;;
      w ) inpSrcPwd="$OPTARG" ;;
      z ) inpDestPwd="$OPTARG" ;;
      v ) inpVerbose=1 ;;
      ? ) helpFunction ;; # Print helpFunction in case parameter is non-existent
   esac
done

# Print helpFunction in case parameters are empty
if [ -z "$inpHost1" ] || [ -z "$inpPort1" ] || [ -z "$inpDB1" ] || [ -z "$inpHost2" ] || [ -z "$inpPort2" ] || [ -z "$inpDB2" ] || [ -z "$inpSrcPwd" ] || [ -z "$inpDestPwd" ] || [ -z "$inpUser1" ] || [ -z "$inpUser2" ]
then
   echo "Some or all of the parameters are empty";
   helpFunction
fi

# Begin script in case all parameters are correct

echo " "
echo "Compare 2 Postgres DBs"
echo " "
echo "DB1"
echo "inpHost1=$inpHost1"
echo "inpPort1=$inpPort1"
echo "inpDB1=$inpDB1"
echo "inpUser1=$inpUser1"
echo " "
echo "DB2"
echo "inpHost2=$inpHost2"
echo "inpPort2=$inpPort2"
echo "inpDB2=$inpDB2"
echo "inpUser2=$inpUser2"
echo " "
echo "inpSrcPwd=*************"
echo "inpDestPwd=*************"
echo " "

#
# Compare Tables
#

echo "Compare Tables"

export PGPASSWORD="${inpSrcPwd}"
data_set_1=$(psql -h $inpHost1 -p $inpPort1 -U $inpUser1 -d $inpDB1 -t << EOF
select 
       n.nspname as table_schema,
       c.relname as table_name
 from pg_class c
 join pg_namespace n on n.oid = c.relnamespace
 where c.relkind = 'r'
       and n.nspname not in ('information_schema','pg_catalog')
 order by 2;
EOF
)

export PGPASSWORD="${inpDestPwd}"
data_set_2=$(psql -h $inpHost2 -p $inpPort2 -U $inpUser2 -d $inpDB2 -t << EOF
 select 
        n.nspname as table_schema,
        c.relname as table_name
 from pg_class c
 join pg_namespace n on n.oid = c.relnamespace
 where c.relkind = 'r'
       and n.nspname not in ('information_schema','pg_catalog')
 order by 2;
EOF
)

temp_file_1="/tmp/tmp_tables_db1.tmp"
temp_file_2="/tmp/tmp_tables_db2.tmp"

echo "$data_set_1" > ${temp_file_1}
echo "$data_set_2" > ${temp_file_2}

if [ $inpVerbose -eq 1 ]
then
  echo " "
  echo "Tables in the DB ${inpDB1} on host ${inpHost1}"
  cat ${temp_file_1}
  echo " "
  echo "Tables in the DB ${inpDB2} on host ${inpHost2}"
  cat ${temp_file_2}
  echo " "
fi

echo "Not matching tables:"

echo " "
echo "Exists in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
grep -vf ${temp_file_2} ${temp_file_1}

chk=`grep -vf ${temp_file_2} ${temp_file_1}`

if [ ${#chk} -ne 0 ]
then
    echo "Error! Not matching tables! Exists in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
    exit 1
fi

echo " "

echo "Exists in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
grep -vf ${temp_file_1} ${temp_file_2}

chk=`grep -vf ${temp_file_1} ${temp_file_2}`

if [ ${#chk} -ne 0 ]
then
    echo "Error! Not matching tables! Exists in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
    exit 1
fi

echo " "

#
# Compare Tables Columns, Types, Defaults
#

echo "Compare Tables Columns, Types, Defaults"

export PGPASSWORD="${inpSrcPwd}"
data_set_11=$(psql -h $inpHost1 -p $inpPort1 -U $inpUser1 -d $inpDB1 -qAtX -F ' ' << EOF
SELECT table_name, column_name, data_type, is_nullable, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_schema = 'public' and table_name != 'pg_stat_statements'
ORDER BY table_name, ordinal_position;
EOF
)

export PGPASSWORD="${inpDestPwd}"
data_set_22=$(psql -h $inpHost2 -p $inpPort2 -U $inpUser2 -d $inpDB2 -qAtX -F ' ' << EOF
SELECT table_name, column_name, data_type, is_nullable, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_schema = 'public' and table_name != 'pg_stat_statements'
ORDER BY table_name, ordinal_position;
EOF
)

temp_file_11="/tmp/tmp_tables_db11.tmp"
temp_file_22="/tmp/tmp_tables_db22.tmp"

echo "$data_set_11" > ${temp_file_11}
echo "$data_set_22" > ${temp_file_22}

if [ $inpVerbose -eq 1 ]
then
  echo " "
  echo "Tables Columns, Types, Defaults in the DB ${inpDB1} on host ${inpHost1}"
  cat ${temp_file_11}
  echo " "
  echo "Tables Columns, Types, Defaults in the DB ${inpDB2} on host ${inpHost2}"
  cat ${temp_file_22}
  echo " "
fi

echo "Not matching tables column_names, data_types, defaults"

echo " "
echo "Exists column, type, default in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
grep -vf ${temp_file_22} ${temp_file_11}

chk=`grep -vf ${temp_file_22} ${temp_file_11}`

if [ ${#chk} -ne 0 ]
then
    echo "Error! Not matching tables columns, types, defaults! Exists in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
    exit 1
fi

echo " "

echo "Exists column, type, default in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
grep -vf ${temp_file_11} ${temp_file_22}

chk=`grep -vf ${temp_file_11} ${temp_file_22}`

if [ ${#chk} -ne 0 ]
then
    echo "Error! Not matching tables columns, types, defaults! Exists in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
    exit 1
fi

echo " "

#
# Compare Indexes
#

echo "Compare Indexes"

export PGPASSWORD="${inpSrcPwd}"
data_set_3=$(psql -h $inpHost1 -p $inpPort1 -U $inpUser1 -d $inpDB1 -t << EOF
SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
WHERE c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
     n.nspname
    ,t.relname
    ,c.relname
EOF
)

export PGPASSWORD="${inpDestPwd}"
data_set_4=$(psql -h $inpHost2 -p $inpPort2 -U $inpUser2 -d $inpDB2 -t << EOF
SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
WHERE c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
     n.nspname
    ,t.relname
    ,c.relname
EOF
)

temp_file_3="/tmp/tmp_indexes_db1.tmp"
temp_file_4="/tmp/tmp_indexes_db2.tmp"

echo "$data_set_3" > ${temp_file_3}
echo "$data_set_4" > ${temp_file_4}

if [ $inpVerbose -eq 1 ]
then
  echo " "
  echo "Indexes in the DB ${inpDB1} on host ${inpHost1}"
  cat ${temp_file_3}
  echo " "
  echo "Indexes in the DB ${inpDB2} on host ${inpHost2}"
  cat ${temp_file_4}
  echo " "
fi

echo "Not matching indexes:"

echo " "
echo "Exists in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
grep -vf ${temp_file_4} ${temp_file_3}

chk=`grep -vf ${temp_file_4} ${temp_file_3}`

if [ ${#chk} -ne 0 ] 
then
    echo "Error! Not matching indexes! Exists in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
    exit 1
fi

echo " "

echo "Exists in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
grep -vf ${temp_file_3} ${temp_file_4}

chk=`grep -vf ${temp_file_3} ${temp_file_4}`

if [ ${#chk} -ne 0 ] 
then
    echo "Error! Not matching indexes! Exists in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
    exit 1
fi

#
# Compare Functions
#

echo "Compare Functions"

export PGPASSWORD="${inpSrcPwd}"
data_set_111=$(psql -h $inpHost1 -p $inpPort1 -U $inpUser1 -d $inpDB1 -t << EOF
select n.nspname as function_schema,
       p.proname as function_name,
       l.lanname as function_language,
       case when l.lanname = 'internal' then p.prosrc
            else pg_get_functiondef(p.oid)
            end as definition,
       pg_get_function_arguments(p.oid) as function_arguments,
       t.typname as return_type
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype
where n.nspname not in ('pg_catalog', 'information_schema') and p.proname not like '%pg_stat_statements%'
order by function_schema,
         function_name;
EOF
)

export PGPASSWORD="${inpDestPwd}"
data_set_222=$(psql -h $inpHost2 -p $inpPort2 -U $inpUser2 -d $inpDB2 -t << EOF
select n.nspname as function_schema,
       p.proname as function_name,
       l.lanname as function_language,
       case when l.lanname = 'internal' then p.prosrc
            else pg_get_functiondef(p.oid)
            end as definition,
       pg_get_function_arguments(p.oid) as function_arguments,
       t.typname as return_type
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype
where n.nspname not in ('pg_catalog', 'information_schema') and p.proname not like '%pg_stat_statements%'
order by function_schema,
         function_name;
EOF
)

temp_file_111="/tmp/tmp_tables_func1.tmp"
temp_file_222="/tmp/tmp_tables_func2.tmp"

echo "$data_set_111" > ${temp_file_111}
echo "$data_set_222" > ${temp_file_222}

if [ $inpVerbose -eq 1 ]
then
  echo " "
  echo "Functions in the DB ${inpDB1} on host ${inpHost1}"
  cat ${temp_file_111}
  echo " "
  echo "Functions in the DB ${inpDB2} on host ${inpHost2}"
  cat ${temp_file_222}
  echo " "
fi

echo "Not matching functions:"

echo " "
echo "Exists in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
diff ${temp_file_222} ${temp_file_111}

chk=`diff ${temp_file_222} ${temp_file_111}`

if [ ${#chk} -ne 0 ]
then
    echo "Error! Not matching functions! Exists in the DB ${inpDB1} on host ${inpHost1} and DOES NOT exist in the DB ${inpDB2} on host ${inpHost2}"
    exit 1
fi

echo " "

echo "Exists in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
diff ${temp_file_111} ${temp_file_222}

chk=`diff ${temp_file_111} ${temp_file_222}`

if [ ${#chk} -ne 0 ]
then
    echo "Error! Not matching functions! Exists in the DB ${inpDB2} on host ${inpHost2} and DOES NOT exist in the DB ${inpDB1} on host ${inpHost1}"
    exit 1
fi

echo " "

echo " --- end of compare 2 DBs script ---"

echo " "
```