check differences between two databases

check_diffs.sql

-- CHECK DIFF BEWEEN TWO DATABASES (A and B) 
-----------------------------------------------

/*********************************************
 * 1.0 CREATE THE APPROPRIATE CONFIGURATION
 *********************************************/

-- create a new database AB (from terminal)
createdb AB -T A
-- connect to AB (sql)
alter schema public rename to A;
create schema public;
-- export/import the B database (by default in the public schema)(from terminal)
pg_dump B > /tmp/B.sql
cat /tmp/B.sql | psql -d AB
-- remame public schema to B (sql)
alter schema public rename to B;
-- recreate the public schema
create schema public;

/*********************************************
 * 2.0 RUN THE SCRIPT
 *********************************************/


DO
$do$
declare
	v_table_name varchar;
	v_column_names varchar;
	v_cnt_a_not_in_b int;
	v_cnt_b_not_in_a int;
begin
	/*create table*/
	  DROP TABLE IF EXISTS TABLE_DIFFS;
      CREATE TABLE TABLE_DIFFS
        (
            ID SERIAL,
            TABLE_NAME varchar,
            CNT_IN_A_NOT_IN_B int,
            CNT_IN_B_NOT_IN_A int,
            CNT int
        );
	FOR v_table_name, v_column_names IN
	    select table_name , string_agg(column_name,',') column_names from 
	    (
	         select
	               table_name,
	               concat('"',column_name,'"') column_name,
	               table_schema
	         from information_schema.columns  
	         where 1=1 
	         and table_schema in ('a')
	         and column_name not in ('create_date', 'write_date', 'write_uid', 'create_uid')
	     )A
	     group by table_name
    loop
      raise notice 'processing table %',v_table_name;  
      EXECUTE format('select count(*) from 
                        (
				SELECT %s FROM a.%I
				EXCEPT
				SELECT %s FROM b.%I
			)A', v_column_names, v_table_name, v_column_names, v_table_name)
						INTO v_cnt_a_not_in_b;
	  EXECUTE format('select count(*) from 
                        (
				SELECT %s FROM b.%I
				EXCEPT
				SELECT %s FROM a.%I
			)A', v_column_names, v_table_name, v_column_names, v_table_name) 
						INTO v_cnt_b_not_in_a;
      insert into TABLE_DIFFS(TABLE_NAME,CNT_IN_A_NOT_IN_B, CNT_IN_B_NOT_IN_A, CNT) 
      values(v_table_name, v_cnt_a_not_in_b, v_cnt_a_not_in_b, v_cnt_b_not_in_a+v_cnt_a_not_in_b );
    END LOOP;
  
end;
$do$;

/*
select * from TABLE_DIFFS where cnt>0