FIND THE DIFFERENCE IN 2 SCHEMA & GENERATE DDL AFTER COMPARING THE SCHEMAS

Here the plpgsql utility to compare schema to find the difference and generate DDL statement.

This function return
            1) List of tables which is exist in one  schema
            2) Generate the  DDL statement for schema synchronization.
( This statement is useful when we synchronize the schema in same database) 
            3) Generate the table backup and restore statement
( This statement is useful when we synchronize the schema in different database, for this we need to backup of the differences table and restore into another database )


before run this function in your database check it has plpgsql by
SELECT * FROM pg_language
if your database not supported plpgsql, add by using
CREATE LANGUAGE plpgsql



Function accept two schema of same database


CREATE OR REPLACE FUNCTION fn_compare_schema(p_fst_schema character varying, p_snd_schema character varying)
RETURNS character varying
language plpgsql
AS $$
-- DECLARE VARIABLE
DECLARE
V_FstSchema varchar(150);
V_SndSchema varchar(150);
i integer;
VtblNot varchar = '';
VtblCrt varchar = '';
VtblCrossCreate varchar = '';
Vrtn varchar = '';
-- DECLARE CURSOR
Ctbl_list1 refcursor;
Ctbl1 pg_stat_user_tables.relname%type;
Vtblkcu information_schema.key_column_usage;
BEGIN
V_FstSchema := p_fst_schema;
V_sndSchema := p_snd_schema;
i :=0;
loop -- for passing both parameter by using single statment
-- CREATE AND OPEN CURSOR TO HOLD THE TABLES IN ONE SCHEMA
VtblNot := 'TABLES NOT IN SCHEMA : ' || V_sndSchema ||chr(10);
raise info 'TABLES NOT IN SCHEMA : % ',V_sndSchema;
open Ctbl_list1 for execute '
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_FstSchema)||'
except
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_sndSchema);
loop fetch next from Ctbl_list1 into Ctbl1;
exit when not found;
raise info 'TABLE NAME : % ',Ctbl1;
--ADD ALL THE TABLES IN ONE SCHEMA AND NOT IN ANOTHER
VtblNot := chr(10)||VtblNot||';'|| chr(10) || V_FstSchema||'.'||Ctbl1 ;
-- ADD CREATE DDL STATEMENT (var used VtblCrt, only Structure of the table )
VtblCrt := VtblCrt|| chr(10) || 'create table '|| V_SndSchema||'.'||Ctbl1 ||' as select * from '|| V_FstSchema||'.'||Ctbl1 ||' where 1=0; ' ;
VtblCrossCreate := VtblCrossCreate || chr(10) || 'pg_dump sourcedbname -t '||V_FstSchema ||'.'||Ctbl1 ||' -c -s | psql -h localhost targetdbname;';
--raise info 'Vtblcrt : %',Vtblcrt ;
end loop;
Vrtn = vrtn || chr(10) || VtblNot;
Vrtn = chr(10)||Vrtn ||chr(10)||VtblCrt;
Vrtn = chr(10)||Vrtn ||chr(10)||VtblCrossCreate ;
VtblCrt = '';
VtblCrossCreate = '';
close Ctbl_list1;
exit when i=1;
i:=1;
--THIS TIME THE SCHEMA WOULD INTERCHANGE LOOP WILL EXECUTE       --AGAIN
V_FstSchema := p_snd_schema;
V_sndSchema := p_fst_schema;
end loop; --for comparing two schema in single statment
raise info 'VRTN : %; ',Vrtn;
RETURN Vrtn;
end;
$$

run above function by ( execute with write result to file option in pgadmin3),

SELECT fn_compare_schema('dbschema1','dbschema2') 

in psql mode

COPY ( SELECT fn_compare_schema('dbschema1','dbschema2') ) To '/tmp/ddloutput.csv' With CSV;

OR

psql dbname
psql>\o '/tmp/ddloutput.csv'
psql>SELECT  fn_compare_schema('dbschema1','dbschema2');
psql>\q

OR


psql -d dbname -t -A -F"," -c "SELECT fn_compare_schema('dbschema1','dbschema2') " > ddloutput.csv

output like

TABLES NOT IN SCHEMA : panmydesk2200;

panmydesk4400.d_item_reject;
panmydesk4400.d_preaudit_cc_map;

create table panmydesk2200.d_item_reject as select * from panmydesk4400.d_item_reject where 1=0;
create table panmydesk2200.d_preaudit_cc_map as select * from panmydesk4400.d_preaudit_cc_map where 1=0;

pg_dump sourcedbname -t panmydesk4400.d_item_reject -c -s | psql -h localhost targetdbname;
pg_dump sourcedbname -t panmydesk4400.d_preaudit_cc_map -c -s | psql -h localhost targetdbname;

note :

sourcedbname → where the table's actually reside 

targetdbname  → to where you have to add the table from source db