Menu
Sign In to the Console
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » PostgreSQL on Amazon RDS

PostgreSQL on Amazon RDS

Amazon RDS supports DB instances running several versions of PostgreSQL. You can create DB instances and DB snapshots, point-in-time restores and backups. DB instances running PostgreSQL support Multi-AZ deployments, Read Replicas (version 9.3.5 and later), Provisioned IOPS, and can be created inside a VPC. You can also use SSL to connect to a DB instance running PostgreSQL.

Before creating a DB instance, you should complete the steps in the Setting Up for Amazon RDS section of this guide.

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. In order to deliver a managed service experience, Amazon RDS does not provide host access to DB instances, and 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 does not allow direct host access to a DB instance via 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, including protected health information (PHI) under an executed Business Associate Agreement (BAA) with AWS. Amazon RDS for PostgreSQL also meets Federal Risk and Authorization Management Program (FedRAMP) security requirements and has received a FedRAMP Joint Authorization Board (JAB) Provisional Authority to Operate (P-ATO) at the FedRAMP HIGH Baseline within the AWS GovCloud (US) region. 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 PostgreSQL on Amazon RDS

The following are the common management tasks you perform with an Amazon RDS PostgreSQL DB instance, with links to relevant documentation for each task.

Task Area Relevant Documentation

Setting up Amazon RDS for first time use

There are prerequisites you must complete before you create your DB instance. For example, DB instances are created by default with a firewall that prevents access to it. You therefore must create a security group with the correct IP addresses and network configuration to access the DB instance.

Setting Up for Amazon RDS

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.

DB Instance Class

Amazon RDS Storage Types

Amazon RDS Provisioned IOPS Storage to Improve Performance

Finding supported PostgreSQL versions

Amazon RDS supports several versions of PostgreSQL.

Supported PostgreSQL Database Versions

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.

High Availability (Multi-AZ)

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. If your account does not have a default VPC, and you want the DB instance in a VPC, you must create the VPC and subnet groups before you create the DB instance.

Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform

Working with an Amazon RDS DB Instance in a VPC

Importing data into Amazon RDS PostgreSQL

You can use several different tools to import data into your PostgreSQL DB instance on Amazon RDS.

Importing Data into PostgreSQL on Amazon RDS

Setting up read only Read Replicas (master/standby)

PostgreSQL on Amazon RDS supports Read Replicas in both the same region and in a different region from the master instance.

Working with PostgreSQL, MySQL, and MariaDB Read Replicas

PostgreSQL Read Replicas (Version 9.3.5 and Later)

Replicating a Read Replica Across Regions

Understanding security groups

By default, DB instances are created with a firewall that prevents access to them. You therefore must create a security group with the correct IP addresses and network configuration to access the DB instance.

In general, if your DB instance is on the EC2-Classic platform, you will need to create a DB security group; if your DB instance is on the EC2-VPC platform, you will need to create a VPC security group.

Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform

Amazon RDS Security Groups

Setting up parameter groups and features

If your DB instance is going to require specific database parameters, you should create a parameter group before you create the DB instance.

Working with DB Parameter Groups

Performing common DBA tasks for PostgreSQL

If your DB instance is going to require specific database options, you should create an option group before you create the DB instance.

Appendix: Common DBA Tasks for PostgreSQL

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 pgadmin III.

Connecting to a DB Instance Running the PostgreSQL Database Engine

Using SSL with a PostgreSQL DB Instance

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.

Backing Up and Restoring Amazon RDS DB Instances

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.

Viewing DB Instance Metrics

Viewing Amazon RDS Events

Upgrading the PostgreSQL database version

You can do both major and minor version upgrades for your PostgreSQL DB instance.

Upgrading a PostgreSQL DB Instance

Major Version Upgrades

Working with log files

You can access the log files for your PostgreSQL DB instance.

PostgreSQL Database Log Files

Understanding the best practices for PostgreSQL DB instances

Find some of the best practices for working with PostgreSQL on Amazon RDS.

Best Practices for Working with PostgreSQL

Amazon RDS PostgreSQL Planning Information

Amazon RDS supports DB instances running several editions of PostgreSQL. This section shows how you can work with PostgreSQL on Amazon RDS. You should also be aware of the limits for PostgreSQL DB instances.

For information about importing PostgreSQL data into a DB instance, see Importing Data into PostgreSQL on Amazon RDS.

Topics

Using the rds_superuser Role

When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions. As with the PostgreSQL superuser role, the rds_superuser role has the most privileges on your DB instance and you should not assign this role to users unless they need the most access to the DB instance.

The rds_superuser role can do the following:

  • Add extensions that are available for use with Amazon RDS. For more information, see Supported PostgreSQL Features and the PostgreSQL documentation.

  • Manage tablespaces, including creating and deleting them. For more information, see this section in the PostgreSQL documentation.

  • View all users not assigned the rds_superuser role using the pg_stat_activity command and kill their connections using the pg_terminate_backend and pg_cancel_backend commands.

  • Grant and revoke the replication attribute onto all roles that are not the rds_superuser role. For more information, see this section in the PostgreSQL documentation.

Supported PostgreSQL Database Versions

Amazon RDS supports the following PostgreSQL versions:

Topics

PostgreSQL Version 9.6.2 on Amazon RDS

