docs.aws.amazon.com Open in urlscan Pro
54.239.23.208 Public Scan

Back to summary

URL:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html
Submission: On June 09 via api (June 9th 2021, 1:58:13 pm UTC) from US

Form analysis
0 forms found in the DOM

Text Content

SELECT YOUR COOKIE PREFERENCES

We use cookies and similar tools to enhance your experience, provide our
services, deliver relevant advertising, and make improvements. Approved third
parties also use these tools to help us deliver advertising and provide certain
site features.

CustomizeAccept all

CUSTOMIZE COOKIE PREFERENCES

We use cookies and similar tools (collectively, "cookies") for the following
purposes.

ESSENTIAL

Essential cookies are necessary to provide our site and services and cannot be
deactivated. They are usually set in response to your actions on the site, such
as setting your privacy preferences, signing in, or filling in forms.

PERFORMANCE

Performance cookies provide anonymous statistics about how customers navigate
our site so we can improve site experience and performance. Approved third
parties may perform analytics on our behalf, but they cannot use the data for
their own purposes.

Allow performance category
Allowed

FUNCTIONAL

Functional cookies help us provide useful site features, remember your
preferences, and display relevant content. Approved third parties may set these
cookies to provide certain site features. If you do not allow these cookies,
then some or all of these services may not function properly.

Allow functional category
Allowed

ADVERTISING

Advertising cookies may be set through our site by us or our advertising
partners and help us deliver relevant marketing content. If you do not allow
these cookies, you will experience less relevant advertising.

Allow advertising category
Allowed

Blocking some types of cookies may impact your experience of our sites. You may
review and change your choices at any time by clicking Cookie preferences in the
footer of this site. We and selected third-parties use cookies or similar
technologies as specified in the AWS Cookie Notice

CancelSave preferences

English

Feedback
Preferences
Amazon Relational Database Service
User Guide
* What is Amazon RDS?
* DB instances
* DB instance classes
* DB instance storage
* Regions, Availability Zones, and Local Zones
* High availability (Multi-AZ)
* DB instance billing for Amazon RDS
* On-Demand DB instances
* Reserved DB instances

* Setting up
* Getting started
* Creating a MariaDB DB instance and connecting to a database
* Creating a SQL Server DB instance and connecting to it
* Creating a MySQL DB instance and connecting to a database
* Creating an Oracle DB instance and connecting to a database
* Creating a PostgreSQL DB instance and connecting to a database
* Tutorial: Create a web server and an Amazon RDS DB instance
* Create a DB instance
* Create a web server

* Tutorials
* Best practices for Amazon RDS
* Configuring a DB instance
* Creating a DB instance
* Creating resources with AWS CloudFormation
* Connecting to a DB instance
* Managing connections with RDS Proxy

* Working with option groups
* Working with parameter groups

* Managing a DB instance
* Stopping a DB instance
* Starting a DB instance
* Modifying a DB instance
* Maintaining a DB instance
* Upgrading the engine version
* Renaming a DB instance
* Rebooting a DB instance
* Working with read replicas
* Creating a read replica in a different AWS Region

* Tagging RDS resources
* Working with ARNs
* Working with storage
* Deleting a DB instance

* Backing up and restoring a DB instance
* Working with backups
* Replicating automated backups to another Region
* Creating a DB snapshot
* Restoring from a DB snapshot
* Copying a snapshot
* Sharing a snapshot
* Exporting snapshot data to Amazon S3
* Point-in-time recovery
* Deleting a snapshot
* Tutorial: Restore a DB instance from a DB snapshot

* Monitoring a DB instance
* Overview of monitoring
* Viewing key monitoring information
* Monitoring with Performance Insights
* Overview
* DB load
* Maximum CPU
* DB engine support for Performance Insights
* AWS Region support for Performance Insights

* Enabling and disabling Performance Insights
* Performance Insights policies
* Monitoring with the Performance Insights dashboard
* Customizing the Performance Insights dashboard
* Retrieving data with the Performance Insights API
* Metrics published to CloudWatch
* Logging Performance Insights calls using AWS CloudTrail

* Monitoring OS metrics
* Overview of Enhanced Monitoring
* Setting up and enabling Enhanced Monitoring
* Viewing OS metrics in the RDS console
* Viewing OS metrics using CloudWatch Logs

