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@xxxxxxxxx> 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@xxxxxxxxx> 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,DavidOn Sat, Jan 13, 2024 at 10:29 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxx <mailto:rodrigoburgosmella@xxxxxxxxx>> 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@xxxxxxxxx <mailto:davidaventimiglia@xxxxxxxxx>>)
> 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.klaver@xxxxxxxxxxx
