News for hackers. Stuff that matters.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by eric4smith on Sunday August 28, 2022 @08:40PM (#32633659)
We are running pg11 for fairly big databases (almost terabyte size data directory).

I was waiting to upgrade to pg12 then 13 then 14.

With this change I seriously think I’ll just upgrade to 15 at the end of the year.

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by alberth on Sunday August 28, 2022 @10:22PM (#32634180)
Do you really want to upgrade to a .0 release?

Now is the perfect time to update to pg14 because it’s on rev 5 (14.5).

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by pilif on Sunday August 28, 2022 @11:23PM (#32634482)
To give some anecdata: I have been bitten twice by going to a .0 release with Postgres, once by index corruption and once by some subqueries returning wrong results in some cases.

I have since decided to always wait for a .1 with Postgres before updating.

The good news is that this is only a few weeks past the initial release.

Now if only more people upgraded during the RC phase already, then everybody could go to a .0 release.

And conversely, if everybody follows my (and your) advice, then .2 will be the new .1.

It’s never easy

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by PikachuEXE on Monday August 29, 2022 @01:21AM (#32635030)
Depends on how great you trust the quality of those released. `.1` is usually good enough for PSQL except the following issue.

14.4 was released with a fix on silent data corruption when using the CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY commands.

https://www.postgresql.org/about/news/postgresql-144-release...

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by pilif on Monday August 29, 2022 @04:18AM (#32635854)
That was a nasty one, but then again, my recommendation would still be to use `pg_repack` over reindex concurrently because that one also gets you concurrent clustering and that one was not affected by this bug.

I'm not downplaying the issue and index corruption is really bad, but I would wager a guess that admins who do need concurrent reindexing would also be aware of `pg_repack` and would prefer that anyways because of the other benefits it provides.

This is probably why it took 6 months for the issue to be reported and fixed.

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by bluetech on Monday August 29, 2022 @05:34AM (#32636253)
What advantage does pg_repack have when you only rebuild an index? Or do you mean it has advantage when pg_repack is run on the entire table?
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by jtc331 on Monday August 29, 2022 @07:43AM (#32637010)
pg_repack has some significant downsides in its implementation; I question whether it’s really a default over re-indexing concurrently. I’ve certainly not gotten that impression, and we maintain many very large Postgres clusters.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by inferiorhuman on Sunday August 28, 2022 @11:26PM (#32634492)
Interesting! Were the queries reliably returning wrong results?
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by pilif on Monday August 29, 2022 @04:16AM (#32635836)
it was in the 2012/2013 time frame, so I can't find the relevant release note any more, but it was reliably returning wrong results for a specific sub query pattern.

Not all of them were broken, but the broken one was returning wrong results 100% of the time.

Index corruption shows the same symptoms, but, of course, it is a different cause.

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by mike_hock on Monday August 29, 2022 @03:50AM (#32635680)
It sounds like Postgres's test coverage could be better ...
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by jeltz on Monday August 29, 2022 @05:56AM (#32636361)
Yes, PostgreSQL needs more tests, but no it is not really a coverage issue. Most of the serious bugs have been related to concurrency or other things which simple test coverage can find. Finding these bugs is usually not trivial.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by endisneigh on Monday August 29, 2022 @06:26AM (#32636511)
This is something foundationDB does pretty well. They built a simulator that tests such things. Doubt you could port to Postgres easily though.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by jeltz on Monday August 29, 2022 @07:08AM (#32636727)
That is exactly the kind of tools PostgreSQL needs more of. There are some tools but more are needed. More plain old test coverage will not help much if at all.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by leaflets2 on Tuesday August 30, 2022 @12:35PM (#32652313)
You meant "cannot" right:

> which simple test coverage cannot find.

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by paulryanrogers on Sunday August 28, 2022 @08:50PM (#32633718)
Major version jumps are always fun. Recently I discovered RDS recommends pglogical over built in replication for a reason, the latter doesn't work well in RDS with larger than RAM replica logs.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by olau on Monday August 29, 2022 @04:05AM (#32635777)
Aurora doesn't work with a query that computes a temporary table larger than memory either. Amazon's Postgres things are not Postgres.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by paulryanrogers on Monday August 29, 2022 @09:33AM (#32638012)
I wasn't using Aurora at the time. And I don't expect non-Aurora RDS to be exactly the same as vanilla Postgres either. Still, it was surprising that the old Pg solution is supported (pglogical) while the new one wasn't yet, at least for my version jump.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by PikachuEXE on Monday August 29, 2022 @01:19AM (#32635018)
I have never used RDS.

Anyway here is a post for using built-in logical replication.

https://dev.to/pikachuexe/postgresql-logical-replication-for...

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by paulryanrogers on Monday August 29, 2022 @09:31AM (#32637988)
Posts like this didn't help in my case. Though depending on versions being replicated and RDS settings it could work.
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by PikachuEXE on Monday August 29, 2022 @09:57PM (#32645823)
Ya there are many managed services and limitations around those.

Only throwing out this as a reference.

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by taspeotis on Sunday August 28, 2022 @09:42PM (#32633956)
Hey, we use logical replication on RDS. Never considered pglogical. Do you have a link to Amazon’s recommendation of pglogical?
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by paulryanrogers on Sunday August 28, 2022 @10:01PM (#32634062)
So I was using their guide for minimal downtime upgrades [0], see option 'D'. And I was replicating Pg 11 to 14. Logical replication appeared to work in that case yet big tables would never catch up or appear to get truncated. If you're replicating among nodes at the same Pg version then built-in replication may work for you.

Once I switched to pglogical jumping major versions worked as intended. Now it's possible in the rush I used the wrong setting somewhere and built-in logical replication can work between major versions. Though I lost enough time experimenting I'll keep using pglogical until AWS officially recommends something else.

[0] https://aws.amazon.com/blogs/database/part-1-upgrade-your-am...

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by taspeotis on Tuesday August 30, 2022 @07:36AM (#32649074)
Thank you!
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by MuffinFlavored on Sunday August 28, 2022 @08:53PM (#32633741)
What are you doing for replication, just curious?

Reads are easy with replicas, right? What are you doing to handle "writes" across all your apps/regions/etc.?

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by eric4smith on Monday August 29, 2022 @09:04AM (#32637678)
Just standard replication on 2 other nodes.

We don’t have huge constant loads. Over-provisioned on massive bare metal so we can take a lot.

Also daily pg_basebackup which does not eat too many resources. Standard pg_dump is impossible.

About to also add log shipping when I upgrade. We don’t do super critical things like banking or anything like that. But I do want to move to the point where at worse we only lose a few minutes of data.

Now we can lose up to 24 hours if all the replicas die at once.

Re:PostgreSQL 15: Stats Collector Gone? What’s New? by MuffinFlavored on Monday August 29, 2022 @06:10PM (#32644197)
So that's 3 nodes total, one permanently master and the other two permanently read-only slaves? Is there any kind of "automatic rollover" if the master goes down where one of the slaves automatically promotes itself?
Re:PostgreSQL 15: Stats Collector Gone? What’s New? by eric4smith on Monday August 29, 2022 @11:25PM (#32646310)
Not necessary. As I said, we are not at all a critical service. If we are down an hour, it's not a big deal (like 95% of the rest of the inter-webs). We are not banksters.

If it goes down, we get a page, and we can have it switched by hand in a few minutes.

I guess if our service was super-critical, I would do that, but since it's not, the three of us that work as developers and sys-admins can deal with it very quickly.