{"id":836,"date":"2020-07-06T12:56:48","date_gmt":"2020-07-06T16:56:48","guid":{"rendered":"http:\/\/jasonralph.org\/?p=836"},"modified":"2020-07-15T00:04:31","modified_gmt":"2020-07-15T04:04:31","slug":"automate-pg_dump-pg_restore-of-tables-from-config-file-send-slack-update","status":"publish","type":"post","link":"https:\/\/jasonralph.org\/?p=836","title":{"rendered":"Automate pg_dump pg_restore Of Tables From Config File Send Slack Update"},"content":{"rendered":"<p>You can use this python code to setup a cron that will sync postgres tables from one database to another.  This will read from a config file and will be able to do multiple tables from the same run. This can be useful to sync a daily table from source to destinations.  This will also send a alert to slack if its ok or critical. <\/p>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n[logging]\r\nlog_file = pg_table_sync_dev_to_prod.log\r\nlog_path = \/home\/postgres\r\n\r\n[pg_table_source_dest]\r\npublic.jason_test_table1 = public.jason_test_table1\r\npublic.jason_test_table2 = public.jason_test_table2\r\npublic.jason_test_table3 = public.jason_test_table3\r\n\r\n[hosts]\r\nsource_db = db-sbx01\r\ndest_db = db10\r\n\r\n[database]\r\ndev_db = devdb\r\nprod_db = proddb\r\n\r\n[dump_location]\r\nlocal_location = \/u04\/pg_data_dumps\/transfer_tables\/\r\n\r\n[slack]\r\nwebhook = https:\/\/hooks.slack.com\/services\/<yourwebhookhere>\r\n<\/pre>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n__author__ = 'jralph'\r\n__version__ = '1.0.0'\r\n\r\nimport configparser\r\nimport os\r\nimport sys\r\nimport logging\r\nimport subprocess\r\nimport shlex\r\nimport socket\r\nimport datetime\r\nimport requests\r\n\r\n# set hostname.\r\nhostname = socket.gethostname()\r\n\r\n# set date now.\r\nnow = datetime.datetime.now()\r\n\r\n# obtain script name and assign to variable.\r\nscript_name = sys.argv[0].split('.')[0]\r\n\r\n# sanity check for configuration environment variable.\r\nif \"INI_PATH\" not in os.environ.keys():\r\n    print('INI_PATH is not set, check the .bashrc')\r\n    sys.exit(1)\r\n\r\n# parse the configuration sections of the ini file.\r\nconfig = configparser.ConfigParser()\r\ntry:\r\n    config.read(os.environ['INI_PATH'] + '\/pg_table_sync_dev_prod.ini')\r\n    config.sections()\r\n    log_file = config.get('logging', 'log_file')\r\n    log_path = config.get('logging', 'log_path')\r\n    slack_hook = config.get('slack', 'webhook')\r\nexcept configparser.NoSectionError as e:\r\n    print('FATAL: Command failed with error [{0}]'.format(e))\r\n\r\n# setup logging.\r\ntry:\r\n    logging.basicConfig(filename='%s\/%s' % (log_path, log_file),\r\n                        format='%(asctime)s %(message)s',\r\n                        datefmt='%m-%d-%Y %I:%M:%S %p -',\r\n                        level=logging.DEBUG)\r\nexcept NameError as e:\r\n    print('FATAL: Command failed with error [{0}]'.format(e))\r\n\r\n# get hosts and tablenames.\r\ntry:\r\n    pg_tables_to_sync = dict(config.items('pg_table_source_dest'))\r\n    source_db = config.get('hosts', 'source_db')\r\n    dest_db = config.get('hosts', 'dest_db')\r\n    dump_location = config.get('dump_location', 'local_location')\r\n    dev_db = config.get('database', 'dev_db')\r\n    prod_db = config.get('database', 'prod_db')\r\nexcept (configparser.NoSectionError, NameError) as e:\r\n    logging.critical('FATAL: Command failed with error [{0}]'.format(e))\r\n\r\n\r\n# pg_dump function.\r\ndef pg_dump():\r\n    cmd_list = []\r\n    tables = {}\r\n    try:\r\n        tables = sorted(pg_tables_to_sync.items())\r\n    except NameError as e:\r\n        logging.critical('FATAL: Command failed with error [{0}]'.format(e))\r\n    for key, value in tables:\r\n        dump_cmd = 'pg_dump -Fc -h {0} -d {1} -t {2} -f {4}{2}.{3}.pgdump'.format(\r\n            source_db, dev_db, key, now.strftime(\"%Y%m%d\"), dump_location)\r\n        cmd_list.append(dump_cmd)\r\n    return cmd_list\r\n\r\n# pg_restore function.\r\ndef pg_restore():\r\n    cmd_list = []\r\n    tables = {}\r\n    try:\r\n        tables = sorted(pg_tables_to_sync.items())\r\n    except NameError as e:\r\n        logging.critical('FATAL: Command failed with error [{0}]'.format(e))\r\n    for key, value in tables:\r\n        dump_cmd = 'pg_restore -c -h {0} -d {1} {4}{2}.{3}.pgdump'.format(\r\n            dest_db, prod_db, key, now.strftime(\"%Y%m%d\"), dump_location)\r\n        cmd_list.append(dump_cmd)\r\n    return cmd_list\r\n\r\n# send to slack function.\r\ndef send_to_slack(slack_url, state, command, date_format, priority, target_os):\r\n    slack_data = {'attachments': [\r\n        {\r\n            \"fallback\": \"Required plain-text summary of the attachment.\",\r\n            \"color\": priority,\r\n            \"pretext\": \"PG Table Sync\",\r\n            \"author_name\": command,\r\n            \"text\": \"%s\" % date_format,\r\n            \"fields\": [\r\n                {\r\n                    \"title\": \"%s\" % target_os,\r\n                    \"value\": state,\r\n                    \"short\": \"false\"\r\n                }\r\n            ],\r\n            \"footer\": \"AFS Slack\",\r\n            \"footer_icon\": \"https:\/\/platform.slack-edge.com\"\r\n                           \"\/img\/default_application_icon.png\"\r\n        }\r\n    ]}\r\n    response = requests.post(\r\n        slack_url, json=slack_data)\r\n    if response.status_code != 200:\r\n        raise ValueError(\r\n            'Request to slack returned an error %s, the response is:\\n%s'\r\n            % (response.status_code, response.text))\r\n\r\n\r\n# execute with logging.\r\ndef execute_jobs(cmd):\r\n    try:\r\n        logging.info('Start Command: [%s]' % cmd)\r\n        subprocess.run(shlex.split(cmd), check=True)\r\n        logging.info('Command Success: [%s]' % cmd)\r\n        try:\r\n            send_to_slack(slack_hook, 'Ok', cmd,\r\n                          datetime.datetime.today(), 'good', hostname)\r\n        except ValueError as e:\r\n            logging.critical('FATAL: Slack post failed with error [%s]'\r\n                             % e)\r\n    except subprocess.CalledProcessError as e:\r\n        logging.critical('[%s] FATAL: Command failed with error [%s]'\r\n                         % (cmd, e))\r\n        try:\r\n            send_to_slack(slack_hook, 'Critical', cmd,\r\n                          datetime.datetime.today(), 'danger', hostname)\r\n        except ValueError as e:\r\n            logging.critical('FATAL: Slack post failed with error [%s]'\r\n                             % e)\r\n\r\n\r\n# main\r\ndef main():\r\n    for command in pg_dump():\r\n        execute_jobs(command)\r\n    for command in pg_restore():\r\n        execute_jobs(command)\r\n\r\n    logging.info('finished ' + script_name)\r\n\r\n\r\nif __name__ == \"__main__\":\r\n    main()\r\n<\/pre>\n<p>LOGGING EXAMPLE:<\/p>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n07-06-2020 11:44:28 AM - Start Command: [pg_dump -Fc -h db-sbx01 -d db1 -t public.jason_test_table -f \/u04\/pg_data_dumps\/transfer_tables\/public.jason_test_table.20200706.pdump]\r\n07-06-2020 11:44:30 AM - Command Success: [pg_dump -Fc -h db-sbx01 -d db1 -t public.jason_test_table -f \/u04\/pg_data_dumps\/transfer_tables\/public.jason_test_table.20200706.pgdump]\r\n07-06-2020 11:44:30 AM - Starting new HTTPS connection (1): hooks.slack.com\r\n07-06-2020 11:44:30 AM - https:\/\/hooks.slack.com:443 \"POST \/services\/T04MEPB2K\/B72JPEUUB\/nfEqv7bsKafUUjLoKgo0oT5S HTTP\/1.1\" 200 22\r\n07-06-2020 11:44:30 AM - Start Command: [pg_restore -c -h db10 -d db1 \/u04\/pg_data_dumps\/transfer_tables\/public.jason_test_table.20200706]\r\n07-06-2020 11:44:31 AM - Command Success: [pg_restore -c -h db10 -d db1 \/u04\/pg_data_dumps\/transfer_tables\/public.jason_test_table.20200706]\r\n07-06-2020 11:44:31 AM - Starting new HTTPS connection (1): hooks.slack.com\r\n07-06-2020 11:44:31 AM - https:\/\/hooks.slack.com:443 \"POST \/services\/T04MEPB2K\/B72JPEUUB\/nfEqv7bsKafUUjLoKgo0oT5S HTTP\/1.1\" 200 22\r\n07-06-2020 11:44:31 AM - finished PgTableSyncDevProd\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>You can use this python code to setup a cron that will sync postgres tables from one database to another. This will read from a config file and will be able to do multiple tables from the same run. This can be useful to sync a daily table from source to destinations. This will also [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[86,87,24],"class_list":["post-836","post","type-post","status-publish","format-standard","hentry","category-python","tag-pg_dump","tag-pg_restore","tag-python-2"],"_links":{"self":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/836","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=836"}],"version-history":[{"count":7,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/836\/revisions"}],"predecessor-version":[{"id":844,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/836\/revisions\/844"}],"wp:attachment":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=836"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=836"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=836"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}