couch-to-postgres

KO | EN

gitlite — search

#javascript #python #hacktoberfest #react #ai #typescript #llm #go #golang #android #machine-learning #rust #deep-learning #linux

couch-to-postgres

(★ 114)

Node libary to stream CouchDB changes into PostgreSQL

couch-to-postgres Latest Version Download

Download Latest Version (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

couch-to-postgres  /  pgcouch / couchpg / couchgres / postcouch
=================

Node libary to stream CouchDB changes into PostgreSQL with a simple client example.  Based on https://github.com/orchestrate-io/orchestrate-couchdb.

By adding a few some extra bits allows not only for SELECT queries on the data but also UPDATE/INSERT/DELETE on your couchdb docs within Postgres.  It is also possible to use your couch views as tables.

Basically it allows postgres to use couchdb as its datastore - sort of like a Foreign Data Wrapper https://wiki.postgresql.org/wiki/Foreign_data_wrappers eg couchdb_fdw - but has a near realtime copy of records in postgres.

For example:

Add a doc to a couch

curl -X PUT http://192.168.3.21:5984/example/1234567 -d '{"myvar":"foo"}'
      {"ok":true,"id":"1234567","rev":"1-d3747a58baa817834a21ceeaf3084c41"}

See it in postgres:

postgresdb=> SELECT id, doc FROM example WHERE id='1234567';

id    |                                       doc                                        
      ---------+----------------------------------------------------------------------------------
       1234567 | {"_id": "1234567", "_rev": "1-d3747a58baa817834a21ceeaf3084c41", "myvar": "foo"}
      (1 row)

Update a doc using postgres:

postgresdb=> UPDATE example 
      postgresdb-> SET doc=json_object_set_key(doc::json, 'myvar'::text, 'bar'::text)::jsonb, from_pg=true 
      postgresdb-> WHERE id='1234567';
      DEBUG:  pgsql-http: queried http://192.168.3.21:5984/example/1234567
      CONTEXT:  SQL statement "SELECT headers FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text)"
      PL/pgSQL function couchdb_put() line 9 at SQL statement
      UPDATE 0

The couchdb_put function needs some more work.

See it in couch:

curl -X GET http://192.168.3.21:5984/example/1234567 
        {"_id":"1234567","_rev":"2-b9f4c54fc36bdeb78c31590920c9751b","myvar":"bar"}

And in postgres:

postgresdb=> SELECT id, doc FROM example WHERE id='1234567';
         id    |                                       doc                                        
      ---------+----------------------------------------------------------------------------------
       1234567 | {"_id": "1234567", "_rev": "2-b9f4c54fc36bdeb78c31590920c9751b", "myvar": "bar"}
      (1 row)

Add a doc using postgres

postgresdb=> INSERT INTO example (id, doc, from_pg) VALUES ('7654321', json_object('{_id,myvar}','{7654321, 100}')::jsonb, true);
      DEBUG:  pgsql-http: queried http://192.168.3.21:5984/example/7654321
      CONTEXT:  SQL statement "SELECT headers FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text)"
      PL/pgSQL function couchdb_put() line 9 at SQL statement
      INSERT 0 0

See it in couch

curl -X GET http://192.168.3.21:5984/example/7654321 
      {"_id":"7654321","_rev":"1-08343cb32bb0903348c0903e574cfbd0","myvar":"100"}

Update doc created postgres with couch

curl -X PUT http://192.168.3.21:5984/example/7654321 -d '{"_id":"7654321","_rev":"1-08343cb32bb0903348c0903e574cfbd0","myvar":"50"}'
      {"ok":true,"id":"7654321","rev":"2-5057c4942c6b92f8a9e2c3e5a75fd0b9"

See it in postgres

SELECT id, doc FROM example WHERE id='1234567';
         id    |                                       doc                                        
      ---------+----------------------------------------------------------------------------------
       1234567 | {"_id": "1234567", "_rev": "2-b9f4c54fc36bdeb78c31590920c9751b", "myvar": "bar"}
      (1 row)

Add some more docs

INSERT INTO example (id, doc, from_pg) VALUES ('test1', json_object('{_id,myvar}','{test1, 100}')::jsonb, true);
      INSERT INTO example (id, doc, from_pg) VALUES ('test2', json_object('{_id,myvar}','{test2, 50}')::jsonb, true);
  
or

curl -X PUT http://192.168.3.21:5984/example/test3 -d '{"_id":"test3", "myvar":"100"}'
      curl -X PUT http://192.168.3.21:5984/example/test4 -d '{"_id":"test4", "myvar":"50"}'
      curl -X PUT http://192.168.3.21:5984/example/test5 -d '{"_id":"test5", "myvar":"70"}'
      curl -X PUT http://192.168.3.21:5984/example/test6 -d '{"_id":"test6", "myvar":"20"}'
      curl -X PUT http://192.168.3.21:5984/example/test7 -d '{"_id":"test7", "myvar":"10"}'

Do a query on the docs

SELECT id, doc->'myvar' AS myvar FROM example 
      WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) > 50
      ORDER BY myvar
      
        id   | myvar 
      -------+-------
       test3 | "100"
       test1 | "100"
       test5 | "70"
      (3 rows)

Update some of the docs

UPDATE example 
     SET doc=json_object_set_key(
            doc::json, 'myvar'::text, (CAST(doc->>'myvar'::text AS numeric) + 50)::text
         )::jsonb,
         from_pg=true  
     WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) < 60
 
 Peform same query 
 
    SELECT id, doc->'myvar' AS myvar FROM example 
    WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) > 50
    ORDER BY myvar