* Monitoring RDS with CloudWatch
* Working with Amazon RDS events
* Overview of events for Amazon RDS
* Viewing Amazon RDS events
* Using Amazon RDS event notification
* Amazon RDS event categories and event messages
* Subscribing to Amazon RDS event notification
* Listing Amazon RDS event notification subscriptions
* Modifying an Amazon RDS event notification subscription
* Adding a source identifier to an Amazon RDS event notification
subscription
* Removing a source identifier from an Amazon RDS event notification
subscription
* Listing the Amazon RDS event notification categories
* Deleting an Amazon RDS event notification subscription

* Creating a rule that triggers on an Amazon RDS event

* Working with database logs
* MariaDB database log files
* Microsoft SQL Server database log files
* MySQL database log files
* Overview of MySQL database logs
* Accessing MySQL error logs
* Accessing the MySQL slow query and general logs
* Accessing the MySQL audit log
* Publishing MySQL logs to Amazon CloudWatch Logs
* Managing table-based MySQL logs
* Setting the binary logging format
* Accessing MySQL binary logs

* Oracle database log files
* PostgreSQL database log files

* Working with AWS CloudTrail and Amazon RDS

* Working with RDS on AWS Outposts
* MariaDB on Amazon RDS
* Connecting to a DB instance running MariaDB
* Updating applications for new SSL/TLS certificates
* Upgrading the MariaDB DB engine
* Migrating data from a MySQL DB snapshot to a MariaDB DB instance
* Working with MariaDB replication
* Working with MariaDB read replicas
* Configuring GTID-based replication

* Importing data into a MariaDB DB instance
* Options for MariaDB
* Parameters for MariaDB
* MariaDB on Amazon RDS SQL reference
* mysql.rds_replica_status
* mysql.rds_set_external_master_gtid
* mysql.rds_kill_query_id

* Microsoft SQL Server on Amazon RDS
* Licensing SQL Server on Amazon RDS
* Connecting to a DB instance running SQL Server
* Updating applications for new SSL/TLS certificates
* Upgrading the SQL Server DB engine
* Importing and exporting SQL Server databases
* Importing and exporting SQL Server data using other methods

* Working with SQL Server read replicas
* Multi-AZ for RDS for SQL Server
* Additional features for SQL Server
* Using SSL with a SQL Server DB instance
* Configuring security protocols and ciphers
* Using Windows Authentication with a SQL Server DB instance
* Amazon S3 integration
* Using Database Mail
* Instance store support for tempdb
* Using extended events

* Options for SQL Server
* Native backup and restore
* Transparent Data Encryption
* Performance considerations

* SQL Server Audit
* SQL Server Analysis Services
* SQL Server Integration Services
* SQL Server Reporting Services
* Microsoft Distributed Transaction Coordinator

* Common DBA tasks for SQL Server
* Accessing the tempdb database
* Analyzing database workload with Database Engine Tuning Advisor
* Collations and character sets
* Determining a recovery model
* Determining the last failover time
* Disabling fast inserts
* Dropping a SQL Server database
* Renaming a Multi-AZ database
* Resetting the db_owner role password
* Restoring license-terminated DB instances
* Transitioning a database from OFFLINE to ONLINE
* Using CDC
* Using SQL Server Agent
* Working with SQL Server logs
* Working with trace and dump files

* MySQL on Amazon RDS
* Connecting to a DB instance running MySQL
* Updating applications for new SSL/TLS certificates
* Upgrading the MySQL DB engine
* Upgrading a MySQL DB snapshot
* Importing data into a MySQL DB instance
* Restoring a backup into an Amazon RDS MySQL DB instance
* Importing data from a MySQL or MariaDB DB to a MySQL or MariaDB DB
instance
* Importing data to an Amazon RDS MySQL or MariaDB DB instance with
reduced downtime
* Importing data from any source to a MySQL or MariaDB DB instance

* Working with MySQL replication
* Working with MySQL read replicas
* Using GTID-based replication
* Replication with a MySQL or MariaDB instance running external to Amazon
RDS

* Exporting data from a MySQL DB instance
* Options for MySQL
* MariaDB Audit Plugin
* memcached

