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

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