Initially I didnt spot the above order being wrong so you need to be careful.

SELECT id, CAST(doc->>'myvar' AS numeric) as myvar FROM example 
      WHERE id LIKE 'test%' AND CAST(doc->>'myvar' AS numeric) > 50
      ORDER BY myvar, doc->>'_id'

And finally in couch

curl -s -X POST '192.168.3.21:5984/example/_temp_view?include_docs=false' -H 'Content-Type: application/json' \
     -d '{"map":"function(doc) { emit(doc._id, doc.myvar) };"}'  
     {"total_rows":7,"offset":0,"rows":[
     {"id":"test1","key":"test1","value":"100"},
     {"id":"test2","key":"test2","value":"100"},
     {"id":"test3","key":"test3","value":"100"},
     {"id":"test4","key":"test4","value":"100"},
     {"id":"test5","key":"test5","value":"70"},
     {"id":"test6","key":"test6","value":"70"},
     {"id":"test7","key":"test7","value":"60"}
     ]}
 
It is also possible to use a couchdb view as a table:

The couch design doc:

{
      "_id": "_design/mw_views",
      "language": "javascript",
      "views": {
          "by_feedName": {
          "map": "function(doc) { emit(doc.feedName,null); }",
          "reduce": "_count"
        },
        "by_tags": {
          "map": "function(doc) { for(var i in doc.tags) { emit (doc.tags[i],null);  } }",
          "reduce": "_count"
        }
     }
    }
 
 
    WITH by_feedname_reduced AS (
      SELECT * FROM json_to_recordset(
        (
         SELECT  (content::json->>'rows')::json  
         FROM http_get('http://192.168.3.23:5984/articles/_design/mw_views/_view/by_feedName?group=true'))
        ) AS x (key text, value text)
    )

SELECT * FROM by_feedname_reduced WHERE value::numeric > 6000 ORDER BY key 
 
 This takes under a second to run but the initial build of the view takes about 20 mins for a fresh copy of the couchdb.
 
 The equivilent query using the the data in postgres 
 
    WITH tbl AS (
        SELECT DISTINCT doc->>'feedName' as key, COUNT(doc->>'feedName') AS value 
        FROM articles
        GROUP BY doc->>'feedName'
    )
    SELECT key, value FROM tbl WHERE value > 6000 ORDER BY key;
 
 This takes over 4 seconds.
 
 
 
 
 Testing with my articles database from birdreader - https://github.com/glynnbird/birdreader 
 
    curl -X GET http://localhost:5984/articles
    {"db_name":"articles","doc_count":63759,"doc_del_count":2,"update_seq":92467,"purge_seq":0,"compact_running":false,"disk_size":151752824,"data_size":121586165,"instance_start_time":"1418686121041424","disk_format_version":6,"committed_update_seq":92467}
 
 
    SELECT DISTINCT jsonb_object_keys(doc) AS myfields
    FROM articles ORDER BY myfields