* Common DBA tasks for MySQL
* Using Kerberos authentication for MySQL
* Known issues and limitations
* MySQL on Amazon RDS SQL reference
* mysql.rds_set_master_auto_position
* mysql.rds_set_external_master
* mysql.rds_set_external_master_with_delay
* mysql.rds_set_external_master_with_auto_position
* mysql.rds_reset_external_master
* mysql.rds_import_binlog_ssl_material
* mysql.rds_remove_binlog_ssl_material
* mysql.rds_set_source_delay
* mysql.rds_start_replication
* mysql.rds_start_replication_until
* mysql.rds_start_replication_until_gtid
* mysql.rds_stop_replication
* mysql.rds_skip_transaction_with_gtid
* mysql.rds_skip_repl_error
* mysql.rds_next_master_log
* mysql.rds_innodb_buffer_pool_dump_now
* mysql.rds_innodb_buffer_pool_load_now
* mysql.rds_innodb_buffer_pool_load_abort
* mysql.rds_set_configuration
* mysql.rds_show_configuration
* mysql.rds_kill
* mysql.rds_kill_query
* mysql.rds_rotate_general_log
* mysql.rds_rotate_slow_log
* mysql.rds_enable_gsh_collector
* mysql.rds_set_gsh_collector
* mysql.rds_disable_gsh_collector
* mysql.rds_collect_global_status_history
* mysql.rds_enable_gsh_rotation
* mysql.rds_set_gsh_rotation
* mysql.rds_disable_gsh_rotation
* mysql.rds_rotate_global_status_history

* Oracle on Amazon RDS
* Connecting to an Oracle instance
* Securing Oracle connections
* Encrypting with SSL
* Using new SSL/TLS certificates
* Configuring Kerberos authentication
* Setting up
* Managing a DB instance
* Connecting with Kerberos authentication

* Configuring outbound network access

* Administering your Oracle DB
* System tasks
* Database tasks
* Log tasks
* RMAN tasks
* Oracle Scheduler tasks
* Diagnostic tasks
* Other tasks

* Importing data into Oracle
* Working with Oracle replicas
* Options for Oracle
* Amazon S3 integration
* Application Express (APEX)
* Enterprise Manager
* OEM Database Express
* OEM Management Agent

* Java virtual machine (JVM)
* Label security
* Locator
* Multimedia
* Native network encryption (NNE)
* OLAP
* Secure Sockets Layer (SSL)
* Spatial
* SQLT
* Statspack
* Time zone
* Transparent Data Encryption (TDE)
* UTL_MAIL
* XML DB

* Upgrading the Oracle DB engine
* Upgrading an Oracle DB snapshot
* Tools and third-party software for Oracle
* Setting up
* Using Oracle GoldenGate
* Using the Oracle Repository Creation Utility
* Installing a Siebel database on Oracle on Amazon RDS

* Oracle database engine release notes
* Database engine: 19.0.0.0
* Database engine: 18.0.0.0
* Database engine: 12.2.0.1
* Database engine: 12.1.0.2
* Database engine: 11.2.0.4

* PostgreSQL on Amazon RDS
* Connecting to a PostgreSQL instance
* Security with RDS for PostgreSQL
* Using SSL with a PostgreSQL DB instance
* Using new SSL/TLS certificates in applications
* Using Kerberos authentication
* Setting up
* Managing a DB instance in a Domain
* Connecting with Kerberos authentication

* Upgrading the PostgreSQL DB engine
* Upgrading a PostgreSQL DB snapshot engine version
* Working with PostgreSQL read replicas
* Importing data into PostgreSQL
* Exporting PostgreSQL data to Amazon S3
* Common DBA tasks for PostgreSQL
* Working with PostgreSQL autovacuum
* Working with the PostGIS extension
* Using a custom DNS server for outbound network access
* Scheduling maintenance with the pg_cron extension
* Managing partitions with the pg_partman extension
* Invoking a Lambda function from RDS for PostgreSQL

* Security
* Database authentication
* Data protection
* Data encryption
* Encrypting Amazon RDS resources
* Customer master key (CMK) management
* Using SSL/TLS to encrypt a connection
* Rotating your SSL/TLS certificate

* Internetwork traffic privacy

* Identity and access management
* How Amazon RDS works with IAM
* Identity-based policy examples
* IAM database authentication for MySQL and PostgreSQL
* Enabling and disabling
* Creating and using an IAM policy for IAM database access
* Creating a database account using IAM authentication
* Connecting to your DB instance using IAM authentication
* Connecting using IAM: AWS CLI and mysql client
* Connecting using IAM authentication from the command line: AWS
CLI and psql client
* Connecting using IAM authentication and the AWS SDK for .NET
* Connecting using IAM authentication and the AWS SDK for Go
* Connecting using IAM authentication and the AWS SDK for Java
* Connecting using IAM authentication and the AWS SDK for Python
(Boto3)

* Troubleshooting

