How I Upgraded RDS PostgreSQL Version From 13.20 to 17.6

#aws #rds #postgres #database

i had to upgrade PostgreSQL version last month because it will be end of life in next month.

Our organization uses postrgeSQL as the main database engine. We have staging and production environments both running version 13.20. According to announcements from AWS -> Amazon RDS PostgreSQL 13.x end of standard support is February 28, 2026. With community end of life being November 2025. So we had to prepare for the upgrade. Below describe the process i used and steps taken during the upgrade process.

Steps Followed:

Check Changelog

First i take a look at the release notes from 13.2 version to 17.6 version and note down the breaking/major changes that are there for my applications. Identify If any i need to pay attention to. Here are few critical changes that are there.
Release Notes
Summary from RN

## Critical Breaking Changes (PostgreSQL 17)

### 1. Search Path Changes in Maintenance Operations ⚠️ CRITICAL

**Issue:** Functions used by expression indexes and materialized views must now specify search_path explicitly.

**Impact:** Maintenance operations (ANALYZE, CLUSTER, CREATE INDEX, CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, REINDEX, VACUUM) now use a safe search_path.

**Action Required:**
sql
-- Check for functions used in indexes/materialized views without explicit search_path
SELECT n.nspname, p.proname, pg_get_functiondef(p.oid)
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.oid IN (
  SELECT indexrelid::regclass::oid 
  FROM pg_index 
  WHERE indexprs IS NOT NULL
)
AND pg_get_functiondef(p.oid) NOT LIKE '%SET search_path%';

-- Fix: Add search_path to function definition
ALTER FUNCTION schema_name.function_name() SET search_path = schema_name, pg_catalog;

### 2. Removed: old_snapshot_threshold Parameter

**Issue:** Server variable `old_snapshot_threshold` has been removed.

**Action Required:**
sql
-- Check if parameter is set
SHOW old_snapshot_threshold;

-- If set, remove from postgresql.conf before upgrade
-- This feature may be re-added in future versions


### 3. Removed: db_user_namespace Feature

**Issue:** Per-database user simulation feature removed.

**Action Required:**
sql
-- Check if enabled
SHOW db_user_namespace;

-- If 'on', must be disabled before upgrade
-- Migrate to standard user management


### 4. Removed: adminpack Extension

**Issue:** adminpack contrib extension removed (was used by pgAdmin III).

**Action Required:**
sql
-- Check if installed
SELECT * FROM pg_extension WHERE extname = 'adminpack';

-- Drop before upgrade
DROP EXTENSION IF EXISTS adminpack;

### 5. System Catalog Column Renames

**Issue:** Several system catalog columns renamed in PostgreSQL 17.

**Action Required:**
sql
-- Check for queries/views using old column names:

-- pg_collation.colliculocale → colllocale
-- pg_database.daticulocale → datlocale
-- pg_attribute.attstattarget (now NULL for default instead of -1)
-- pg_stat_progress_vacuum columns renamed
-- pg_stat_slru columns renamed
-- pg_stat_statements: blk_read_time → shared_blk_read_time
-- pg_stat_statements: blk_write_time → shared_blk_write_time

-- Search for usage in views/functions
SELECT schemaname, viewname, definition
FROM pg_views
WHERE definition LIKE '%colliculocale%'
   OR definition LIKE '%daticulocale%'
   OR definition LIKE '%blk_read_time%'
   OR definition LIKE '%blk_write_time%';

### 6. Removed: pg_stat_bgwriter Columns

**Issue:** `buffers_backend` and `buffers_backend_fsync` removed from pg_stat_bgwriter.

**Action Required:**
sql
-- Check for queries using these columns
SELECT schemaname, viewname, definition
FROM pg_views
WHERE definition LIKE '%buffers_backend%'
   OR definition LIKE '%buffers_backend_fsync%';

-- Migrate to pg_stat_io view instead
SELECT * FROM pg_stat_io;

