Unveiling Differences: A Deep Dive into Comparing Tables, Indexes, and Functions Across PostgreSQL Databases
Dmitry Romanoff
Posted on April 5, 2024
Comparison of structures between PostgreSQL databases ensures efficiency, stability, and security of data operations, as well as contributes to a smoother development and change implementation process.
One example where there can be differences in the structure of tables, indexes, and functions between two PostgreSQL databases is the process of migrating from one database version to a newer one or changing the application architecture.
During such migration or reorganization, one database may be updated by adding new tables, altering the structure of existing tables (for instance, adding or removing columns), creating new indexes to optimize query performance, and adding or modifying user-defined functions for data processing.
These changes make the structure of the new database different from the original, which can affect performance, security, and the overall functionality of the system.
Therefore, before fully transitioning to the updated database, it is necessary to carefully compare the structures to ensure compatibility and the correct operation of all system components.
The following bash script compares the structure of tables, indexes, and functions of two PostgreSQL databases.
#!/bin/bash
##################################################################################################################################
#
# Name: Compare the structure of tables, indexes, and functions of two PostgreSQL databases.
#
# Description: Compare two Postgres DBs: Tables, Indexes, and Functions.
# In case no matching print out the details.
#
# Author: Dmitry
#
# Date Created: 14-Mar-2024
#
# Usage Example:
#
# ./compare_2_dbs_postgres.sh -h localhost -p 5432 -d some_db_1 -l user_db_1 -g localhost -q 5432 -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 5432 -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 " "
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 Two 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 " "
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 lower(table_name), lower(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 lower(table_name), ordinal_position;
EOF
)
export PGPASSWORD="${inpDestPwd}"
data_set_22=$(psql -h $inpHost2 -p $inpPort2 -U $inpUser2 -d $inpDB2 -qAtX -F ' ' << EOF
SELECT lower(table_name), lower(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 lower(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 " "
echo "Not matching tables column_names, data_types, defaults"
diff ${temp_file_22} ${temp_file_11}
chk=`diff ${temp_file_22} ${temp_file_11} | wc -l`
if [ $chk -ne 0 ]
then
echo "Error! Not matching tables columns, types, defaults!"
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 " "
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_%'
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_%'
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 " "
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 " "
####################################################################################################################################
#
# The End
#
####################################################################################################################################
💖 💪 🙅 🚩
Dmitry Romanoff
Posted on April 5, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.