bruzen/modex — pull request #10 by kousu (2014-09-07 00:05:20+00:00)
Title: Databased

RAII fixes for the databased branch:

Things now shutdown and remove themselves and clean up properly in every possible process death scenario I can think of: websocket.close or closed browser window, a ctrl-c, a kill or even (eventually) a kill -9 on the server all cause the replication to that client to silence itself and stop wasting CPU, and *all* clients leaving even removes the trigger so that postgres doesn't have to waste cycles thinking about not broadcasting changes.

This is a good place to merge since it represents a level of reliability that I feel makes this usable in production.

Also, server.sh and client.sh and replicate.py all can be pointed at a given database on the command line, and there's an init.sh script to match which installs the replication hooks into postgres. Perhaps still need to make a couple of tweaks to make switching between the ./server.sh dev postgres and a production postgres painless:
* use PGDATABASE, PGHOST, PGPORT, PGUSER instead of PGDATA; standard postgres deployments use a single postgres instfance listening on tcp:0.0.0.0:5432 and unix:/run/postgresql/.s.PGSQL.5432, containing multiple databases containing multiple tables, in contrast to the dev server which also listens on the TCP port (which will conflict with any previously existing postgres!) and on unix:./data/.s.PGSQL.5432 (which won't conflict, but also is hardcoded and not findable by default)
* make sure replicate.py uses a user account while init.sh uses an administrator account (or at least make instructions about how to do this very clear)

Note to self:
Next steps:
* stick the my_pg_replicate_* things into a truly different namespaces rather than prefixing their names (postgres has something called tablespaces which sound promiseing for this!)
* see how postgis gets along with replicate.pysql; what does changing a geometry column look like?
* start building filtered replication and an API that maps javascript query ops to postgres query ops; requires extending replicate.pysql's notion of a "stream" to include select and where clauses
* write/find geographic query ops and map them too, if postgis plays nice with what I have so far
* try setting up a WAL hot standby and see if you can install the hooks there; if so, this might be the safest way to use replicate.pysql, since it avoids possibly crashing or locking up the main database