PostgreSQL version 9.6.2 contains several new features and bug fixes. The new version also includes the following extension versions:

  • PostGIS version 2.3.2

  • pg_freespacemap version 1.1 - Provides a way to examine the free space map (FSM). This extension provides an overloaded function called pg_freespace. The functions show the value recorded in the free space map for a given page, or for all pages in the relation.

  • pg_hint_plan version 1.1.3 - Provides control of execution plans by using hinting phrases at the beginning of SQL statements.

  • log_fdw version 1.0 - This extension from Amazon RDS lets you load and query your database engine log from within the database. For more information, see Using the log_fdw Extension.

  • With this version release, you can now edit the max_worker_processes parameter in a DB parameter group.

PostgreSQL version 9.6.2 on Amazon RDS also supports altering enum values. For more information, see ALTER ENUM for PostgreSQL.

For more information on the fixes in 9.6.2, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

PostgreSQL Version 9.6.1 on Amazon RDS

PostgreSQL version 9.6.1 contains several new features and improvements. For more information about the fixes and improvements in PostgreSQL 9.6.1, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance. For information about performing parallel queries and phrase searching using Amazon RDS for PostgreSQL 9.6.1, see the AWS Database Blog.

PostgreSQL version 9.6.1 includes the following changes:

  • Parallel query execution: Supports parallel execution of large read-only queries, allowing sequential scans, hash joins, nested loops and aggregates to be run in parallel. By default, parallel query execution is not enabled. To allow it, set the parameter max_parallel_workers_per_gather to a value larger than zero.

  • Updated postgres_fdw extension: Supports remote JOINs, SORTs, UPDATEs, and DELETEs.

  • PL/v8 update: Provides version 1.5.3 of the PL/v8 language.

  • PostGIS version update: Supports POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.1.1, released 2016/07/07" LIBXML="2.9.1" LIBJSON="0.12" RASTER

  • Vacuum improvement: Avoids scanning pages unnecessarily during vacuum freeze operations.

  • Full-text search support for phrases: Supports the ability to specify a phrase-search query in tsquery input using the new operators <-> and <N>.

  • Two new extensions: bloom, an index access method based on Bloom filters, and pg_visibility, which provides a means for examining the visibility map and page-level visibility information of a table.

  • With the release of version 9.6.2, you can now edit the max_worker_processes parameter in a PostgreSQL version 9.6.1 DB parameter group.

You can create a new PostgreSQL 9.6.1 database instance using the AWS Management Console, AWS CLI, or RDS API. You can also upgrade an existing PostgreSQL 9.5 instance to version 9.6.1 using major version upgrade. If you want to upgrade a DB instance from version 9.3 or 9.4 to 9.6, you must perform a point-and-click upgrade to the next major version first. Each upgrade operation involves a short period of unavailability for your DB instance.

PostgreSQL Version 9.5.6 on Amazon RDS

PostgreSQL version 9.5.6 contains several new features and bug fixes. The new version also includes the following extension versions:

  • PostGIS version 2.2.5

  • pg_freespacemap version 1.1 - Provides a way to examine the free space map (FSM). This extension provides an overloaded function called pg_freespace. The functions show the value recorded in the free space map for a given page, or for all pages in the relation.

  • pg_hint_plan version 1.1.3 - Provides control of execution plans by using hinting phrases at the beginning of SQL statements.

PostgreSQL version 9.5.6 on Amazon RDS also supports altering enum values. For more information, see ALTER ENUM for PostgreSQL.

For more information on the fixes in 9.5.6, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

PostgreSQL Version 9.5.4 on Amazon RDS

PostgreSQL version 9.5.4 contains several fixes to issue found in previous versions. For more information on the fixes in 9.5.4, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

Beginning with PostgreSQL version 9.4, PostgreSQL supports the streaming of WAL changes using logical replication decoding. Amazon RDS supports logical replication for PostgreSQL version 9.4.9 and higher and 9.5.4 and higher. For more information about PostgreSQL logical replication on Amazon RDS, see Logical Replication for PostgreSQL on Amazon RDS.

Beginning with PostgreSQL version 9.5.4 for Amazon RDS, the command ALTER USER WITH BYPASSRLS is supported.

PostgreSQL versions 9.4.9 and later and version 9.5.4 and later support event triggers, and Amazon RDS supports event triggers for these versions. The master user account can be used to create, modify, rename, and delete event triggers. Event triggers are at the DB instance level, so they can apply to all databases on an instance. For more information about PostgreSQL event triggers on Amazon RDS, see Event Triggers for PostgreSQL on Amazon RDS.

PostgreSQL Version 9.5.2 on Amazon RDS

PostgreSQL version 9.5.2 contains several fixes to issues found in previous versions. For more information on the features in 9.5.2, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

PostgreSQL version 9.5.2 does not support the previous generation db.t1, db.m1, or db.m2 instance classes. If your PostgreSQL DB instance is using one of these instance classes, you will need to scale compute to a comparable current generation db.t2 or db.m3 instance class before you can upgrade a DB instance running PostgreSQL version 9.4 to version 9.5.2. For more information on DB instance classes, see DB Instance Class.

Native PostgreSQL version 9.5.2 introduced the command ALTER USER WITH BYPASSRLS.

This release includes updates from previous versions, including the following:

  • CVE-2016-2193: Fixes an issue where a query plan might be reused for more than one ROLE in the same session. Reusing a query plan can cause the query to use the wrong set of Row Level Security (RLS) policies.

  • CVE-2016-3065: Fixes a server crash bug triggered by using pageinspect with BRIN index pages. Because an attacker might be able to expose a few bytes of server memory, this crash is being treated as a security issue.

