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 <ronljohnso...@gmail.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 < > davidaventimig...@hasura.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.kla...@aklaver.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 >>> > <rodrigoburgosme...@gmail.com <mailto:rodrigoburgosme...@gmail.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 >>> > (<davidaventimig...@hasura.io <mailto:davidaventimig...@hasura.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.kla...@aklaver.com >>> >>>
