Dmitry RomanoffThe 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 " "