Major enhancements in RDS PostgreSQL 9.5 include the following:

  • UPSERT: Allow INSERTs that would generate constraint conflicts to be turned into UPDATEs or ignored

  • Add the GROUP BY analysis features GROUPING SETS, CUBE and ROLLUP

  • Add row-level security control

  • Create mechanisms for tracking the progress of replication, including methods for identifying the origin of individual changes during logical replication

  • Add Block Range Indexes (BRIN)

  • Add substantial performance improvements for sorting

  • Add substantial performance improvements for multi-CPU machines

  • PostGIS 2.2.2 - To use this latest version of PostGIS, use the ALTER EXTENSION UPDATE statement to update after you upgrade to version 9.5.2. Example:

    ALTER EXTENSION POSTGIS UPDATE TO '2.2.2'

  • Improved visibility of autovacuum sessions by allowing the rds_superuser account to view autovacuum sessions in pg_stat_activity. For example, you can identify and terminate an autovacuum session that is blocking a command from running, or executing slower than a manually issued vacuum command.

RDS PostgreSQL version 9.5.2 includes the following new extensions:

  • address_standardizer – A single line address parser that takes an input address and normalizes it based on a set of rules stored in a table, helper lex, and gaz tables.

  • hstore_plperl – Provides transforms for the hstore type for PL/Perl.

  • tsm_system_rows – Provides the table sampling method SYSTEM_ROWS, which can be used in the TABLESAMPLE clause of a SELECT command.

  • tsm_system_time – Provides the table sampling method SYSTEM_TIME, which can be used in the TABLESAMPLE clause of a SELECT command.

PostgreSQL Version 9.4.11 on Amazon RDS

PostgreSQL version 9.4.11 contains several fixes to issue found in previous versions.

For more information on the fixes in 9.4.11, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

Beginning with PostgreSQL version 9.4, PostgreSQL supports the streaming of WAL changes using logical replication decoding. Amazon RDS supports logical replication for PostgreSQL version 9.4.9 and higher and 9.5.4 and higher. For more information about PostgreSQL logical replication on Amazon RDS, see Logical Replication for PostgreSQL on Amazon RDS.

PostgreSQL versions 9.4.9 and later and version 9.5.4 and later support event triggers, and Amazon RDS supports event triggers for these versions. The master user account can be used to create, modify, rename, and delete event triggers. Event triggers are at the DB instance level, so they can apply to all databases on an instance. For more information about PostgreSQL event triggers on Amazon RDS, see Event Triggers for PostgreSQL on Amazon RDS.

PostgreSQL Version 9.4.9 on Amazon RDS

PostgreSQL version 9.4.9 contains several fixes to issue found in previous versions. For more information on the fixes in 9.4.9, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

Beginning with PostgreSQL version 9.4, PostgreSQL supports the streaming of WAL changes using logical replication decoding. Amazon RDS supports logical replication for PostgreSQL version 9.4.9 and higher and 9.5.4 and higher. For more information about PostgreSQL logical replication on Amazon RDS, see Logical Replication for PostgreSQL on Amazon RDS.

PostgreSQL versions 9.4.9 and later and version 9.5.4 and later support event triggers, and Amazon RDS supports event triggers for these versions. The master user account can be used to create, modify, rename, and delete event triggers. Event triggers are at the DB instance level, so they can apply to all databases on an instance. For more information about PostgreSQL event triggers on Amazon RDS, see Event Triggers for PostgreSQL on Amazon RDS.

PostgreSQL Version 9.4.7 on Amazon RDS

PostgreSQL version 9.4.7 contains several fixes to issue found in previous versions. For more information on the fixes in 9.4.7, see the PostgreSQL documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

PostgreSQL version 9.4.7 includes improved visibility of autovacuum sessions by allowing the rds_superuser account to view autovacuum sessions in pg_stat_activity. For example, you can identify and terminate an autovacuum session that is blocking a command from running, or executing slower than a manually issued vacuum command.

PostgreSQL Version 9.3.16 on Amazon RDS

PostgreSQL version 9.3.16 contains several fixes for bugs found in previous versions. This version contains the same extension components as version 9.3.14. For a list of fixes in version 9.3.16, see the PostgreSQl documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

PostgreSQL Version 9.3.14 on Amazon RDS

PostgreSQL version 9.3.14 contains several fixes for bugs found in previous versions. For a list of fixes in version 9.3.14, see the PostgreSQl documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

PostgreSQL Version 9.3.12 on Amazon RDS

PostgreSQL version 9.3.12 contains several fixes for bugs found in previous versions. For a list of fixes in version 9.3.12, see the PostgreSQl documentation. For information on upgrading the engine version for your PostgreSQL DB instance, see Upgrading a PostgreSQL DB Instance.

PostgreSQL version 9.3.12 includes improved visibility of autovacuum sessions by allowing the rds_superuser account to view autovacuum sessions in pg_stat_activity. For example, you can identify and terminate an autovacuum session that is blocking a command from running, or executing slower than a manually issued vacuum command.

Supported PostgreSQL Features and Extensions

Amazon RDS supports many of the most common PostgreSQL extensions and features.

Topics

PostgreSQL Extensions Supported on Amazon RDS

PostgreSQL supports many PostgreSQL extensions. The following sections show the extensions supported by Amazon RDS for the major PostgreSQL versions.

Topics

You can also 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: 

Copy
SHOW rds.extensions;
PostgreSQL Version 9.3.x Extensions Supported on Amazon RDS

The following table shows PostgreSQL extensions for PostgreSQL version 9.3.x that are currently supported by PostgreSQL on Amazon RDS. "N/A" indicates that the extension is not available for that PostgreSQL version. For more information on PostgreSQL extensions, see Packaging Related Objects into an Extension.

