Re: PostgreSQL

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
>

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