Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

Thanks for the reply, Jim.  No, I'm afraid that's not the missing piece.  I knew enough to use jq to transform the JSON output into SQL statements.  What I didn't know enough was about jq.  No, the missing piece turned out not to have anything to do with PostgreSQL or pg_recvlogical (I guessed incorrectly that it might), but rather with jq itself.  I didn't realize that jq buffers its input and it turns out all I had to do was use its --unbuffered switch.  The full chapter-and-verse is described in this Stack Overflow question and answer.  

Cheers,

David

On Tue, Jan 16, 2024 at 12:57 PM Jim Nasby <jim.nasby@gmail.com> wrote:

On 1/13/24 3:34 PM, David Ventimiglia wrote:
> The business problem I'm trying to solve is:
>
> "How do I capture logical decoding events with the wal2json output
> encoder, filter them with jq, and pipe them to psql, using pg_recvlogical?"

I think the missing piece here is that you can't simply pipe JSON into
psql and expect anything useful to happen. Are you using jq to turn the
JSON into actual SQL statements? What does some of your jq output look like?
--
Jim Nasby, Data Architect, Austin TX