Re: PostgreSQL

This document is for Aurora, not Postgres - different product that claims compatibility in marketing materials.

About upgrades - Postgres database should be reloaded from dump or reindexed when OS is upgraded to a new major version (glibc version changes):
https://elephanttamer.net/?p=61

On Sat, 7 Jan 2023 at 02:08, Ramsankar Cheruvattath < ram.cheruvattath_at_gmail.com> wrote:

> Aurora Postgres does have zero downtime patching I believe.
>
>
> https://aws.amazon.com/about-aws/whats-new/2022/06/amazon-aurora-postgresql-compatible-edition-supports-zero-downtime-patching/
>
> Thanks
> Ramsankar Cheruvattath (Ram)
>
>
>
> On Fri, Jan 6, 2023 at 3:17 PM Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:
>
>> Postgres is a single instance database, so "Zero downtime patching with
>> PostgreSQL" can't be possible... As is the case with Oracle single instance
>> RDBMS. They are exactly the same in this regard.
>> The link Jeff posted requires RAC. As would be the case also with
>> Postgres - you need like Yugabyte or some similar multi-instance flavored
>> Postgres-compatible software - not Postgres.
>> Near zero isn't zero.
>>
>
>> On Fri, Jan 6, 2023 at 8:34 PM Terrian Thomas J CTR DLA INFO OPERATIONS <
>> dmarc-noreply_at_freelists.org> wrote:
>>
>>> Jeff, that is not possible with Oracle on AWS RDS is it?
>>>
>>>
>>>
>>> *From:* Jeff Smith <jeff.d.smith_at_oracle.com>
>>> *Sent:* Friday, January 6, 2023 1:15 PM
>>> *To:* Terrian, Thomas J CTR DLA INFO OPERATIONS (USA) <
>>> Tom.Terrian.ctr_at_dla.mil>; Clay Jackson (cjackson) <
>>> Clay.Jackson_at_quest.com>; tim.evdbt_at_gmail.com; dbakevlar_at_gmail.com
>>> *Cc:* oracle-l <oracle-l_at_freelists.org>
>>> *Subject:* [Non-DoD Source] RE: PostgreSQL
>>>
>>>
>>>
>>> Well we can take down reason #1
>>>
>>>
>>>
>>>
>>> https://docs.oracle.com/en/database/oracle/oracle-database/19/fppad/patching-oracle-database-without-downtime.html
>>>
>>>
>>>
>>> Jeff
>>>
>>>
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>>> Behalf Of *Terrian Thomas J CTR DLA INFO OPERATIONS
>>> *Sent:* Friday, January 6, 2023 1:06 PM
>>> *To:* Clay Jackson (cjackson) <Clay.Jackson_at_quest.com>;
>>> tim.evdbt_at_gmail.com; dbakevlar_at_gmail.com
>>> *Cc:* oracle-l <oracle-l_at_freelists.org>
>>> *Subject:* [External] : RE: PostgreSQL
>>>
>>>
>>>
>>> The “Why” that I was given by the customer: 1. Zero downtime patching
>>> with PostgreSQL. 2. Cost less.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> *From:* Clay Jackson (cjackson) <Clay.Jackson_at_quest.com>
>>> *Sent:* Friday, January 6, 2023 11:58 AM
>>> *To:* tim.evdbt_at_gmail.com; dbakevlar_at_gmail.com; Terrian, Thomas J CTR
>>> DLA INFO OPERATIONS (USA) <Tom.Terrian.ctr_at_dla.mil>
>>> *Cc:* oracle-l <oracle-l_at_freelists.org>
>>> *Subject:* [Non-DoD Source] RE: PostgreSQL
>>>
>>>
>>>
>>> Lots of great comments from Kellyn and Tim on the pitfalls and ways to
>>> avoid/mitigate them or at least know what you’re getting into.
>>>
>>>
>>>
>>> Taking a step back – you haven’t shared the “high-level WHY”. As Tim
>>> stated, “it is what it is” and “if it ain’t broke, don’t fix it” can be
>>> pretty compelling.
>>>
>>>
>>>
>>> Any “migration” or move is going to cost something, both “external hard
>>> costs” and “soft costs” including foregoing implementation of NEW systems.
>>> What’s the projected return on investment, and what BUSINESS gains do you
>>> expect as a result of this effort?
>>>
>>>
>>>
>>> We’ve observed several cases where a “mandate from on high” to “move off
>>> Oracle” was tempered into, “Let’s do FUTURE development of NEW systems in
>>> PostgreSQL” when the “real” cost of “moving off Oracle” was discovered.
>>> You mentioned you have both OLTP and a DataWarehouse. Depending on
>>> “which PostgreSQL” you choose, you might consider moving the DataWarehouse
>>> first and then using some sort of replication or ETL to move the data from
>>> the OLTP system(s) to the DataWarehouse. Then you can move forward with
>>> NEW systems in PostgreSQL and let your Oracle OLTP systems die a “natural”
>>> death.
>>>
>>>
>>>
>>> Clay Jackson
>>>
>>>
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>>> Behalf Of *Tim Gorman
>>> *Sent:* Friday, January 6, 2023 8:23 AM
>>> *To:* dbakevlar_at_gmail.com; Tom.Terrian.ctr_at_dla.mil
>>> *Cc:* oracle-l <oracle-l_at_freelists.org>
>>> *Subject:* Re: PostgreSQL
>>>
>>>
>>>
>>> *CAUTION:* This email originated from outside of the organization. Do
>>> not follow guidance, click links, or open attachments unless you recognize
>>> the sender and know the content is safe.
>>>
>>>
>>>
>>> Expanding on Kellyn's last point, specifically about trying to cram 50
>>> kg of Oracle database onto 5 kg of infrastructure...
>>>
>>> In each of the source Oracle databases, have you measured...
>>>
>>> - I/O latency
>>>
>>>
>>> - a good indication is the average wait time on "db file sequential
>>> read" events from AWR/STATSPACK reports
>>>
>>>
>>> - I/O throughput
>>>
>>>
>>> - IOStats By Function and IOStats by FileType are two sections of
>>> the AWR/STATSPACK reports to reference
>>>
>>>
>>> Most importantly, are you generating AWR/STATSPACK reports from "peak
>>> workload" periods of time? Or are you just using any old snapshots from
>>> any old time period? If the latter, then don't bother. As with any use of
>>> AWR/STATSPACK reports, the time period captured is the whole point of the
>>> exercise. When one is assessing workloads to move to new infrastructure,
>>> it is best to assess the worst-case scenario, which are peak workloads. If
>>> you don't know when peak workloads occur in your database, one bit of help
>>> might be the "busiest_awr.sql" script posted on Github HERE
>>> <https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FAzure%2FOracle-Workloads-for-Azure%2Fblob%2Fmain%2Faz-oracle-sizing%2Fbusiest_awr.sql&data=05%7C01%7Cclay.jackson%40quest.com%7Cc54f796bb74142539fee08daf00269d4%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638086190405673374%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=gQ7r1rD2mJGq7mODn4%2FMWL%2Fo%2BlOTw5KGwi6Agn7MVp4%3D&reserved=0>.
>>> It queries the DBA_HIST_SYSMETRIC_HISTORY views to display AWR snapshots
>>> with the highest values for CPU and I/O. I don't yet have a version of the
>>> script for STATSPACK, but I expect to have one posted by the end of next
>>> week, if not sooner. Since STATSPACK doesn't capture SYSMETRIC
>>> information, I anticipate having to use SYSSTAT information, which I
>>> believe is less useful and has more problems for this sort of analysis.
>>>
>>> Anyway, if an AWR/STATSPACK report generated under peak workload
>>> indicates an Oracle database with the following characteristics...
>>>
>>> - 73 average active sessions (AAS) for significant periods of time
>>> - I/O latency averaging 0.3 ms
>>> - I/O throughput averaging greater than 3000 MB/s
>>>
>>>
>>> ...and the plan is to replatform it into a PaaS service or
>>> infrastructure capped at 300 MB/s, then it is a non-starter. Same if the
>>> PaaS service or infrastructure has a maximum of 64 vCPUs.
>>>
>>> Of course, my own self from 10-15 years ago would have stepped in now
>>> and pontificated about how any Oracle workload can be tuned to run on an
>>> iPhone, but the reality is that it is unlikely. I still know that there is
>>> always room for improvement, but also that not many organizations are
>>> interested in optimizing. The dreaded phrases "*it is what it is*" and
>>> "*if it ain't broke don't fix it*" come into play.
>>>
>>> This platform-agnostic baseline information is so easy to obtain, and it
>>> provides immediate value when assessing rehosting or replatforming.
>>>
>>> On 1/6/2023 7:33 AM, Kellyn Pot'Vin-Gorman wrote:
>>>
>>> I'm going to jump in here and start with the reason why- Although Tim
>>> and I both do Oracle on Azure IaaS, we receive a lot of Oracle databases
>>> that fail migrating to PostgreSQL. The reasons that this commonly happens
>>> is as follows:
>>>
>>> 1. The team underestimated all the applications and connectors post the
>>> database migration that required refactoring. Oracle is never just a
>>> database- there's always more to consider.
>>>
>>> 2. The database housed complex PL/SQL functions and advanced Oracle
>>> features that weren't able, (and remember, I'm not the one who did the
>>> migration, just received the database back and hearing this second hand) to
>>> duplicate it with PostgreSQL.
>>>
>>> 3. The performance had years to be optimized in Oracle and refactoring
>>> takes considerable time and dedication. If the customer and the team
>>> performing the migration isn't willing to not just refactor but dedicate a
>>> full redesign to use the best of PostgreSQL to optimize the workload, that
>>> workload "explosion" can impact performance so significantly that it's
>>> unacceptable.
>>>
>>> 4. The flavor of PostgreSQL MATTERS. What EnterpriseDB Big Animal can
>>> handle is different than CosmosDB Hyperscale Citus and that's different
>>> than Amazon Redshift or what Yugabyte offers. Use the right PostgreSQL for
>>> the job. If I see one more 3000 MBPs Oracle workload attempted in a PaaS
>>> PostgreSQL that can only do 300MBPs max, I'm going to scream. :)
>>>
>>>
>>>
>>>
>>>
>>> *Kellyn Gorman*
>>>
>>> DBAKevlar Blog
>>> <https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdbakevlar.com%2F&data=05%7C01%7Cclay.jackson%40quest.com%7Cc54f796bb74142539fee08daf00269d4%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638086190405673374%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=PETWViiX7giuv3UXS8n5ngnV24L7V7a43pd%2BMMT4whY%3D&reserved=0>
>>>
>>> about.me/dbakevlar
>>> <https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fabout.me%2Fdbakevlar&data=05%7C01%7Cclay.jackson%40quest.com%7Cc54f796bb74142539fee08daf00269d4%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638086190405830668%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=DgG3zkfEw3%2F7z2M8J7TwqVi8bWYb8%2BBH2MHYtU9nGNI%3D&reserved=0>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Fri, Jan 6, 2023 at 4:37 AM Terrian Thomas J CTR DLA INFO OPERATIONS <
>>> dmarc-noreply_at_freelists.org> wrote:
>>>
>>> Here are some open ended question for the group…We are starting to look
>>> at migrating our databases from Oracle to PostgreSQL. I know nothing about
>>> PostgreSQL.
>>>
>>>
>>>
>>> Has anyone done a pro’s and con’s list of Oracle vs. PostgreSQL?
>>>
>>> Anyone have a lesson’s learned list from migrating from Oracle to
>>> PostgreSQL?
>>>
>>> Any thoughts/comments on PostgreSQL?
>>>
>>> I kind of think that you get what you pay for…wouldn’t that mean that
>>> Oracle would outperform PostgreSQL in every way?
>>>
>>>
>>>
>>> Any comments would be appreciated.
>>>
>>>
>>>
>>> Tom
>>>
>>>
>>>
>>
>>
>> --
>> Ilmar Kerm
>>
> --
Ilmar Kerm

--
http://www.freelists.org/webpage/oracle-l