Thread

1. How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

   David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-12T19:34:36Z

   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
   

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-01-12T19:48:42Z

   On 1/12/24 11:34, David Ventimiglia wrote:
   > 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.
   
   You left out the important part of the SO question:
   
   pg_recvlogical -d postgres --slot test --create-slot -P wal2json
   
   I can get:
   
   pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl
   
   to work when I do:
   
   pg_recvlogical -d postgres --slot test --create-slot
   
   wal2json seems to be the issue.
   
   > 
   > 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@aklaver.com
   
   
   
   
   

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

   Francisco Olarte <folarte@peoplecall.com> — 2024-01-12T19:50:24Z

   David:
   
   On Fri, 12 Jan 2024 at 20:35, David Ventimiglia
   <davidaventimiglia@hasura.io> wrote:
   > 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.
   
   Have you tested the command as printed ( with -f - ) but without redirection?
   
   Have you ruled out the usual suspect, stdout is line buffered when
   going to a tty, full buffered when not ? ( by killing pg_revlogical
   and/or insuring a long enough output is generated )
   
   Francisco Olarte.
   
   
   
   

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-01-12T23:23:56Z

   On 1/12/24 14:03, David Ventimiglia wrote:
   
   Reply to list also.
   Ccing list
   > Hi Adrian,
   > 
   > I left out the creation of the slot both from the SO question and from 
   > this mailing list question, because I believe it's a red herring.  I run 
   > into the same problem with the default output plugin as I do with the 
   > wal2json plugin.  However, the problem is a little different from what I 
   > described.  It turns out it's not output redirection from pg_recvlogical 
   > sending to stdout that fails.  Rather, it's output redirection from 
   > pg_recvlogical sending to stdout and then passed through a pipeline that 
   > fails.  Or something like that.
   > 
   > This works.  The sample.txt file is non-empty.
   > 
   >     pg_recvlogical -d postgres --slot=test --create-slot
   >     pg_recvlogical -d postgres -n --slot=test --start -f - > sample.txt
   > 
   > 
   > This does not work.  The sample.txt file is empty.
   > 
   >     pg_recvlogical -d postgres --slot=test --create-slot
   >     pg_recvlogical -d postgres -n --slot=test --start -f - | awk
   >     '{print}' > sample.txt
   > 
   > 
   > Weirdly, this works.  The sample.txt file is non-empty.
   > 
   >     pg_recvlogical -d postgres --slot=test --create-slot
   >     pg_recvlogical -d postgres -n --slot=test --start -f - | cat >
   >     sample.txt
   > 
   > 
   > FWIW, this is demonstrated in this screen-cast 
   > <https://asciinema.org/a/631166>.  I suspect it has something to do with 
   > the flushing of buffers, as other people have suggested.
   > 
   > Thanks!
   > David
   > 
   > 
   > 
   > 
   > On Fri, Jan 12, 2024 at 1:48 PM Adrian Klaver <adrian.klaver@aklaver.com 
   > <mailto:adrian.klaver@aklaver.com>> wrote:
   > 
   >     On 1/12/24 11:34, David Ventimiglia wrote:
   >      > 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.
   > 
   >     You left out the important part of the SO question:
   > 
   >     pg_recvlogical -d postgres --slot test --create-slot -P wal2json
   > 
   >     I can get:
   > 
   >     pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl
   > 
   >     to work when I do:
   > 
   >     pg_recvlogical -d postgres --slot test --create-slot
   > 
   >     wal2json seems to be the issue.
   > 
   >      >
   >      > 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@aklaver.com <mailto:adrian.klaver@aklaver.com>
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

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

   David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-13T05:23:46Z

   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@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 (<
   > davidaventimiglia@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
   >>
   >
   

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-01-13T16:29:43Z

   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@gmail.com <mailto:rodrigoburgosmella@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
   >     (<davidaventimiglia@hasura.io <mailto:davidaventimiglia@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.klaver@aklaver.com
   
   
   
   
   

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

   David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-13T16:48:46Z

   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.klaver@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
   > > <rodrigoburgosmella@gmail.com <mailto:rodrigoburgosmella@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
   > >     (<davidaventimiglia@hasura.io <mailto:davidaventimiglia@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.klaver@aklaver.com
   >
   >
   

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-01-13T17:54:10Z

   On 1/13/24 08:48, David Ventimiglia 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?"/
   
   I don't know. For those that might a self contained example of what you 
   want to achieve would be a good start. I doubt that many will look at 
   the screencast.
   
   > Best,
   > David
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

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

   Ron Johnson <ronljohnsonjr@gmail.com> — 2024-01-13T19:04:15Z

   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@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.klaver@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
   >> > <rodrigoburgosmella@gmail.com <mailto:rodrigoburgosmella@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
   >> >     (<davidaventimiglia@hasura.io <mailto:davidaventimiglia@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.klaver@aklaver.com
   >>
   >>
   

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

    David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-13T21:34:40Z

    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@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 <
    > davidaventimiglia@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.klaver@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
    >>> > <rodrigoburgosmella@gmail.com <mailto:rodrigoburgosmella@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
    >>> >     (<davidaventimiglia@hasura.io <mailto:davidaventimiglia@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.klaver@aklaver.com
    >>>
    >>>
    

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

    Ron Johnson <ronljohnsonjr@gmail.com> — 2024-01-13T22:53:14Z

    *No,* that's a technology problem.  What is the purpose of storing them
    back in the database using psql?
    
    On Sat, Jan 13, 2024 at 4:34 PM David Ventimiglia <
    davidaventimiglia@hasura.io> wrote:
    
    > 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?"
    >
    >>
    >>>>
    

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

    Karsten Hilbert <karsten.hilbert@gmx.net> — 2024-01-13T23:01:45Z

    Am Sat, Jan 13, 2024 at 05:53:14PM -0500 schrieb Ron Johnson:
    
    > *No,* that's a technology problem.  What is the purpose of storing them
    > back in the database using psql?
    
    Or even the end goal to be achieved by that ?
    
    Karsten
    --
    GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
    
    
    
    

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

    David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-13T23:07:06Z

    It satisfies business constraints.
    
    On Sat, Jan 13, 2024, 5:01 PM Karsten Hilbert <Karsten.Hilbert@gmx.net>
    wrote:
    
    > Am Sat, Jan 13, 2024 at 05:53:14PM -0500 schrieb Ron Johnson:
    >
    > > *No,* that's a technology problem.  What is the purpose of storing them
    > > back in the database using psql?
    >
    > Or even the end goal to be achieved by that ?
    >
    > Karsten
    > --
    > GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
    >
    >
    >
    

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

    David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-13T23:10:42Z

    I'm asking a question about technology. It has an answer. Whatever that
    answer is, it's independent of anyone's purpose.
    
    On Sat, Jan 13, 2024, 4:53 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
    
    > *No,* that's a technology problem.  What is the purpose of storing them
    > back in the database using psql?
    >
    > On Sat, Jan 13, 2024 at 4:34 PM David Ventimiglia <
    > davidaventimiglia@hasura.io> wrote:
    >
    >> 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?"
    >>
    >>>
    >>>>>
    

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

    Jim Nasby <jim.nasby@gmail.com> — 2024-01-16T18:57:14Z

    On 1/13/24 3:34 PM, David Ventimiglia wrote:
    > 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?"
    
    I think the missing piece here is that you can't simply pipe JSON into 
    psql and expect anything useful to happen. Are you using jq to turn the 
    JSON into actual SQL statements? What does some of your jq output look like?
    -- 
    Jim Nasby, Data Architect, Austin TX
    
    
    
    
    

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

    David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-16T19:15:20Z

    Thanks for the reply, Jim.  No, I'm afraid that's not the missing piece.  I
    knew enough to use jq to transform the JSON output into SQL statements.
    What I didn't know enough was about jq.  No, the missing piece turned out
    not to have anything to do with PostgreSQL or pg_recvlogical (I guessed
    incorrectly that it might), but rather with jq itself.  I didn't realize
    that jq buffers its input and it turns out all I had to do was use its
    --unbuffered switch.  The full chapter-and-verse is described in this Stack
    Overflow question and answer
    <https://stackoverflow.com/questions/75784345/how-to-pipe-pg-recvlogical-to-psql-for-logical-replication>
    .
    
    Cheers,
    David
    
    On Tue, Jan 16, 2024 at 12:57 PM Jim Nasby <jim.nasby@gmail.com> wrote:
    
    > On 1/13/24 3:34 PM, David Ventimiglia wrote:
    > > 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?"
    >
    > I think the missing piece here is that you can't simply pipe JSON into
    > psql and expect anything useful to happen. Are you using jq to turn the
    > JSON into actual SQL statements? What does some of your jq output look
    > like?
    > --
    > Jim Nasby, Data Architect, Austin TX
    >
    >
    

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

    David Ventimiglia <davidaventimiglia@hasura.io> — 2024-01-16T19:16:19Z

    Whoops!  Wrong SO link.  Here's the correct SO link:
    
    https://stackoverflow.com/questions/77808615/how-to-use-logical-decoding-with-pg-recvlogical-to-pass-changes-through-a-non-tr
    
    On Tue, Jan 16, 2024 at 1:15 PM David Ventimiglia <
    davidaventimiglia@hasura.io> wrote:
    
    > Thanks for the reply, Jim.  No, I'm afraid that's not the missing piece.
    > I knew enough to use jq to transform the JSON output into SQL statements.
    > What I didn't know enough was about jq.  No, the missing piece turned out
    > not to have anything to do with PostgreSQL or pg_recvlogical (I guessed
    > incorrectly that it might), but rather with jq itself.  I didn't realize
    > that jq buffers its input and it turns out all I had to do was use its
    > --unbuffered switch.  The full chapter-and-verse is described in this
    > Stack Overflow question and answer
    > <https://stackoverflow.com/questions/75784345/how-to-pipe-pg-recvlogical-to-psql-for-logical-replication>
    > .
    >
    > Cheers,
    > David
    >
    > On Tue, Jan 16, 2024 at 12:57 PM Jim Nasby <jim.nasby@gmail.com> wrote:
    >
    >> On 1/13/24 3:34 PM, David Ventimiglia wrote:
    >> > 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?"
    >>
    >> I think the missing piece here is that you can't simply pipe JSON into
    >> psql and expect anything useful to happen. Are you using jq to turn the
    >> JSON into actual SQL statements? What does some of your jq output look
    >> like?
    >> --
    >> Jim Nasby, Data Architect, Austin TX
    >>
    >>