Auto Minor Version Upgrade (AMVU) caused downtime — How to ensure seamless upgrade?

In RDS Postgres, engine upgrades (both major and Minor ) have downtime associated with them.

In Aurora Postgres Database, AWS provides Zero Downtime Upgrade in Specific Engine versions. [+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.MinorUpgrade.html#USER_UpgradeDBInstance.PostgreSQL.Minor.zdp

Specific to RDS PostgreSQL :

• Minor upgrades : shorter downtime because we need to restart the server to bring the new binaries into affect (thats when the upgrade completes)
• Major Upgrades : longer duration because it dumps and reload the database.

We cannot eliminate the downtime in RDS Postgres associated with upgrades however it can be minimized using approaches like:

1/ Multi-AZ configuration : standby is upgraded first followed with a failover (which is the duration of downtime) then the new standby gets upgraded. [+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html

2/ B/G Deployment : Create a Green environment, perform required actions on it (like upgrade) and then perform a switchover. Switchover is the duration of downtime. [+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-overview.html

Answering your specific questions below:

• Under what conditions can an Auto Minor Version Upgrade cause brief downtime?

This is (for now) the default behavior of RDS Postgres upgrades and cant be changed. Upgrades have associated downtime.

• Is downtime expected behavior, or could our experience indicate a misconfiguration or issue?

It is expected.

• What are the differences in behavior between: AMVU disabled (manual minor version upgrades) AMVU enabled (automatic upgrades)

In the backend, both behave the same way, nothing changes in terms of how they are taking place. Only difference is when AmVU is disabled, you have to manually upgrade the DB engine where as this happens automatically AmVU is enabled during the DB maintenance window.

• What steps or architecture choices (e.g., Multi-AZ, RDS Proxy, connection draining, maintenance window tuning) can ensure truly seamless or zero-downtime minor version upgrades?

This is for now not possible in RDS Postgres. Downtime can be minimized by following the approach shared above but cant be eliminated completely.

• If seamless upgrades are only possible in specific configurations (e.g., Multi-AZ + Proxy), could you please confirm the best practices?

Its not possible.

Auto Minor Version Upgrades (AMVU) for RDS PostgreSQL can indeed cause brief periods of downtime, contrary to expectations of seamless upgrades. Here's a clarification on your questions:

1. Conditions for downtime during AMVU:

• Single-AZ deployments will experience downtime during the upgrade process.
• Multi-AZ deployments may experience a brief period of unavailability, typically lasting several minutes.
• The size of your database and the specific changes in the new version can affect the duration of the upgrade process.

2. Some downtime during AMVU is expected behavior, especially for single-AZ deployments. However, the extent of downtime you experienced might indicate a need for architectural improvements.

3. Differences between AMVU disabled and enabled:

• With AMVU disabled, you have more control over when upgrades occur but must manually initiate them.
• With AMVU enabled, upgrades occur automatically during your maintenance window, which can be less predictable but ensures you're running the latest minor version.

To minimize or eliminate downtime during minor version upgrades:

1. Use Multi-AZ deployments: In a Multi-AZ setup, Amazon RDS upgrades the standby instance first, then promotes it to primary, reducing downtime to approximately 35 seconds.

2. Implement RDS Proxy: Using RDS Proxy in conjunction with Multi-AZ can further reduce downtime to as little as one second or less.

3. Consider Multi-AZ DB clusters: These deployments upgrade reader instances one at a time, then promote a reader to writer, minimizing downtime.

4. Use connection draining: This helps manage existing connections during the upgrade process.

5. Optimize your maintenance window: Schedule upgrades during periods of low traffic, which you can identify using Performance Insights.

6. Implement retry logic: Use AWS SDKs with exponential backoff and jitter for better handling of API requests during the upgrade.

7. For immediate switchover to an upgraded cluster, consider using Amazon Aurora Blue/Green Deployments.

Best practices for truly seamless upgrades typically involve a combination of Multi-AZ deployments and RDS Proxy. This configuration provides the most robust protection against downtime during minor version upgrades. However, it's important to note that while these practices significantly reduce downtime, they may not completely eliminate it in all scenarios.

Remember to test your upgrade process on a non-production environment that mirrors your production setup to ensure it meets your specific requirements for minimal downtime.
Sources
Upgrades of the RDS for PostgreSQL DB engine - Amazon Relational Database Service
Our DB Cluster is Down during Minor Version Upgrade | AWS re:Post
Upgrading a DB instance engine version - Amazon Relational Database Service