This queries all of the documents and retrieves the couch documents fields.

On another couch database with a 'type' field for different doc types stored in the same database - about 70k docs.

SELECT DISTINCT doc->>'type' as doctype, count(doc->>'type')
    FROM mytable GROUP BY doctype ORDER BY doctype

Takes under a second.

SELECT DISTINCT doc->>'type' as doctype, jsonb_object_keys(doc) AS myfields
    FROM mytable
    ORDER BY doctype , myfields;
    
With no indexes the above query takes just over 10 secs.  I have made no indexes or adjustments to the default FreeBSD postgresql94-server-9.4.r1 port.

----------

Example setup and postgres configuration
    
    git clone git@github.com:sysadminmike/couch-to-postgres.git

Get needed modules:

npm install

Edit ./bin/index.js to suite your settings:

var settings =
          {
            couchdb: {
             url: 'http://192.168.3.21:5984',
             pgtable:  'example',
             database: 'example'
           }
          };

pgclient = new pg.Client("postgres://mike@localhost/pgdatabase");

Before starting it up create the since_checkpoints table

CREATE TABLE since_checkpoints
	(
	  pgtable text NOT NULL,
	  since numeric DEFAULT 0,
	  enabled boolean DEFAULT false, --not used in the simple client example
	  CONSTRAINT since_checkpoint_pkey PRIMARY KEY (pgtable)
	)

This table is used to store the checkpoint for the database(s) being synced something akin to the couchdb _replicator database.

Create the table to store the couch docs:

CREATE TABLE example
    (
      id text NOT NULL,
      doc jsonb,
      CONSTRAINT example_pkey PRIMARY KEY (id)
    )

Start watching changes

./bin/index.js

It will add a record to the since_checkpoints table and begin syncing.

At this point you can now perform SELECT queries the docs within postgres as in the above example.  This should be fine to use against a production couchdb as it makes no changes to and is performing the same tasks as the elastic search river plugin.  With a bit of copy/pasting it is possible to use sql to create simple scripts or one liners to run in a shell with curl.

Also take a look at /bin/daemon.js and https://github.com/sysadminmike/couch-to-postgres/blob/master/daemon-README.md

-------

To handle UPDATE/INSERT/DELETE more configuration is required.  Note this is still experimental so I wouldnt point this at any production data.

First install the postgres extension pgsql-http  at https://github.com/pramsey/pgsql-http

See note about pgsql-http module install if you not sure how to install a postgres extension - note this has just been updated to handle PUT and DELETE requests - i have not yet had a chane to test anything i have done on this page with the new version but will try to shortly but think any reference to http_post need updating as the new version specifies:

http_post(uri VARCHAR, content VARCHAR, content_type VARCHAR)
	
But on this page I am using the old one:

http_post(url VARCHAR, params VARCHAR, data VARCHAR, contenttype VARCHAR DEFAULT NULL)
	
So please bear this in mind if setting this up.

Then add it in the database you want to use:

CREATE EXTENSION http

If you havent already done it:

CREATE TABLE since_checkpoints   
    (
      pgtable text NOT NULL,
      since numeric DEFAULT 0,
      enabled boolean DEFAULT false,
      CONSTRAINT since_checkpoint_pkey PRIMARY KEY (pgtable)
    );

Add function to put data into couchdb:

CREATE OR REPLACE FUNCTION couchdb_put() RETURNS trigger AS $BODY$
    DECLARE
        RES RECORD;
    BEGIN
     IF (NEW.from_pg) IS NULL THEN
       RETURN NEW;
     ELSE 
       
       SELECT status FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text) INTO RES;

