[Kea-users] Host reservations in PostgreSQL (was Host reservations in DB)

[Tomek Mrugalski]

On 29.12.2015 16:59, Shane Kerr wrote:
>>> It's not clear for me how Kea stores address reservations of hosts.
>>> There is something about it in 1.0.0-beta release notes "Leases and
>>> (DHCPv4)host reservations stored in MySQL or Postgres".  
>> Does it say MySQL or Postgres? Uh oh, in this case it's a mistake.
>> Storing host reservations is supported in MySQL only.
> 
> What's the reason for this? Is it just that the schema for PostgreSQL
> hasn't been devised yet? Or are there MySQL-only features that are used
> (perhaps specific functions or data structures)?
The reason for this disparity is simply a lack of time. We had to
release 1.0 in 2015 and we decided that adding just partial (mysql, v4
only) reservation is better than no DB support in reservations at all.

We originally didn't plan to include this in 1.0, but Adam, who is
working on his master thesis, completed several reviews for his MySQL
code. The code was almost ready, so I decided to spend couple evenings
and make it usable.

It's not just schema, it's much more than that. There's a new class
called MySQLHostDataSource that implements reservations storage in
MySQL. It's reasonably simple, but there's fair amount of code in it.
For performance reasons, we use compiled statements, so there's code for
managing that. Also, DHCPv6 allows more than one address/prefix per
host, so it's going to be stored in a separate table with host_id being
the primary/foreign key. It's to be determined, whether we need one
complex or two simple queries for it.

> I ask because if it's a schema issue then I'm happy to attempt this,
> because I kind of want this for my environment. :) (I could switch to
By all means, please do! Updating the schema is the first step to add
this support. Take a look at the schema init and upgrade scripts in
src/bin/admin/scripts/pgsql. We do have a proper design for this
feature: http://kea.isc.org/wiki/HostReservationDesign. It would be
highly appreciated if the patch also covered unit-tests :)

> MySQL, but I'd prefer to avoid that since I have a working setup and
> also I would have to figure out which of the 32 forks of MySQL to
> use....) ;)
Acknowledged. There are users who prefer MySQL, so we decided to keep
supporting both. Also, as part of the 1.0 post mortem discussions, we
decided to aim for MySQL/PostgreSQL parity. I admit that we neglected
its development a bit, but we'll get it back in shape soon.

We're still in the 1.1 planning, but it's very likely that finishing
IPv6 support in MySQL and adding reservations is Postgres will be part
of the next release after 1.0.

Tomek