* Logging and monitoring
* Compliance validation
* Resilience
* Infrastructure security
* VPC endpoints (AWS PrivateLink)
* Security best practices
* Controlling access with security groups
* DB security groups on EC2-Classic

* Master user account privileges
* Service-linked roles
* Using Amazon RDS with Amazon VPC
* Determining whether you are using the EC2-VPC or EC2-Classic platform
* Scenarios for accessing a DB instance in a VPC
* Scenarios for accessing a DB instance not in a VPC

* Working with a DB instance in a VPC
* Tutorial: Create an Amazon VPC for use with a DB instance

* Quotas and constraints
* Troubleshooting
* Amazon RDS API reference
* Using the Query API
* Troubleshooting applications

* Document history
* AWS glossary

Upgrading the PostgreSQL DB engine for Amazon RDS - Amazon Relational Database
Service
AWSDocumentationAmazon Relational Database Service (RDS)User Guide
Overview of upgradingPostgreSQL version numbersChoosing a major version
upgradeHow to perform a major version upgradeAutomatic minor version
upgradesUpgrading PostgreSQL extensions

UPGRADING THE POSTGRESQL DB ENGINE FOR AMAZON RDS

PDF
Kindle
RSS

There are two types of upgrades you can manage for your PostgreSQL DB instance:

* Operating system updates – Occasionally, Amazon RDS might need to update the
underlying operating system of your DB instance to apply security fixes or OS
changes. You can decide when Amazon RDS applies OS updates by using the RDS
console, AWS Command Line Interface (AWS CLI), or RDS API. For more
information about OS updates, see Applying updates for a DB instance.

* Database engine upgrades – When Amazon RDS supports a new version of a
database engine, you can upgrade your DB instances to the new version.

When 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 for
PostgreSQL DB instances: major version upgrades and minor version upgrades.

Major version upgrades

Major version upgrades can contain database changes that are not
backward-compatible with existing applications. As a result, you must manually
perform major version upgrades of your DB instances. You can initiate a major
version upgrade by modifying your DB instance. However, before you perform a
major version upgrade, we recommend that you follow the steps described in
Choosing a major version upgrade for PostgreSQL . During a major version
upgrade, Amazon RDS also upgrades all of your in-Region read replicas along with
the primary DB instance.

minor version upgrades

In contrast, minor version upgrades include only changes that are
backward-compatible with existing applications. You can initiate a minor version
upgrade manually by modifying your DB instance. Or you can enable the Auto minor
version upgrade option when creating or modifying a DB instance. Doing so means
that your DB instance is automatically upgraded after Amazon RDS tests and
approves the new version. If your PostgreSQL DB instance is using read replicas,
you must upgrade all of the read replicas before the minor version upgrade of
the source instance. For more details, see Automatic minor version upgrades for
PostgreSQL. For information about manually performing a minor version upgrade,
see Manually upgrading the engine version.

Topics

* Overview of upgrading PostgreSQL
* PostgreSQL version numbers
* Choosing a major version upgrade for PostgreSQL
* How to perform a major version upgrade
* Automatic minor version upgrades for PostgreSQL
* Upgrading PostgreSQL extensions

OVERVIEW OF UPGRADING POSTGRESQL

To safely upgrade your DB instances, Amazon RDS uses the pg_upgrade utility
described in the PostgreSQL documentation.

Amazon RDS takes two DB snapshots during the upgrade process if your backup
retention period is greater than 0. The first DB snapshot is of the DB instance
before any upgrade changes have been made. If the upgrade doesn't work for your
databases, you can restore this snapshot to create a DB instance running the old
version. The second DB snapshot is taken after the upgrade completes.

Note

Amazon RDS takes DB snapshots during the upgrade process only if you have set
the backup retention period for your DB instance to a number greater than 0. To
change your backup retention period, see Modifying an Amazon RDS DB instance.

When you upgrade the primary DB instance, all the in-Region read replicas are
also automatically upgraded. After the upgrade workflow starts, the replica
instances wait for the pg_upgrade to complete successfully on the primary DB
instance. Then the primary instance upgrade waits for the replica instance
upgrades to complete. You experience an outage until the upgrade is complete. If
you have any older replicas from earlier major versions they aren't upgraded.

If your DB instance is in a Multi-AZ deployment, both the primary writer DB
instance and standby DB instances are upgraded. The writer and standby DB
instances are upgraded at the same time.

After an upgrade is complete, you can't revert to the previous version of the
database engine. If you want to return to the previous version, restore the DB
snapshot that was taken before the upgrade to create a new DB instance.

