RDS Postgres reg data types and extensions

Question

Q1 - As per the upgrade documentation reg* data types except regclass and regtype needs to be removed, what are the other reg* type class supported in postgres? And by remove what data types should be used? Does it mean if these data types are used in any tables, the columns data type needs to be modified? Any example shared would be helpful.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html

Q2 - When extensions are upgraded, is there any downtime? Any sessions using the extenions will they error or no issues and new sessions will start using new version after upgrade?

Mrityunjay Singh · Answer

The reg* data types in PostgreSQL are used to represent regular expressions data types. These data types include retype, regclass etc. This is a PostgreSQL feature, refer pgsql [documentation](https://www.postgresql.org/docs/current/datatype-oid.html#DATATYPE-OID).

Before attempting a major version upgrade of your PostgreSQL database on Amazon RDS, you should remove 
or migrate any data stored in the reg* data types that are not supported by the “pg_upgrade” utility.
[AWS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process) provides the sql statement which can be used to understand if any un-supported reg* data-types are being used.

Most of the Postgresql Extensions are generally not upgraded alongside the db engine major upgrade. Refer [documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.ExtensionUpgrades) 
It's essential that you ensure compatibility of the pg extensions with the new PostgreSQL version and your application code. It's recommended to schedule a maintenance window to upgrade the extensions and perform any necessary testing or application changes to ensure a smooth transition. Post upgrade, new sessions will automatically use the upgraded extension versions.

RDS Postgres reg data types and extensions

Relevant content