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

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?"

On Sat, Jan 13, 2024, 1:04 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> I think this might be an A-B problem. Tell us the "business problem" you
> are trying to solve, not the problem you're having with your solution to
> the "business problem".
>
> (If you've already mentioned it, please restate it.)
>
> On Sat, Jan 13, 2024 at 11:49 AM David Ventimiglia <
> davidaventimiglia(at)hasura(dot)io> wrote:
>
>> Thanks. I'm aware of all of those other alternatives, but the thing is,
>> I'm not trying to answer this broader question:
>>
>> *"What are some options for capturing change events in PostgreSQL?"*
>>
>> Rather, I'm trying to answer a narrower question:
>>
>> *"How does one capture output from pg_recvlogical and pipe it back into
>> the database with psql?"*
>>
>> Best,
>> David
>>
>> On Sat, Jan 13, 2024 at 10:29 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>> wrote:
>>
>>> On 1/12/24 21:23, David Ventimiglia wrote:
>>> > Let me just lay my cards on the table. What I'm really trying to do
>>> is
>>> > capture change events with logical decoding and then send them back
>>> into
>>> > the database into a database table. To do that, I believe I need to
>>> > process the event records into SQL insert statements somehow. xargs
>>> is
>>> > one option. jq is another. My idea was to pipe the pg_recvlogical
>>> > output through a jq transform into psql, but that didn't work (neither
>>> > did earlier experiments with xargs). Redirecting the output to an
>>> > intermediate file via stdout was just an attempt to reduce the problem
>>> > to a simpler problem. I had /thought/ (incorrectly, as it turns out)
>>> > that I was unable even to redirect it to a file, but evidently that's
>>> > not the case. I can redirect it to a file. What I cannot seem to do
>>> is
>>> > run it through a jq filter and pipe it back into psql. I can run it
>>> > through a jq filter and redirect it to a file, no problem. But the
>>> > minute I change it to pipe to psql, it ceases to produce the desired
>>> result.
>>> >
>>> > I tried illustrating this in this screencast:
>>> >
>>> > https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y
>>> > <https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y>
>>> >
>>> > Perhaps another way to put this is, how /does/ one capture output from
>>> > pg_recvlogical and pipe it back into the database (or if you like,
>>> some
>>> > other database) with psql. When I set out to do this I didn't think
>>> > bash pipes and redirection would be the hard part, and yet here I am.
>>> > Maybe there's some other way, because I'm fresh out of ideas.
>>>
>>> This is going to depend a lot on what you define as a change event. Is
>>> that DDL changes or data changes or both?
>>>
>>> Some existing solutions that cover the above to a one degree or another:
>>>
>>> Event triggers:
>>>
>>> https://www.postgresql.org/docs/current/event-triggers.html
>>>
>>> PGAudit
>>>
>>> https://github.com/pgaudit/pgaudit/blob/master/README.md
>>>
>>> Or since you are part of the way there already just using logical
>>> replication entirely:
>>>
>>> https://www.postgresql.org/docs/current/logical-replication.html
>>>
>>>
>>> >
>>> > Best,
>>> > David
>>> >
>>> > On Fri, Jan 12, 2024 at 8:42 PM Juan Rodrigo Alejandro Burgos Mella
>>> > <rodrigoburgosmella(at)gmail(dot)com <mailto:rodrigoburgosmella(at)gmail(dot)com>>
>>> wrote:
>>> >
>>> >
>>> > try use the following syntax (yes, with a 2 before the greater
>>> sign)
>>> >
>>> > pg_recvlogical -d postgres --slot test --start -f - 2>>
>>> sample.jsonl
>>> >
>>> > Atte
>>> > JRBM
>>> >
>>> > El vie, 12 ene 2024 a las 16:35, David Ventimiglia
>>> > (<davidaventimiglia(at)hasura(dot)io <mailto:davidaventimiglia(at)hasura(dot)io
>>> >>)
>>> > escribió:
>>> >
>>> > Hello! How do I redirect logical decoding output from the
>>> > PostgreSQL CLI tool |pg_recvlogical| either to a file or to
>>> > another command via a pipe? I ask because when I try the
>>> > obvious, no output is recorded or sent:
>>> >
>>> > |pg_recvlogical -d postgres --slot test --start -f - >>
>>> > sample.jsonl |
>>> >
>>> > Lest there be any confusion, I already created the slot in an
>>> > earlier step. Moreover, I can verify that if I omit the output
>>> > redirection |>> sample| then it does work, insofar as it emits
>>> > the expected change events when I perform DML in another
>>> > terminal window. When I include the redirection (or
>>> > alternatively, set up a pipeline), then nothing happens.
>>> >
>>> > Note that I am aware of the option to pass a filename to the -f
>>> > switch to write to a file. That works, but it's not what I'm
>>> > after because it doesn't help update my mental model of how
>>> this
>>> > is supposed to work. Based on my current (flawed) mental model
>>> > built up from command line experience with other tools, this
>>> > /should/ work. I should be able to send the output to stdout
>>> > and then redirect it to a file. It surprises me that I cannot.
>>> >
>>> > Anyway, thanks!
>>> >
>>> > Best,
>>> >
>>> > David
>>> >
>>>
>>> --
>>> Adrian Klaver
>>> adrian(dot)klaver(at)aklaver(dot)com
>>>
>>>