POSTGRESQL VERSION NUMBERS

The version numbering sequence for the PostgreSQL database engine is as follows:

* For PostgreSQL versions 10 and later, the engine version number is in the
form major.minor. The major version number is the integer part of the version
number. The minor version number is the fractional part of the version
number.

A major version upgrade increases the integer part of the version number,
such as upgrading from 10.minor to 11.minor.

* For PostgreSQL versions earlier than 10, the engine version number is in the
form major.major.minor. The major engine version number is both the integer
and the first fractional part of the version number. For example, 9.6 is a
major version. The minor version number is the third part of the version
number. For example, for version 9.6.12, the 12 is the minor version number.

A major version upgrade increases the major part of the version number. For
example, an upgrade from 9.6.12 to 10.11 is a major version upgrade, where
9.6 and 10 are the major version numbers.

CHOOSING A MAJOR VERSION UPGRADE FOR POSTGRESQL

Major version upgrades can contain database changes that are not
backward-compatible with previous versions of the database. This functionality
can cause your existing applications to stop working correctly.

As a result, Amazon RDS doesn't apply major version upgrades automatically. To
perform a major version upgrade, you modify your DB instance manually. Make sure
that you thoroughly test any upgrade to verify that your applications work
correctly before applying the upgrade to your production DB instances. When you
do a PostgreSQL major version upgrade, we recommend that you follow the steps
described in How to perform a major version upgrade.

You can upgrade a PostgreSQL database to its next major version. From some
PostgreSQL database versions, you can skip to a higher major version when
upgrading. If your upgrade skips a major version, the read replicas are also
upgraded to that target major version. The following table lists the source
PostgreSQL database versions and the associated target major versions available
for upgrading.

Note

Upgrade targets are enabled to a higher version released at the same time as the
source minor version or later.

If a database uses the PostGIS extension, you can't skip major versions for some
source to target combinations. For these circumstances, upgrade to a recent
minor version, then upgrade to PostgreSQL 12, and finally upgrade to your
desired target version.

The pgRouting extension isn't supported for an upgrade that skips a major
version to versions 11.x. A major version is skipped when the upgrade goes from
versions 9.4.x, 9.5.x, or 9.6.x to versions 11.x. You can drop the pgRouting
extension and then add it again after an upgrade.

The tsearch2 and chkpass extensions aren't supported in PostgreSQL 11 or later.
If you are upgrading to version 11.x, drop these extensions before the upgrade.

Current source version Newest upgrade target Preferred major upgrade targets
12.6, 12.5, 12.4, 12.3, 12.2 13.2 13.2 11.11 13.2 13.2, 12.6 11.10 13.1 13.1
12.6 11.11 11.9, 11.8, 11.7, 11.6, 11.5, 11.4, 11.2, 11.1 12.6 12.6 11.11 10.16
13.2 13.2 12.6 11.11 10.15 13.1 13.1 12.5 11.11 10.14 12.4 12.4 11.11 10.13 12.3
12.3 11.11 10.12 12.2 12.2 11.11 10.11, 10.10, 10.9, 10.7, 10.6, 10.5, 10.4,
10.3, 10.1 11.11 11.11 9.6.21 13.2 13.2, 12.6 11.11 10.16 9.6.20 13.1 13.1 12.5
11.10 10.16 9.6.19 12.4 12.4 11.9 10.16 9.6.18 12.3 12.3 11.8, 10.16 9.6.17 12.2
12.2 11.7, 10.16 9.6.16 11.6 11.6 10.16 9.6.15 11.5 11.5 10.16 9.6.14 11.4 11.4
10.16 9.6.12 11.2 11.2 10.16 9.6.11 11.1 11.1 10.16 9.6.10, 9.6.9, 9.6.8, 9.6.6,
9.6.5, 9.6.3, 9.6.2, 9.6.1 10.16 10.16 9.5.25 12.6 12.6 11.11 10.16 9.6.21
9.5.24 12.5 12.5 11.10 10.16 9.6.21 9.5.23 12.4 12.4 11.9 10.16 9.6.21 9.5.22
12.3 12.3 11.8 10.16 9.6.21 9.5.21 12.2 12.2 11.7 10.16 9.6.21 9.5.20 11.6 11.6
10.16 9.6.21 9.5.19 11.5 11.5 10.16 9.6.21 9.5.18 11.4 11.4 10.16 9.6.21 9.5.16
11.2 11.2 10.16 9.6.21 9.5.15 11.1 11.1 10.16 9.6.21 9.5.14, 9.5.13, 9.5.12,
9.5.10, 9.5.9, 9.5.9, 9.5.7, 9.5.6, 9.5.4 9.6.21 9.6.21