Extension 9.3.12 9.3.14 9.3.16
address_standardizer N/A N/A N/A
address_standardizer_data_us N/A N/A N/A
bloom N/A N/A N/A
btree_gin 1.0 1.0 1.0
btree_gist 1.0 1.0 1.0
chkpass 1.0 1.0 1.0
citext 1.0 1.0 1.0
cube 1.0 1.0 1.0
dblink 1.1 1.1 1.1
dict_int 1.0 1.0 1.0
dict_xsyn 1.0 1.0 1.0
earthdistance 1.0 1.0 1.0
fuzzystrmatch 1.0 1.0 1.0
hstore 1.2 1.2 1.2
hstore_plperl N/A N/A N/A
intagg 1.0 1.0 1.0
intarray 1.0 1.0 1.0
ip4r N/A N/A N/A
isn 1.0 1.0 1.0
log_fdw - See Using the log_fdw Extension. N/A N/A N/A
ltree 1.0 1.0 1.0
pg_buffercache N/A N/A N/A
pg_freespacemap N/A N/A N/A
pg_hint_plan N/A N/A N/A
pg_prewarm N/A N/A N/A
pg_stat_statements 1.1 1.1 1.1
pg_trgm 1.1 1.1 1.1
pg_visibility N/A N/A N/A
pgcrypto 1.0 1.0 1.0
pgrowlocks 1.1 1.1 1.1
pgstattuple N/A N/A N/A
plcoffee 1.4.4 1.4.4 1.4.4
plls 1.4.4 1.4.4 1.4.4
plperl 1.0 1.0 1.0
plpgsql 1.0 1.0 1.0
pltcl 1.0 1.0 1.0
plv8 1.4.4 1.4.4 1.4.4
PostGIS 2.1.8 2.1.8 2.1.8
postgis_tiger_geocoder 2.1.8 2.1.8 2.1.8
postgis_topology 2.1.8 2.1.8 2.1.8
postgres_fdw 1.0 1.0 1.0
sslinfo 1.0 1.0 1.0
tablefunc 1.0 1.0 1.0
test_parser 1.0 1.0 1.0
tsearch2 1.0 1.0 1.0
tsm_system_rows N/A N/A N/A
tsm_system_time N/A N/A N/A
unaccent 1.0 1.0 1.0
uuid-ossp 1.0 1.0 1.0

PostgreSQL Version 9.4.x Extensions Supported on Amazon RDS

The following table shows PostgreSQL extensions for PostgreSQL version 9.4.x that are currently supported by PostgreSQL on Amazon RDS. "N/A" indicates that the extension is not available for that PostgreSQL version. For more information on PostgreSQL extensions, see Packaging Related Objects into an Extension.

Extension 9.4.7 9.4.9 9.4.11
address_standardizer N/A N/A N/A
address_standardizer_data_us N/A N/A N/A
bloom N/A N/A N/A
btree_gin 1.0 1.0 1.0
btree_gist 1.0 1.0 1.0
chkpass 1.0 1.0 1.0
citext 1.0 1.0 1.0
cube 1.0 1.0 1.0
dblink 1.1 1.1 1.1
dict_int 1.0 1.0 1.0
dict_xsyn 1.0 1.0 1.0
earthdistance 1.0 1.0 1.0
fuzzystrmatch 1.0 1.0 1.0
hstore 1.3 1.3 1.3
hstore_plperl N/A N/A N/A
intagg 1.0 1.0 1.0
intarray 1.0 1.0 1.0
ip4r 2.0 2.0 2.0
isn 1.0 1.0 1.0
log_fdw - See Using the log_fdw Extension. N/A N/A N/A
ltree 1.0 1.0 1.0
pg_buffercache 1.0 1.0 1.0
pg_freespacemap N/A N/A N/A
pg_hint_plan N/A N/A N/A
pg_prewarm 1.0 1.0 1.0
pg_stat_statements 1.2 1.2 1.2
pg_trgm 1.1 1.1 1.1
pg_visibility N/A N/A N/A
pgcrypto 1.1 1.1 1.1
pgrowlocks 1.1 1.1 1.1
pgstattuple 1.2 1.2 1.2
plcoffee 1.4.4 1.4.4 1.4.4
plls 1.4.4 1.4.4 1.4.4
plperl 1.0 1.0 1.0
plpgsql 1.0 1.0 1.0
pltcl 1.0 1.0 1.0
plv8 1.4.4 1.4.4 1.4.4
PostGIS 2.1.8 2.1.8 2.1.8
postgis_tiger_geocoder 2.1.8 2.1.8 2.1.8
postgis_topology 2.1.8 2.1.8 2.1.8
postgres_fdw 1.0 1.0 1.0
sslinfo 1.0 1.0 1.0
tablefunc 1.0 1.0 1.0
test_parser 1.0 1.0 1.0
tsearch2 1.0 1.0 1.0
tsm_system_rows N/A N/A N/A
tsm_system_time N/A N/A N/A
unaccent 1.0 1.0 1.0
uuid-ossp 1.0 1.0 1.0

PostgreSQL Version 9.5.x Extensions Supported on Amazon RDS

The following table shows PostgreSQL extensions for PostgreSQL version 9.5.x that are currently supported by PostgreSQL on Amazon RDS. "N/A" indicates that the extension is not available for that PostgreSQL version. For more information on PostgreSQL extensions, see Packaging Related Objects into an Extension.

