feature: PostgreSQL support

[thibaultcha]

Support for database abstraction with Cassandra (original) and PostgreSQL (new). Related: #331, #883.

This 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.

For the new DAO, see:

• kong/dao/factory.lua
• kong/dao/dao.lua
• kong/dao/postgres_db.lua
• kong/dao/cassandra_db.lua

The 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.

How to test it (if you want to try it out or just a glimpse of how to use it):

• Get your hands on a 9.4+ server
• Create a database, (maybe a user too?), let's say kong
• Use the feature/postgres branch of Kong (you must have a source install for this)
• Update your Kong configuration:

# be careful about your YAML formatting
database: postgres
postgres:
  host: "127.0.0.1"
  port: 5432
  user: kong
  password: kong
  database: kong

As usual, migrations should run from kong start, but as a reminder and just in case, here are some tips:

Reset the database with (careful, you'll lose all data):

$ kong migrations reset --config kong.yml

Run the migrations manually with:

$ kong migrations up --config kong.yml

If needed, list your migrations for debug purposes with:

$ kong migrations list --config kong.yml

If you test it, feel free to report any issues :)

[nijikokun]

💥 👏 👏 🎉

[codebien]

How can we transfer data from cassandra to postgresql? Is there a builtin tool or we need to create it?

[thibaultcha]

Sadly not, we do not provide a way to migrate from one to the other. Sorry!

[subnetmarco]

@codebien but migration could still be done by leveraging the API.

[jakehow]

👏

[samgaw]

I know you've been working on this for awhile @thibaultcha. Really appreciate the effort! 👍

[chrisanderton]

@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.

Firstly - on the heartbeat, the ttl is missing - so it never gets updated:

--- a/kong/core/cluster.lua
+++ b/kong/core/cluster.lua
@@ -79,7 +79,7 @@ local function send_keepalive(premature)
       ngx.log(ngx.ERR, tostring(err))
     elseif #nodes == 1 then
       local node = nodes[1]
-      local _, err = singletons.dao.nodes:update(node, node)
+      local _, err = singletons.dao.nodes:update(node, node, {ttl = 3600})
       if err then
         ngx.log(ngx.ERR, tostring(err))
       end

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:

https://github.com/Mashape/kong/blob/next/kong/dao/postgres_db.lua#L230

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)

It should be using the existing expiry_at as the base time for the new TTL.

I haven't figured out how to patch that yet as my Lua skills are weak.

[thibaultcha]

Thanks for investigating this. @thefosk implemented the TTL part and will review this.

On Mar 31, 2016, at 7:35 AM, Chris Anderton [email protected] wrote:

@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.

Firstly - on the heartbeat, the ttl is missing - so it never gets updated:

--- a/kong/core/cluster.lua
+++ b/kong/core/cluster.lua
@@ -79,7 +79,7 @@ local function send_keepalive(premature)
ngx.log(ngx.ERR, tostring(err))
elseif #nodes == 1 then
local node = nodes[1]

•  local _, err = singletons.dao.nodes:update(node, node)
  

•  local _, err = singletons.dao.nodes:update(node, node, {ttl = 3600})
  

  if err then
  ngx.log(ngx.ERR, tostring(err))
  end
  `

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:

https://github.com/Mashape/kong/blob/next/kong/dao/postgres_db.lua#L230

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)

It should be using the existing expiry_at as the base time for the new TTL.

I haven't figured out how to patch that yet as my Lua skills are weak.
—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub

[subnetmarco]

@chrisanderton

Firstly - on the heartbeat, the ttl is missing - so it never gets updated

Good catch.

The second issue is that the TTL is never updated

Let me look into this, your reasoning seems accurate.

I will push the fixes and a couple more tests for the update bug.

[subnetmarco]

Okay, I have been looking at the code and the ttl was intentionally omitted in the update, since we are already setting one during the insert: https://github.com/Mashape/kong/blob/release/0.8.0/kong/cli/services/serf.lua#L160-L163

Updating an entity without specifying the TTL should not override any TTL previously set at insertion time, so I am looking into this now.

[subnetmarco]

Forget about my last comment - The issue you reported is correct, and the bug was introduced because Cassandra renews the TTL every time an update operation is being executed (even if the TTL is not being specified again), while with Postgres we need to manually renew it every time. So both your considerations are correct.