### 6. Postgres Extention version upgrade - check for breaking changes
pg_partman extention version needed to be upgraded from version "4.5.1" to version "5.2.4" . you can see the breaking changes here 
[changelog](https://github.com/pgpartman/pg_partman/blob/development/CHANGELOG.md)

## Breaking Changes (PostgreSQL 16)

### 1. PL/pgSQL Bound Cursor Variable Changes

**Issue:** String value of bound cursor variables no longer matches variable name during assignment.

**Action Required:**
sql
-- Review PL/pgSQL functions with bound cursors
SELECT n.nspname, p.proname, pg_get_functiondef(p.oid)
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql')
  AND pg_get_functiondef(p.oid) LIKE '%CURSOR%FOR%';

-- If cursor variable name is used, assign explicitly before OPEN
-- Old behavior: cursor_var := 'cursor_name' (automatic)
-- New behavior: Must explicitly assign before OPEN

### 2. Removed: Postfix Operators

**Issue:** Support for postfix operators removed.

**Action Required:**
sql
-- Check for postfix operators
SELECT oprname, oprleft, oprright
FROM pg_operator
WHERE oprright = 0;

-- pg_dump and pg_upgrade will warn about these
-- Convert to prefix or infix operators before upgrade

## Breaking Changes (PostgreSQL 15)

### 1. PUBLIC Schema Permission Changes ⚠️ CRITICAL

**Issue:** PUBLIC creation permission removed from public schema by default.

**Impact:** Users can no longer create objects in public schema without explicit GRANT.

**Action Required:**
sql
-- Check current permissions
SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';

-- If needed, restore old behavior (not recommended for security)
GRANT CREATE ON SCHEMA public TO PUBLIC;

-- Better: Grant to specific roles
GRANT CREATE ON SCHEMA public TO app_role;

### 2. Removed: Exclusive Backup Mode

**Issue:** pg_start_backup()/pg_stop_backup() exclusive mode removed.

**Action Required:**sql
-- Check for usage of exclusive backup mode
-- Search application code for:
-- pg_start_backup(label, true)  -- Second parameter 'true' = exclusive mode

-- Migrate to non-exclusive mode:
-- pg_backup_start(label, false)  -- Note: function renamed
-- pg_backup_stop(false)

### 3. Removed: Python 2.x Support

**Issue:** plpython2u and plpythonu (Python 2) removed.

**Action Required:**
sql
-- Check for Python 2 functions
SELECT n.nspname, p.proname, l.lanname
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE l.lanname IN ('plpython2u', 'plpythonu');

-- Migrate to plpython3u
-- Rewrite functions for Python 3 compatibility

### 4. array_to_tsvector() Empty String Error

**Issue:** Now generates error for empty-string array elements.

**Action Required:**sql
-- Check for empty lexemes in tsvector columns
SELECT tablename, attname
FROM pg_stats
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND atttypid = 'tsvector'::regtype;

-- Verify no empty lexemes exist
SELECT * FROM your_table WHERE to_tsvector('') = ANY(your_tsvector_column);

-- Clean up empty lexemes before upgrade

## Breaking Changes (PostgreSQL 14)

### 1. Array Function Signature Changes ⚠️ CRITICAL

**Issue:** Array functions changed from `anyarray` to `anycompatiblearray`.

**Affected Functions:**
- array_append()
- array_prepend()
- array_cat()
- array_position()
- array_positions()
- array_remove()
- array_replace()
- width_bucket()

**Action Required:**
sql
-- Find user-defined objects referencing these functions
SELECT n.nspname, p.proname, pg_get_functiondef(p.oid)
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE pg_get_functiondef(p.oid) ~ 'array_(append|prepend|cat|position|positions|remove|replace)|width_bucket'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- Find aggregates using these functions
SELECT n.nspname, a.aggfnoid::regproc
FROM pg_aggregate a
JOIN pg_proc p ON a.aggfnoid = p.oid
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema');

-- Drop and recreate after upgrade

### 2. Removed: Containment Operators @ and ~

**Issue:** Deprecated operators @ and ~ removed for geometric types and contrib modules.

**Affected:** geometric data types, cube, hstore, intarray, seg

**Action Required:**
sql
-- Search for usage of @ and ~ operators
SELECT schemaname, viewname, definition
FROM pg_views
WHERE definition ~ '[@~]'
  AND schemaname NOT IN ('pg_catalog', 'information_schema');

-- Replace with <@ and @> operators
-- Old: geometry1 @ geometry2
-- New: geometry1 <@ geometry2

### 3. to_tsquery() and websearch_to_tsquery() Parsing Changes

**Issue:** Discarded tokens now properly parsed.

**Action Required:**
sql
-- Review queries using these functions with underscores or other discarded tokens
-- Test query results after upgrade
-- Example: websearch_to_tsquery('"pg_class pg"')
-- Old output: ( 'pg' & 'class' ) <-> 'pg'
-- New output: 'pg' <-> 'class' <-> 'pg'

### 4. Regular Expression \D and \W Changes

**Issue:** \D and \W now match newlines in newline-sensitive mode.

**Action Required:**
sql
-- Review regex patterns using \D or \W
SELECT schemaname, viewname, definition
FROM pg_views
WHERE definition ~ '\\[DW]'
  AND schemaname NOT IN ('pg_catalog', 'information_schema');

-- Use [^[:digit:]] or [^[:word:]] for old behavior

### 5. Removed: pg_standby Utility

**Issue:** contrib program pg_standby removed.

**Action Required:**
- Check if pg_standby is used in recovery.conf or restore_command
- Migrate to restore_command with alternative tools

In my case non of the above changes were affected my applications so no changes were required from my end. Typically that would be the case here unless you are using anything special to postgreSQL.

Check Official AWS upgrade Guide

Then using the AWS documentation i generated a set of steps need to do in order to perform major version upgrade. You can check them from below link.
RDS Major Upgrade Guide

check target version

After opening the doc i needed to decide my upgrade target 13.2-> 17.6 is supported. It was supported.

Check database instance compatibility

Afterward, I needed to checked if my database instance type was compatible with the target version. In my case db.m5.large. It was supported.
InstanceClass.Support

Decide on Upgrade Path

I considered below upgrade paths

Blue/Green Deployment (Recommended) : we had some pre-requisites that don’t match our database to support blue/green.
blue-green-deployments
Snapshot & In-Place Upgrade
pg_dump/restore : pg_dump/restore would incur longer downtime (rebuilding indexes is slow for restore) .

After doing some feasibility study identified the most feasible path for us is in-place upgrade with thorough testing.

Create parameter group

I created custom parameter group for postgres17. In my currently database i am using pg_cron extention. So needed to update shared_libraries parameter to include pg_cron in new param group as well. Just like that you can compare the parameter groups and do required changes if you changed any defaults values.

Upgrading extentions

A PostgreSQL upgrade doesn’t upgrade any PostgreSQL extensions. To upgrade extensions, see
PostgreSQL.ExtensionUpgrades

I am using pg_partman and pg_cron extentions. In order to check supported version for postgreSQL 17 see below doc.
postgresql-extensions-support

In my case upgrade requirement

pg_partman : 4.5.1 to 5.2.4
pg_cron 1.3 to 1.6.

I checked the changes in partman and pg_cron release notes identified the changes that need to happen.

Other checks needed..

There are some other checks in this doc that need to be fulfilled in order to do a successful upgrade. you can check below by following the official aws doc mentioned previously.

1.Check for unsupported usage
2.Check for invalid databases
3.Handle logical replication slots 
4.Handle read replicas 
5.Handle zero-ETL integrations

In my case 1–3 all are zero and i don’t have replicas or zero-etl configured. So now i am all set for the upgrade.

Perform an upgrade dry run
Good thing about using AWS is you can quickly spin up new servers with click of a button. So in order to test my upgrade procedure working i created a new db instance using a snapshot of my existing db. Then perform pre-checks above mentioned on it and after verification i performed an in place upgrade to the new instance. You can do this my modifying the instance and update postgreSQL version to 17.6 and update parameter group to the new one created. Then applied the changes immediately. You can use the process to time your process and identify the downtime needed during the real upgrade.

In my case database upgrade took around 12mins so during this time database was unavailable. AWS takes a backup of instance before and after the upgrade. So after the upgrade it is recomended to run the ANALYZE on all databases to update pg_statistic table. This took around 30mins. Also i am using pg_partman, pg_cron which then i upgraded to supported versions. Then connected to database locally and performed validations. Then Documented all steps performed during dry-run.

Upgrade Staging/Production Database
After successful dry-run then its was time to perform staging upgrade. Can follow same steps for prod as well. Inform the stake holders beforehand and acquired a required downtime. Better to perform during an off-peak time interval. Then perform same steps as the dry-run.

Check logs

After upgrading, I checked the logs. There should be two new logs.

pg_upgrade_internal.log: Log of RDS upgrade (pg_upgrade)
pg_upgrade_server.log: Log of stopping/starting RDS

Checked any Error or Fatal log in those two logs and confirmed that “Upgrade Complete” is printed at the bottom of internal.log.

After the upgrade connected to the database and perform some validations on content.

I had an issue where some of the pods in eks were crashloopbackoff state after the upgrade. After checking the logs identified it was failing to connect database due to SSL error. Identified this was due to in postgreSQL17 param group rds.force_ssl is 1 by default and in postgreSQL13 it was 0. So after updating that pods were running without errors. Noted this down for production upgrade.

Also note after the upgrade some SQL queries were taking longer time to execute hence increasing CPU load for rds and causing performance degradations.