Streaming replication from PostgreSQL to the DNS

2016-12-23 - Future

This entry is backdated - I'm writing this one year after I made this experimental prototype.

Our current DNS update mechanism runs as an hourly batch job. It would be nice to make DNS changes happen as soon as possible.

user interface matters

Instant DNS updates have tricky implications for the user interface.

At the moment it's possible to make changes to the database in between batch runs, knowing that broken intermediate states don't matter, and with plenty of time to check the changes and make sure the result will be OK.

If the DNS is updated immediately, we need a way for users to be able to prepare a set of inter-related changes, and submit them to the database as a single transaction.

(Aside: I vaguely imagine something like a shopping-cart UI that's available for collecting more complicated changes, though it should be possible to submit simple updates without a ceremonial transaction.)

This kind of UI change is necessary even is we simply run the current batch process more frequently. So we can't reasonalbly deploy this without a lot of front-end work.

back-end experiments

Ideally I would like to keep the process of exporting the database to the DNS and DHCP servers as a purely back-end matter; the front-end user interface should only be a database client.

So, assuming we have a better user interface, we would like to be able to get instant DNS updates by improvements to the back end without any help from the front end.

PostgreSQL has a very tempting replication feature called "logical decoding", which takes a replication stream and turns it into a series of database transactions. You can write a logical decoding plugin which emits these transactions in whatever format you want.

With logical decoding, we can (with a bit of programming) treat the DNS as a PostgreSQL replication target, with a script that looks something like pg_recvlogical | nsupdate.

I wrote a prototype along these lines, which is published at https://git.uis.cam.ac.uk/x/uis/ipreg/pg-decode-dns-update.git

status of this prototype

The plugin itself works in a fairly satisfactory manner.

However it needs a wrapper script to massage transactions before they are fed into nsupdate, mainly to split up very large transactions that cannot fit in a single UPDATE request.

The remaining difficult work is related to starting, stopping, and pausing replication without losing transactions. In particular, during initial deployment we need to be able to pause replication and verify that the replicated updates are faithfully reproducing what the batch update would have done. We can use the same pause/batch/resume mechanism to update the parts of the DNS that are not maintained in the database.

At the moment we are not doing any more work in this area until the other prerequisites are in place.