from pyPgSQL import PgSQL 

#host:port:database:user:password:options:tty

src_db_conn_str = "ip:5432:dbname:username:password"

dst_db_conn_str = "ip:5432:dbname:username:password"



src_db = PgSQL.connect(src_db_conn_str)

dst_db = PgSQL.connect(dst_db_conn_str)

src_cursor = src_db.cursor()

dst_cursor = dst_db.cursor()

src_cursor.execute("select tablename from pg_tables where schemaname = 'public'");

tbl_name_list = src_cursor.fetchall()



for tbl_name in tbl_name_list:

    dst_cursor.execute("drop table " + tbl_name[0]);

    dst_cursor.execute("create table " + tbl_name[0] + " ()");

    src_cursor.execute("select * from table_coldef where tbl_name = '" + tbl_name[0] +

"'");

    tbl_def = src_cursor.fetchall()

    print "tbl_def = ", tbl_def

    for col_def in tbl_def:

        dst_cursor.execute("alter table " + col_def[0] + " add column " + col_def[1] +

" " + col_def[2]);

        print "col_def = ", col_def

src_db.commit()	        

dst_db.commit()	



src_cursor.close();

src_db.close()     



dst_cursor.close();

dst_db.close()