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

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

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.

Best,
David

On Fri, Jan 12, 2024 at 8:42 PM Juan Rodrigo Alejandro Burgos Mella <
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>) 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
>>
>