Extension 9.5.2 9.5.4 9.5.6
address_standardizer 2.2.2 2.2.2 2.2.5
address_standardizer_data_us 2.2.2 2.2.2 2.2.5
bloom N/A N/A N/A
btree_gin 1.0 1.0 1.0
btree_gist 1.1 1.1 1.1
chkpass 1.0 1.0 1.0
citext 1.1 1.1 1.1
cube 1.0 1.0 1.0
dblink 1.1 1.1 1.1
dict_int 1.0 1.0 1.0
dict_xsyn 1.0 1.0 1.0
earthdistance 1.0 1.0 1.0
fuzzystrmatch 1.0 1.0 1.0
hstore 1.3 1.3 1.3
hstore_plperl 1.0 1.0 1.0
intagg 1.0 1.0 1.0
intarray 1.0 1.0 1.0
ip4r 2.0 2.0 2.0
isn 1.0 1.0 1.0
log_fdw - See Using the log_fdw Extension. N/A N/A N/A
ltree 1.0 1.0 1.0
pg_buffercache 1.1 1.1 1.1
pg_freespacemap N/A N/A 1.0
pg_hint_plan N/A N/A 1.1.3
pg_prewarm 1.0 1.0 1.0
pg_stat_statements 1.3 1.3 1.3
pg_trgm 1.1 1.1 1.1
pg_visibility N/A N/A N/A
pgcrypto 1.2 1.2 1.2
pgrowlocks 1.1 1.1 1.1
pgstattuple 1.3 1.3 1.3
plcoffee 1.4.4 1.4.4 1.4.4
plls 1.4.4 1.4.4 1.4.4
plperl 1.0 1.0 1.0
plpgsql 1.0 1.0 1.0
pltcl 1.0 1.0 1.0
plv8 1.4.4 1.4.4 1.4.4
PostGIS 2.2.2 2.2.2 2.2.5
postgis_tiger_geocoder 2.2.2 2.2.2 2.2.5
postgis_topology 2.2.2 2.2.2 2.2.5
postgres_fdw 1.0 1.0 1.0
sslinfo 1.0 1.0 1.0
tablefunc 1.0 1.0 1.0
test_parser 1.0 1.0 1.0
tsearch2 1.0 1.0 1.0
tsm_system_rows N/A N/A 1.0
tsm_system_time N/A N/A 1.0
unaccent 1.0 1.0 1.0
uuid-ossp 1.0 1.0 1.0

PostgreSQL Version 9.6.x Extensions Supported on Amazon RDS

The following table shows PostgreSQL extensions for PostgreSQL version 9.6.x that are currently supported by PostgreSQL on Amazon RDS. "N/A" indicates that the extension is not available for that PostgreSQL version. For more information on PostgreSQL extensions, see Packaging Related Objects into an Extension.

Extension 9.6.1 9.6.2
address_standardizer 2.3.0 2.3.2
address_standardizer_data_us 2.3.0 2.3.2
bloom 1.0 1.0
btree_gin 1.0 1.0
btree_gist 1.2 1.2
chkpass 1.0 1.0
citext 1.3 1.3
cube 1.2 1.2
dblink 1.2 1.2
dict_int 1.0 1.0
dict_xsyn 1.0 1.0
earthdistance 1.1 1.1
fuzzystrmatch 1.1 1.1
hstore 1.4 1.4
hstore_plperl 1.0 1.0
intagg 1.1 1.1
intarray 1.2 1.2
ip4r 2.0 2.0
isn 1.1 1.1
log_fdw - See Using the log_fdw Extension. N/A 1.0
ltree 1.1 1.1
pg_buffercache 1.2 1.2
pg_freespacemap N/A 1.1
pg_hint_plan N/A 1.1.3
pg_prewarm 1.1 1.1
pg_stat_statements 1.4 1.4
pg_trgm 1.3 1.3
pg_visibility 1.1 1.1
pgcrypto 1.3 1.3
pgrowlocks 1.2 1.2
pgstattuple 1.4 1.4
plcoffee 1.5.3 1.5.3
plls 1.5.3 1.5.3
plperl 1.0 1.0
plpgsql 1.0 1.0
pltcl 1.0 1.0
plv8 1.5.3 1.5.3
PostGIS 2.3.0 2.3.2
postgis_tiger_geocoder 2.3.0 2.3.2
postgis_topology 2.3.0 2.3.2
postgres_fdw 1.0 1.0
sslinfo 1.2 1.2
tablefunc 1.0 1.0
test_parser 1.0 1.0
tsearch2 1.0 1.0
tsm_system_rows 1.0 1.0
tsm_system_time 1.0 1.0
unaccent 1.1 1.1
uuid-ossp 1.1 1.1

PostgreSQL Extension Support for PostGIS on Amazon RDS

The following table shows the PostGIS component versions that ship with the Amazon RDS PostgreSQL versions:

Version PostGIS GEOS GDAL PROJ
9.3.12 2.1.8 r13780 3.5.0-CAPI-1.9.0 r4084

GDAL 1.11.4, released 2016/01/25

Rel. 4.9.2, 08 September 2015
9.3.14 2.1.8 r13780 3.5.0-CAPI-1.9.0 r4084

GDAL 1.11.5, released 2016/07/01

Rel. 4.9.2, 08 September 2015
9.3.16 2.1.8 r13780 3.5.0-CAPI-1.9.0 r4084

GDAL 1.11.5, released 2016/07/01

Rel. 4.9.2, 08 September 2015
9.4.7

2.1.8 r13780

3.5.0-CAPI-1.9.0 r4084

GDAL 1.11.4, released 2016/01/25

 Rel. 4.9.2, 08 September 2015
9.4.9

2.1.8 r13780

3.5.0-CAPI-1.9.0 r4084

GDAL 1.11.5, released 2016/07/01

 Rel. 4.9.2, 08 September 2015
9.4.11

2.1.8 r13780

3.5.0-CAPI-1.9.0 r4084