--Need to check RES for response code
       --RAISE EXCEPTION 'Result: %', RES;
       RETURN null;
     END IF;
    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE

Add function to modify fields inside the PostgreSQL JSON datatype - from: http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype

CREATE OR REPLACE FUNCTION json_object_set_key(json json, key_to_set text, value_to_set anyelement)
      RETURNS json AS
    $BODY$
    SELECT COALESCE(
      (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
         FROM (SELECT *
                 FROM json_each("json")
                WHERE "key" <> "key_to_set"
                UNION ALL
               SELECT "key_to_set", to_json("value_to_set")) AS "fields"),
      '{}'
    )::json
    $BODY$
      LANGUAGE sql IMMUTABLE STRICT;

Create table to hold the docs

CREATE TABLE example
    (
      id text NOT NULL,
      doc jsonb,
      from_pg boolean, -- for trigger nothing stored here
      CONSTRAINT example_pkey PRIMARY KEY (id)
    );

Create trigger to stop data being inserted into the table from sql and send off to couch instead

CREATE TRIGGER add_doc_to_couch 
    BEFORE INSERT OR UPDATE 
    ON example FOR EACH ROW EXECUTE PROCEDURE couchdb_put();

Note: All queries in postgres must have "from_pg=true" for inserts and updates or the postgres will send the data to the table and not send it to couch.

I plan to reverse this logic and make the libary include this so it will be possible to issue inserts/updates and exclude this field.

You can now start the node client and give it a test.

-----

A few variable to tune in ./lib/index.js need to move to config options

In checkpoint_changes function:

ckwait = 3 * 1000;

This is how often the stream is checkpointed when the stream is active. I would adjust this depending on how busy you couchdb is.  When the stream is idle this increases to 10 secs.

In startFollowing function there is:
     // The inactivity timer is for time between *changes*, or time between the
     // initial connection and the first change. Therefore it goes here.
     stream.inactivity_ms = 30000;

Maybe use NOTIFY and have node client LISTEN for a message when postgres calls couchdb_put() for the first time (can you do a timer in postgres?? or node will get notified about every update and only needs a wake up after idle time).

-----

Performance wise compared to the php dumping script

On a test with a couchdb of about 150Mb with 65k docs the node libary complete working through _changes in about 17 minutes to add all the docs to an empty table and then keeps it in sync.

The couch-to-postgres-php-dumper script - https://github.com/sysadminmike/couch-to-postgres-php-dump takes about 28 minutes for the initial sync and 11 secs for a resync.

Replicating the same db from one jail running couch to another couch jail on the same machine as a baseline takes just over 8 minutes:

{"session_id":"661411f2137c64efc940f55b802dc35b","start_time":"Tue, 16 Dec 2014 17:00:05 GMT","end_time":"Tue, 16 Dec 2014 17:08:10 GMT","start_last_seq":0,"end_last_seq":92862,"recorded_seq":92862,"missing_checked":63840,"missing_found":63840,"docs_read":63840,"docs_written":63840,"doc_write_failures":0}
    
Looking at top I think postgres is waiting on the disk most of the time rather than the process being cpu bound - the single php process calling curl for each doc was hitting the cpu hard and couldnt be used as a solution for huge databases or have the ability to deal with more than one db at once.

On further testing with some dogey postgres conf settings:

fsync = off
    synchronous_commit = off
    
As postgres is not the primary datastore its ok if the data dies considering a full rebuild now is under 2 mins:

