<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Kumaran rajendran on Medium]]></title>
        <description><![CDATA[Stories by Kumaran rajendran on Medium]]></description>
        <link>https://medium.com/@mathew_paul?source=rss-cda368cdc66a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*B6Xq4P6s9O2m7IRL</url>
            <title>Stories by Kumaran rajendran on Medium</title>
            <link>https://medium.com/@mathew_paul?source=rss-cda368cdc66a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 03:10:17 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mathew_paul/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Zero-Downtime Amazon RDS PostgreSQL Upgrade from Version 12 to 16 Using AWS Blue/Green Deployments]]></title>
            <link>https://medium.com/@mathew_paul/zero-downtime-amazon-rds-postgresql-upgrade-from-version-12-to-16-using-aws-blue-green-deployments-096e9152e693?source=rss-cda368cdc66a------2</link>
            <guid isPermaLink="false">https://medium.com/p/096e9152e693</guid>
            <dc:creator><![CDATA[Kumaran rajendran]]></dc:creator>
            <pubDate>Sat, 20 Jun 2026 18:35:31 GMT</pubDate>
            <atom:updated>2026-06-20T18:35:31.201Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Zero-Downtime Amazon RDS PostgreSQL Upgrade from Version 12 to 16 Using AWS Blue/Green Deployments</strong></p><p><strong>Introduction</strong></p><p>Upgrading a production PostgreSQL database is often one of the most critical activities for database administrators and platform engineers. Traditional upgrade approaches typically require downtime windows, application outages, and extensive rollback planning.</p><p>Amazon RDS Blue/Green Deployments significantly simplify major version upgrades by creating a synchronized staging environment (Green) from the production environment (Blue), enabling thorough validation before switching traffic with minimal downtime.</p><p>This article documents the end-to-end process of upgrading an Amazon RDS PostgreSQL production environment from PostgreSQL 12 to PostgreSQL 16 using AWS Blue/Green Deployments, including prerequisite checks, configuration requirements, validation steps, switchover procedures, and rollback considerations.</p><p><strong>Architecture Overview</strong></p><p>In a Blue/Green deployment:</p><ul><li><strong>Blue Environment</strong> = Current Production PostgreSQL 12</li><li><strong>Green Environment</strong> = New PostgreSQL 16 environment</li><li>AWS uses <strong>logical replication</strong> to continuously synchronize changes from Blue to Green.</li><li>During switchover, AWS redirects application traffic to the Green environment with minimal downtime.</li></ul><p><strong>Benefits</strong></p><ul><li>Near-zero downtime upgrades</li><li>Full validation before production cutover</li><li>Simplified rollback strategy</li><li>Reduced operational risk</li><li>Automatic endpoint renaming during switchover</li></ul><p><strong>Pre-Upgrade Assessment</strong></p><p>Before creating the Blue/Green deployment, several compatibility checks must be completed.</p><p><strong>1. Verify Absence of Unlogged Tables</strong></p><p>Unlogged tables are not replicated to the Green environment.</p><p>SELECT count(1)</p><p>FROM pg_class</p><p>WHERE relkind = ‘r’</p><p>AND relpersistence = ‘u’;</p><p>Expected result:</p><p>count</p><p>— — —</p><p>0</p><p>A result of zero confirms that all tables are eligible for replication.</p><p><strong>2. Verify Logical Replication Is Not Already Configured</strong></p><p>The Blue database cannot already act as:</p><ul><li>Logical Publisher</li><li>Logical Subscriber</li></ul><p>Check existing publications:</p><p>SELECT * FROM pg_publication;</p><p>Check existing subscriptions:</p><p>SELECT * FROM pg_subscription;</p><p>Expected result:</p><p>(0 rows)</p><p>Any existing publication or subscription must be removed before creating the Blue/Green deployment.</p><p><strong>3. Verify Foreign Data Wrapper (FDW) Configuration</strong></p><p>If PostgreSQL Foreign Data Wrappers are used, always configure remote servers using RDS endpoint names rather than IP addresses.</p><p>Check for configured foreign servers:</p><p>SELECT srvname,</p><p>srvtype,</p><p>srvversion</p><p>FROM pg_foreign_server;</p><p>Using endpoint names ensures FDW connectivity remains functional after switchover because AWS automatically updates endpoints.</p><p><strong>4. Identify Tables Without Primary Keys</strong></p><p>Logical replication requires a method to uniquely identify rows during UPDATE and DELETE operations.</p><p>Tables without a primary key must use:</p><p>REPLICA IDENTITY FULL</p><p><strong>Identify Tables Missing Primary Keys</strong></p><p>SELECT schemaname,</p><p>tablename</p><p>FROM pg_tables t</p><p>WHERE NOT EXISTS (</p><p>SELECT 1</p><p>FROM pg_index i</p><p>WHERE i.indrelid =</p><p>(t.schemaname || ‘.’ || t.tablename)::regclass</p><p>AND i.indisprimary</p><p>);</p><p><strong>Generate REPLICA IDENTITY FULL Statements</strong></p><p>SELECT</p><p>‘ALTER TABLE ‘ || schemaname || ‘.’ || tablename ||</p><p>‘ REPLICA IDENTITY FULL;’</p><p>FROM pg_tables t</p><p>WHERE NOT EXISTS (</p><p>SELECT 1</p><p>FROM pg_index i</p><p>WHERE i.indrelid =</p><p>(t.schemaname || ‘.’ || t.tablename)::regclass</p><p>AND i.indisprimary</p><p>);</p><p><strong>Validation Query</strong></p><p>Verify no table is missing the required replica identity configuration:</p><p>SELECT</p><p>n.nspname AS schema_name,</p><p>c.relname AS table_name,</p><p>c.relreplident</p><p>FROM pg_class c</p><p>JOIN pg_namespace n</p><p>ON n.oid = c.relnamespace</p><p>WHERE c.relkind = ‘r’</p><p>AND n.nspname NOT IN (‘pg_catalog’,’information_schema’)</p><p>AND NOT EXISTS (</p><p>SELECT 1</p><p>FROM pg_constraint con</p><p>WHERE con.conrelid = c.oid</p><p>AND con.contype IN (‘p’,’u’)</p><p>)</p><p>AND c.relreplident &lt;&gt; ‘f’;</p><p>The result set should be empty.</p><p><strong>Enable Logical Replication</strong></p><p>Blue/Green deployments for major PostgreSQL version upgrades rely on logical replication.</p><p>Create a custom parameter group and enable:</p><p>rds.logical_replication = 1</p><p>A reboot is required after modifying this parameter.</p><p><strong>Recommended Parameter Settings</strong></p><p>For this upgrade, the following values were configured:</p><p>rds.logical_replication = 1</p><p>max_replication_slots = 20</p><p>max_wal_senders = 35</p><p>max_logical_replication_workers = 20</p><p>max_worker_processes = 50</p><p><strong>Parameter Guidance</strong></p><p><strong>Parameter</strong></p><p><strong>Recommendation</strong></p><p>max_replication_slots</p><p>Greater than number of databases</p><p>max_wal_senders</p><p>Slightly higher than replication slots</p><p>max_logical_replication_workers</p><p>Number of databases plus reserve</p><p>max_worker_processes</p><p>At least logical replication workers + 1</p><p>These settings ensure sufficient worker capacity during synchronization.</p><p><strong>Read Replica Considerations</strong></p><p>Existing read replicas must be removed before creating the Blue/Green deployment.</p><p><strong>Important</strong></p><p>Record the read replica endpoint before deletion.</p><p>Example:</p><p>nonprod-postgres-ingcsprd-read-repl-us-west2.cgx8rjk5grjp.us-west-2.rds.amazonaws.com</p><p>After switchover, recreate the replica using the same naming convention if required by applications or monitoring tools.</p><p><strong>Materialized Views</strong></p><p>Materialized views are not automatically refreshed after switchover.</p><p>List all materialized views:</p><p>SELECT *</p><p>FROM pg_matviews;</p><p>Or:</p><p>\dm+</p><p>After cutover:</p><p>REFRESH MATERIALIZED VIEW view_name;</p><p>Schedule refresh operations as part of the post-upgrade checklist.</p><p><strong>Large Object Validation</strong></p><p>Large objects may introduce additional migration considerations.</p><p>Check for large objects:</p><p>SELECT pg_size_pretty(</p><p>sum(octet_length(data))</p><p>)</p><p>FROM pg_largeobject;</p><p>In this upgrade assessment, no significant large object data was identified.</p><p><strong>Change Freeze Requirement</strong></p><p>Once the Blue/Green deployment starts:</p><ul><li>No DDL changes</li><li>No DCL changes</li><li>No schema modifications</li><li>No object creation</li><li>No object deletion</li></ul><p>Examples:</p><ul><li>CREATE TABLE</li><li>ALTER TABLE</li><li>DROP TABLE</li><li>CREATE INDEX</li><li>GRANT</li><li>REVOKE</li></ul><p>Any structural change may impact replication consistency.</p><p><strong>Creating the Blue/Green Deployment</strong></p><p>After all prerequisite checks are complete:</p><ol><li>Open Amazon RDS Console.</li><li>Select the PostgreSQL 12 instance.</li><li>Choose <strong>Create Blue/Green Deployment</strong>.</li><li>Select PostgreSQL 16 as the target version.</li><li>Review configuration.</li><li>Create deployment.</li></ol><p>AWS automatically:</p><ul><li>Creates Green environment</li><li>Establishes logical replication</li><li>Synchronizes ongoing transactions</li></ul><p><strong>Pre-Switchover Validation</strong></p><p>Before cutover, perform the following checks.</p><p><strong>1. Verify Target Version</strong></p><p>SELECT version();</p><p>Expected output:</p><p>PostgreSQL 16.x</p><p><strong>2. Verify Replication Lag</strong></p><p>SELECT</p><p>slot_name,</p><p>active,</p><p>pg_size_pretty(</p><p>pg_current_wal_lsn() — confirmed_flush_lsn</p><p>) AS lag,</p><p>(pg_current_wal_lsn() — confirmed_flush_lsn)</p><p>AS lag_bytes</p><p>FROM pg_replication_slots</p><p>WHERE slot_type=’logical’</p><p>ORDER BY lag_bytes DESC;</p><p>Replication lag should be zero or near zero.</p><p><strong>3. Check Long-Running Transactions</strong></p><p>SELECT</p><p>pid,</p><p>usename,</p><p>application_name,</p><p>now() — xact_start AS txn_duration,</p><p>state,</p><p>query</p><p>FROM pg_stat_activity</p><p>WHERE xact_start IS NOT NULL</p><p>AND now() — xact_start &gt; interval ‘1 minute’</p><p>ORDER BY txn_duration DESC;</p><p>No long-running transactions should exist before switchover.</p><p><strong>4. Validate Replication Synchronization</strong></p><p>SELECT</p><p>slot_name,</p><p>confirmed_flush_lsn AS flushed,</p><p>pg_current_wal_lsn(),</p><p>(pg_current_wal_lsn() -</p><p>confirmed_flush_lsn) AS lsn_distance</p><p>FROM pg_replication_slots</p><p>WHERE slot_type=’logical’;</p><p>LSN distance should be minimal.</p><p><strong>5. Detect Unauthorized DDL Activity</strong></p><p>Install extension:</p><p>CREATE EXTENSION rds_tools;</p><p>Check Blue/Green DDL status:</p><p>SELECT *</p><p>FROM rds_tools.blue_green_get_status(‘ddl’);</p><p>Expected:</p><p>file_exists = false</p><p>If true, DDL activity occurred and must be reviewed.</p><p><strong>What Happens During Switchover?</strong></p><p>When the switchover is initiated, Amazon RDS performs the following operations:</p><ol><li>Executes guardrail validation checks.</li><li>Stops new write operations.</li><li>Terminates active connections.</li><li>Waits for Green replication to fully synchronize.</li><li>Renames database instances.</li><li>Renames endpoints.</li><li>Enables connections on the new production environment.</li><li>Enables write access on PostgreSQL 16.</li></ol><p><strong>Endpoint Preservation</strong></p><p>Example:</p><p>Before:</p><p>Blue : mydb</p><p>Green : mydb-green-abc123</p><p>After:</p><p>Production : mydb</p><p>Old Blue : mydb-old1</p><p>Applications continue using the same endpoint without configuration changes.</p><p><strong>Post-Switchover Activities</strong></p><p>After successful cutover:</p><p><strong>Refresh Materialized Views</strong></p><p>REFRESH MATERIALIZED VIEW view_name;</p><p><strong>Validate Application Connectivity</strong></p><ul><li>Connection pools</li><li>ETL jobs</li><li>Reporting workloads</li><li>Scheduled jobs</li></ul><p><strong>Recreate Read Replicas</strong></p><p>Recreate any read replicas removed during preparation.</p><p><strong>Validate Database Objects</strong></p><p>Verify:</p><ul><li>Tables</li><li>Indexes</li><li>Functions</li><li>Sequences</li><li>Extensions</li><li>Materialized Views</li></ul><p><strong>Rollback Strategy</strong></p><p>Although Blue/Green deployments are designed to minimize risk, rollback planning remains essential.</p><p>If rollback becomes necessary:</p><p><strong>Step 1</strong></p><p>Stop application traffic to the Green environment.</p><p><strong>Step 2</strong></p><p>Redirect applications back to the original Blue environment.</p><p><strong>Step 3</strong></p><p>Re-enable write operations on Blue.</p><p>ALTER SYSTEM SET default_transaction_read_only = off;</p><p>Verify:</p><p>SHOW default_transaction_read_only;</p><p><strong>Step 4</strong></p><p>Restart application traffic.</p><p>Because AWS preserves the original environment during switchover, rollback can be performed quickly if required.</p><p><strong>Key Lessons Learned</strong></p><ul><li>Validate all tables without primary keys and configure REPLICA IDENTITY FULL.</li><li>Enable logical replication well before the upgrade window.</li><li>Remove read replicas before creating the Blue/Green deployment.</li><li>Enforce a strict DDL freeze throughout synchronization.</li><li>Monitor replication lag continuously.</li><li>Refresh materialized views after cutover.</li><li>Maintain a tested rollback procedure.</li></ul><p><strong>Conclusion</strong></p><p>AWS Blue/Green Deployments provide a reliable and operationally safe approach for major PostgreSQL upgrades. By leveraging logical replication, organizations can upgrade from PostgreSQL 12 to PostgreSQL 16 with minimal downtime, reduced risk, and comprehensive validation before production cutover.</p><p>Through careful preparation, replication readiness checks, parameter tuning, and disciplined change management, the upgrade process can be executed smoothly while maintaining application availability and data consistency.</p><p><strong>Challenges Encountered and Resolutions</strong></p><p>During the PostgreSQL 12 to PostgreSQL 16 upgrade using AWS Blue/Green Deployments, two significant issues were encountered. These lessons are valuable for teams planning similar upgrades.</p><p><strong>Issue 1: Blue/Green Deployment Creation Failed Due to pg_repack Extension</strong></p><p><strong>Problem</strong></p><p>The initial Blue/Green deployment creation failed during the validation phase. Investigation revealed that the source PostgreSQL 12 environment contained an older version of the pg_repack extension that was not compatible with the target PostgreSQL 16 environment.</p><p>This dependency was overlooked during the prerequisite assessment and was not identified until deployment creation was attempted.</p><p><strong>Symptoms</strong></p><ul><li>Blue/Green deployment creation failed.</li><li>AWS validation reported extension compatibility issues.</li><li>Green environment provisioning could not proceed.</li></ul><p><strong>Root Cause</strong></p><p>The installed version of pg_repack on the PostgreSQL 12 instance was not supported for the target PostgreSQL 16 version.</p><p>Major PostgreSQL upgrades require all installed extensions to be compatible with the target engine version. Even if an extension is not actively used, its presence can block deployment creation.</p><p><strong>Resolution</strong></p><p>The extension was removed from the PostgreSQL 12 environment before recreating the Blue/Green deployment.</p><p>DROP EXTENSION pg_repack;</p><p>After the Blue/Green deployment was successfully created and the switchover completed, the extension was reinstalled on the PostgreSQL 16 environment using the supported version.</p><p><strong>Recommendation</strong></p><p>As part of pre-upgrade validation, review all installed extensions and verify compatibility with the target PostgreSQL version.</p><p>SELECT extname, extversion</p><p>FROM pg_extension</p><p>ORDER BY extname;</p><p><strong>Issue 2: Replication Lag Continuously Increased During Synchronization</strong></p><p><strong>Problem</strong></p><p>After the Blue/Green deployment was created, replication lag began increasing rapidly and never stabilized.</p><p>The Green environment could not catch up with ongoing changes from the production database, making switchover impossible.</p><p><strong>Diagnostic Findings</strong></p><p>AWS diagnostic logs showed the following behavior:</p><p>The replication slot was stuck in a continuous retry loop. Logical decoding was repeatedly terminated due to replication timeout while processing a large transaction. The replication process reconnected, restarted decoding from the same LSN, timed out again, and repeated indefinitely. As a result, replication slots never advanced and lag continued to grow.</p><p><strong>Root Cause</strong></p><p>The source (Blue) environment was experiencing heavy transactional activity during deployment synchronization.</p><p>Contributing factors included:</p><ul><li>High write volume on the production database.</li><li>Large transactions requiring extensive logical decoding.</li><li>Long-running transactions.</li><li>Increased WAL generation rate.</li><li>Logical decoding spillover to disk due to memory pressure.</li></ul><p>Under these conditions, logical replication could not process changes quickly enough to maintain synchronization.</p><p><strong>Resolution</strong></p><p>The Blue/Green deployment was deleted and recreated during a significantly lower traffic period.</p><p>After recreating the deployment during the maintenance window:</p><ul><li>Replication slots advanced normally.</li><li>Replication lag remained stable.</li><li>Green environment successfully synchronized.</li><li>Switchover completed without issues.</li></ul><p><strong>Key Observation</strong></p><p>Although AWS Blue/Green Deployments are designed to minimize downtime, they are not entirely independent of database workload characteristics.</p><p>For databases with sustained high write activity, large transactions, or heavy ETL workloads, creating the deployment during off-peak hours can significantly improve synchronization performance and reduce the risk of replication-related failures.</p><p><strong>Recommendation</strong></p><p>Before creating a Blue/Green deployment:</p><ul><li>Schedule deployment creation during low-traffic periods whenever possible.</li><li>Avoid running large ETL jobs or bulk data loads.</li><li>Monitor transaction duration and write throughput.</li><li>Review replication lag frequently after deployment creation.</li><li>Consider postponing deployment creation if sustained high write activity is observed.</li></ul><p><strong>Lessons Learned</strong></p><p>The upgrade highlighted several important operational considerations:</p><ol><li>Extension compatibility should be validated early in the planning phase.</li><li>All installed extensions must be reviewed, even those not actively used.</li><li>Blue/Green Deployments depend on logical replication and can be affected by heavy database workloads.</li><li>Deployment creation during low-traffic periods significantly improves synchronization success rates.</li><li>Continuous monitoring of replication lag is essential before scheduling switchover activities.</li></ol><p>These findings have now been incorporated into the standard PostgreSQL major version upgrade checklist to reduce risk in future upgrade projects.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=096e9152e693" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>