{"action":"created","comment":{"url":"https://api.github.com/repos/geoadmin/deploy/pulls/comments/28036644","id":28036644,"diff_hunk":"@@ -0,0 +1,80 @@\n+database managment scripts\n+==========================\n+These scripts have been developped for the database and table duplication on the postgres master instance.\n+The scripts have to be executed with ``geodata`` user. To become geodata you have to type:\n+```bash\n+$ sudo su - geodata\n+```\n+##Installation\n+Clone the git repository to geodata's home:\n+```bash\n+$ git clone git@github.com:geoadmin/deploy.git\n+$ cd deploy/db_master_deploy/\n+```\n+Make sure that geodata .pgpass file exists. It should contain the credentials for superuser ``pgkogis``\n+```bash\n+$ echo \"*:5432:*:pgkogis:xxxxxxxx\" >> /home/geodata/.pgpass\n+$ chmod 0600 /home/geodata/.pgpass\n+```\n+\n+The script output will be written to syslog ``/var/log/messages`` and can be analyzed with kibana interface.\n+\n+###database naming convention and service address\n+The read-only hot-standby slaves can be accessed via this hostname: ``pg.bgdi.ch``\n+Each database has to have one of the following suffixes\n+\n+DB Suffix    | Meaning\n+-------------|------------|\n+**_master** |  Original database, will be continuosly updated, should not be used in any applications |\n+**_dev**   | development copy of _master database  | \n+**_int**   | integration copy of _master database | \n+**_prod**  | production copy of _master database  | \n+**_demo** |  demo copy of _master database |\n+**_YYYYMMDD** | timestamped copy of _master database (bod only, use parameter -a YYYYMMDD for the archive suffix) |\n+\n+###copy databases and or tables (deploy.sh)\n+You can copy a comma delimited list of tables and/or database to one target. \n+The following targets can be used ``dev int prod demo``. \n+Normally we are deploying from a _master datasource to one of these targets. If you choose another source, the script will ask you for confirmation.\n+Table copy is performance optimized sql copy from stdout to stdin. \n+The job will be using all the available cores by splitting up the table into  parts with equal number of rows, indexes and constraints will be removed before writing the table and re-created afterwards.\n+\n+####Examples for table copies\n+```bash\n+$ bash deploy.sh -s stopo_master.tlm.strasse -t int\n+```\n+```bash\n+$ bash deploy.sh -s stopo_master.public.grid_ta25,bafu_master.prtr.swissprtr -t dev\n+```\n+The script fires the sphinx trigger script dml_trigger.sh\n+\n+Database copy is is an optimized version of ``createdb -T source_db``.\n+\n+####Examples for database copies\n+Deploy bod to integration:\n+```bash\n+$ bash deploy.sh -s bod_master -t int\n+```\n+Deploy bod to production and make an archive copy, the script will create a new database named: ``bod_master20150315``\n+```\n+$ bash deploy.sh -s bod_master -t prod -a 20150315\n+```\n+Deploy bafu and stopo to production\n+```bash\n+$ bash deploy.sh -s stopo_master,bafu_master -t prod\n+```\n+\n+####Examples for mixed database/table copy\n+Deploy bod to integration:\n+```bash\n+$ bash deploy.sh -s bod_master,stopo_master.tlm.strasse -t int\n+```\n+\n+The script fires the sphinx trigger script dml_trigger.sh for table and database copies.\n+The script fires the github trigger script ddl_trigger.sh for database copies only.","path":"db_master_deploy/README.md","position":74,"original_position":74,"commit_id":"1f75af948b2a794dd5e6d50abf2a2782db2379c2","original_commit_id":"1f75af948b2a794dd5e6d50abf2a2782db2379c2","user":{"login":"gjn","id":2572317,"avatar_url":"https://avatars.githubusercontent.com/u/2572317?v=3","gravatar_id":"","url":"https://api.github.com/users/gjn","html_url":"https://github.com/gjn","followers_url":"https://api.github.com/users/gjn/followers","following_url":"https://api.github.com/users/gjn/following{/other_user}","gists_url":"https://api.github.com/users/gjn/gists{/gist_id}","starred_url":"https://api.github.com/users/gjn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gjn/subscriptions","organizations_url":"https://api.github.com/users/gjn/orgs","repos_url":"https://api.github.com/users/gjn/repos","events_url":"https://api.github.com/users/gjn/events{/privacy}","received_events_url":"https://api.github.com/users/gjn/received_events","type":"User","site_admin":false},"body":"As specifice below, dml is sphinx. So a database copy does not update sphinx?","created_at":"2015-04-09T05:51:21Z","updated_at":"2015-04-09T05:51:21Z","html_url":"https://github.com/geoadmin/deploy/pull/1#discussion_r28036644","pull_request_url":"https://api.github.com/repos/geoadmin/deploy/pulls/1","_links":{"self":{"href":"https://api.github.com/repos/geoadmin/deploy/pulls/comments/28036644"},"html":{"href":"https://github.com/geoadmin/deploy/pull/1#discussion_r28036644"},"pull_request":{"href":"https://api.github.com/repos/geoadmin/deploy/pulls/1"}}},"pull_request":{"url":"https://api.github.com/repos/geoadmin/deploy/pulls/1","id":29791949,"html_url":"https://github.com/geoadmin/deploy/pull/1","diff_url":"https://github.com/geoadmin/deploy/pull/1.diff","patch_url":"https://github.com/geoadmin/deploy/pull/1.patch","issue_url":"https://api.github.com/repos/geoadmin/deploy/issues/1","number":1,"state":"open","locked":false,"title":"streaming replication database management scripts","user":{"login":"ltclm","id":5286659,"avatar_url":"https://avatars.githubusercontent.com/u/5286659?v=3","gravatar_id":"","url":"https://api.github.com/users/ltclm","html_url":"https://github.com/ltclm","followers_url":"https://api.github.com/users/ltclm/followers","following_url":"https://api.github.com/users/ltclm/following{/other_user}","gists_url":"https://api.github.com/users/ltclm/gists{/gist_id}","starred_url":"https://api.github.com/users/ltclm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ltclm/subscriptions","organizations_url":"https://api.github.com/users/ltclm/orgs","repos_url":"https://api.github.com/users/ltclm/repos","events_url":"https://api.github.com/users/ltclm/events{/privacy}","received_events_url":"https://api.github.com/users/ltclm/received_events","type":"User","site_admin":false},"body":"this pr contains db managment scripts\r\n* deploy.sh - copy table/database \r\n* dml_trigger.sh - update sphinx\r\n* ddl_trigger.sh - update git repository with db dumps\r\n\r\nThe bash scripts are writing their output to stdout/stderr and syslog.","created_at":"2015-02-23T08:24:02Z","updated_at":"2015-04-09T05:51:21Z","closed_at":null,"merged_at":null,"merge_commit_sha":"1451da9ec5aebede68620e0f1bb338ad6665c7fd","assignee":{"login":"gjn","id":2572317,"avatar_url":"https://avatars.githubusercontent.com/u/2572317?v=3","gravatar_id":"","url":"https://api.github.com/users/gjn","html_url":"https://github.com/gjn","followers_url":"https://api.github.com/users/gjn/followers","following_url":"https://api.github.com/users/gjn/following{/other_user}","gists_url":"https://api.github.com/users/gjn/gists{/gist_id}","starred_url":"https://api.github.com/users/gjn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gjn/subscriptions","organizations_url":"https://api.github.com/users/gjn/orgs","repos_url":"https://api.github.com/users/gjn/repos","events_url":"https://api.github.com/users/gjn/events{/privacy}","received_events_url":"https://api.github.com/users/gjn/received_events","type":"User","site_admin":false},"milestone":null,"commits_url":"https://api.github.com/repos/geoadmin/deploy/pulls/1/commits","review_comments_url":"https://api.github.com/repos/geoadmin/deploy/pulls/1/comments","review_comment_url":"https://api.github.com/repos/geoadmin/deploy/pulls/comments{/number}","comments_url":"https://api.github.com/repos/geoadmin/deploy/issues/1/comments","statuses_url":"https://api.github.com/repos/geoadmin/deploy/statuses/1f75af948b2a794dd5e6d50abf2a2782db2379c2","head":{"label":"geoadmin:dev_ltclm_db_master_management_scripts","ref":"dev_ltclm_db_master_management_scripts","sha":"1f75af948b2a794dd5e6d50abf2a2782db2379c2","user":{"login":"geoadmin","id":3369793,"avatar_url":"https://avatars.githubusercontent.com/u/3369793?v=3","gravatar_id":"","url":"https://api.github.com/users/geoadmin","html_url":"https://github.com/geoadmin","followers_url":"https://api.github.com/users/geoadmin/followers","following_url":"https://api.github.com/users/geoadmin/following{/other_user}","gists_url":"https://api.github.com/users/geoadmin/gists{/gist_id}","starred_url":"https://api.github.com/users/geoadmin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/geoadmin/subscriptions","organizations_url":"https://api.github.com/users/geoadmin/orgs","repos_url":"https://api.github.com/users/geoadmin/repos","events_url":"https://api.github.com/users/geoadmin/events{/privacy}","received_events_url":"https://api.github.com/users/geoadmin/received_events","type":"Organization","site_admin":false},"repo":{"id":12749462,"name":"deploy","full_name":"geoadmin/deploy","owner":{"login":"geoadmin","id":3369793,"avatar_url":"https://avatars.githubusercontent.com/u/3369793?v=3","gravatar_id":"","url":"https://api.github.com/users/geoadmin","html_url":"https://github.com/geoadmin","followers_url":"https://api.github.com/users/geoadmin/followers","following_url":"https://api.github.com/users/geoadmin/following{/other_user}","gists_url":"https://api.github.com/users/geoadmin/gists{/gist_id}","starred_url":"https://api.github.com/users/geoadmin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/geoadmin/subscriptions","organizations_url":"https://api.github.com/users/geoadmin/orgs","repos_url":"https://api.github.com/users/geoadmin/repos","events_url":"https://api.github.com/users/geoadmin/events{/privacy}","received_events_url":"https://api.github.com/users/geoadmin/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/geoadmin/deploy","description":"Deployment scripts","fork":false,"url":"https://api.github.com/repos/geoadmin/deploy","forks_url":"https://api.github.com/repos/geoadmin/deploy/forks","keys_url":"https://api.github.com/repos/geoadmin/deploy/keys{/key_id}","collaborators_url":"https://api.github.com/repos/geoadmin/deploy/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/geoadmin/deploy/teams","hooks_url":"https://api.github.com/repos/geoadmin/deploy/hooks","issue_events_url":"https://api.github.com/repos/geoadmin/deploy/issues/events{/number}","events_url":"https://api.github.com/repos/geoadmin/deploy/events","assignees_url":"https://api.github.com/repos/geoadmin/deploy/assignees{/user}","branches_url":"https://api.github.com/repos/geoadmin/deploy/branches{/branch}","tags_url":"https://api.github.com/repos/geoadmin/deploy/tags","blobs_url":"https://api.github.com/repos/geoadmin/deploy/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/geoadmin/deploy/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/geoadmin/deploy/git/refs{/sha}","trees_url":"https://api.github.com/repos/geoadmin/deploy/git/trees{/sha}","statuses_url":"https://api.github.com/repos/geoadmin/deploy/statuses/{sha}","languages_url":"https://api.github.com/repos/geoadmin/deploy/languages","stargazers_url":"https://api.github.com/repos/geoadmin/deploy/stargazers","contributors_url":"https://api.github.com/repos/geoadmin/deploy/contributors","subscribers_url":"https://api.github.com/repos/geoadmin/deploy/subscribers","subscription_url":"https://api.github.com/repos/geoadmin/deploy/subscription","commits_url":"https://api.github.com/repos/geoadmin/deploy/commits{/sha}","git_commits_url":"https://api.github.com/repos/geoadmin/deploy/git/commits{/sha}","comments_url":"https://api.github.com/repos/geoadmin/deploy/comments{/number}","issue_comment_url":"https://api.github.com/repos/geoadmin/deploy/issues/comments{/number}","contents_url":"https://api.github.com/repos/geoadmin/deploy/contents/{+path}","compare_url":"https://api.github.com/repos/geoadmin/deploy/compare/{base}...{head}","merges_url":"https://api.github.com/repos/geoadmin/deploy/merges","archive_url":"https://api.github.com/repos/geoadmin/deploy/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/geoadmin/deploy/downloads","issues_url":"https://api.github.com/repos/geoadmin/deploy/issues{/number}","pulls_url":"https://api.github.com/repos/geoadmin/deploy/pulls{/number}","milestones_url":"https://api.github.com/repos/geoadmin/deploy/milestones{/number}","notifications_url":"https://api.github.com/repos/geoadmin/deploy/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/geoadmin/deploy/labels{/name}","releases_url":"https://api.github.com/repos/geoadmin/deploy/releases{/id}","created_at":"2013-09-11T06:41:02Z","updated_at":"2014-05-20T06:45:15Z","pushed_at":"2015-04-08T19:32:06Z","git_url":"git://github.com/geoadmin/deploy.git","ssh_url":"git@github.com:geoadmin/deploy.git","clone_url":"https://github.com/geoadmin/deploy.git","svn_url":"https://github.com/geoadmin/deploy","homepage":null,"size":315,"stargazers_count":0,"watchers_count":0,"language":"Shell","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":0,"default_branch":"master"}},"base":{"label":"geoadmin:master","ref":"master","sha":"5f4fc710542eb217a3fd7101b4eaa5f260c8514b","user":{"login":"geoadmin","id":3369793,"avatar_url":"https://avatars.githubusercontent.com/u/3369793?v=3","gravatar_id":"","url":"https://api.github.com/users/geoadmin","html_url":"https://github.com/geoadmin","followers_url":"https://api.github.com/users/geoadmin/followers","following_url":"https://api.github.com/users/geoadmin/following{/other_user}","gists_url":"https://api.github.com/users/geoadmin/gists{/gist_id}","starred_url":"https://api.github.com/users/geoadmin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/geoadmin/subscriptions","organizations_url":"https://api.github.com/users/geoadmin/orgs","repos_url":"https://api.github.com/users/geoadmin/repos","events_url":"https://api.github.com/users/geoadmin/events{/privacy}","received_events_url":"https://api.github.com/users/geoadmin/received_events","type":"Organization","site_admin":false},"repo":{"id":12749462,"name":"deploy","full_name":"geoadmin/deploy","owner":{"login":"geoadmin","id":3369793,"avatar_url":"https://avatars.githubusercontent.com/u/3369793?v=3","gravatar_id":"","url":"https://api.github.com/users/geoadmin","html_url":"https://github.com/geoadmin","followers_url":"https://api.github.com/users/geoadmin/followers","following_url":"https://api.github.com/users/geoadmin/following{/other_user}","gists_url":"https://api.github.com/users/geoadmin/gists{/gist_id}","starred_url":"https://api.github.com/users/geoadmin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/geoadmin/subscriptions","organizations_url":"https://api.github.com/users/geoadmin/orgs","repos_url":"https://api.github.com/users/geoadmin/repos","events_url":"https://api.github.com/users/geoadmin/events{/privacy}","received_events_url":"https://api.github.com/users/geoadmin/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/geoadmin/deploy","description":"Deployment scripts","fork":false,"url":"https://api.github.com/repos/geoadmin/deploy","forks_url":"https://api.github.com/repos/geoadmin/deploy/forks","keys_url":"https://api.github.com/repos/geoadmin/deploy/keys{/key_id}","collaborators_url":"https://api.github.com/repos/geoadmin/deploy/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/geoadmin/deploy/teams","hooks_url":"https://api.github.com/repos/geoadmin/deploy/hooks","issue_events_url":"https://api.github.com/repos/geoadmin/deploy/issues/events{/number}","events_url":"https://api.github.com/repos/geoadmin/deploy/events","assignees_url":"https://api.github.com/repos/geoadmin/deploy/assignees{/user}","branches_url":"https://api.github.com/repos/geoadmin/deploy/branches{/branch}","tags_url":"https://api.github.com/repos/geoadmin/deploy/tags","blobs_url":"https://api.github.com/repos/geoadmin/deploy/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/geoadmin/deploy/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/geoadmin/deploy/git/refs{/sha}","trees_url":"https://api.github.com/repos/geoadmin/deploy/git/trees{/sha}","statuses_url":"https://api.github.com/repos/geoadmin/deploy/statuses/{sha}","languages_url":"https://api.github.com/repos/geoadmin/deploy/languages","stargazers_url":"https://api.github.com/repos/geoadmin/deploy/stargazers","contributors_url":"https://api.github.com/repos/geoadmin/deploy/contributors","subscribers_url":"https://api.github.com/repos/geoadmin/deploy/subscribers","subscription_url":"https://api.github.com/repos/geoadmin/deploy/subscription","commits_url":"https://api.github.com/repos/geoadmin/deploy/commits{/sha}","git_commits_url":"https://api.github.com/repos/geoadmin/deploy/git/commits{/sha}","comments_url":"https://api.github.com/repos/geoadmin/deploy/comments{/number}","issue_comment_url":"https://api.github.com/repos/geoadmin/deploy/issues/comments{/number}","contents_url":"https://api.github.com/repos/geoadmin/deploy/contents/{+path}","compare_url":"https://api.github.com/repos/geoadmin/deploy/compare/{base}...{head}","merges_url":"https://api.github.com/repos/geoadmin/deploy/merges","archive_url":"https://api.github.com/repos/geoadmin/deploy/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/geoadmin/deploy/downloads","issues_url":"https://api.github.com/repos/geoadmin/deploy/issues{/number}","pulls_url":"https://api.github.com/repos/geoadmin/deploy/pulls{/number}","milestones_url":"https://api.github.com/repos/geoadmin/deploy/milestones{/number}","notifications_url":"https://api.github.com/repos/geoadmin/deploy/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/geoadmin/deploy/labels{/name}","releases_url":"https://api.github.com/repos/geoadmin/deploy/releases{/id}","created_at":"2013-09-11T06:41:02Z","updated_at":"2014-05-20T06:45:15Z","pushed_at":"2015-04-08T19:32:06Z","git_url":"git://github.com/geoadmin/deploy.git","ssh_url":"git@github.com:geoadmin/deploy.git","clone_url":"https://github.com/geoadmin/deploy.git","svn_url":"https://github.com/geoadmin/deploy","homepage":null,"size":315,"stargazers_count":0,"watchers_count":0,"language":"Shell","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":0,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/geoadmin/deploy/pulls/1"},"html":{"href":"https://github.com/geoadmin/deploy/pull/1"},"issue":{"href":"https://api.github.com/repos/geoadmin/deploy/issues/1"},"comments":{"href":"https://api.github.com/repos/geoadmin/deploy/issues/1/comments"},"review_comments":{"href":"https://api.github.com/repos/geoadmin/deploy/pulls/1/comments"},"review_comment":{"href":"https://api.github.com/repos/geoadmin/deploy/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/geoadmin/deploy/pulls/1/commits"},"statuses":{"href":"https://api.github.com/repos/geoadmin/deploy/statuses/1f75af948b2a794dd5e6d50abf2a2782db2379c2"}}}}