mike:~/postgres-couch/couch-to-postgres-test % time ./bin/index.js
	articlespg: {"db_name":"articlespg","doc_count":63838,"doc_del_count":2,"update_seq":63840,"purge_seq":0,"compact_running":false,"disk_size":242487416,"data_size":205414817,"instance_start_time":"1418749205916149","disk_format_version":6,"committed_update_seq":63840}
	Connected to postgres
	articlespg: initial since=0
	articlespg: Starting checkpointer
	articlespg: Checkpoint set to 7180 next check in 3 seconds
	articlespg: Checkpoint set to 9344 next check in 3 seconds
	articlespg: Checkpoint set to 11536 next check in 3 seconds
	...
	articlespg: Checkpoint set to 60920 next check in 3 seconds
	articlespg: Checkpoint set to 63636 next check in 3 seconds
	articlespg: Checkpoint set to 63840 next check in 3 seconds
	articlespg: Checkpoint 63840 is current next check in: 10 seconds
	^C45.919u 3.226s 1:42.10 48.1%  10864+321k 158+0io 0pf+0w
	mike:~/postgres-couch/couch-to-postgres-test %

So down to well under 2 minutes now todo the initial sync of the same test db - so 4 times faster than a native couch to couch sync.  I think this is faster than Elastic search river doing a similar task.

Snippet from top while it was syncing:

PID USERNAME    THR PRI NICE   SIZE    RES STATE   C   TIME    WCPU COMMAND
	57635 mike          6  45    0   621M 66064K uwait   1   0:25  50.78% node
	57636     70        1  36    0   186M 97816K sbwait  1   0:11  22.75% postgres
	44831    919       11  24    0   181M 30048K uwait   0  67:28  20.51% beam.smp
	23891    919       11  20    0   232M 69168K uwait   0  26:22   0.39% beam.smp
	57624     70        1  20    0   180M 17840K select  0   0:00   0.29% postgres
	57622     70        1  21    0   180M 65556K select  1   0:00   0.20% postgres

-----

Possible ways to deploy - master-master postgres setup using couchdbs primary data store and setting up replication between all locations using Postgres and Couch as a pair.

Location 1                              Location 2
     Postgres == CouchDB ---------- CouchDB == Postgres
                         \        /
                          \      /
                           \    /
                            \  /
                             \/    
                         Location 3
                          CouchDB
                            ||
                          Postgres  
                               
     Where === is the node client keeping the paired postgres up to date
     And ----- is couchdb performing replication

-----

IDEAS/TODOS - Comments most welcome.

How to do bulk updates:

WITH new_docs AS (
      SELECT json_object_set_key(doc::json, 'test'::text, 'Couch & Postgres are scool'::text)::jsonb AS docs
      FROM articlespg
    ),
    agg_docs AS (
      SELECT json_agg(docs) AS aggjson FROM new_docs
    )

SELECT headers FROM 
      http_post('http://192.168.3.21:5984/articlespg/_bulk_docs', '',
      '{"all_or_nothing":true, "docs":' || (SELECT * FROM agg_docs) || '}',
      'application/json'::text) ;

I tried on the articles test db i am using and it was very fast for an update to < 100 rows
I then tried to update all docs and crashed couch

DEBUG:  pgsql-http: queried http://192.168.3.21:5984/articlespg/_bulk_docs
    ERROR:  Failed to connect to 192.168.3.21 port 5984: Connection refused
    couchplay=> 
    
However if we split up the request in to smaller chunks:

WITH newdocs AS ( -- Make chage to json here 
      SELECT id, json_object_set_key(doc::json, 'test'::text, 'Couch & Postgres are scool'::text)::jsonb AS docs
      FROM articlespg 
    ),
    chunked AS (  -- get in chunks 
	SELECT docs, ((ROW_NUMBER() OVER (ORDER BY id) - 1)  / 50) +1 AS chunk_no  
	FROM newdocs
    ),
    chunked_newdocs AS (  -- Bulk up bulk_docs chunks to send 
        SELECT json_agg(docs) AS bulk_docs, chunk_no FROM chunked GROUP BY chunk_no  ORDER BY chunk_no
    )

SELECT chunk_no, status FROM chunked_newdocs,
           http_post('http://192.168.3.21:5984/articlespg/_bulk_docs', '',
           '{"all_or_nothing":true, "docs":' || (bulk_docs) || '}',
           'application/json'::text); 
  
Chunk size - in this case 50 - i think safe to go to about 500 or 1000 depending on doc size - I tried 1000 to begin with but http_post timed out - and 500 seems to be fine.

