{"push_id":2307224448,"size":1,"distinct_size":1,"ref":"refs/heads/master","head":"5a4b5413efebb44168c4050db20ccf78953e831d","before":"bbd204c955b9a222795c256f8faf2448fb915a1d","commits":[{"sha":"5a4b5413efebb44168c4050db20ccf78953e831d","author":{"name":"pgyogesh","email":"53800862a8a2ba2c33cbe96da9fea180584c7a67@gmail.com"},"message":"import sys\nimport os\nimport re\nimport time\nimport datetime\nimport logging\nimport ConfigParser\nimport argparse\nfrom pygresql.pg import DB\n\nlogging.basicConfig(format='%(asctime)s:%(levelname)s:%(message)s',level=logging.DEBUG)\n\nparser = argparse.ArgumentParser()\nparser.add_argument(\"-t\",\"--type\", metavar=\"<type>\", choices=['pg_dump','gpcrondump'],\n                   required=True, action=\"store\",help=\"Specify the type of backup\")\nparser.add_argument(\"-c\",\"--config_file\", required=True,\n                    action=\"store\",help=\"Specify the config file\")\nargs = parser.parse_args()\nconfig = ConfigParser.ConfigParser()\nconfig.read(args.config_file)\n\n# Source System Information\nlogging.info(\"Getting source database Information\")\nsource_db = config.get(\"source\",\"database\")\nsource_host = config.get(\"source\",\"host\")\nsource_user = config.get(\"source\",\"user\")\nsource_port = config.get(\"source\",\"port\")\nsource_schemafile = config.get(\"source\",\"schema-file\")\nsource_environment = config.get(\"source\",\"environment\")\n\n# Target System Information\nlogging.info(\"Getting target database Information\")\ntarget_db = config.get(\"target\",\"database\")\ntarget_host = config.get(\"target\",\"host\")\ntarget_user = config.get(\"target\",\"user\")\ntarget_port = config.get(\"target\",\"port\")\ntarget_schemafile = config.get(\"target\",\"schema-file\")\ntarget_environment = config.get(\"target\",\"environment\")\n\n# Getting timestamp of script start, Later this timestamp will be used to compare with dump_key from gpcrondump_history.\n\n#logging.info(\"Script Start Timestamp = %d\" %start_timestamp)\nlogging.info(\"Source Database        = %s\" %source_db)\nlogging.info(\"Target Database        = %s\" %target_db)\n\n#backup_command=\"gpcrondump -x %s -s %s -h -a\" %(source_db,source_schema)\nnow = datetime.datetime.now()\nstart_timestamp = int(now.strftime(\"%Y%m%d%H%M%S\"))\n\ndef get_starttime():\n    return int(start_timestamp)\n\ndef pg_dump_backup():\n    backup_command=\"pg_dump -d %s -h %s -U %s -n %s > %s\" %(source_db,source_host,source_user,source_schema,backup_file)\n    os.popen(backup_command)\n\ndef pg_dump_restore():\n    restore_command=\"psql -d %s -h %s -U %s < %s\" %(target_db,target_host,target_user,backup_file)\n    os.popen(restore_command)\n\ndef schema_list_for_cmd(option):\n    schemas = ''\n    schema_file = open(source_schemafile,'r')\n    schema_file.seek(0)\n    for num,line in enumerate(schema_file, 1):\n        schema = line.rstrip('\\n')\n        schemas = schemas + option + ' ' + schema + ' '\n    schema_file.close()\n    return schemas\n\ndef gpdbrestore_restore():\n    target_schema_check()\n    restore_command=\"gpdbrestore -t %s --noanalyze --redirect %s -a 2> /dev/null\" %(get_backupkey(),target_db)\n    os.popen(restore_command)\n    schemas = ''\n    schema_file = open(source_schemafile,'r')\n    schema_file.seek(0)\n    for num,line in enumerate(schema_file, 1):\n        schema = line.rstrip('\\n')\n        con = DB(dbname=target_db)\n        get_schema = con.query(\"SELECT nspname FROM pg_namespace where nspname = \\'%s\\'\" %schema)\n        row = get_schema.getresult()\n        if row:\n            logging.info(\"Restore completed for %s schema\" %schema)\n        else:\n            logging.info(\"Restore failed for %s schema\" %schema)\n\ndef target_schema_check():\n    con = DB(dbname=target_db)\n    schema_list = open(source_schemafile,'r')\n    schema_list.seek(0)\n    for schema in schema_list:\n        schema = schema.rstrip('\\n')\n        date = now.strftime(\"%Y%m%d%H%M%S\")\n        logging.info(\"Checking if %s schema exists in %s database\" %(schema,target_db))\n        query = \"SELECT nspname FROM pg_namespace where nspname = \\'%s\\'\" %schema\n        print(query)\n        get_schema = con.query(query)\n        row = get_schema.getresult()\n        print(row)\n        if row:\n            logging.info(\"%s schema already exists in %s database\" %(schema,target_db))\n            logging.info(\"Renaming %s schema to %s_hold_%s\" %(schema,schema,date))\n            con.query(\"ALTER SCHEMA %s RENAME to %s_hold_%s\" %(schema,schema,date))\n            nsp = con.query(\"SELECT nspname FROM pg_namespace where nspname = \\'%s\\'\" %schema)\n            row = nsp.getresult()\n            if row:\n                logging.info(\"Failed to rename %s schema to %s_hold_%s\" %(schema,schema,date))\n                logging.info(\"Please rename the schemas and run restore manually using timestamp: %s\" %get_backupkey())\n                sys.exit()\n            else:\n                logging.info(\"%s schema renamed successfully to %s_hold_%s\" %(schema,schema,date))\n        else:\n            logging.info(\"%s schema doesn't exists in %s. Good to restore backup\" %(schema,target_db))\n    schema_list.close()\n    con.close()\n\ndef get_backupkey():\n    con = DB(dbname=source_db, host=source_host, user=source_user)\n    opts = backup_command[11:-13]\n    key = con.query(\"SELECT dump_key FROM gpcrondump_history where options = '%s' AND exit_text = 'COMPLETED' ORDER BY dump_key desc limit 1\" %opts)\n    row = key.dictresult()\n    dump_key = row[0][\"dump_key\"]\n    return int(dump_key)\n\ndef permission_switch(schemaname):\n    logging.info(\"Switching roles from %s to %s\" %(source_environment, target_environment)\n    temp_files = ['/tmp/grantfile.sql','/tmp/grantfile_temp.sql','/tmp/revokefile.sql','/tmp/revokefile_temp.sql','/tmp/ownerfile.sql','/tmp/ownerfile_temp.sql']\n    logging.info(\"Checking if temp files already exists\")\n\n    for file in temp_files:\n        if os.path.isfile(file):\n            os.remove(file)\n\n    sql_file='/tmp/%s_%s.sql' %(schemaname,now.strftime(\"%Y%m%d\"))\n    logging.info(\"Taking schema only backup from target database for %s schemas in %s\" %(schemaname,sql_file))\n    schema_backup_command = \"pg_dump %s -n %s > %s\" %(target_db,schemaname,sql_file)\n    os.popen(schema_backup_command)\n    v_sqlfile=open(sql_file,'r')\n    logging.info(\"Fetching grant SQL statement from \" + sql_file)\n    v_grantfile=open(\"/tmp/grantfile.sql\",\"a\")\n    for g_line in v_sqlfile:\n        g_line = g_line.rstrip()\n        if re.search('^GRANT',g_line):\n            v_grantfile.writelines(g_line)\n            v_grantfile.write('\\n')\n    v_grantfile.close()\n\n    logging.info(\"Fetching 'ALTER TABLE .. OWNER TO' SQL statement from \" + sql_file)\n    v_ownerfile=open(\"/tmp/ownerfile.sql\",\"a\")\n    for o_line in v_sqlfile:\n        o_line = o_line.rstrip()\n        if re.search(\"OWNER TO\",o_line):\n            v_ownerfile.writelines(o_line)\n            v_ownerfile.write('\\n')\n    v_ownerfile.close()\n\n    logging.info(\"Generating revoke statements from \" + sql_file)\n    v_grantfile=open(\"/tmp/grantfile.sql\",\"r\")\n    v_revokefile=open(\"/tmp/revokefile.sql\",\"a\")\n    for r_line in v_grantfile:\n        revoke_line = r_line.replace(\"GRANT\",\"REVOKE\")\n        from_line = revoke_line.replace(\" TO \", \" FROM \")\n        v_revokefile.writelines(from_line)\n    v_revokefile.close()\n    v_grantfile.close()\n    logging.info(\"Creating new GRANT statement's file with \"+ target_environment + \" roles\")\n    v_grantfile=open(\"/tmp/grantfile.sql\",\"r\")\n    v_grantfile_temp=open(\"/tmp/grantfile_temp.sql\",\"a\")\n    for r_line in v_grantfile:\n        new_role_line = r_line.replace('_' + source_environment + '_', '_' + target_environment + '_')\n        v_grantfile_temp.writelines(new_role_line)\n    v_grantfile.close()\n    v_grantfile_temp.close()\n    logging.info(\"Creating new 'ALTER TABLE .. OWNER TO' statement file with \"+ target_environment + \" roles\")\n    v_ownerfile=open(\"/tmp/ownerfile.sql\",\"r\")\n    v_ownerfile_temp=open(\"/tmp/ownerfile_temp.sql\",\"a\")\n    for o_line in v_ownerfile:\n        new_role_line = o_line.replace('_' + source_environment + '_', '_' + target_environment + '_')\n        v_ownerfile_temp.writelines(new_role_line)\n    v_ownerfile.close()\n    v_ownerfile_temp.close()\n    final_sql_file= '/tmp/' + target_environment + '_' + schema + \"_refresh_%s.sql\" %now.strftime(\"%Y%m%d\")\n    logging.info(\"Gathering all statements in one SQL file: %s\" %final_sql_file)\n    revoke_sql=open(\"/tmp/revokefile.sql\",\"r\")\n    grant_sql=open(\"/tmp/grantfile_temp.sql\",\"r\")\n    owner_sql=open(\"/tmp/ownerfile_temp.sql\",\"r\")\n    sql_file=open(final_sql_file,\"a+\")\n    sql_file.write(\"set search_path to \" + schemaname + ';')\n\n    sql_file.write(\"--------------------REVOKE STATEMENTS---------------------\\n\\n\")\n    for line in revoke_sql:\n        sql_file.writelines(line)\n\n    sql_file.write(\"\\n\\n--------------------GRANT STATEMENTS---------------------\\n\\n\")\n    for line in grant_sql:\n        sql_file.writelines(line)\n\n    sql_file.write(\"\\n\\n--------------------OWNER STATEMENTS---------------------\\n\\n\")\n    for line in owner_sql:\n        sql_file.writelines(line)\n\n    revoke_sql.close()\n    grant_sql.close()\n    owner_sql.close()\n    sql_file.close()\n\n    logging.info(\"Deleting temporary files\")\n    logging.info(\"Running generated SQL file\")\n    run_permissions = \"psql -d %s -f %s > /tmp/permissions.out\" %(target_db,final_sql_file)\n    os.popen(run_permissions)\n    for file in temp_files:\n        if os.path.isfile(file):\n            os.remove(file)\n    logging.info(\"Environment refresh from %s to %s completed\" %(source_environment,target_environment))\n\nif __name__ == '__main__':\n    get_starttime()\n    if args.type == 'pg_dump':\n        pg_dump_backup()\n        pg_dump_restore()\n    else:\n        time.sleep(1)\n        backup_command=\"gpcrondump -x %s %s-h -a 2> /dev/null\" %(source_db,schema_list_for_cmd('-s'))\n        os.popen(backup_command)\n        if get_backupkey() < get_starttime():\n            logging.error(\"Backup is failed. Please check backup log /home/gpadmin/gpAdminlogs/gpcrondump_%s.log\" %now.strftime(\"%Y%m%d\"))\n            sys.exit()\n        else:\n            logging.info(\"Backup completed successfully\")\n            gpdbrestore_restore()\n            file = open(source_schemafile,'r')\n            for schema in file:\n                schema = schema.rstrip('\\n')\n                permission_switch(schema)","distinct":true,"url":"https://api.github.com/repos/pgyogesh/greenplum-environment-refresh/commits/5a4b5413efebb44168c4050db20ccf78953e831d"}]}
{"actor":{"display_login":"pgyogesh"}}