Re: [External] : Re: PostgreSQL

Here is some information on Aurora Postgres and Zero-Downtime Patching (ZDP):
https://aws.amazon.com/about-aws/whats-new/2022/06/amazon-aurora-postgresql-compatible-edition-supports-zero-downtime-patching/

Thanks
Jeremiah

On Fri, Jan 6, 2023 at 12:34 PM Jeff Smith <dmarc-noreply_at_freelists.org> wrote:

> What you want, is possible, with Oracle.
>
>
>
> Whether you want Oracle or not, is something I can’t help with.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Ilmar Kerm
> *Sent:* Friday, January 6, 2023 3:17 PM
> *To:* Oracle-L Freelists <oracle-l_at_freelists.org>
> *Subject:* [External] : Re: PostgreSQL
>
>
>
> 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://urldefense.com/v3/__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__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJQ!!ACWV5N9M2RV99hQ!Jnj4LOGjOKOySonbc1dnXcKzptHymZ1CvPS2Kzyv7mnlVPIP6HaLTLW6yhid3xjiitOmJxJ66Bkae2rjTVx-Fw$>.
> 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://urldefense.com/v3/__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__;JSUlJSUlJSUlJSUlJSUlJSUlJSUl!!ACWV5N9M2RV99hQ!Jnj4LOGjOKOySonbc1dnXcKzptHymZ1CvPS2Kzyv7mnlVPIP6HaLTLW6yhid3xjiitOmJxJ66Bkae2q99LtbPw$>
>
> about.me/dbakevlar
> <https://urldefense.com/v3/__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__;JSUlJSUlJSUlJSUlJSUlJSUlJSUlJQ!!ACWV5N9M2RV99hQ!Jnj4LOGjOKOySonbc1dnXcKzptHymZ1CvPS2Kzyv7mnlVPIP6HaLTLW6yhid3xjiitOmJxJ66Bkae2oVTRt_-g$>
>
>
>
>
>
>
>
> 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