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

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

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&gt;

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

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&gt;

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

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&gt;

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

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

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

*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?"

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

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&gt;
.

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