To get a list of all valid upgrade targets for a current source version in a
particular AWS Region, use the describe-db-engine-versions CLI command. For
example:

export REGION=eu-central-1
export ENDPOINT=https://rds.eu-central-1.amazonaws.com

aws rds describe-db-engine-versions --engine postgres --region $REGION --endpoint $ENDPOINT --output text --query '*[].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].{EngineVersion:EngineVersion}' --engine-version DB-current-version

HOW TO PERFORM A MAJOR VERSION UPGRADE

We recommend the following process when upgrading an Amazon RDS PostgreSQL DB
instance:

1. Have a version-compatible parameter group ready – If you are using a custom
parameter group, you have two options. You can specify a default parameter
group for the new DB engine version. Or you can create your own custom
parameter group for the new DB engine version.

If you associate a new parameter group with a DB instance, reboot the
database after the upgrade completes. If the instance needs to be rebooted
to apply the parameter group changes, the instance's parameter group status
shows pending-reboot. You can view an instance's parameter group status in
the console or by using a describe command, such as describe-db-instances.

2. Check for unsupported DB instance classes – Check that your database's
instance class is compatible with the PostgreSQL version you are upgrading
to. For more information, see Supported DB engines for DB instance classes.

3. 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.

SELECT count(*) FROM pg_catalog.pg_prepared_xacts;

* Reg* data types – Remove all uses of the reg* data types before
attempting an upgrade. Except for regtype and regclass, you can't
upgrade the reg* data types. The pg_upgrade utility can't persist this
data type, which is used by Amazon RDS to do the upgrade.

To verify that there are no uses of unsupported reg* data types, use the
following query for each database.

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');

4. Handle read replicas – An upgrade also upgrades the in-Region read replicas
along with the primary instance.

You can't upgrade read replicas separately. If you could, it could lead to
situations where the primary and replica instances have different
PostgreSQL major versions. However, replica upgrades might increase
downtime on the primary instance. To prevent a replica upgrade, promote the
replica to a standalone instance or delete it before starting the upgrade
process.

The upgrade process recreates the replica's parameter group based on the
replica instance's current parameter group. You can apply a custom
parameter group to a replica only after the upgrade completes by using the
modify-db-parameter-group CLI command.

Read replicas on the virtual private cloud (VPC) platform are upgraded but
replicas on the EC2-Classic platform aren't upgraded. Any EC2-Classic
replicas are left in the replication terminated state after the upgrade
process completes. To move a DB instance from the EC2-Classic platform into
a VPC, see Moving a DB instance not in a VPC into a VPC. For more
information, see Working with PostgreSQL read replicas in Amazon RDS.

5. Perform a backup – We recommend that you perform a backup before performing
the major version upgrade so that you have a known restore point for your
database. If your backup retention period is greater than 0, the upgrade
process creates DB snapshots of your DB instance before and after
upgrading. To change your backup retention period, see Modifying an Amazon
RDS DB instance. To perform a backup manually, see Creating a DB snapshot.

6. Upgrade certain extensions before the major version upgrade – If you plan
to skip a major version with the upgrade, you need to update certain
extensions before performing the major version upgrade. Upgrading from
versions 9.4.x, 9.5.x, or 9.6.x to versions 11.x skip a major version. The
extensions to update include:

* address_standardizer

* address_standardizer_data_us

* postGIS

* postgis_tiger_geocoder

* postgis_topology

Run the following command for each extension you are using.

ALTER EXTENSION PostgreSQL-extension UPDATE TO 'new-version'

For more information, see Upgrading PostgreSQL extensions.

7. Drop certain extensions before the major version upgrade – An upgrade that
skips a major version to version 11.x doesn't support updating the
pgRouting extension. Upgrading from versions 9.4.x, 9.5.x, or 9.6.x to
versions 11.x skip a major version. It's safe to drop the pgRouting
extension and then reinstall it to a compatible version after the upgrade.
For the extension versions you can update to, see PostgreSQL extensions
supported on Amazon RDS.

The tsearch2 and chkpass extensions are no longer supported for PostgreSQL
versions 11 or later. If you are upgrading to version 11.x, drop the
tsearch2, and chkpass extensions before the upgrade.