Watching the node daemon while running chunked bulk updates i can see the changes streaming back to postgres almost as soon as the start so i think better using an UPDATE as postgres doesnt lock the table while this is happening ***Note need to retest this.

However I think better to change all PUTS to bulks POSTS - need a function like:

post_docs(docs,chunk_size) - returning recordset of status codes? or just true/false?

how to deal with the case where there are 5 chunks and the first 2 sucseed but the 3rd fails?
is it possible to rollback a transaction in postgres and give the function oldocs and newdocs 
then a post_docs chunk fails it can rollback the chunks which have succeeded?

to be used after like:

SELECT post_docs(json_object_set_key(doc::json, 'test'::text, 'Couch & Postgres are scool'::text)::jsonb,100)
            AS results
     FROM articlespg

This also makes it very simple to make new databases - just add a new db in couch and change the url to point to it:

chunked AS (
   	  SELECT docs, ((ROW_NUMBER() OVER (ORDER BY id) - 1)  / 500) +1 AS chunk_no  
  	  FROM articlespg
    ),
    chunked_newdocs AS (
       SELECT json_agg(docs) AS bulk_docs, chunk_no FROM chunked GROUP BY chunk_no  ORDER BY chunk_no
    )
    SELECT chunk_no, status FROM chunked_newdocs,
           http_post('http://192.168.3.21:5984/NEW_articlespg_COPY/_bulk_docs', '',
           '{"all_or_nothing":true, "docs":' || (bulk_docs) || '}', 'application/json'::text);

I think maybe faster than a replication.

------

Note: On pgsql-http module install:

https://wiki.postgresql.org/wiki/Building_and_Installing_PostgreSQL_Extension_Modules

For FreeBSD you need to have curl and gettext-tools installed.

# gmake PG_CONFIG=/usr/local/bin/pg_config
    cc -O2 -pipe  -fstack-protector -fno-strict-aliasing -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fPIC -DPIC -I. -I./ -I/usr/local/include/postgresql/server -I/usr/local/include/postgresql/internal -I/usr/local/include/libxml2 -I/usr/include -I/usr/local/include -I/usr/local/include  -c -o http.o http.c
    http.c:89:1: warning: unused function 'header_value' [-Wunused-function]
    header_value(const char* header_str, const char* header_name)
    ^
    1 warning generated.
    cc -O2 -pipe  -fstack-protector -fno-strict-aliasing -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fPIC -DPIC -I. -I./ -I/usr/local/include/postgresql/server -I/usr/local/include/postgresql/internal -I/usr/local/include/libxml2 -I/usr/include -I/usr/local/include -I/usr/local/include  -c -o stringbuffer.o stringbuffer.c
    cc -O2 -pipe  -fstack-protector -fno-strict-aliasing -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fPIC -DPIC -shared -o http.so http.o stringbuffer.o -L/usr/local/lib -L/usr/local/lib -pthread -Wl,-rpath,/usr/lib:/usr/local/lib -fstack-protector -L/usr/local/lib -L/usr/lib  -L/usr/local/lib -Wl,--as-needed -Wl,-R'/usr/local/lib'  -L/usr/local/lib -lcurl

# gmake PG_CONFIG=/usr/local/bin/pg_config install
    /bin/mkdir -p '/usr/local/lib/postgresql'
    /bin/mkdir -p '/usr/local/share/postgresql/extension'
    /bin/mkdir -p '/usr/local/share/postgresql/extension'
    /usr/bin/install -c -o root -g wheel -m 755  http.so '/usr/local/lib/postgresql/http.so'
    /usr/bin/install -c -o root -g wheel -m 644 http.control '/usr/local/share/postgresql/extension/'
    /usr/bin/install -c -o root -g wheel -m 644 http--1.0.sql '/usr/local/share/postgresql/extension/'

-------------------

Futher thoughts and ideas/questions or want to help?  https://github.com/sysadminmike/couch-to-postgres/issues

More testing:

Update to all records in test articles db -

SELECT id , doc->>'read' FROM articlespg WHERE doc->>'read'='false'

couchplay=> SELECT id , doc->>'read' FROM articlespg WHERE doc->>'read'='false'
	couchplay-> 
	couchplay-> ;
		id        | ?column? 
	------------------+----------
	 _design/mw_views | false
	(1 row)

Returns just the design doc.

On running:

UPDATE articlespg 
      SET doc = json_object_set_key(doc::json, 'read'::text, true)::jsonb, from_pg=true ;

Something interesting happens with the feed and postgres - I think postgres locks the table while the update takes place as the feeder carries on querying couch but does not update postgres until the update is complete.

While the query is runing you can see the commit sequence in couch updating:

articlespg: {"db_name":"articlespg","doc_count":63838,"doc_del_count":2,"update_seq":233296,"purge_seq":0,"compact_running":false,"disk_size":2145373958,"data_size":214959726,"instance_start_time":"1418762851354294","disk_format_version":6,"committed_update_seq":233224}
	articlespg: Checkpoint 192414 is current next check in: 10 seconds
	PG_WATCHDOG: OK

articlespg: {"db_name":"articlespg","doc_count":63838,"doc_del_count":2,"update_seq":242301,"purge_seq":0,"compact_running":false,"disk_size":2234531964,"data_size":215440194,"instance_start_time":"1418762851354294","disk_format_version":6,"committed_update_seq":242301}
	articlespg: Checkpoint 192414 is current next check in: 10 seconds
	PG_WATCHDOG: OK

As soon as I get a return for the query the feed goes mad so think postgres has locked the table while the update runs. *** I need to restest this as i may have been doing this test after i introduced a bug stalling the feed on updates.

The UPDATE takes 475 seconds to return
The river then takes about 3 minutes to catch up after the return
So about 10 minutes to do an update on all 60k records.

I need to look at the bulk updates as i do now think it is possible to do all or nothing update and possible do in a transaction - i think if 2 updates to couch were issued and the second failed then the first would have still taken place as far as couch is concerned.  
At the moment if a single PUT were to fail postgres assume no data has been updated but all of the docs up to then would have been updated - in a bulk this would not be a problem i think.  Note so far not one insert or update has failed but i havent killed couch 1/2 way through.

This did give me an idea for another use for this.  Populate a new couchdb from a subset of the couchdb tables in postgres by simply updating the put_function to temporarly submit updates to a different ip or db eg:

--SELECT headers FROM http_post('http://192.168.3.21:5984/' || TG_TABLE_NAME || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text) INTO RES;    
      SELECT headers FROM http_post('http://192.168.3.21:5984/articlespg-subset' || '/' || NEW.id::text, '', NEW.doc::text, 'application/json'::text) INTO RES;

Then re-run the update but with a WHERE

UPDATE articlespg 
    SET doc = json_object_set_key(doc::json, 'read'::text, true)::jsonb, from_pg=true 
    WHERE doc ->>'feedName' ='::Planet PostgreSQL::';

About 10 secs later a populated couchdb with just 761 docs matching the WHERE:

{"db_name":"articlespg-subset","doc_count":761,"doc_del_count":0,"update_seq":761,"purge_seq":0,"compact_running":false,"disk_size":6107249,"data_size":3380130,"instance_start_time":"1418770153501066","disk_format_version":6,"committed_update_seq":761}

A lot simpler that creating a design doc for a one of filtered replication.
There is no reason why you couldnt do a union on two couch db tables in posgres and merge them into a new couchdb provided there are no id issues.

I have also done a quick test with excel and ms query & access and a passthrough sql query both via odbc to postgres - i can see the couch data in both - this makes ad hoc reports so simple.

Note on name - I think I like postcouch best - and i think most of the work will be done by http_post function POSTing (from postgres) to couchdb

(I think a good idea to also also give option to do PUTS)

couch-to-postgres

couch-to-postgres Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

couch-to-postgres

couch-to-postgres Latest Version Download

Today's picks

Was this content helpful?

Recent Feedback

Download README