GDAL 1.11.5, released 2016/07/01

 Rel. 4.9.2, 08 September 2015
9.5.2

2.2.2 r14797

3.5.0-CAPI-1.9.0 r4084

GDAL 2.0.2, released 2016/01/26

 Rel. 4.9.2, 08 September 2015
9.5.4

2.2.2 r14797

3.5.0-CAPI-1.9.0 r4084

GDAL 2.0.3, released 2016/07/01

 Rel. 4.9.2, 08 September 2015
9.5.6

2.2.5 r15298

3.5.1-CAPI-1.9.1 r4246

GDAL 2.0.3, released 2016/07/01

Rel. 4.9.3, 15 August 2016
9.6.1

2.3.0 r15146

3.5.0-CAPI-1.9.0 r4084

GDAL 2.1.1, released 2016/07/07

 Rel. 4.9.2, 08 September 2015
9.6.2

2.3.2 r15302

3.5.1-CAPI-1.9.1 r4246

GDAL 2.1.3, released 2017/20/01

Rel. 4.9.3, 15 August 2016

Note that you have to create the PostGIS extension before you can use it by running the following command. 

Copy

 CREATE EXTENSION POSTGIS;
Using the log_fdw Extension

log_fdw is a new extension from Amazon RDS for PostgreSQL version 9.6.2 and later. It allows you to access your database engine log using a SQL interface. In addition to viewing the stderr log files that are generated by default on RDS, you can view CSV logs (set the log_destination parameter to csvlog) and build foreign tables with the data neatly split into several columns.

This extension introduces two new functions that make it easy to create foreign tables for database logs:

  • list_postgres_log_files() - Lists the files in the database log directory and the file size in bytes.

  • create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text) - Builds a foreign table for the specified file in the current database.

All functions created by log_fdw are owned by rds_superuser. Members of the rds_superuser role can grant access to these functions to other database users.

The following example shows how to use the log_fdw extension.

To use the log_fdw extension

  1. Get the log_fdw extension. 

    Copy
    
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION

  2. Create the log server as a foreign data wrapper.

    Copy
    
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER

  3. Select all from a list of log files.

    Copy
    
postgres=> SELECT * from list_postgres_log_files() order by 1;

    A sample response is as follows:

    Copy
    
            file_name             | file_size_bytes 
----------------------------------+-----------------
 postgresql.log.2016-08-09-22.csv |            1111
 postgresql.log.2016-08-09-23.csv |            1172
 postgresql.log.2016-08-10-00.csv |            1744
 postgresql.log.2016-08-10-01.csv |            1102
(4 rows)

  4. Create a table with a single 'log_entry' column for non-CSV files

    Copy
    
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log', 
     'log_server', 'postgresql.log.2016-08-09-22.csv');

    A sample response is as follows:

    Copy
    -----------------------------------

(1 row)

  5. Select a sample of the log file. The following code retrieves the log time and error message description. 

    Copy
    
postgres=> SELECT log_time, message from my_postgres_error_log order by 1;

    A sample response is as follows:

    Copy
    
             log_time             |                                  message                                 
----------------------------------+---------------------------------------------------------------------------
Tue Aug 09 15:45:18.172 2016 PDT | ending log output to stderr
Tue Aug 09 15:45:18.175 2016 PDT | database system was interrupted; last known up at 2016-08-09 22:43:34 UTC
Tue Aug 09 15:45:18.223 2016 PDT | checkpoint record is at 0/90002E0
Tue Aug 09 15:45:18.223 2016 PDT | redo record is at 0/90002A8; shutdown FALSE
Tue Aug 09 15:45:18.223 2016 PDT | next transaction ID: 0/1879; next OID: 24578
Tue Aug 09 15:45:18.223 2016 PDT | next MultiXactId: 1; next MultiXactOffset: 0
Tue Aug 09 15:45:18.223 2016 PDT | oldest unfrozen transaction ID: 1822, in database 1
(7 rows)

Supported PostgreSQL Features

Amazon RDS supports many of the most common PostgreSQL features. These include:

Topics

Logical Replication for PostgreSQL on Amazon RDS

Beginning with PostgreSQL version 9.4, PostgreSQL supports the streaming of WAL changes using logical replication slots. Amazon RDS supports logical replication for a PostgreSQL DB instance version 9.4.9 and higher and 9.5.4 and higher. Using logical replication, you can set up logical replication slots on your instance and stream database changes through these slots to a client like pg_recvlogical. Logical slots are created at the database level and support replication connections to a single database.

PostgreSQL logical replication on Amazon RDS is enabled by a new parameter, a new replication connection type, and a new security role. The client for the replication can be any client that is capable of establishing a replication connection to a database on a PostgreSQL DB instance.

The most common clients for PostgreSQL logical replication are AWS Database Migration Service or a custom-managed host on an AWS EC2 instance. The logical replication slot knows nothing about the receiver of the stream; there is no requirement that the target be a replica database. Note that if you set up a logical replication slot and do not read from the slot, data can be written to your DB instance's storage and you can quickly fill up the storage on your instance.

