Re: Pg_logical without subscription. Can we log the operations ?

> On Oct 9, 2018, at 11:45 PM, Dilshan <dilshanjsha(at)gmail(dot)com> wrote:
>
> Hi Team,
> I am working on a requirement, where I need to log each and every operation on the master db. So I was thinking if I could do with pg_logical by setting my master db as publisher and setting a new db as subscriber and dropping the subscription there after. I am wondering, how to get the operations that a subscription would receive just into logs. Could you guide me on that? Otherwise is there a possibility to receive all the operation without dropping subscription and logging the details and deleting the subscription tables to save space. I am planning to have logs rotated and purging logs every month. Could you please guide me about the possibility of this approach?

You can read the logical replication stream with clients other than postgresql, and you can use plugins to format it in different ways.

https://wiki.postgresql.org/wiki/Logical_Decoding_Plugins has some of the third-party plugins to format the changes.

There are libraries for most languages to consume the logical decoding stream, or the included client "pg_recvlogical" can be used to write it to disk.

pg_recvlogical + wal2json might be the simplest way to do a basic audit trail.

Cheers,
Steve