8. Drop unknown data types – Drop unknown data types depending on the target
version.

PostgreSQL version 10 stopped supporting the unknown data type. If a
version 9.6 database uses the unknown data type, an upgrade to a version 10
shows an error message such as the following:

Database instance is in a state that cannot be upgraded: PreUpgrade checks failed:
The instance could not be upgraded because the 'unknown' data type is used in user tables.
Please remove all usages of the 'unknown' data type and try again."

To find the unknown data type in your database so you can remove the
offending column or change it to a supported data type, use the following
SQL:

SELECT DISTINCT data_type FROM information_schema.columns WHERE data_type ILIKE 'unknown';

9. Perform an upgrade dry run – We highly recommend testing a major version
upgrade on a duplicate of your production database before attempting the
upgrade on your production database. To create a duplicate test instance,
you can either restore your database from a recent snapshot or do a
point-in-time restore of your database to its latest restorable time. For
more information, see Restoring from a snapshot or Restoring a DB instance
to a specified time. For details on performing the upgrade, see Manually
upgrading the engine version.

During the major version upgrade, the public and template1 databases and
the public schema in every database on the instance are temporarily
renamed. These objects appear in the logs with their original name and a
random string appended. The string is appended so that custom settings such
as locale and owner are preserved during the major version upgrade. After
the upgrade completes, the objects are renamed back to their original
names.

Note

During the major version upgrade process, you can't do a point-in-time
restore of your instance. After Amazon RDS performs the upgrade, it takes
an automatic backup of the instance. You can perform a point-in-time
restore to times before the upgrade began and after the automatic backup of
your instance has completed.

10. If an upgrade fails with precheck procedure errors, resolve the issues –
During the major version upgrade process, Amazon RDS for PostgreSQL first
runs a precheck procedure to identify any issues that might cause the
upgrade to fail. The precheck procedure checks all potential incompatible
conditions across all databases in the instance.

If the precheck encounters an issue, it creates a log event indicating the
upgrade precheck failed. The precheck process details are in an upgrade log
named pg_upgrade_precheck.log for all the databases of a DB instance.
Amazon RDS appends a timestamp to the file name. For more information about
viewing logs, see Working with Amazon RDS database log files.

If a replica upgrade fails at precheck, replication on the failed replica
is broken and the replica is put in the terminated state. Delete the
replica and recreate a new replica based on the upgraded primary instance.

Resolve all of the issues identified in the precheck log and then retry the
major version upgrade. The following is an example of a precheck log.

------------------------------------------------------------------------
Upgrade could not be run on Wed Apr 4 18:30:52 2018
-------------------------------------------------------------------------
The instance could not be upgraded from 9.6.11 to 10.6 for the following reasons.
Please take appropriate action on databases that have usage incompatible with the requested major engine version upgrade and try the upgrade again.

* There are uncommitted prepared transactions. Please commit or rollback all prepared transactions.* One or more role names start with 'pg_'. Rename all role names that start with 'pg_'.

* The following issues in the database 'my"million$"db' need to be corrected before upgrading:** The ["line","reg*"] data types are used in user tables. Remove all usage of these data types.
** The database name contains characters that are not supported by RDS for PostgreSQL. Rename the database.
** The database has extensions installed that are not supported on the target database version. Drop the following extensions from your database: ["tsearch2"].

* The following issues in the database 'mydb' need to be corrected before upgrading:** The database has views or materialized views that depend on 'pg_stat_activity'. Drop the views.

11. If a replica upgrade fails while upgrading the database, resolve the issue
– A failed replica is placed in the incompatible-restore state and
replication is terminated on the DB instance. Delete the replica and
recreate a new replica based on the upgraded primary instance.

A replica upgrade might fail for the following reasons:

* It was unable to catch up with the primary instance even after a wait
time.

* It was in a terminal or incompatible lifecycle state such as
storage-full, incompatible-restore, and so on.

* When the primary instance upgrade started, there was a separate minor
version upgrade running on the replica.

* The replica instance used incompatible parameters.

* The replica instance was unable to communicate with the primary instance
to synchronize the data folder.

12. Upgrade your production instance – When the dry-run major version upgrade
is successful, you should be able to upgrade your production database with
confidence. For more information, see Manually upgrading the engine
version.

After the major version upgrade is complete, we recommend the following:

* Run the ANALYZE operation to refresh the pg_statistic table.

* A PostgreSQL upgrade doesn't upgrade any PostgreSQL extensions. To upgrade
extensions, see Upgrading PostgreSQL extensions.

