building a singularity image from docker hub postgres image

I am trying to build a singularity image from postgres docker image. I am issuing the command below. $ singularity build /local/data/some/postgres.16.1.sif docker://postgres/postgres:16.1 INFO:Starting build... INFO:Fetching OCI image... FATAL: While performing build: conveyor failed to

Good overview of programming IN Postgres?

Is there a good overview of programming WITHIN Postgres? Not writing code in client languages that communicates WITH Postgres, but how to take full advantage of Postgres’ unique programming features — the advanced type system, the various PL languages, triggers, stored procedures, functions, …

Re: Best practices for data buffer cache setting/tuning (v15)

On Mon, Jan 29, 2024 at 01:46:30PM -0800, Christophe Pettus wrote: > > > > On Jan 29, 2024, at 11:39, David Gauthier > > wrote: > > > > Is there a document which makes recommendations on sizing data > > buffer cache, tuning options which evict old/unused data in mem, and > > cache fragmentation av

Re: Monitoring logical replication

Hi Saheed! I monitor our replication this way: 1. Every 10 seconds i fetch the current LSN and write it into a table, next with the current timestamp. Further I fetch confirmend LSNs from the replication slots and delete old entries in lsn2data table. calculate_logical_replication_lag.php:

Re: Scriptable way to validate a pg_dump restore ?

On 1/29/24 11:35, Shaheed Haque wrote: On Tue, 30 Jan 2024, 00:27 Adrian Klaver, > wrote: On 1/29/24 10:12, Shaheed Haque wrote: > > Yes. But I was under the impression that the initial copy of logical > replication was the same? >

Re: What should I expect when creating many logical replication slots?

Am 2024-01-16 19:51, schrieb Jim Nasby: On 1/11/24 6:17 PM, Antonin Bas wrote: Hi all, I have a use case for which I am considering using Postgres Logical Replication, but I would like to scale up to 100 or even 200 replication slots. I have increased max_wal_senders and max_replication_slo

Re: Best practices for data buffer cache setting/tuning (v15)

> On Jan 29, 2024, at 11:39, David Gauthier wrote: > > Is there a document which makes recommendations on sizing data buffer cache, > tuning options which evict old/unused data in mem, and cache fragmentation > avoidance for a v15.3 DB ? On any modern system, set shared_buffers to 25% of in

Re: Seeking help extricating data from Amazon RDS Aurora/Postgres

On Mon, 2024-01-29 at 14:22 -0500, Bill Mitchell wrote: > We are attempting to extract one of our database from Amazon RDS > Aurora/Postgres to another PostgreSQL cluster that is running > directly on EC2 instances. Aurora PostgreSQL supports logical replication and purports to use the native WAL

Re: Best practices for data buffer cache setting/tuning (v15)

On Mon, 2024-01-29 at 14:39 -0500, David Gauthier wrote: > Is there a document which makes recommendations on sizing data buffer cache, > tuning options which evict old/unused data in mem, and cache fragmentation > avoidance for a v15.3 DB ? Start with "shared_buffers" sized as a quarter of the av

Best practices for data buffer cache setting/tuning (v15)

Is there a document which makes recommendations on sizing data buffer cache, tuning options which evict old/unused data in mem, and cache fragmentation avoidance for a v15.3 DB ? Thanks in Advance.

Re: Scriptable way to validate a pg_dump restore ?

On Tue, 30 Jan 2024, 00:27 Adrian Klaver, wrote: > On 1/29/24 10:12, Shaheed Haque wrote: > > > > > > Yes. But I was under the impression that the initial copy of logical > > replication was the same? > > > > Are you taking about the copy_data option to WITH? > > If so yes and no. > > Yes as it u

Re: Seeking help extricating data from Amazon RDS Aurora/Postgres

> On Jan 29, 2024, at 11:22, Bill Mitchell wrote: > > Wondering if any of the other members of this LISTSERV have tried migrating > their data off of Amazon RDS Aurora Postgres with success. Any logical-replication based solution (DMS, fivetran, in-core logical replication) will handle the

Seeking help extricating data from Amazon RDS Aurora/Postgres

We are attempting to extract one of our database from Amazon RDS Aurora/Postgres to another PostgreSQL cluster that is running directly on EC2 instances. This particular database is currently about 3.5TB. We are sensitive to this database being down for an extended period of time as it will a

