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

[Adrian Klaver]

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 



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 
mailto:rodrigoburgosme...@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
(mailto:davidaventimig...@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.kla...@aklaver.com

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

[David Ventimiglia]

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 
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
> > 
> >
> > 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
> > mailto:rodrigoburgosme...@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
> > (mailto:davidaventimig...@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.kla...@aklaver.com
>
>

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

[Adrian Klaver]

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.kla...@aklaver.com

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

[Ron Johnson]

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 <
davidaventimig...@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 
> 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
>> > 
>> >
>> > 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
>> > mailto:rodrigoburgosme...@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
>> > (mailto:davidaventimig...@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.kla...@aklaver.com
>>
>>

multiple missing providers from pgdg-common

[Joseph G]

>
> Problem: package  from @commandline requires postgis33_15, but none of
> the providers can be installed
>   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
> gdal35-libs >= 3.5.2, but none of the providers can be installed
>   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
> libgdal.so.31()(64bit), but none of the providers can be installed
>   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
> gdal35-libs >= 3.5.2, but none of the providers can be installed
>   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
> libgdal.so.31()(64bit), but none of the providers can be installed
>   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
> gdal35-libs >= 3.5.3, but none of the providers can be installed
>   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
> libgdal.so.31()(64bit), but none of the providers can be installed
>   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
> gdal36-libs >= 3.6.3, but none of the providers can be installed
>   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
> libgdal.so.32()(64bit), but none of the providers can be installed
>   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
> gdal36-libs >= 3.6.3, but none of the providers can be installed
>   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
> libgdal.so.32()(64bit), but none of the providers can be installed
>   - conflicting requests
>   - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal35-libs-3.5.3-4.rhel8.x86_64 from pgdg-common
>   - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal35-libs-3.5.3-7PGDG.rhel8.x86_64 from pgdg-common
>   - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal36-libs-3.6.4-4PGDG.rhel8.x86_64 from pgdg-common
>   - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal36-libs-3.6.4-5PGDG.rhel8.x86_64 from pgdg-common
> (try to add '--skip-broken' to skip uninstallable packages or '--nobest'
> to use not only best candidate packages)


I can confirm that *postgis33_15-3.3.1-1.rhel8.x86_64* exists within my
repositories:

>
> *sudo dnf provides postgis33_15*Last metadata expiration check: 3:14:38
> ago on Sat 13 Jan 2024 08:06:49 AM PST.
> postgis33_15-3.3.1-1.rhel8.x86_64 : Geographic Information Systems
> Extensions to PostgreSQL
> Repo: pgdg15
> Matched from:
> Provide: postgis33_15 = 3.3.1-1.rhel8


postgis33_15-3.3.2-1.rhel8.x86_64 : Geographic Information Systems
> Extensions to PostgreSQL
> Repo: pgdg15
> Matched from:
> Provide: postgis33_15 = 3.3.2-1.rhel8


postgis33_15-3.3.3-1.rhel8.x86_64 : Geographic Information Systems
> Extensions to PostgreSQL
> Repo: pgdg15
> Matched from:
> Provide: postgis33_15 = 3.3.3-1.rhel8


postgis33_15-3.3.4-2PGDG.rhel8.x86_64 : Geographic Information Systems
> Extensions to PostgreSQL
> Repo: pgdg15
> Matched from:
> Provide: postgis33_15 = 3.3.4-2PGDG.rhel8


postgis33_15-3.3.5-1PGDG.rhel8.x86_64 : Geographic Information Systems
> Extensions to PostgreSQL
> Repo: pgdg15
> Matched from:
> Provide: postgis33_15 = 3.3.5-1PGDG.rhel8


I additionally have the following repositories enabled:

>
> *sudo dnf repolist*repo id  repo
> name
> appstreamRocky Linux 8 - AppStream
> baseos   Rocky Linux 8 - BaseOS
> epel Extra Packages for
> Enterprise Linux 8 - x86_64
> extras   Rocky Linux 8 - Extras
> pgdg-common  PostgreSQL common RPMs
> for RHEL / Rocky / AlmaLinux 8 - x86_64
> pgdg12   PostgreSQL 12 for RHEL /
> Rocky / AlmaLinux 8 - x86_64
> pgdg13   PostgreSQL 13 for RHEL /
> Rocky / AlmaLinux 8 - x86_64
> pgdg14   PostgreSQL 14 for RHEL /
> Rocky / AlmaLinux 8 - x86_64
> pgdg15   PostgreSQL 15 for RHEL /
> Rocky / AlmaLinux 8 - x86_64
> pgdg16   PostgreSQL 16 for RHEL /
> Rocky / AlmaLinux 8 - x86_64
> powertools   Rocky Linux 8 - PowerTools

Re: multiple missing providers from pgdg-common

[Devrim Gündüz]

What's that ?

On 13 January 2024 20:26:19 CET, Joseph G  wrote:
>>
>> Problem: package  from @commandline requires postgis33_15, but none of
>> the providers can be installed
>>   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
>> gdal35-libs >= 3.5.2, but none of the providers can be installed
>>   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
>> libgdal.so.31()(64bit), but none of the providers can be installed
>>   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
>> gdal35-libs >= 3.5.2, but none of the providers can be installed
>>   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
>> libgdal.so.31()(64bit), but none of the providers can be installed
>>   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
>> gdal35-libs >= 3.5.3, but none of the providers can be installed
>>   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
>> libgdal.so.31()(64bit), but none of the providers can be installed
>>   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
>> gdal36-libs >= 3.6.3, but none of the providers can be installed
>>   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
>> libgdal.so.32()(64bit), but none of the providers can be installed
>>   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
>> gdal36-libs >= 3.6.3, but none of the providers can be installed
>>   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
>> libgdal.so.32()(64bit), but none of the providers can be installed
>>   - conflicting requests
>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>> gdal35-libs-3.5.3-4.rhel8.x86_64 from pgdg-common
>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>> gdal35-libs-3.5.3-7PGDG.rhel8.x86_64 from pgdg-common
>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>> gdal36-libs-3.6.4-4PGDG.rhel8.x86_64 from pgdg-common
>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>> gdal36-libs-3.6.4-5PGDG.rhel8.x86_64 from pgdg-common
>> (try to add '--skip-broken' to skip uninstallable packages or '--nobest'
>> to use not only best candidate packages)
>
>
>I can confirm that *postgis33_15-3.3.1-1.rhel8.x86_64* exists within my
>repositories:
>
>>
>> *sudo dnf provides postgis33_15*Last metadata expiration check: 3:14:38
>> ago on Sat 13 Jan 2024 08:06:49 AM PST.
>> postgis33_15-3.3.1-1.rhel8.x86_64 : Geographic Information Systems
>> Extensions to PostgreSQL
>> Repo: pgdg15
>> Matched from:
>> Provide: postgis33_15 = 3.3.1-1.rhel8
>
>
>postgis33_15-3.3.2-1.rhel8.x86_64 : Geographic Information Systems
>> Extensions to PostgreSQL
>> Repo: pgdg15
>> Matched from:
>> Provide: postgis33_15 = 3.3.2-1.rhel8
>
>
>postgis33_15-3.3.3-1.rhel8.x86_64 : Geographic Information Systems
>> Extensions to PostgreSQL
>> Repo: pgdg15
>> Matched from:
>> Provide: postgis33_15 = 3.3.3-1.rhel8
>
>
>postgis33_15-3.3.4-2PGDG.rhel8.x86_64 : Geographic Information Systems
>> Extensions to PostgreSQL
>> Repo: pgdg15
>> Matched from:
>> Provide: postgis33_15 = 3.3.4-2PGDG.rhel8
>
>
>postgis33_15-3.3.5-1PGDG.rhel8.x86_64 : Geographic Information Systems
>> Extensions to PostgreSQL
>> Repo: pgdg15
>> Matched from:
>> Provide: postgis33_15 = 3.3.5-1PGDG.rhel8
>
>
>I additionally have the following repositories enabled:
>
>>
>> *sudo dnf repolist*repo id  repo
>> name
>> appstreamRocky Linux 8 - AppStream
>> baseos   Rocky Linux 8 - BaseOS
>> epel Extra Packages for
>> Enterprise Linux 8 - x86_64
>> extras   Rocky Linux 8 - Extras
>> pgdg-common  PostgreSQL common RPMs
>> for RHEL / Rocky / AlmaLinux 8 - x86_64
>> pgdg12   PostgreSQL 12 for RHEL /
>> Rocky / AlmaLinux 8 - x86_64
>> pgdg13   PostgreSQL 13 for RHEL /
>> Rocky / AlmaLinux 8 - x86_64
>> pgdg14   PostgreSQL 14 for RHEL /
>> Rocky / AlmaLinux 8 - x86_64
>> pgdg15   PostgreSQL 15 for RHEL /
>> Rocky / AlmaLinux 8 - x86_64
>> pgdg16   PostgreSQL 16 for RHEL /
>> Rocky / AlmaLinux 8 - x86_64
>> powertools   Rocky Linux 8 - PowerTools

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: multiple missing providers from pgdg-common

[Joseph G]

the  is the RPM that I'm trying to install, here is the output for the
requirements:


sudo rpm -qp takserver-*.noarch.rpm --requires
> warning: takserver-5.0-RELEASE31.noarch.rpm: Header V4 RSA/SHA512
> Signature, key ID 6851f5b5: NOKEY
> rpmlib(VersionedDependencies) <= 3.0.3-1
> rpmlib(CompressedFileNames) <= 3.0.4-1
> rpmlib(PayloadFilesHavePrefix) <= 4.0-1
> java-17-openjdk-devel
> postgis33_15
> postgis33_15-utils
> postgresql15-server
> postgresql15-contrib
> openssl


Do you need anything more than that?

On Sat, Jan 13, 2024 at 12:40 PM Devrim Gündüz  wrote:

> What's that ?
>
>
> On 13 January 2024 20:26:19 CET, Joseph G 
> wrote:
>
>> Problem: package  from @commandline requires postgis33_15, but none
>>> of the providers can be installed
>>>   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
>>> gdal35-libs >= 3.5.2, but none of the providers can be installed
>>>   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
>>> libgdal.so.31()(64bit), but none of the providers can be installed
>>>   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
>>> gdal35-libs >= 3.5.2, but none of the providers can be installed
>>>   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
>>> libgdal.so.31()(64bit), but none of the providers can be installed
>>>   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
>>> gdal35-libs >= 3.5.3, but none of the providers can be installed
>>>   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
>>> libgdal.so.31()(64bit), but none of the providers can be installed
>>>   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
>>> gdal36-libs >= 3.6.3, but none of the providers can be installed
>>>   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
>>> libgdal.so.32()(64bit), but none of the providers can be installed
>>>   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
>>> gdal36-libs >= 3.6.3, but none of the providers can be installed
>>>   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
>>> libgdal.so.32()(64bit), but none of the providers can be installed
>>>   - conflicting requests
>>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>>> gdal35-libs-3.5.3-4.rhel8.x86_64 from pgdg-common
>>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>>> gdal35-libs-3.5.3-7PGDG.rhel8.x86_64 from pgdg-common
>>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>>> gdal36-libs-3.6.4-4PGDG.rhel8.x86_64 from pgdg-common
>>>   - nothing provides libarmadillo.so.10()(64bit) needed by
>>> gdal36-libs-3.6.4-5PGDG.rhel8.x86_64 from pgdg-common
>>> (try to add '--skip-broken' to skip uninstallable packages or '--nobest'
>>> to use not only best candidate packages)
>>
>>
>> I can confirm that *postgis33_15-3.3.1-1.rhel8.x86_64* exists within my
>> repositories:
>>
>>>
>>> *sudo dnf provides postgis33_15*Last metadata expiration check: 3:14:38
>>> ago on Sat 13 Jan 2024 08:06:49 AM PST.
>>> postgis33_15-3.3.1-1.rhel8.x86_64 : Geographic Information Systems
>>> Extensions to PostgreSQL
>>> Repo: pgdg15
>>> Matched from:
>>> Provide: postgis33_15 = 3.3.1-1.rhel8
>>
>>
>> postgis33_15-3.3.2-1.rhel8.x86_64 : Geographic Information Systems
>>> Extensions to PostgreSQL
>>> Repo: pgdg15
>>> Matched from:
>>> Provide: postgis33_15 = 3.3.2-1.rhel8
>>
>>
>> postgis33_15-3.3.3-1.rhel8.x86_64 : Geographic Information Systems
>>> Extensions to PostgreSQL
>>> Repo: pgdg15
>>> Matched from:
>>> Provide: postgis33_15 = 3.3.3-1.rhel8
>>
>>
>> postgis33_15-3.3.4-2PGDG.rhel8.x86_64 : Geographic Information Systems
>>> Extensions to PostgreSQL
>>> Repo: pgdg15
>>> Matched from:
>>> Provide: postgis33_15 = 3.3.4-2PGDG.rhel8
>>
>>
>> postgis33_15-3.3.5-1PGDG.rhel8.x86_64 : Geographic Information Systems
>>> Extensions to PostgreSQL
>>> Repo: pgdg15
>>> Matched from:
>>> Provide: postgis33_15 = 3.3.5-1PGDG.rhel8
>>
>>
>> I additionally have the following repositories enabled:
>>
>>>
>>> *sudo dnf repolist*repo id
>>>  repo name
>>> appstreamRocky Linux 8 -
>>> AppStream
>>> baseos   Rocky Linux 8 - BaseOS
>>> epel Extra Packages for
>>> Enterprise Linux 8 - x86_64
>>> extras   Rocky Linux 8 - Extras
>>> pgdg-common  PostgreSQL common RPMs
>>> for RHEL / Rocky / AlmaLinux 8 - x86_64
>>> pgdg12   PostgreSQL 12 for RHEL
>>> / Rocky / AlmaLinux 8 - x86_64
>>> pgdg13   PostgreSQL 13 for RHEL
>>> / Rocky / AlmaLinux 8 - x86_64
>>> pgdg14   PostgreSQL 14 for RHEL
>>> / Rocky / AlmaLinux 8 - x86_64
>>> pgdg15   P

Re: multiple missing providers from pgdg-common

[Joseph G]

>
> sudo rpm -qp
> https://dl.fedoraproject.org/pub/epel/8/Everything/x86_64/Packages/a/armadillo-12.6.6-1.el8.x86_64.rpm
> --provides
> armadillo = 12.6.6-1.el8
> armadillo(x86-64) = 12.6.6-1.el8
> libarmadillo.so.12()(64bit)


sudo rpm -qp
> https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/a/armadillo-10.8.2-1.el7.x86_64.rpm
> --provides
> warning:
> https://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/a/armadillo-10.8.2-1.el7.x86_64.rpm:
> Header V4 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
> armadillo = 10.8.2-1.el7
> armadillo(x86-64) = 10.8.2-1.el7
> libarmadillo.so.10()(64bit)


If we look, gdal35 or 36 is asking for libarmadillo.so.10 which doesn't
look like its available for EL8 just an older EL7?  I can't get past this
error at the moment.

> - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal35-libs-3.5.3-4.rhel8.x86_64 from pgdg-common
> - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal35-libs-3.5.3-7PGDG.rhel8.x86_64 from pgdg-common
> - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal36-libs-3.6.4-4PGDG.rhel8.x86_64 from pgdg-common
> - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal36-libs-3.6.4-5PGDG.rhel8.x86_64 from pgdg-common


Here is me trying 3.5.3

> sudo dnf install
> https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-8-x86_64/gdal35-3.5.3-4.rhel8.x86_64.rpm
> Last metadata expiration check: 1:32:19 ago on Sat 13 Jan 2024 11:50:46 AM
> PST.
> gdal35-3.5.3-4.rhel8.x86_64.rpm
>182 kB/s | 226 kB 00:01
> Error:
>  Problem: package gdal35-3.5.3-4.rhel8.x86_64 from @commandline requires
> gdal35-libs(x86-64) = 3.5.3-4.rhel8, but none of the providers can be
> installed
>   - conflicting requests
>   - nothing provides libarmadillo.so.10()(64bit) needed by
> gdal35-libs-3.5.3-4.rhel8.x86_64 from pgdg-common
> (try to add '--skip-broken' to skip uninstallable packages or '--nobest'
> to use not only best candidate packages)


On Sat, Jan 13, 2024 at 1:07 PM Joseph G  wrote:

> the  is the RPM that I'm trying to install, here is the output for the
> requirements:
>
>
> sudo rpm -qp takserver-*.noarch.rpm --requires
>> warning: takserver-5.0-RELEASE31.noarch.rpm: Header V4 RSA/SHA512
>> Signature, key ID 6851f5b5: NOKEY
>> rpmlib(VersionedDependencies) <= 3.0.3-1
>> rpmlib(CompressedFileNames) <= 3.0.4-1
>> rpmlib(PayloadFilesHavePrefix) <= 4.0-1
>> java-17-openjdk-devel
>> postgis33_15
>> postgis33_15-utils
>> postgresql15-server
>> postgresql15-contrib
>> openssl
>
>
> Do you need anything more than that?
>
> On Sat, Jan 13, 2024 at 12:40 PM Devrim Gündüz  wrote:
>
>> What's that ?
>>
>>
>> On 13 January 2024 20:26:19 CET, Joseph G 
>> wrote:
>>
>>> Problem: package  from @commandline requires postgis33_15, but none
 of the providers can be installed
   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
 gdal35-libs >= 3.5.2, but none of the providers can be installed
   - package postgis33_15-3.3.1-1.rhel8.x86_64 from pgdg15 requires
 libgdal.so.31()(64bit), but none of the providers can be installed
   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
 gdal35-libs >= 3.5.2, but none of the providers can be installed
   - package postgis33_15-3.3.2-1.rhel8.x86_64 from pgdg15 requires
 libgdal.so.31()(64bit), but none of the providers can be installed
   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
 gdal35-libs >= 3.5.3, but none of the providers can be installed
   - package postgis33_15-3.3.3-1.rhel8.x86_64 from pgdg15 requires
 libgdal.so.31()(64bit), but none of the providers can be installed
   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
 gdal36-libs >= 3.6.3, but none of the providers can be installed
   - package postgis33_15-3.3.4-2PGDG.rhel8.x86_64 from pgdg15 requires
 libgdal.so.32()(64bit), but none of the providers can be installed
   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
 gdal36-libs >= 3.6.3, but none of the providers can be installed
   - package postgis33_15-3.3.5-1PGDG.rhel8.x86_64 from pgdg15 requires
 libgdal.so.32()(64bit), but none of the providers can be installed
   - conflicting requests
   - nothing provides libarmadillo.so.10()(64bit) needed by
 gdal35-libs-3.5.3-4.rhel8.x86_64 from pgdg-common
   - nothing provides libarmadillo.so.10()(64bit) needed by
 gdal35-libs-3.5.3-7PGDG.rhel8.x86_64 from pgdg-common
   - nothing provides libarmadillo.so.10()(64bit) needed by
 gdal36-libs-3.6.4-4PGDG.rhel8.x86_64 from pgdg-common
   - nothing provides libarmadillo.so.10()(64bit) needed by
 gdal36-libs-3.6.4-5PGDG.rhel8.x86_64 from pgdg-common
 (try to add '--skip-broken' to skip uninstallable packages or
 '--nobest' to use not only best candidate packages)
>>>
>>>
>>> I can confirm that *postgis33_15-3.3.1-

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

[David Ventimiglia]

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  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 <
> davidaventimig...@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 
>> 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
>>> > 
>>> >
>>> > 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
>>> > mailto:rodrigoburgosme...@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
>>> > (mailto:davidaventimig...@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 li

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

[Ron Johnson]

*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 <
davidaventimig...@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?"
>
>>

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

[Karsten Hilbert]

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

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

[David Ventimiglia]

It satisfies business constraints.

On Sat, Jan 13, 2024, 5:01 PM Karsten Hilbert 
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
>
>
>

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

[David Ventimiglia]

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  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 <
> davidaventimig...@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?"
>>
>>>
>

Help with "Create Extension unaccent"

[Lan Xu]

With CREATE EXTENSION unaccent, I’m getting the following: 
ERROR:  extension "unaccent" has no installation script nor update path for 
version “1.0"

But I can find it under /usr/share/pgsql/extension/unaccent001.0.sql

Thank you for your help!

Lan