Thread: Pg_logical without subscription. Can we log the operations ?
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?
Thanks in advance,
Dilshan
> On Oct 9, 2018, at 11:45 PM, Dilshan <dilshanjsha@gmail.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 coulddo with pg_logical by setting my master db as publisher and setting a new db as subscriber and dropping the subscriptionthere after. I am wondering, how to get the operations that a subscription would receive just into logs. Couldyou guide me on that? Otherwise is there a possibility to receive all the operation without dropping subscription andlogging the details and deleting the subscription tables to save space. I am planning to have logs rotated and purginglogs 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 differentways. 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" canbe used to write it to disk. pg_recvlogical + wal2json might be the simplest way to do a basic audit trail. Cheers, Steve