Re: Scriptable way to validate a pg_dump restore ?

On Monday, 29 January 2024 at 09:06, Ron Johnson wrote: > > That's kinda like being asked to prove that rocks always fall when you drop > them. Either you trust physics, because physics has always worked, or you > must watch every rock, because next time it might not fall. The analogy is >

Re: Scriptable way to validate a pg_dump restore ?

On 1/29/24 10:12, Shaheed Haque wrote: Yes. But I was under the impression that the initial copy of logical replication was the same? Are you taking about the copy_data option to WITH? If so yes and no. Yes as it uses COPY to transfer the data. No as what COPY transfers can be affected

Re: Query performance in 9.6.24 vs 14.10

Thanks for the update. On Mon, Jan 29, 2024, 16:53 Ron Johnson wrote: > According to my tests, sometimes JIT is a little faster, and sometimes > it's a little slower. Mostly within the realm of statistical noise > (especially with each query having a sample size of only 13, on a VM that > lives

Re: Scriptable way to validate a pg_dump restore ?

On Mon, 29 Jan 2024, 23:57 Adrian Klaver, wrote: > On 1/29/24 09:28, Shaheed Haque wrote: > > > > > > Right, for me, state, not just record count is what I'm interested in > > (for the initial full table copy part of replication). So, given the > > explanation about the possible per-table window,

Re: Scriptable way to validate a pg_dump restore ?

On 1/29/24 09:28, Shaheed Haque wrote: Right, for me, state, not just record count is what I'm interested in (for the initial full table copy part of replication). So, given the explanation about the possible per-table window, is there some property of the table that could be used to confir

Re: Scriptable way to validate a pg_dump restore ?

On Mon, 29 Jan 2024, 22:52 Adrian Klaver, wrote: > On 1/29/24 00:12, Laura Smith wrote: > > Hi > > > > Let's say I've got a scenario where I'm doing a pg_dump replication > rather than online streaming, e.g. due to air-gap or whatever. > > > > Is there a scriptable way to validate the restore ?

Re: Query performance in 9.6.24 vs 14.10

According to my tests, sometimes JIT is a little faster, and sometimes it's a little slower. Mostly within the realm of statistical noise (especially with each query having a sample size of only 13, on a VM that lives on a probably-busy host). On Mon, Jan 29, 2024 at 9:18 AM Ron Johnson wrote: >

Re: Scriptable way to validate a pg_dump restore ?

On 1/29/24 00:12, Laura Smith wrote: Hi Let's say I've got a scenario where I'm doing a pg_dump replication rather than online streaming, e.g. due to air-gap or whatever. Is there a scriptable way to validate the restore ?  e.g. using doing something clever with ctid or something to ensure bo

Re: Query performance in 9.6.24 vs 14.10

Yes, jit=on. I'll test them with jit=off, to see the difference. (The application is 3rd party, so will change it at the system level.) On Mon, Jan 29, 2024 at 7:09 AM Bob Jolliffe wrote: > Out of curiosity, is the pg14 running with the default jit=on setting? > > This is obviously entirely du

Re: Query performance in 9.6.24 vs 14.10

Out of curiosity, is the pg14 running with the default jit=on setting? This is obviously entirely due to the nature of the particular queries themselves, but we found that for our workloads that pg versions greater than 11 were exacting a huge cost due to the jit compiler. Once we explicitly turn

Re: Scriptable way to validate a pg_dump restore ?

On Mon, Jan 29, 2024 at 3:12 AM Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > Hi > > Let's say I've got a scenario where I'm doing a pg_dump replication rather > than online streaming, e.g. due to air-gap or whatever. > > Is there a scriptable way to validate the restore ? e.g. using

Re: Scriptable way to validate a pg_dump restore ?

I'd also like to know how to do this. The current approaches seem, afaict, to involve making on both end of the connection. Even given the inherently racy nature of the issue, that seems unwieldy to me. https://www.postgresql.org/message-id/CAHAc2jdAHvp7tFZBP37awcth%3DT3h5WXCN9KjZOvuTNJaAAC_hg%40m

Scriptable way to validate a pg_dump restore ?

Hi Let's say I've got a scenario where I'm doing a pg_dump replication rather than online streaming, e.g. due to air-gap or whatever. Is there a scriptable way to validate the restore ?  e.g. using doing something clever with ctid or something to ensure both the schema and all its rows were re