Page 1 of 1
Compare Table Row Counts Between Databases
Posted: Tue Sep 17, 2013 3:27 pm
by debfawn
Hello,
I recently copied a database from one cluster to another by copying all of the tables. Is there an easy way to compare the row counts for each table between the old and new clusters so that I can verify that the copy was successful?
Thank you
Re: Compare Table Row Counts Between Databases
Posted: Tue Sep 17, 2013 4:22 pm
by id10t
Hi!
Manually or script (or special apps like QualityGates -
http://quality-gates.com/)
Example of script:
Code: Select all
#!/bin/bash
SRC_DB=localhost
TRG_DB=127.0.0.1
function sql
{
HOST=$1
QUERY=$2
/opt/vertica/bin/vsql -h ${HOST} -Atqnc "${QUERY}"
}
echo
echo 'COMPARE TABLES ROW COUNT:'
echo '-------------------------'
echo
ALL_TABLES=`sql ${SRC_DB} "SELECT schema_name || '.' || table_name FROM all_tables WHERE table_type = 'TABLE'"`
for TABLE in ${ALL_TABLES}; do
SRC_CNT=`sql ${SRC_DB} "select count(*) from ${TABLE}"`
TRG_CNT=`sql ${TRG_DB} "select count(*) from ${TABLE}"`
if [[ src_cnt -ne trg_cnt ]]; then
echo -e "ERROR: ${TABLE}"
else
echo -e "OK : ${TABLE}"
fi
done
exit 0
Output (of cause everything will be equals, because I use in same DB):
Code: Select all
daniel@synapse:/tmp$ ./debfawn
COMPARE TABLES ROW COUNT:
-------------------------
OK : online_sales.call_center_dimension
OK : online_sales.online_page_dimension
OK : online_sales.online_sales_fact
OK : public.all_types
OK : public.char
OK : public.customer_dimension
OK : public.date_dimension
OK : public.employee_dimension
OK : public.int1
OK : public.int2
OK : public.inventory_fact
OK : public.product_dimension
OK : public.promotion_dimension
OK : public.rand_nums
OK : public.shipping_dimension
OK : public.vchar
OK : public.vendor_dimension
OK : public.warehouse_dimension
OK : store.store_dimension
OK : store.store_orders_fact
OK : store.store_sales_fact
Output should be as follow, if rows count not equals:
Code: Select all
COMPARE TABLES ROW COUNT:
-------------------------
ERR : online_sales.call_center_dimension
ERR : online_sales.online_page_dimension
ERR : online_sales.online_sales_fact
...
Re: Compare Table Row Counts Between Databases
Posted: Tue Sep 17, 2013 11:29 pm
by id10t
HI!
2 Vertica servers...? PROD and DR? For DR may be next algorithm will be a better choice then incremental backup:
* CONNECT (Connects to another Vertica database to enable data import )
* COPY FROM VERTICA (Copies data from another Vertica database. )
All this you can automate by scheduled script (or External Procedure) that will update DR every "X time". In such way you will be sure all table have same rows.