From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | David Ventimiglia <davidaventimiglia(at)hasura(dot)io>, Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe? |
Date: | 2024-01-13 16:29:43 |
Message-ID: | 3567b5db-19e4-48ff-ac6d-60a2439a1b8b@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)com <mailto:rodrigoburgosmella(at)gmail(dot)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(at)hasura(dot)io <mailto:davidaventimiglia(at)hasura(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David Ventimiglia | 2024-01-13 16:48:46 | Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe? |
Previous Message | Julian Coccia | 2024-01-13 11:10:08 | Re: Software Bill of Materials (SBOM) |