For more information on using logical replication with PostgreSQL, see the PostgreSQL documentation. [https://www.postgresql.org/docs/current/static/logicaldecoding-example.html]

To enable logical replication for an Amazon RDS for PostgreSQL DB instance, you must do the following:

  • The AWS user account initiating the logical replication for the PostgreSQL database on Amazon RDS must have the rds_superuser role and the rds_replication role. The rds_replication role grants permissions to manage logical slots and to stream data using logical slots.

  • Set the rds.logical_replication parameter to 1. It is a static parameter that requires a reboot to take effect. As part of applying this parameter, we set the wal_level, max_wal_senders, max_replication_slots, max_connections parameters. Note that these parameter changes can increase WAL generation so you should only set the rds.logical_replication parameter when you are using logical slots.

  • Create a logical replication slot as explained below. This process requires a decoding plugin to be specified; currently we support the ‘test_decoding’ output plugin that ships with PostgreSQL.

Working with Logical Replication Slots

You can use SQL commands to work with logical slots. For example, the following command creates a logical slot named test_slot using the default PostgreSQL output plugin test_decoding. 

Copy

SELECT * FROM pg_create_logical_replication_slot('test_slot', 'test_decoding');

The output should be similar to the following:

Copy
slot_name    | xlog_position
-----------------+---------------
regression_slot | 0/16B1970
(1 row)

To list logical slots, use the following command:

Copy

SELECT * FROM pg_replication_slots;

To drop a logical slot, use the following command:

Copy

SELECT pg_drop_replication_slot('test_slot');

The output should be similar to the following:

Copy

pg_drop_replication_slot
-----------------------

(1 row)

For more examples on working with logical replication slots, see Logical Decoding Examples in the PostgreSQL documentation.

Once you create the logical replication slot, you can start streaming. The following example shows how logical decoding is controlled over the streaming replication protocol, using the program pg_recvlogical included in the PostgreSQL distribution. This requires that client authentication is set up to allow replication connections. 

Copy

pg_recvlogical -d postgres --slot test_slot -U master 
    --host sg-postgresql1.c6c8mresaghgv0.us-west-2.rds.amazonaws.com 
    -f -  --start
Event Triggers for PostgreSQL on Amazon RDS

PostgreSQL versions 9.4.9 and later and version 9.5.4 and later support event triggers, and Amazon RDS supports event triggers for these versions. The master user account can be used to create, modify, rename, and delete event triggers. Event triggers are at the DB instance level, so they can apply to all databases on an instance.

For example, the following code creates an event trigger that prints the current user at the end of every DDL command. 

Copy

CREATE OR REPLACE FUNCTION raise_notice_func()
                RETURNS event_trigger
                LANGUAGE plpgsql AS
$$
BEGIN
                RAISE NOTICE 'In trigger function: %', current_user;
END;
$$;

CREATE EVENT TRIGGER event_trigger_1 
                ON ddl_command_end
EXECUTE PROCEDURE raise_notice_func();

For more information about PostgreSQL event triggers, see Event Triggers in the PostgreSQL documentation.

There are several limitations to using PostgreSQL event triggers on Amazon RDS. These include:

  • You cannot create event triggers on read replicas. You can, however, create event triggers on a read replica master and the event triggers will be copied to the read replica. The event triggers on the read replica will not fire on the read replica when changes are pushed from the master, but if the read replica is promoted, the existing event triggers will fire when database operations occur.

  • To perform a major version upgrade to a PostgreSQL DB instance that uses event triggers, you must delete the event triggers before you upgrade the instance.

Huge Pages for Amazon RDS for PostgreSQL

Amazon RDS for PostgreSQL supports multiple page sizes for PostgreSQL versions 9.4.11 and later, 9.5.6 and later, and 9.6.2 and later. This support includes 4 K and 2 MB page sizes.

Huge pages reduce overhead when using large contiguous chunks of memory. You allocate huge pages for your application by using calls to mmap or SYSV shared memory. You enable huge pages on an Amazon RDS for PostgreSQL database by using the huge_pages parameter. Set this parameter to "on" to use huge pages; the default value is "off."

When you set the huge_pages parameter to "on," Amazon RDS uses huge pages based on the available shared memory. If the DB instance is unable to use huge pages due to shared memory constraints, Amazon RDS prevents the instance from starting and sets the status of the DB instance to an incompatible parameters state. In this case, you can set the huge_pages parameter to "off" to allow Amazon RDS to start the DB instance.

The shared_buffers parameter is key to setting the shared memory pool that is required for using huge pages. The default value for the shared_buffers parameter is set to a percentage of the total 8K pages available for that instance's memory. When you use huge_pages, those pages will be allocated in the huge_pages collocated together. Note that Amazon RDS puts a DB instance into an incompatible parameters state if the shared memory parameters are set to require more than 90 percent of the DB instance memory. For more information about setting shared memory for PostgreSQL, see the PostgreSQL documentation.

Note

Huge pages are not supported for the db.m1, db.m2, db.m3, and db.t1 DB instance classes.

Tablespaces for PostgreSQL on Amazon RDS

Tablespaces are supported in PostgreSQL on Amazon RDS for compatibility; since all storage is on a single logical volume, tablespaces cannot be used for IO splitting or isolation. We have benchmarks and practical experience that shows that a single logical volume is the best setup for most use cases.

Autovacuum for PostgreSQL on Amazon RDS

The PostgreSQL auto-vacuum is an optional, but highly recommended, parameter that by default is turned on for new PostgreSQL DB instances. Do not turn this parameter off. For more information on using auto-vacuum with Amazon RDS PostgreSQL, see Working with PostgreSQL Autovacuum on Amazon RDS.

RAM Disk for the stats_temp_directory

The Amazon RDS for PostgreSQL parameter, rds.pg_stat_ramdisk_size, can be used to specify the system memory allocated to a RAM disk for storing the PostgreSQL stats_temp_directory. The RAM disk parameter is available for all PostgreSQL versions on Amazon RDS.

Under certain workloads, setting this parameter can improve performance and decrease IO requirements. For more information about the stats_temp_directory, see the PostgreSQL documentation.

To enable a RAM disk for your stats_temp_directory, set the rds.pg_stat_ramdisk_size parameter to a non-zero value in the parameter group used by your DB instance. The parameter value is in MB. You must reboot the DB instance before the change will take effect.

For example, the following AWS CLI command sets the RAM disk parameter to 256 MB: 

Copy

postgres=>aws rds modify-db-parameter-group \
    --db-parameter-group-name pg-95-ramdisk-testing \
    --parameters "ParameterName=rds.pg_stat_ramdisk_size, ParameterValue=256, ApplyMethod=pending-reboot"

After you reboot, run the following command to see the status of the stats_temp_directory: 

Copy

postgres=>show stats_temp_directory;

The command should return the following: 

Copy

stats_temp_directory
---------------------------
/rdsdbramdisk/pg_stat_tmp
(1 row)
ALTER ENUM for PostgreSQL

Amazon RDS PostgreSQL versions 9.6.2 and 9.5.6 and later support the ability to alter enumerations. This feature is not available in other versions on Amazon RDS.

The following code shows an example of altering an enum value.

Copy

postgres=> CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');
CREATE TYPE
postgres=> CREATE TABLE t1 (colors rainbow);
CREATE TABLE
postgres=> INSERT INTO t1 VALUES ('red'), ( 'orange');
INSERT 0 2
postgres=> SELECT * from t1;
colors
--------
red
orange
(2 rows)
postgres=> ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
ALTER TYPE
postgres=> SELECT * from t1;
colors
---------
crimson
orange
(2 rows)

Limits for PostgreSQL DB Instances

You can have up to 40 PostgreSQL DB instances. The following is a list of limitations for PostgreSQL on Amazon RDS:

  • The minimum storage size for a PostgreSQL DB instance is 5 GB.

  • The maximum storage size for a PostgreSQL DB instance is 6 TB for each instance.

  • Amazon RDS reserves up to 3 connections for system maintenance. If you specify a value for the user connections parameter, you will need to add 3 to the number of connections that you expect to use.

Upgrading a PostgreSQL DB Instance

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

  • OS Updates — Occasionally, Amazon RDS may 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 Updating the Operating System for a DB Instance or DB Cluster.

  • Database Engine Upgrades — 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: major version upgrades and minor version upgrades. Amazon RDS supports both major and minor version upgrades for PostgreSQL DB instances.

    For more information about PostgreSQL DB engine upgrades, see Upgrading the PostgreSQL DB Engine.

Using SSL with a PostgreSQL DB Instance

Amazon RDS supports SSL encryption for PostgreSQL DB instances. Using SSL, you can encrypt a PostgreSQL connection between your applications and your PostgreSQL DB instances. You can also force all connections to your PostgreSQL DB instance to use SSL.

Topics

SSL support is available in all AWS regions for PostgreSQL. Amazon RDS creates an SSL certificate for your PostgreSQL DB instance when the instance is created. If you enable SSL certificate verification, then the SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks.

To connect to a PostgreSQL DB instance over SSL

  1. Download the certificate stored at https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem.

  2. Import the certificate into your operating system.

  3. Connect to your PostgreSQL DB instance over SSL by appending sslmode=verify-full to your connection string. When you use sslmode=verify-full, the SSL connection verifies the DB instance endpoint against the endpoint in the SSL certificate.

    Use the sslrootcert parameter to reference the certificate, for example, sslrootcert=rds-ssl-ca-cert.pem.

Requiring an SSL Connection to a PostgreSQL DB Instance

You can require that connections to your PostgreSQL DB instance use SSL by using the rds.force_ssl parameter. By default, the rds.force_ssl parameter is set to 0 (off). You can set the rds.force_ssl parameter to 1 (on) to require SSL for connections to your DB instance. Updating the rds.force_ssl parameter also sets the PostgreSQL ssl parameter to 1 (on) and modifies your DB instance’s pg_hba.conf file to support the new SSL configuration.

You can set the rds.force_ssl parameter value by updating the parameter group for your DB instance. If the parameter group for your DB instance is not the default parameter group, and the ssl parameter is already set to 1 when you set the rds.force_ssl parameter to 1, then you do not need to reboot your DB instance. Otherwise you must reboot your DB instance for the change to take effect. For more information on parameter groups, see Working with DB Parameter Groups.

When the rds.force_ssl parameter is set to 1 for a DB instance, you will see output similar to the following when you connect, indicating that SSL is now required: 

Copy

$ psql postgres -h SOMEHOST.amazonaws.com -p 8192 -U someuser
psql (9.3.12, server 9.4.4)
WARNING: psql major version 9.3, server major version 9.4.
Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=>
Determining the SSL Connection Status

The encrypted status of your connection is shown in the logon banner when you connect to the DB instance: 

Copy
Password for user master: 
psql (9.3.12) 
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) 
Type "help" for help.   

postgres=>

You can also load the sslinfo extension and then call the show ssl function to determine if SSL is being used. The function returns on if the connection is using SSL, otherwise it returns off. 

Copy

postgres=> create extension sslinfo;
CREATE EXTENSION

postgres=> show ssl;
 ssl
----
 on
(1 row)

You can use the select ssl_cipher() command to determine the SSL cipher: 

Copy

postgres=> select ssl_cipher();
ssl_cipher
--------------------
DHE-RSA-AES256-SHA
(1 row)

If you enable set rds.force_ssl and restart your instance, non-SSL connections are refused with the following message: 

Copy

$ export PGSSLMODE=disable
$ psql postgres -h SOMEHOST.amazonaws.com -p 8192 -U someuser
psql: FATAL: no pg_hba.conf entry for host "host.ip", user "someuser", database "postgres", SSL off
$