Back to all use cases

ConsultingHealthcare / Benefits

A major-version upgrade is found out in the rehearsal, not the runbook

Regional Health Benefits Administrator

Planning and rehearsing an upgrade from Greenplum 6 to Greenplum 7 on AWS, including catalog checks, a compatibility review of SQL and extensions, and a tested rollback.

Overview

Greenplum 7 is built on a newer PostgreSQL than Greenplum 6, so objects, SQL and extensions that depend on 6 behavior have to be found before the upgrade window, not during it.

Challenge

The warehouse supports claims analytics and had run on Greenplum 6 for several years. The team had deferred the upgrade because they could not say what would break. A catalog with years of drift, a set of legacy backup scripts, and several dozen reporting views and external table definitions made the risk hard to size, and the maintenance window was one weekend.

Environment

Greenplum 6 on AWS EC2 with EBS-backed segments, a standby coordinator, gpbackup to S3, and PXF external tables reading from object storage.

Approach

AceMQ built a compatibility inventory first, then rehearsed the upgrade twice on a restored copy of production before agreeing the window. The rehearsals produced the real list of changes and timings, and a rollback that had been run end to end.

Solution

  1. Ran gpcheckcat and cleaned catalog inconsistencies before any upgrade step
  2. Inventoried SQL, views, functions, extensions and external tables against Greenplum 7 behavior and removed or rewrote the incompatible ones
  3. Replaced legacy backup scripts with gpbackup and gprestore jobs, tested with a real restore
  4. Rehearsed the upgrade twice on a copy restored from backup, recording timings for each stage
  5. Wrote and tested a rollback to the Greenplum 6 cluster, including the decision point after which rollback is no longer cheap
  6. Planned the cutover window, a post-upgrade analyze run and a query plan comparison on the twenty most expensive reports

Outcome

The upgrade ran inside a single weekend window and the rollback was never needed. The post-upgrade plan comparison found two regressed queries, both fixed before Monday.

Technologies

GreenplumAWSPostgreSQL
  • Assessment

    Greenplum MPP Cluster and Distribution Assessment

    Enterprise Technology OrganizationsCross-Industry

    A review of Greenplum segment count, distribution keys, storage selection and vacuum scheduling for petabyte-scale analytics, aimed at the design decisions that cause skew.

    GreenplumPostgreSQL
    Read case study: Greenplum MPP Cluster and Distribution Assessment
  • Assessment

    PostgreSQL Performance and Bloat Assessment

    Retail Technology ProviderCross-Industry

    Independent assessment of query performance, index health, table and index bloat, and connection management for a cluster with degrading response times.

    PostgreSQLPgBouncerPrometheusLinux
    Read case study: PostgreSQL Performance and Bloat Assessment
  • Remediation

    Amazon SQS Dead-Letter Queues That Expired Their Own Evidence

    Benefits Enrolment PlatformHealthcare

    Failed enrolment events were disappearing from the dead-letter queue before anyone looked at them. The fix was a retention setting, a receive count, and a redrive path that did not create duplicates.

    Amazon SQSAWS LambdaAWSTerraform
    Read case study: Amazon SQS Dead-Letter Queues That Expired Their Own Evidence
  • Remediation

    Greenplum Degraded Cluster and Mirror Recovery

    Regional Telecom OperatorTelecommunications

    Recovering a Greenplum cluster that had run on a single copy of its data for seven weeks after a host failure, then rebuilding the mirroring layout so one host loss no longer halves performance.

    GreenplumPostgreSQLLinux
    Read case study: Greenplum Degraded Cluster and Mirror Recovery
  • Consulting

    Pentaho to Modern ELT Stack Migration

    Healthcare Payer OrganizationHealthcare

    Planning and executing a staged move off Pentaho Data Integration onto a modern ELT stack, without a big-bang cutover of hundreds of transformations.

    PentahoAirbytedbtPostgreSQLKubernetes
    Read case study: Pentaho to Modern ELT Stack Migration

Related Reading

Need Greenplum Architecture Guidance?

AceMQ's senior Greenplum engineers have handled this exact type of engagement before. Whether you need architectural guidance, hands-on remediation, or an ongoing managed partnership, we're ready to help.