Skip to content

Instantly share code, notes, and snippets.

@matthias-dirickx

matthias-dirickx/postgresql-schema-compare.sh

Created December 7, 2021 10:23
Show Gist options
  • Save matthias-dirickx/610e345e0854a415608f45b130c750ef to your computer and use it in GitHub Desktop.
Save matthias-dirickx/610e345e0854a415608f45b130c750ef to your computer and use it in GitHub Desktop.
Download ZIP
Compare schema's of two databases. Variables need to be added to the script by passing them as variables or loading them in another way. Description in gist comments.
Raw
postgresql-schema-compare.sh
#!/usr/bin/bash
#Rrequired_parameters to be supplied
# - target_db
# - target_db_new
# - target_port
# - pg_user
# Produced output
# - List of all tables that were added (in new db, but not in old)
# - List of all tables that were removed ( in old db, but not in new)
# - List of all fields that were added in existing tables
# - List of all already existing fields that changed types
# The script compares 1 DB to another on the schema level.
# There is the old DB $target_db, and the new db $target_db_new.
# In the context that this was written a diff report was set up
# after applying the schema to a new database. This made sense
# because it was going to be done anyway. It is in the context
# of upgrading test instances of a database that may have slightly
# separate lives to the current production schema.
# Create temp files
dbold_csv=$(mktemp);
dbnew_csv=$(mktemp);
dboldcounts_csv=$(mktemp);
dbnewcounts_csv=$(mktemp);
db=$(mktemp)
# Define log file
logfile="db_verify_schema.log"
# Long SQL statements
dbold_qry="\\copy (select table_schema, \
table_name, \
column_name as col_name, \
data_type as col_type \
from information_schema.columns \
where table_schema in ('public', 'fifr') \
order by table_schema asc, \
table_name asc, \
column_name asc) \
to '${dbold_csv}' \
with (format csv, delimiter ',', FORCE_QUOTE *)"
dbnew_qry="\\copy (select table_schema, \
table_name, \
column_name as col_name, \
data_type as col_type \
from information_schema.columns \
where table_schema in ('public', 'fifr') \
order by table_schema asc, \
table_name asc, \
column_name asc) \
to '${dbnew_csv}' \
with (format csv, delimiter ',', FORCE_QUOTE *)"
dboldcount_qry="do \
\$\$ \
declare \
temprow record; \
lcount numeric; \
countqry text; \
begin \
drop table if exists tmp_linecounts; \
create temp table if not exists tmp_linecounts ( \
table_schema text, \
table_name text, \
linecount numeric); \
for temprow in \
select table_schema, table_name \
from information_schema.tables \
where table_schema in ('public', 'fifr') \
order by table_schema, table_name \
loop \
execute format('select count(*) from %s.%s;', temprow.table_schema, temprow.table_name) into lcount; \
insert into tmp_linecounts (table_schema, table_name, linecount) \
values (temprow.table_schema, temprow.table_name, lcount); \
end loop; \
end; \
\$\$; \
copy (select * from tmp_linecounts) \
to '${dboldcounts_csv}' \
with (format csv, delimiter ',', FORCE_QUOTE *)";
dbnewcount_qry="do \
\$build_counts\$ \
declare \
temprow record; \
lcount numeric; \
countqry text; \
begin \
drop table if exists tmp_linecounts; \
create temp table if not exists tmp_linecounts ( \
table_schema text, \
table_name text, \
linecount numeric); \
for temprow in \
select table_schema, table_name \
from information_schema.tables \
where table_schema in ('public', 'fifr') \
order by table_schema, table_name \
loop \
execute format('select count(*) from %s.%s;', temprow.table_schema, temprow.table_name) into lcount; \
insert into tmp_linecounts (table_schema, table_name, linecount) \
values (temprow.table_schema, temprow.table_name, lcount); \
end loop; \
end; \
\$build_counts\$; copy (select * from tmp_linecounts) \
to '${dbnewcounts_csv}' \
with (format csv, delimiter ',', FORCE_QUOTE *)";
# Get data from the db's
psql -p ${target_port} \
-h localhost \
-U ${pg_user} \
-d ${target_db} \
-c "$dbold_qry"
psql -p ${qa_target_port} \
-h localhost \
-U ${qa_pg_user} \
-d ${target_db_new} \
-c "$dbnew_qry"
echo "old count"
psql -p ${target_port} \
-h localhost \
-U ${pg_user} \
-d ${target_db} \
-c "$dboldcount_qry"
echo "new count"
psql -p ${target_port} \
-h localhost \
-U ${pg_user} \
-d ${target_db_new} \
-c "$dbnewcount_qry"
wait;
# Create the SQLITE DB
createdb_qry="create table db_current (\
table_schema text, \
table_name text, \
col_name text, \
col_type text); \
create table db_new (\
table_schema text, \
table_name text, \
col_name text, \
col_type text);\
create table db_current_counts (\
table_schema text,\
table_name text,\
linecount numeric);\
create table db_new_counts (\
table_schema text,\
table_name text,\
linecount numeric);"
sqlite3 $db "$createdb_qry"
# Load CSV files to the tables
sqlite3 $db -cmd ".mode csv" -cmd ".separator ','" ".import $dbold_csv db_current";
sqlite3 $db -cmd ".mode csv" -cmd ".separator ','" ".import $dbnew_csv db_new";
sqlite3 $db -cmd ".mode csv" -cmd ".separator ','" ".import $dboldcounts_csv db_current_counts";
sqlite3 $db -cmd ".mode csv" -cmd ".separator ','" ".import $dbnewcounts_csv db_new_counts";
echo ""
echo "Tables that are no longer in new db:"
echo "(Empty when no results)"
sqlite3 $db \
-cmd ".header on" \
-cmd ".mode column" \
-cmd ".width 15 50" \
"select table_schema, table_name from db_current where table_schema||'.'||table_name not in (select distinct table_schema||'.'||table_name from db_new) group by table_schema, table_name;"
echo ""
echo "Tables that are added in the new db:"
echo "(Empty when no results)"
sqlite3 $db \
-cmd ".header on" \
-cmd ".mode column" \
-cmd ".width 15 50" \
"select table_schema, table_name from db_new where table_schema||'.'||table_name not in (select distinct table_schema||'.'||table_name from db_current) group by table_schema, table_name;"
echo ""
echo "Added fields for existing tables in the new db:"
echo "(Empty when no results)"
sqlite3 $db \
-cmd ".header on" \
-cmd ".mode column" \
-cmd ".width 15 30 30" \
"select dbnew.table_schema, dbnew.table_name, dbnew.col_name from db_new dbnew left join db_current dbold on dbnew.table_schema = dbold.table_schema and dbnew.table_name = dbold.table_name and dbnew.col_name = dbold.col_name where dbold.col_name is null and dbnew.table_name in (select distinct table_name from db_current);"
echo ""
echo "Changed fields types in the new db:"
echo "(Empty when no results)"
sqlite3 $db \
-cmd ".header on" \
-cmd ".mode column" \
-cmd ".width 15 30 30" \
"select dbnew.table_schema, dbnew.table_name, dbnew.col_name, dbnew.col_type col_type_new, dbold.col_type col_type_old from db_new dbnew left join db_current dbold on dbnew.table_schema = dbold.table_schema and dbnew.table_name = dbold.table_name and dbnew.col_name = dbold.col_name where dbold.col_type <> dbnew.col_type;"
echo ""
echo "Removed fields in the new db:"
echo "(Empty when no results)"
sqlite3 $db \
-cmd ".header on" \
-cmd ".mode column" \
-cmd ".width 15 30 30" \
"select dbnew.table_schema, dbnew.table_name, dbnew.col_name, dbnew.col_type col_type_new from db_current dbold left join db_new dbnew on dbnew.table_schema = dbold.table_schema and dbnew.table_name = dbold.table_name and dbnew.col_name = dbold.col_name where dbold.col_name is null;"
echo ""
echo "Rowcounts where they are not equal"
echo "(Empty when no results)"
sqlite3 $db \
-cmd ".header on" \
-cmd ".mode column" \
-cmd ".width 15 30 30" \
"select dbnew.table_schema, \
dbnew.table_name, \
dbnew.linecount dbnew_count, \
dbold.linecount dbold_count \
from db_new_counts dbnew \
left join db_current_counts dbold \
on dbnew.table_schema = dbold.table_schema \
and dbnew.table_name = dbold.table_name \
where dbnew.linecount <> dbold.linecount \
order by dbnew.table_schema asc, \
dbnew.table_name asc;"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment