PostgreSQL equivalent of Oplog Tailing in MongoDB

Question

Is there an equivalent process similar to oplog tailing for MongoDB in PostgreSQL? I find it very useful in MongoDB for real-time analytics and building out dashboards on what is going on in the DB by peeking at the log. Unfortunately MongoDB is not useful for my particular DB needs. I'm looking really for a legitimate, non-hackish, way of doing it. This would be put in a production environment and I can't cause more problems than it's worth down the line.

Thanks in advance and lets try to not make this a NO-SQL vs RDBMS debate.

Answer 1

In PostgreSQL 9.4 and newer you can use the test_decoding plugin via pg_recvlogical to stream changes from a replication slot.

In 9.3 and newer pg_xlogdump can decode the transaction log segments, but that means you have to capture and dump each segment, and it really requires WAL archiving to be enabled in order to be practical.

You should also look at:

• The pg_stat_statements extension
• The built-in pg_stat_activity view
• The built-in pg_stat_.. views like pg_stat_user_indexes, etc.