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

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