Hi Sam,
Thanks for the looking - I think I looked at couch_tap a while ago but
as our documents are quite complicated trying to fit them into a
structured schema would require a lot of work. With the new json
datatypes in postgres you dont have to.
I did do a simple dump script with php:
https://github.com/sysadminmike/couch-to-postgres-php-dump
But node was much faster and used the _changes feed as opposed to doing
a comparison between the databases and due to is async nature it doesnt
block - load on the machine was much lower and memory use with multiple
streams stays constant.
I have started work on a daemon similar to the _replicator part of couch
allowing you to add remove couchdb to watch - without having a separate
process for each one database you want to stream from - I have fleshed a
fair bit of it out like reconnecting if postgres/couch disappears and
restarting streams - plus a basic api to query whats happening.
Mike.
Samuel Lown wrote:
Mike,
Interesting approach. I'll pass it along to some of my colleagues.
At Cabify we took a slightly different approach as we wanted to be able to
perform relational analysis of the data. We developed a Ruby app that
listens to the changes feed and synchronises with structured tables using
SQL.
Here's our code:
https://github.com/samlown/couch_tap
We haven't had chance yet to finish the documentation, so we've never
published it.
Cheers,
sam
On 16 December 2014 at 11:43, Mike <[email protected]> wrote:
Hi Everyone,
We switch from Postgres to Couch for our main datasource about 5 years
ago. One of the main reasons was for master/master replication our data
suites the using a schema less model.
Ever since the switch I have always wanted to be able use sql to query the
data as opposed to writing add hoc scripts to put csv/xls reports
together. With all the json/jsonb stuff happening with postgres recently I
decided to scratched that itch last week with:
Node libary to stream CouchDB changes into PostgreSQL with a simple client
example.
https://github.com/sysadminmike/couch-to-postgres/
It is fairly simple to get going and start running SELECT queries on the
docs in your couchdb. It is also possible to use your couch views as
tables. It provides something similar Elastic Search river plugin but for
Postgres.
By adding a few some extra bits allows not only for SELECT queries on the
data but also UPDATE/INSERTS/(DELETES todo) on your couchdb docs within
Postgres.
Thanks for reading,
Mike.
