Upgrading the PostgreSQL DB Engine
When Amazon Relational Database Service (Amazon RDS) supports a new version of a database engine, you can upgrade your DB instances to the new version. There are two kinds of upgrades: major version upgrades and minor version upgrades.
Amazon RDS supports major and minor version upgrades for PostgreSQL DB instances.
Major version upgrades can contain database changes that are not backward-compatible with existing applications. As a result, Amazon Relational Database Service (Amazon RDS) doesn't apply major version upgrades automatically; you must manually modify your DB instance. You can initiate a major version upgrade manually by modifying your instance. However, there are recommended steps to follow when performing a major version upgrade. For details, see Major Version Upgrades.
You can initiate a minor version upgrade manually by modifying your instance, or select the Auto Minor Version Upgrade option when creating or modifying a DB instance to have your instance automatically upgraded once the new version is tested and approved by Amazon RDS.
AWS RDS does not automatically upgrade PostgreSQL extensions. To upgrade an extension, you must use the ALTER EXTENSION UPDATE command. For example, to upgrade PostGIS when you upgrade the PostgreSQL DB engine from 9.4.x to 9.5.x, you would run the following command:
ALTER EXTENSION POSTGIS UPDATE TO '2.2.2'
Major Version Upgrades
Amazon RDS supports an in-place upgrade from the following:
A PostgreSQL 9.3.x DB instance to a PostgreSQL 9.4.x DB instance
A PostgreSQL 9.4.x DB instance to a PostgreSQL 9.5.x DB instance
A PostgreSQL 9.5.x DB instance to a PostgreSQL 9.6.x DB instance
pg_upgradeutility found at http://www.postgresql.org/docs/9.4/static/pgupgrade.html to safely upgrade your instance.
Because some PostgreSQL minor versions updates for 9.3 were released after major version 9.4 was released, you cannot upgrade from version 9.3.9 to 9.4.1, and you cannot upgrade from version 9.3.10 to 9.4.1 or 9.4.4.
Read Replicas cannot undergo a major version upgrade. The source instance can undergo a major version upgrade, but all Read Replicas remain as readable nodes on the previous engine version. After a source instance is upgraded, its Read Replicas can no longer replicate changes performed on the source instance. We recommend that you either promote your Read Replicas, or delete and recreate them after the source instance has upgraded to a different major version.
Major Version Upgrade Process
We recommend the following process when upgrading an Amazon RDS PostgreSQL DB instance:
Have a version-compatible parameter group ready – If you are using a custom parameter group, you must specify either a default parameter group for the new DB engine version or create your own custom parameter group for the new DB engine version. Associating the new parameter group with the DB instance requires a customer-initiated database reboot after the upgrade completes. The instance's parameter group status will show
pending-rebootif the instance needs to be rebooted to apply the parameter group changes. An instance's parameter group status can be viewed in the AWS console or by using a "describe" call such as
Check for unsupported usage:
Prepared transactions – Commit or roll back all open prepared transactions before attempting an upgrade.
You can use the following query to verify that there are no open prepared transactions on your instance:Copy
SELECT count(*) FROM pg_catalog.pg_prepared_xacts;
linedata type – If you are upgrading an RDS PostgreSQL 9.3 instance, you must remove all uses of the
linedata type before attempting an upgrade, because the
linedata type was not fully implemented in PostgreSQL until version 9.4.
You can use the following query on each database to be upgraded to verify that there are no uses of the
linedata type in each database:Copy
SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid = 'pg_catalog.line'::pg_catalog.regtype AND c.relnamespace = n.oid AND n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema');
To list all databases on an instance, use the following query:Copy
SELECT d.datname FROM pg_catalog.pg_database d WHERE d.datallowconn = true;
Reg* data types – Remove all uses of the reg* data types before attempting an upgrade, because these data types contain information that cannot be persisted with
pg_upgrade. Uses of reg* data types cannot be upgraded, except for
regclass. Remove all usages before attempting an upgrade.
You can use the following query to verify that there are no uses of unsupported reg* data types in each database:Copy
SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ('pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype, 'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema');
VACUUMoperation before upgrading your instance. The
pg_upgradeutility vacuums each database when you upgrade to a different major version. If you haven't performed a
VACUUMoperation, the upgrade process can take much longer, causing increased downtime for your RDS instance.
Perform a dry run of your major version upgrade. We highly recommend testing major version upgrade on a duplicate of your production database before attempting it on your production database. To create a duplicate test instance, you can either restore your database from a recent snapshot or point-in-time restore your database to its latest restorable time. After you have completed the major version upgrade, consider testing your application on the upgraded database with a similar workload in order to verify that everything works as expected. After the upgrade is verified, you can delete this test instance.
We recommend that you perform a backup before performing the major version upgrade so that you have a known restore point for your database.
Upgrade your production instance. If the dry-run major version upgrade was successful, you should now be able to upgrade your production database with confidence.
You can use Amazon RDS to view two logs that the
pg_upgrade utility produces:
pg_upgrade_server.log. Amazon RDS appends a timestamp
to the file name for these logs. You can view these logs as you can any other log.
You cannot perform a point-in-time restore of your instance to a point in time during the upgrade process. During the upgrade process, RDS takes an automatic backup of the instance after the upgrade has been performed. You can perform a point-in-time restore to times before the upgrade began and after the automatic backup of your instance has completed.
template1 databases and the
public schema in
every database on the instance are renamed during the major version upgrade. These objects will appear
in the logs with their original name and a random string appended. The string is appended so that custom settings
such as the
owner are preserved during the major version upgrade.
Once the upgrade completes, the objects are renamed back to their original names.
Minor Version Upgrades for PostgreSQL
Minor version upgrades occur automatically if a minor upgrade has been tested and approved by Amazon RDS and you selected the Auto Minor Version Upgrade option. In all other cases, you must modify the DB instance manually to perform a minor version upgrade. If you select the Auto Minor Version Upgrade option when creating or modifying a DB instance, you can have your instance automatically upgraded after the new version is tested and approved by Amazon RDS.
If your PostgreSQL DB instance is using read replication, you must upgrade all of the Read Replicas before upgrading the source instance. If the DB instance is in a Multi-AZ deployment, both the primary and standby replicas are upgraded, and the instance might not be available until the upgrade is complete.
AWS Management Console
To apply a DB engine major version upgrade to a DB instance
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
In the navigation pane, choose Instances.
Choose the check box for the DB instance that you want to upgrade.
Choose Instance Actions, and then choose Modify.
For DB Engine Version, choose the new version.
To upgrade immediately, select Apply Immediately. To delay the upgrade to the next maintenance window, clear Apply Immediately.
Review the modification summary information. To proceed with the upgrade, choose Modify DB Instance. To cancel the upgrade, choose Cancel or Back.
To upgrade the engine version of a DB instance, use the AWS CLI modify-db-instance command. Specify the following parameters:
--db-instance-identifier– the name of the db instance.
--engine-version– the version number of the database engine to upgrade to.
--allow-major-version-upgrade– to to upgrade major version.
--no-apply-immediately– apply changes during the next maintenance window. To apply changes immediately, use
For Linux, OS X, or Unix:
aws rds modify-db-instance \ --db-instance-identifier
<new_version>\ --allow-major-version-upgrade \ --apply-immediately
aws rds modify-db-instance ^ --db-instance-identifier
<new_version>^ --allow-major-version-upgrade ^ --apply-immediately
To upgrade the engine version of a DB instance, use the ModifyDBInstance action. Specify the following parameters:
DBInstanceIdentifier– the name of the db instance, for example
EngineVersion– the version number of the database engine to upgrade to.
AllowMajorVersionUpgrade– set to
trueto upgrade major version.
ApplyImmediately– whether to apply changes immediately or during the next maintenance window. To apply changes immediately, set the value to
true. To apply changes during the next maintenance window, set the value to
https://rds.us-east-1.amazonaws.com/ ?Action=ModifyDBInstance &ApplyImmediately=false &DBInstanceIdentifier=mydbinstance &EngineVersion=new_version &SignatureMethod=HmacSHA256 &SignatureVersion=4 &Version=2013-09-09 &X-Amz-Algorithm=AWS4-HMAC-SHA256 &X-Amz-Credential=AKIADQKE4SARGYLE/20131016/us-east-1/rds/aws4_request &X-Amz-Date=20131016T233051Z &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date &X-Amz-Signature=087a8eb41cb1ab5f99e81575f23e73757ffc6a1e42d7d2b30b9cc0be988cff97