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