* Optionally, use Amazon RDS to view two logs that the pg_upgrade utility
produces. These are pg_upgrade_internal.log and 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. For more information, see Working with Amazon
RDS database log files.

You can also upload the upgrade logs to Amazon CloudWatch Logs. For more
information, see Publishing PostgreSQL logs to Amazon CloudWatch Logs.

* To verify that everything works as expected, test your application on the
upgraded database with a similar workload. After the upgrade is verified, you
can delete this test instance.

AUTOMATIC MINOR VERSION UPGRADES FOR POSTGRESQL

If you enable the Auto minor version upgrade option when creating or modifying a
DB instance, you can have your DB instance automatically upgraded.

For each RDS for PostgreSQL major version, one minor version is designated by
RDS as the automatic upgrade version. After a minor version has been tested and
approved by Amazon RDS, the minor version upgrade occurs automatically during
your maintenance window. RDS doesn't automatically set newer released minor
versions as the automatic upgrade version. Before RDS designates a newer
automatic upgrade version, several criteria are considered, such as the
following:

* Known security issues

* Bugs in the PostgreSQL community version

* Overall fleet stability since the minor version was released

You can use the following AWS CLI command and script to determine the current
automatic upgrade minor versions.

Note

If no results are returned, there is no automatic minor version upgrade
available and scheduled.

A PostgreSQL DB instance is automatically upgraded during your maintenance
window if the following criteria are met:

* The DB instance has the Auto minor version upgrade option enabled.

* The DB instance is running a minor DB engine version that is less than the
current automatic upgrade minor version.

For more information, see Automatically upgrading the minor engine version.

Note

A PostgreSQL upgrade doesn't upgrade PostgreSQL extensions. To upgrade
extensions, see Upgrading PostgreSQL extensions.

UPGRADING POSTGRESQL EXTENSIONS

A PostgreSQL engine upgrade doesn't upgrade most PostgreSQL extensions. To
update an extension after a version upgrade, use the ALTER EXTENSION UPDATE
command.

Note

If you are running the PostGIS extension in your Amazon RDS PostgreSQL DB
instance, make sure that you follow the PostGIS upgrade instructions in the
PostGIS documentation before you update the extension.

To upgrade an extension, use the following command.

ALTER EXTENSION extension_name UPDATE TO 'new_version'

For the list of supported versions of PostgreSQL extensions, see PostgreSQL
extensions supported on Amazon RDS.

To list your currently installed extensions, use the PostgreSQL pg_extension
catalog in the following command.

SELECT * FROM pg_extension;

To view a list of the specific extension versions that are available for your
installation, use the PostgreSQL pg_available_extension_versions view in the
following command.

SELECT * FROM pg_available_extension_versions;

This page is helpful.

Thanks for your vote. To provide details, send feedback.

This page is not helpful.

Javascript is disabled or is unavailable in your browser.

To use the AWS Documentation, Javascript must be enabled. Please refer to your
browser's Help pages for instructions.

Document Conventions
Connecting with Kerberos authentication
Upgrading a PostgreSQL DB snapshot engine version
Did this page help you?
Yes No

Did this page help you? - Yes

Thanks for letting us know we're doing a good job!

If you've got a moment, please tell us what we did right so we can do more of
it.

Feedback

Did this page help you? - No

Thanks for letting us know this page needs work. We're sorry we let you down.

If you've got a moment, please tell us how we can make the documentation better.

Feedback

Provide feedback

Edit this page on GitHub
Previous topic: Connecting with Kerberos authentication ...

Next topic: Upgrading a PostgreSQL DB snapshot engine version ...
Need help?

* Try the forums

* Connect with an AWS IQ expert

Thanks for letting us know we're doing a good job!

If you've got a moment, please tell us what we did right so we can do more of
it.

Feedback

Did this page help you? - No

Thanks for letting us know this page needs work. We're sorry we let you down.

If you've got a moment, please tell us how we can make the documentation better.

Feedback

On this page
* Overview of upgrading
* PostgreSQL version numbers
* Choosing a major version upgrade
* How to perform a major version upgrade
* Automatic minor version upgrades
* Upgrading PostgreSQL extensions

docs.aws.amazon.com Open in urlscan Pro 54.239.23.208 Public Scan

Form analysis 0 forms found in the DOM

Text Content

docs.aws.amazon.com Open in urlscan Pro
54.239.23.208 Public Scan

Form analysis
0 forms found in the DOM