Re: Handling glibc v2.28 breaking changes
On Sun, 2022-04-24 at 23:31 +0800, Pradeep Chhetri wrote: > I am sure this has been discussed multiple times in the past but I would like > to initiate > this discussion again. I have 3 nodes cluster of Postgres v9.6. They all are > currently > running on Debian 9 (with glibc v2.24) and need to upgrade them to Debian 10 > (with glibc v2.28) > without downtime. In order to bypass the glibc issue, I am trying to evaluate > whether I can > compile glibc v2.24 on Debian 10, pin postgres to use this manually compiled > glibc and > upgrade the linux distribution in rolling fashion. Don't use an old glibc. You will want to move to a different machine or upgrade the operating system, so you will have some down time anyway. You could consider upgrade in several steps: - pg_upgrade to v14 on the current operating system - use replication, than switchover to move to a current operating system on a different machine - REINDEX CONCURRENTLY all indexes on string expressions You could get data corruption and bad query results between the second and the third steps, so keep that interval short. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Handling glibc v2.28 breaking changes
On Mon, 25 Apr 2022 at 12:45, Laurenz Albe wrote: > > You could consider upgrade in several steps: > > - pg_upgrade to v14 on the current operating system > - use replication, than switchover to move to a current operating system > on a different > machine > - REINDEX CONCURRENTLY all indexes on string expressions > > You could get data corruption and bad query results between the second and > the third steps, > so keep that interval short. > We did something like this, with the addition of a step where we used a new-OS replica to run amcheck's bt_index_check() over all of the btree indexes to find those actually corrupted by the libc upgrade in practice with our data. It was a small fraction of them, and we were able to fit an offline reindex of those btrees and all texty non-btree indexes into an acceptable downtime window, with REINDEX CONCURRENTLY of everything else as a lower priority after the upgrade.
Re: Handling glibc v2.28 breaking changes
Thank you Laurenz and Nick. That sounds like a good plan to me. Best Regards, Pradeep On Mon, Apr 25, 2022 at 9:44 PM Nick Cleaton wrote: > On Mon, 25 Apr 2022 at 12:45, Laurenz Albe > wrote: > >> >> You could consider upgrade in several steps: >> >> - pg_upgrade to v14 on the current operating system >> - use replication, than switchover to move to a current operating system >> on a different >> machine >> - REINDEX CONCURRENTLY all indexes on string expressions >> >> You could get data corruption and bad query results between the second >> and the third steps, >> so keep that interval short. >> > > We did something like this, with the addition of a step where we used a > new-OS replica to run amcheck's bt_index_check() over all of the btree > indexes to find those actually corrupted by the libc upgrade in practice > with our data. It was a small fraction of them, and we were able to fit an > offline reindex of those btrees and all texty non-btree indexes into an > acceptable downtime window, with REINDEX CONCURRENTLY of everything else as > a lower priority after the upgrade. > >
Re: LwLocks contention
On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett wrote: > We're working to update our application so that we can > take advantage of the pruning. Are you also using native partitioning? > No partitioned tables at all, but we do have 1800 tables and some very complex functions, some trigger insanity, huge number of indexes, etc etc. There are lots of things to fix, but I just do not yet have a good sense of the most important thing to address right now to reduce the odds of this type of traffic jam occurring again. I very much appreciate you sharing your experience. If I could reliably reproduce the issue or knew what data points to start collecting going forward, that would at least give me something to go on, but it feels like I am just waiting for it to happen again and hope that some bit of information makes itself known that time. Perhaps I should have posted this to the performance list instead of general.
Re: LwLocks contention
On Mon, Apr 25, 2022 at 10:33 AM Michael Lewis wrote: > > On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett wrote: >> >> We're working to update our application so that we can >> take advantage of the pruning. Are you also using native partitioning? > > > No partitioned tables at all, but we do have 1800 tables and some very > complex functions, some trigger insanity, huge number of indexes, etc etc. > > There are lots of things to fix, but I just do not yet have a good sense of > the most important thing to address right now to reduce the odds of this type > of traffic jam occurring again. I very much appreciate you sharing your > experience. If I could reliably reproduce the issue or knew what data points > to start collecting going forward, that would at least give me something to > go on, but it feels like I am just waiting for it to happen again and hope > that some bit of information makes itself known that time. > > Perhaps I should have posted this to the performance list instead of general. In my experience lwlock contention (especially around buffer_mapping) is more about concurrent write activity than any particular number of tables/partitions. The first recommendation I would have is to install pg_buffercache and see if you can capture some snapshots of what the buffer cache looks like, especially looking for pinning_backends. I'd also spend some time capturing pg_stat_activity output to see what relations are in play for the queries that are waiting on said lwlocks (especially trying to map write queries to tables/indexes). Robert Treat https://xzilla.net
Re: Problems with installation on Mac OS
Hello Yes, i had this Execute the command it says and JDBC got installed without any problem. Don't remember if location shown was wrong, but you can install it if search for the package. El 23/4/22 a las 17:15, Daria Lesyk escribió: Hello, I'm trying to install PostgreSQL (the latest version) on Mac but the stackbuilder returns the following errors in the end of the installation of PostgreSQL. What I have to do to install that smoothly. Thank you in advance for your help.
Re: Problems with installation on Mac OS
Hi, El 23/4/22 a las 17:15, Daria Lesyk escribió: > > Hello, > I'm trying to install PostgreSQL (the latest version) on Mac but the > stackbuilder returns the following errors in the end of the installation of > PostgreSQL. > What I have to do to install that smoothly. > Thank you in advance for your help. > > Have you tried Postgres.app? Easy, quick install. > https://postgresapp.com/
