Amazon RDS for PostgreSQL
Amazon RDS supports DB instances running several versions of PostgreSQL. For a list of available versions, see Available PostgreSQL database versions.
Note
Deprecation of PostgreSQL 9.6 is scheduled for April 26, 2022. For more information, see Deprecation of PostgreSQL version 9.6.
You can create DB instances and DB snapshots, point-in-time restores and backups. DB instances running PostgreSQL support Multi-AZ deployments, read replicas, Provisioned IOPS, and can be created inside a virtual private cloud (VPC). You can also use Secure Socket Layer (SSL) to connect to a DB instance running PostgreSQL.
Before creating a DB instance, make sure to complete the steps in Setting up for Amazon RDS.
You can use any standard SQL client application to run commands for the instance from your client computer. Such applications include pgAdmin, a popular Open Source administration and development tool for PostgreSQL, or psql, a command line utility that is part of a PostgreSQL installation. To deliver a managed service experience, Amazon RDS doesn't provide host access to DB instances. Also, it restricts access to certain system procedures and tables that require advanced privileges. Amazon RDS supports access to databases on a DB instance using any standard SQL client application. Amazon RDS doesn't allow direct host access to a DB instance by using Telnet or Secure Shell (SSH).
Amazon RDS for PostgreSQL is compliant with many industry standards. For example, you can use
Amazon RDS for PostgreSQL databases to build HIPAA-compliant applications and to store
healthcare-related information. This includes storage for protected health information (PHI)
under a completed Business Associate Agreement (BAA) with AWS. Amazon RDS for PostgreSQL also
meets Federal Risk and Authorization Management Program (FedRAMP) security requirements.
Amazon RDS for PostgreSQL has received a FedRAMP Joint Authorization Board (JAB) Provisional
Authority to Operate (P-ATO) at the FedRAMP HIGH Baseline within the AWS GovCloud (US) Regions.
For more information on supported compliance standards, see AWS cloud compliance
To import PostgreSQL data into a DB instance, follow the information in the Importing data into PostgreSQL on Amazon RDS section.
Topics
- Common management tasks for Amazon RDS for PostgreSQL
- Working with the Database Preview environment
- PostgreSQL version 16 in the Database Preview environment
- Available PostgreSQL database versions
- Supported PostgreSQL extension versions
- Working with PostgreSQL features supported by Amazon RDS for PostgreSQL
- Connecting to a DB instance running the PostgreSQL database engine
- Securing connections to RDS for PostgreSQL with SSL/TLS
- Using Kerberos authentication with Amazon RDS for PostgreSQL
- Using a custom DNS server for outbound network access
- Upgrading the PostgreSQL DB engine for Amazon RDS
- Upgrading a PostgreSQL DB snapshot engine version
- Working with read replicas for Amazon RDS for PostgreSQL
- Improving query performance for RDS for PostgreSQL with Amazon RDS Optimized Reads
- Importing data into PostgreSQL on Amazon RDS
- Exporting data from an RDS for PostgreSQL DB instance to Amazon S3
- Invoking an AWS Lambda function from an RDS for PostgreSQL DB instance
- Common DBA tasks for Amazon RDS for PostgreSQL
- Tuning with wait events for RDS for PostgreSQL
- Tuning RDS for PostgreSQL with Amazon DevOps Guru proactive insights
- Using PostgreSQL extensions with Amazon RDS for PostgreSQL
- Working with the supported foreign data wrappers for Amazon RDS for PostgreSQL
- Working with Trusted Language Extensions for PostgreSQL
Common management tasks for Amazon RDS for PostgreSQL
The following are the common management tasks you perform with an Amazon RDS for PostgreSQL DB instance, with links to relevant documentation for each task.
Task area | Relevant documentation |
---|---|
Setting up Amazon RDS for first-time use Before you can create your DB instance, make sure to complete a few prerequisites. For example, DB instances are created by default with a firewall that prevents access to it. So you need to create a security group with the correct IP addresses and network configuration to access the DB instance. |
|
Understanding Amazon RDS DB instances If you are creating a DB instance for production purposes, you should understand how instance classes, storage types, and Provisioned IOPS work in Amazon RDS. |
|
Finding available PostgreSQL versions Amazon RDS supports several versions of PostgreSQL. |
|
Setting up high availability and failover support A production DB instance should use Multi-AZ deployments. Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. |
|
Understanding the Amazon Virtual Private Cloud (VPC) network If your AWS account has a default VPC, then your DB instance is automatically created inside the default VPC. In some cases, your account might not have a default VPC, and you might want the DB instance in a VPC. In these cases, create the VPC and subnet groups before you create the DB instance. |
|
Importing data into Amazon RDS PostgreSQL You can use several different tools to import data into your PostgreSQL DB instance on Amazon RDS. |
|
Setting up read-only read replicas (primary and standbys) RDS for PostgreSQL supports read replicas in both the same AWS Region and in a different AWS Region from the primary instance. |
Working with DB instance read replicas |
Understanding security groups By default, DB instances are created with a firewall that prevents access to them. To provide access through that firewall, you edit the inbound rules for the VPC security group associated with the VPC hosting the DB instance. |
|
Setting up parameter groups and features To change the default parameters for your DB instance, create a custom DB parameter group and change settings to that. If you do this before creating your DB instance, you can choose your custom DB parameter group when you create the instance. |
|
Connecting to your PostgreSQL DB instance After creating a security group and associating it to a DB
instance, you can connect to the DB instance using any standard SQL
client application such as |
Connecting to a DB instance running the PostgreSQL database engine |
Backing up and restoring your DB instance You can configure your DB instance to take automated backups, or take manual snapshots, and then restore instances from the backups or snapshots. |
|
Monitoring the activity and performance of your DB instance You can monitor a PostgreSQL DB instance by using CloudWatch Amazon RDS metrics, events, and enhanced monitoring. |
|
Upgrading the PostgreSQL database version You can do both major and minor version upgrades for your PostgreSQL DB instance. |
|
Working with log files You can access the log files for your PostgreSQL DB instance. |
|
Understanding the best practices for PostgreSQL DB instances Find some of the best practices for working with PostgreSQL on Amazon RDS. |
Following is a list of other sections in this guide that can help you understand and use important features of RDS for PostgreSQL:
Working with the Database Preview environment
The PostgreSQL community continuously releases new PostgreSQL version
and extensions, including beta versions. This gives PostgreSQL users the opportunity to try
out a new PostgreSQL version early. To learn more about the PostgreSQL community
beta release process, see Beta Information
RDS for PostgreSQL DB instances in the Database Preview Environment are functionally similar to other RDS for PostgreSQL instances. However, you can't use a Preview version for production.
Keep in mind the following important limitations:
-
All DB instances are deleted 60 days after you create them, along with any backups and snapshots.
-
You can only create a DB instance in a virtual private cloud (VPC) based on the Amazon VPC service.
-
You can only use General Purpose SSD and Provisioned IOPS SSD storage.
-
You can't get help from AWS Support with DB instances. Instead, you can post your questions to the AWS‐managed Q&A community, AWS re:Post
. -
You can't copy a snapshot of a DB instance to a production environment.
The following options are supported by the Preview.
-
You can create DB instances using M6i, R6i, M6g, M5, T3, R6g, and R5 instance types only. For more information about RDS instance classes, see DB instance classes.
-
You can use both single-AZ and multi-AZ deployments.
-
You can use standard PostgreSQL dump and load functions to export databases from or import databases to the Database Preview Environment.
Features not supported in the Database Preview environment
The following features aren't available in the Database Preview environment:
-
Cross-Region snapshot copy
-
Cross-Region read replicas
Creating a new DB instance in the Database Preview environment
Use the following procedure to create a DB instance in the preview environment.
To create a DB instance in the Database Preview environment
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
Choose Dashboard from the navigation pane.
-
In the Dashboard page, locate the Database Preview Environment section on the Dashboard page, as shown in the following image.
You can navigate directly to the Database Preview environment
. Before you can proceed, you must acknowledge and accept the limitations. -
To create the RDS for PostgreSQL DB instance, follow the same process as that for creating any Amazon RDS DB instance. For more information, see the Console procedure in Creating a DB instance.
To create an instance in the Database Preview Environment using the RDS API or the AWS CLI, use the following endpoint.
rds-preview.us-east-2.amazonaws.com
PostgreSQL version 16 in the Database Preview environment
This is preview documentation for Amazon RDS PostgreSQL version 16. It is subject to change.
Note
RDS for PostgreSQL versions 16 RC1, 16 Beta 3, 16 Beta 2, and 16 Beta 1 will not be supported after RDS for PostgreSQL version 16.0 is released in the Database Preview environment.
PostgreSQL version 16.0 is now available in the Amazon RDS Database Preview environment. PostgreSQL version 16 contains several improvements that are described in the following PostgreSQL documentation:
For information on the Database Preview Environment, see Working with the
Database Preview environment. To access the
Preview Environment from the console, select https://console.aws.amazon.com/rds-preview/
Available PostgreSQL database versions
Amazon RDS supports DB instances running several editions of PostgreSQL. You can specify any currently available PostgreSQL version when creating a new DB instance. You can specify the major version (such as PostgreSQL 14), and any available minor version for the specified major version. If no version is specified, Amazon RDS defaults to an available version, typically the most recent version. If a major version is specified but a minor version is not, Amazon RDS defaults to a recent release of the major version you have specified.
To see a list of available versions, as well as defaults for newly created DB
instances, use the describe-db-engine-versions
AWS CLI command. For example,
to display the default PostgreSQL engine version, use the following command:
aws rds describe-db-engine-versions --default-only --engine postgres
For details about the PostgreSQL versions that are supported on Amazon RDS, see the Amazon RDS for PostgreSQL Release Notes.
If you aren't ready to manually upgrade to a new major engine version before the RDS
end of standard support date, or for Amazon RDS to automatically upgrade your major engine
version after the RDS end of standard support date, you can enable Amazon RDS Extended Support on your DB
instances that are running RDS for PostgreSQL version 11 and higher. For more information, see
Using Amazon RDS Extended Support and
Amazon RDS pricing
Deprecation of PostgreSQL version 10
On April 17, 2023, Amazon RDS plans to deprecate PostgreSQL 10 using the following schedule. We recommend that you take action and upgrade your PostgreSQL databases running on major version 10 to a later version, such as PostgreSQL version 14. To upgrade your RDS for PostgreSQL major version 10 DB instance from a PostgreSQL version older than 10.19, we recommend that you first upgrade to version 10.19 and then upgrade to version 14. For more information, see Upgrading the PostgreSQL DB engine for Amazon RDS.
Action or recommendation | Dates |
---|---|
The PostgreSQL community plans to deprecate PostgreSQL 10 and won't provide any security patches after this date. |
November 10, 2022 |
Start upgrading RDS for PostgreSQL 10 DB instances to a later major version, such as PostgreSQL 14. Although you can continue to restore PostgreSQL 10 snapshots and create read replicas with version 10, be aware of the other critical dates in this deprecation schedule and their impact. |
Until February 14, 2023 |
After this date, you can't create new Amazon RDS instances with PostgreSQL major version 10 from either the AWS Management Console or the AWS CLI. |
February 14, 2023 |
After this date, Amazon RDS automatically upgrades PostgreSQL 10 instances to version 14. If you restore a PostgreSQL 10 database snapshot, Amazon RDS automatically upgrades the restored database to PostgreSQL 14. |
April 17, 2023 |
For more information about RDS for PostgreSQL version 10 deprecation, see
[Announcement]:
RDS for PostgreSQL 10 deprecation
Deprecation of PostgreSQL version 9.6
On March 31, 2022, Amazon RDS plans to deprecate PostgreSQL 9.6 using the following schedule. This extends the previously announced date of January 18, 2022 to April 26, 2022. You should upgrade all your PostgreSQL 9.6 DB instances to PostgreSQL 12 or higher as soon as possible. We recommend that you first upgrade to minor version 9.6.20 or higher and then upgrade directly to PostgreSQL 12 rather than upgrading to an intermediate major version. For more information, see Upgrading the PostgreSQL DB engine for Amazon RDS.
Action or recommendation | Dates |
---|---|
The PostgreSQL community discontinued support for PostgreSQL 9.6, and will no longer provide bug fixes or security patches for this version. |
November 11, 2021 |
Start upgrading RDS for PostgreSQL 9.6 DB instances to PostgreSQL 12 or higher as soon as possible. Although you can continue to restore PostgreSQL 9.6 snapshots and create read replicas with version 9.6, be aware of the other critical dates in this deprecation schedule and their impact. |
Until March 31, 2022 |
After this date, you can't create new Amazon RDS instances with PostgreSQL major version 9.6 from either the AWS Management Console or the AWS CLI. |
March 31, 2022 |
After this date, Amazon RDS automatically upgrades PostgreSQL 9.6 instances to version 12. If you restore a PostgreSQL 9.6 database snapshot, Amazon RDS automatically upgrades the restored database to PostgreSQL 12. |
April 26, 2022 |
Deprecated versions for Amazon RDS for PostgreSQL
RDS for PostgreSQL 9.5 is deprecated as of March, 2021. For more information about RDS for PostgreSQL 9.5 deprecation, see
Upgrading from Amazon RDS for PostgreSQL version 9.5
To learn more about deprecation policy for RDS for PostgreSQL, see
Amazon RDS FAQs
Supported PostgreSQL extension versions
RDS for PostgreSQL supports many PostgreSQL extensions. The PostgreSQL community
sometimes refers to these as modules. Extensions expand on the functionality
provided by the PostgreSQL engine. You can find a list of extensions supported
by Amazon RDS in the default DB parameter group for that PostgreSQL version. You can
also see the current extensions list using psql
by showing the
rds.extensions
parameter as in the following example.
SHOW rds.extensions;
Note
Parameters added in a minor version release might display inaccurately
when using the rds.extensions
parameter in psql
.
As of RDS for PostgreSQL 13, certain extensions can be installed by database
users other than the rds_superuser
. These are known as trusted
extensions. To learn more, see PostgreSQL trusted extensions.
Certain versions of RDS for PostgreSQL support the
rds.allowed_extensions
parameter. This parameter lets an
rds_superuser
limit the extensions that can be installed in the
RDS for PostgreSQL DB instance. For more information, see Restricting installation of PostgreSQL extensions.
For lists of PostgreSQL extensions and versions that are supported by each available RDS for PostgreSQL version, see PostgreSQL extensions supported on Amazon RDS in Amazon RDS for PostgreSQL Release Notes.
Restricting installation of PostgreSQL extensions
You can restrict which extensions can be installed on a PostgreSQL DB
instance. By default, this parameter isn't set, so any supported
extension can be added if the user has permissions to do so. To do so,
set the rds.allowed_extensions
parameter to a
string of comma-separated extension names. By adding a list of
extensions to this parameter, you explicitly identify the extensions that your
RDS for PostgreSQL DB instance can use. Only these extensions can then be
installed in the PostgreSQL DB instance.
The default string for the rds.allowed_extensions
parameter is '*',
which means that any extension available for the engine version can be installed.
Changing the rds.allowed_extensions
parameter does not require a
database restart because it's a dynamic parameter.
The PostgreSQL DB instance engine must be one of the following versions for you to use
the rds.allowed_extensions
parameter:
-
All PostgreSQL 16 versions
-
PostgreSQL 15 and all higher versions
-
PostgreSQL 14 and all higher versions
-
PostgreSQL 13.3 and higher minor versions
-
PostgreSQL 12.7 and higher minor versions
To see which extension installations are allowed, use the following psql command.
postgres=>
SHOW rds.allowed_extensions;
rds.allowed_extensions ------------------------ *
If an extension was installed prior to it being left out of the list in
the rds.allowed_extensions
parameter, the extension can still
be used normally, and commands such as ALTER EXTENSION
and
DROP EXTENSION
will continue to work. However, after an
extension is restricted, CREATE EXTENSION
commands for the
restricted extension will fail.
Installation of extension dependencies with CREATE EXTENSION
CASCADE
are also restricted. The extension and its dependencies
must be specified in rds.allowed_extensions
. If an extension
dependency installation fails, the entire CREATE EXTENSION
CASCADE
statement will fail.
If an extension is not included with the
rds.allowed_extensions
parameter, you will see an error
such as the following if you try to install it.
ERROR: permission denied to create extension "extension-name
"
HINT: This extension is not specified in "rds.allowed_extensions".
PostgreSQL trusted extensions
To install most PostgreSQL extensions requires
rds_superuser
privileges. PostgreSQL 13 introduced trusted
extensions, which reduce the need to grant rds_superuser
privileges to regular users.
With this feature, users can install many extensions if they have the
CREATE
privilege on the current database instead of requiring the
rds_superuser
role. For more information, see the SQL CREATE
EXTENSION
The following lists the extensions that can be installed by a user who
has the CREATE
privilege on the current database and do not
require the rds_superuser
role:
-
bool_plperl
-
jsonb_plperl
For lists of PostgreSQL extensions and versions that are supported by each available RDS for PostgreSQL version, see PostgreSQL extensions supported on Amazon RDS in Amazon RDS for PostgreSQL Release Notes.