{"action":"created","issue":{"url":"https://api.github.com/repos/Mashape/kong/issues/1054","repository_url":"https://api.github.com/repos/Mashape/kong","labels_url":"https://api.github.com/repos/Mashape/kong/issues/1054/labels{/name}","comments_url":"https://api.github.com/repos/Mashape/kong/issues/1054/comments","events_url":"https://api.github.com/repos/Mashape/kong/issues/1054/events","html_url":"https://github.com/Mashape/kong/pull/1054","id":139445868,"number":1054,"title":"feature: PostgreSQL support","user":{"login":"thibaultCha","id":1125431,"avatar_url":"https://avatars.githubusercontent.com/u/1125431?v=3","gravatar_id":"","url":"https://api.github.com/users/thibaultCha","html_url":"https://github.com/thibaultCha","followers_url":"https://api.github.com/users/thibaultCha/followers","following_url":"https://api.github.com/users/thibaultCha/following{/other_user}","gists_url":"https://api.github.com/users/thibaultCha/gists{/gist_id}","starred_url":"https://api.github.com/users/thibaultCha/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thibaultCha/subscriptions","organizations_url":"https://api.github.com/users/thibaultCha/orgs","repos_url":"https://api.github.com/users/thibaultCha/repos","events_url":"https://api.github.com/users/thibaultCha/events{/privacy}","received_events_url":"https://api.github.com/users/thibaultCha/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2016-03-09T01:29:29Z","updated_at":"2016-03-31T16:37:31Z","closed_at":"2016-03-14T19:56:38Z","pull_request":{"url":"https://api.github.com/repos/Mashape/kong/pulls/1054","html_url":"https://github.com/Mashape/kong/pull/1054","diff_url":"https://github.com/Mashape/kong/pull/1054.diff","patch_url":"https://github.com/Mashape/kong/pull/1054.patch"},"body":"Support for database abstraction with Cassandra (original) and PostgreSQL (new). Related: #331, #883.\r\n\r\nThis implements a new DAO, which can interface with both of those databases. At the same time, it tries to keep changes relatively backwards-compatible so changes to existing plugins (in this repo or custom ones) are relatively small.\r\n\r\nFor the new DAO, see:\r\n- `kong/dao/factory.lua`\r\n- `kong/dao/dao.lua`\r\n- `kong/dao/postgres_db.lua`\r\n- `kong/dao/cassandra_db.lua`\r\n\r\nThe underlying `*_db` implementations try to take advantages of each database's features. As such, the Postgres implementation relies on SQL's FOREIGN constraints and CASCADE deletion. As a trade-off between optimal SQL support and backwards-compatibility, Postgres' schema is as close as possible as Cassandra's.\r\n\r\n###### How to test it (if you want to try it out or just a glimpse of how to use it):\r\n- Get your hands on a 9.4+ server\r\n- Create a database, (maybe a user too?), let's say `kong`\r\n- Use the `feature/postgres` branch of Kong (you must have a source install for this)\r\n- Update your Kong configuration:\r\n```yaml\r\n# be careful about your YAML formatting\r\ndatabase: postgres\r\npostgres:\r\n  host: \"127.0.0.1\"\r\n  port: 5432\r\n  user: kong\r\n  password: kong\r\n  database: kong\r\n```\r\n\r\nAs usual, migrations should run from `kong start`, but as a reminder and just in case, here are some tips:\r\n\r\nReset the database with (careful, you'll lose all data):\r\n```\r\n$ kong migrations reset --config kong.yml\r\n```\r\n\r\nRun the migrations manually with:\r\n```\r\n$ kong migrations up --config kong.yml\r\n```\r\n\r\nIf needed, list your migrations for debug purposes with:\r\n```\r\n$ kong migrations list --config kong.yml\r\n```\r\n\r\nIf you test it, feel free to report any issues :)"},"comment":{"url":"https://api.github.com/repos/Mashape/kong/issues/comments/204012810","html_url":"https://github.com/Mashape/kong/pull/1054#issuecomment-204012810","issue_url":"https://api.github.com/repos/Mashape/kong/issues/1054","id":204012810,"user":{"login":"thibaultCha","id":1125431,"avatar_url":"https://avatars.githubusercontent.com/u/1125431?v=3","gravatar_id":"","url":"https://api.github.com/users/thibaultCha","html_url":"https://github.com/thibaultCha","followers_url":"https://api.github.com/users/thibaultCha/followers","following_url":"https://api.github.com/users/thibaultCha/following{/other_user}","gists_url":"https://api.github.com/users/thibaultCha/gists{/gist_id}","starred_url":"https://api.github.com/users/thibaultCha/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/thibaultCha/subscriptions","organizations_url":"https://api.github.com/users/thibaultCha/orgs","repos_url":"https://api.github.com/users/thibaultCha/repos","events_url":"https://api.github.com/users/thibaultCha/events{/privacy}","received_events_url":"https://api.github.com/users/thibaultCha/received_events","type":"User","site_admin":false},"created_at":"2016-03-31T16:37:31Z","updated_at":"2016-03-31T16:37:31Z","body":"Thanks for investigating this. @thefosk implemented the TTL part and will review this. \n\n> On Mar 31, 2016, at 7:35 AM, Chris Anderton <notifications@github.com> wrote:\n> \n> @thibaultCha i've been experimenting with Postgres and found an issue where the nodes disappear after one hour - i've traced it through to the TTL functionality.\n> \n> Firstly - on the heartbeat, the ttl is missing - so it never gets updated:\n> \n> --- a/kong/core/cluster.lua\n> +++ b/kong/core/cluster.lua\n> @@ -79,7 +79,7 @@ local function send_keepalive(premature)\n>        ngx.log(ngx.ERR, tostring(err))\n>      elseif #nodes == 1 then\n>        local node = nodes[1]\n> -      local _, err = singletons.dao.nodes:update(node, node)\n> +      local _, err = singletons.dao.nodes:update(node, node, {ttl = 3600})\n>        if err then\n>          ngx.log(ngx.ERR, tostring(err))\n>        end\n> `\n> \n> The second issue is that the TTL is never updated - the stored procedure is called to perform the update, but the expiry never moves - this is because of a bug i think - here:\n> \n> https://github.com/Mashape/kong/blob/next/kong/dao/postgres_db.lua#L230\n> \n> The base time for the new TTL is the created_at date of the node - but this will never change - so adding 1 hour to the created_at date will always result in the same time (as created_at is set once)\n> \n> It should be using the existing expiry_at as the base time for the new TTL. \n> \n> I haven't figured out how to patch that yet as my Lua skills are weak.\n> —\n> You are receiving this because you were mentioned.\n> Reply to this email directly or view it on GitHub\n> \n"}}