Thursday, August 30, 2012

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

Friday, August 17, 2012

Hadoop : Bad connection to FS. command aborted.

I've successfully configured Hadoop on Debian, I got following error message while running the command,
hduser@boss:/opt/hadoop-0.21.0/bin$ ./hadoop fs  -ls /

localhost/127.0.0.1:54310. Already tried 0 time(s).
12/08/16 15:46:12 INFO ipc.Client: Retrying connect to server: 
localhost/127.0.0.1:54310. Already tried 1 time(s).
............
Bad connection to FS. command aborted. 
solution for the above issues is,

just check whether all the port are up and running by
hduser@boss:/opt/hadoop-0.21.0/bin$ netstat -nltp

tcp  0  0 127.0.0.1:8020  0.0.0.0:*   LISTEN      9469/java       
tcp        0  127.0.0.1:8021    0.0.0.0:*  LISTEN   9879/java  
tcp   0   0 0.0.0.0:37301     0.0.0.0:*     LISTEN   9879/java 
tcp     0      0 0.0.0.0:50070  0.0.0.0:*    LISTEN  9469/java  
............ 

in my case, the port which was mentioned in core-site.xml not running, then I figured it out, 

 fs.default.name

  was mentioned in both hdfs-site.xml and core-site.xml, 
in hdfs-site.xml

 fs.default.name
 localhost:9000
 
 
    dfs.replication
    1
  
and in core-site.xml


  hadoop.tmp.dir
  /app/hadoop/tmp



  fs.default.name
  hdfs://localhost:8020


after I removed the property from the hdfs-site.xml, it is working fine.  

Wednesday, August 1, 2012

PG_READ_FILE : absolute path not allowed, SQL state: 42501

Just I have got few error while working with PostgreSQL pg_read_file recently, later found the corrective action for that, Here are ..

 select pg_read_file('/root/server.key',0,(pg_stat_file('server.key')).size);
1) Error : Absolute path not allowed, SQL state: 42501 
                   Solution : pg_read_file can read the files only from the data directory path, if you would like to know your data directory path use

SHOW  data_directory command. 
It would return like "/var/lib/postgresql/9.1/main" 

move the " server.key " file into data_directory and execute the above statement by 
select pg_read_file('server.key',0,  (pg_stat_file('server.key')).size); 
here   (pg_stat_file('server.key')).size) --> return the size of the file.


2) Error: could not open file "serrrrrrver.key" for reading: No such file or directory,  SQL state: 58P01
              Solution : The given file not exist in the data dictionary.

note : Empty result would be returned  if the specified file is an image.