Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

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

These are the common management tasks you perform with a PostgreSQL DB instance, with links to information about each task:

  • For planning information, such as PostgreSQL versions, storage engines, security, and features supported in Amazon RDS, see Amazon RDS PostgreSQL Planning Information.

  • There are prerequisites you must complete before you create your DB instance; for more information, see the Setting Up for Amazon RDS section of this guide. 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 you will use to access the DB instance.

  • If you are creating a DB instance for production purposes, you should understand how instance classes, storage, and Provisioned IOPS work in Amazon RDS. For more information about DB instance classes, see DB Instance Class For more information about Amazon RDS storage, see Amazon RDS Storage Types. For more information about Provisioned IOPS, see Amazon RDS Provisioned IOPS Storage to Improve Performance.

  • A production DB instance should also use Multi-AZ deployments. All Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. For more information about Multi-AZ deployments, see High Availability (Multi-AZ).

  • You can create a PostgreSQL Read Replica (master/standby) DB instance to service read-only traffic. For more information about PostgreSQL Read Replicas, see Working with PostgreSQL, MySQL, and MariaDB Read Replicas.

  • If your AWS account has a default VPC (a default virtual private network), then your DB instance will automatically be created inside the default VPC. If your account does not have a default VPC and you want the DB instance to be inside a VPC, you must create the VPC and subnet groups before you create the DB instance. For more information about determining if your account has a default VPC, see Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform. For more information about using VPCs with Amazon RDS, see Virtual Private Clouds (VPCs) and Amazon RDS.

  • If you want to modify PostgreSQL database parameters, you must create a parameter group and assign it to your DB instance. For more information on parameter groups, see Working with DB Parameter Groups.

  • After you create a security group and associate it to a DB instance, you can connect to the DB instance using any standard SQL client application such as pgAdmin. For more information on connecting to a DB instance, see Connecting to a DB Instance Running the PostgreSQL Database Engine.

  • You can configure your DB instance to take automated backups, or take manual snapshots, and then restore instances from the backups or snapshots. For information, see Backing Up and Restoring.

  • You can monitor an instance through actions such as viewing the PostgreSQL logs, CloudWatch Amazon RDS metrics, and events. For information, see Monitoring Amazon RDS.

There is also an important appendix with useful information about working with PostgreSQL DB instances. For information on common DBA tasks for PostgreSQL on Amazon RDS, see Appendix: Common DBA Tasks for 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.

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

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.

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 Best Practices for Working with PostgreSQL.

To import PostgreSQL data into a DB instance, follow the information in the Importing Data into PostgreSQL on Amazon RDS section.

Supported PostgreSQL Database Versions

Currently, Amazon RDS supports the following PostgreSQL versions:

  • PostgreSQL versions 9.3.1, 9.3.2, 9.3.3, 9.3.5, 9.3.6, 9.3.9, 9.3.10, 9.3.12

  • PostgreSQL versions 9.4.1, 9.4.4, 9.4.5, 9.4.7

  • PostgreSQL versions 9.5.2

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 Database Version Upgrades.

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.

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

  • 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.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 Database Version Upgrades.

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.4.5 on Amazon RDS

PostgreSQL version 9.4.5 contains several fixes, including the following:

  • CVE-2015-5289 - Input values for JSON or JSONB data types that are constructed from arbitrary user input can crash the PostgreSQL server and cause a denial of service.

  • CVE-2015-5288 - The crypt() function included with the optional pgCrypto extension can be exploited to read a few additional bytes of memory. No working exploit for this issue has been developed.

  • PostGIS 2.1.8

PostgreSQL version 9.4.5 includes three new extensions. These extensions are only supported for version 9.4.5. If you create any of these extensions on a version prior to 9.4.5, you will need to drop and recreate them when you convert to 9.4.5.

  • pgstattuple - provides various functions to obtain tuple-level statistics. pgstattuple returns a relation's physical length, percentage of "dead" tuples, and other info. This may help users to determine whether vacuuming is necessary.

    This extension lets you see certain statistics about a table, such as how much space the table is using, how many entries there are, how much space the entries are using, and several other statistics. When a row is deleted from a table in PostgreSQL, the row is not deleted from memory; the row is marked as a "dead" row in PostgreSQL. For the row to be removed from memory, a “vacuum” action must be performed on that table. With the pgstattuple extension, you can determine when to vacuum a table by viewing the statistics that show you how many "dead" rows are in the table.

  • pg_buffercache - provides a means for examining what's happening in the shared buffer cache in real time.

    Each PostgreSQL server uses a certain number of buffers. The number of buffers is determined by the shared_buffers parameter, which you can configure, and the buffer block size parameter, which is not customer configurable. For example, if a server had 128MB of shared_buffers and the size of each block was 8 KB, then there would be 16,384 buffers total in the system. With this extension, you can see what tables/relations are cached on the server. Data cached on the server allows queries or other actions to be performed faster because the data is cached in memory and doesn’t need to be loaded from the disk.

  • ip4r- Provides data types for IP addresses. These can be used as a more flexible, indexable version of the cidr type. This extension adds 6 data types.   (Supported for version 9.4.5 and later)

    • ip4   - a single IPv4 address

    • ip4r  - an arbitrary range of IPv4 addresses

    • ip6   - a single IPv6 address 

    • ip6r  - an arbitrary range of IPv6 addresses

    • ipaddress - a single IPv4 or IPv6 address

    • iprange - an arbitrary range of IPv4 or IPv6 addresses

Version 9.4.5 also includes three new parameters. These include:

  • rds.force_admin_logging_level - Logs actions by the RDS internal user (rds_admin) in the databases on the DB instance, and writes the output to the PostgreSQL error log. This parameter overrides the other logging parameters such as log_min_messages.

    Allowed values are  disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic. The default value is disabled.

  • rds.force_autovacuum_logging_level - Logs autovacuum worker operations in all databases on the DB instance, and writes the output to the PostgreSQL error log.

    Allowed values are  disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic. The default value is disabled.

    The Amazon RDS recommended setting for rds.force_autovacuum_logging_level: is LOG. Set log_autovacuum_min_duration to a value from 1000 or 5000. Setting this value to 5000 will write activity to the log that takes more than 5 seconds and will show "vacuum skipped" messages.

  • rds.rds_superuser_reserved_connections - Allows rds_superuser to have reserved connections, just like a PostgreSQL superuser account.

    The parameter value is taken out of the pool size given by max_connections, so the number of connections available to non-superusers on RDS is  actually max_connections - superuser_reserved_connections - rds.rds_superuser_reserved_connections. So max_connections must be greater than the sum of superuser_reserved_connections plus rds.rds_superuser_reserved_connections.

    Allowed values can range from 0 to 8388607. The default value is 0.

For more information about PostgreSQL version 9.4.5, see 2015-10-08 Security Update Release.

PostgreSQL Version 9.4.4 on Amazon RDS

PostgreSQL version 9.4.4 contains fixes from previous releases as well as fixes to those previous releases. All PostgreSQL update releases are cumulative, and version 9.4.4 fixes a number of problems inadvertently introduced by fixes in earlier versions. We strongly urge users to upgrade to this version, rather than installing less recent versions that have known issues. Version 9.4.4 closes multiple known bugs with multixact handling, and the PostgreSQL Project does not anticipate additional update releases soon. For more information about PostgreSQL version 9.4.4, see PostgreSQL 9.4.4, 9.3.9, 9.2.13, 9.1.18 and 9.0.22 Released. For information on upgrading the engine version for your PostgreSQL DB instance, see Database Version Upgrades.

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

PostgreSQL Version 9.4.1 on Amazon RDS

PostgreSQL version 9.4.1 contains multiple security updates, including several patches to buffer overruns. Version 9.4.1 also includes a change in the way Unicode strings are escaped for the JSON and JSONB data types. For more information on the 9.4.1 release, see the PostgreSQL documentation and the PostgreSQL wiki.

The new PostgreSQL versions for Amazon RDS also includes the following:

  • JSONB data type - The ability to include JSON-formatted fields in PostgreSQL tables give you more flexibility when managing schemas. JSONB items are stored in a decomposed binary format that speeds query operations. For more information on using the JSONB data type with a PostgreSQL database, see the PostgreSQL documentation.

  • pg_prewarm - When a database instance is restarted, its shared buffers are empty, which means that all queries will initially have to read data direct from disk. The pg_prewarm module can be used to load relation data back into the buffers to "warm" the buffers back up again. This means that queries that would otherwise have to load parts of a table in bit by bit can have the data available in shared buffers ready for use. For more information on pg_warm, see the PostgreSQL documentation.

  • PostGIS version 2.1.5

  • plv8 version 1.4.3

In PostgreSQL 9.4.1 on Amazon RDS, the fsync and full_page_writes database parameters are not modifiable. Disabling the fsync and full_page_writes database parameters can lead to data corruption, so we have enabled them for you. We recommend that customers with other 9.3 DB engine versions of PostgreSQL not disable the fsync and full_page_writes parameters.

To create a new PostgreSQL 9.4.1 DB instance, select the DB engine version "9.4.1" when you use the Launch DB Instance Wizard in the RDS console. For information on upgrading the engine version for your PostgreSQL DB instance, see Database Version Upgrades.

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 Database Version Upgrades.

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.

PostgreSQL Version 9.3.10 on Amazon RDS

PostgreSQL version 9.3.10 contains several fixes, including the following:

  • CVE-2015-5289 - Input values for JSON or JSONB data types that are constructed from arbitrary user input can crash the PostgreSQL server and cause a denial of service.

  • CVE-2015-5288 - The crypt() function included with the optional pgCrypto extension can be exploited to read a few additional bytes of memory. No working exploit for this issue has been developed.

  • PostGIS 2.1.8

Version 9.3.10 also includes two new parameters. These include:

  • rds.force_admin_logging_level - Logs actions by the RDS internal user (rds_admin) in the databases on the DB instance, and writes the output to the Postgres error log. This parameter overrides the other logging parameters such as log_min_messages.

    Allowed values are  disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic. The efault value is disabled.

  • rds.force_autovacuum_logging_level - Logs autovacuum worker operations in all databases on the DB instance, and writes the output to the Postgres error log.

    Allowed values are  disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic. The default value is disabled.

    The Amazon RDS recommended setting for rds.force_autovacuum_logging_level: is LOG. Set log_autovacuum_min_duration to a value from 1000 or 5000. Setting this value to 5000 will write activity to the log that takes more than 5 seconds and will show "vacuum skipped" messages.

For more information about PostgreSQL version 9.3.10, see 2015-10-08 Security Update Release.

PostgreSQL Version 9.3.9 on Amazon RDS

PostgreSQL version 9.3.9 contains fixes from previous releases as well as fixes to those previous releases. All PostgreSQL update releases are cumulative, and version 9.3.9 fixes a number of problems inadvertently introduced by fixes in earlier versions. We strongly urge users to upgrade to this version, rather than installing less recent versions that have known issues. Version 9.3.9 closes multiple known bugs with multixact handling, and the PostgreSQL Project does not anticipate additional update releases soon. For more information about PostgreSQL version 9.3.9, see PostgreSQL 9.4.4, 9.3.9, 9.2.13, 9.1.18 and 9.0.22 Released. For information on upgrading the engine version for your PostgreSQL DB instance, see Database Version Upgrades.

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

PostgreSQL Version 9.3.6 on Amazon RDS

PostgreSQL version 9.3.6 contains multiple security updates, including several patches to buffer overruns.

The new PostgreSQL versions for Amazon RDS also includes the following:

  • PostGIS version 2.1.5

  • plv8 version 1.4.3

For information on upgrading the engine version for your PostgreSQL DB instance, see Database Version Upgrades. To use the latest version of PostGIS and plv8, use the ALTER EXTENSION UPDATE statement to update after you upgrade to version 9.3.6.

PostgreSQL Version 9.3.5 on Amazon RDS

PostgreSQL version 9.3.5 has several important changes, including the following:

  • Adds support for Read Replicas. For more information on PostgreSQL Read Replicas, see Working with PostgreSQL, MySQL, and MariaDB Read Replicas

  • Allows the rds_superuser role to set the session_replication_role parameter. This change means that you can use open source, trigger-based replication tools such as Londiste to migrate existing PostgreSQL data to Amazon RDS with minimal downtime.

    You can also use the session_replication_role parameter to run a replica of your PostgreSQL DB instance on an on-premises server or on an EC2 instance. For example, you could install Bucardo, an open source trigger-based lazy replication solution, on a remote instance and set it as a replica to a source PostgreSQL DB instance.

    For more information about using the session_replication_role parameter, see this blog post.

  • Adds the PostGIS version 2.1.3 extension.

  • Expands access to pg_stat_statements. Users can view the performance of the queries they execute. Users granted the rds_superuser privileges can view all user queries and can reset all queries tracked by pg_stat_statements.

    You can view pg_stat_statements by setting the SHARED_PRELOAD_LIBRARIES parameter to pg_stat_statements. In previous PostgreSQL versions on Amazon RDS, changing this setting was not allowed.

    The rds_superuser role includes privileges for the following commands:

    • pg_stat_reset

    • pg_stat_statements

    • pg_stat_statements_reset

    • pg_stat_replication

    Important

    If you executed the CREATE EXTENSION pg_stat_statements; statement on your RDS Postgres instance when it was running version 9.3.3, you will need to drop and recreate the extension when you upgrade to version 9.3.5. The create extension command on version 9.3.5 will grant the correct privileges to rds_superuser.

    DROP EXTENSION pg_stat_statements;
    CREATE EXTENSION pg_stat_statements; 
  • Adds support for the PL/V8 extension, which is a PostgreSQL procedural language extension that lets you write JavaScript functions that can be called from SQL.

  • Adds support for the postgres_fdw extension, which gives you the ability to access and modify data stored on other PostgreSQL servers as if the data was in tables on your Amazon RDS PostgreSQL DB instance.

Supported PostgreSQL Features and Extensions

PostgreSQL uses extensions that allow related pieces of functionality, such as datatypes and functions, to be bundled together and installed in a database with a single command. Note that the XML data type is currently supported only in version 9.3.2 and later.

The following list shows a subset of the key PostgreSQL extensions that are currently supported by PostgreSQL on Amazon RDS. For more information on PostgreSQL extensions, see Packaging Related Objects into an Extension.

  • Data Type Extensions:

    • hstore - Provides a key/value pair store.

    • citext - Provides a case-insensitive character string type.

    • ltree - Provides a data type for representing labels of data stored in a hierarchical tree-like structure.

    • isn - Provides data types for international product numbering standards such as EAN13, UPC, ISSN, and ISBN.

    • cube - Provides a data type for representing multidimensional cubes.

    • ip4r- Provides data types for IP addresses. These can be used as a more flexible, indexable version of the cidr type. This extension adds 6 data types.   (Supported for version 9.4.5 only)

      • ip4   - a single IPv4 address

      • ip4r  - an arbitrary range of IPv4 addresses

      • ip6   - a single IPv6 address 

      • ip6r  - an arbitrary range of IPv6 addresses

      • ipaddress - a single IPv4 or IPv6 address

      • iprange - an arbitrary range of IPv4 or IPv6 addresses

                    

  • Full Text Search Dictionaries:

    • dict_int - An add-on dictionary template for full-text search often used to control the indexing of integers.

    • unaccent - A text search dictionary that removes accents (diacritic signs) from lexemes.

  • PostGIS, postgis_tiger_geocoder, and postgis_topology - Spatial and geographic objects for PostgreSQL.

  • dblink- Supports connections to other PostgreSQL databases from within a database session.

  • Misc Extensions

    • earthdistance - Calculates great circle distances on the surface of the Earth.

    • fuzzystrmatch - Determines similarities and distance between strings.

    • intarray - Provides functions and operators for manipulating null-free arrays of integers.

    • postgres_fdw - (Version 9.3.5 or later) Foreign-data wrapper that can be used to access data stored on external PostgreSQL servers.

    • pg_stat_statements - (Version 9.3.5 or later) Provides a means for tracking execution statistics of all SQL statements executed.

    • pgcrypto- Provides cryptographic functions.

    • pg_trgm - Functions that determine the similarity of alphanumeric text based on trigram matching.

    • tablefunc - Provides various functions that return tables.

    • uuid-ossp - Generates UUIDs (does requires the OSSP UUID library, which can be found at http://www.ossp.org/pkg/lib/uuid/ - MIT License).

    • btree_gin - Provides a sample GIN operator that uses B-tree-like behavior for certain data types.

    • chkpass - Provides a data type designed for storing encrypted passwords.

    • intagg - Provides an integer aggregator and enumerator. This module is now obsolete but still provides a compatible wrapper around the built-in functions that superseded it.

    • tsearch2 - Provides backwards-compatible text search functionality.

    • pgrowlocks - Provides row locking information for a specified table.

    • sslinfo - Provides information about the SSL certificate provided by the current client when it connected to PostgreSQL.

    • pgstattuple - (Version 9.4.5 only) provides various functions to obtain tuple-level statistics. pgstattuple returns a relation's physical length, percentage of "dead" tuples, and other info. This may help users to determine whether vacuuming is necessary.

      This extension lets you see certain statistics about a table, such as how much space the table is using, how many entries there are, how much space the entries are using, and several other statistics. When a row is deleted from a table in PostgreSQL, the row is not deleted from memory; the row is marked as a "dead" row in PostgreSQL. For the row to be removed from memory, a “vacuum” action must be performed on that table. With the pgstattuple extension, you can determine when to vacuum a table by viewing the statistics that show you how many "dead" rows are in the table.

    • pg_buffercache - (Version 9.4.5 only) provides a means for examining what's happening in the shared buffer cache in real time.

      Each PostgreSQL server uses a certain number of buffers. The number of buffers is determined by the shared_buffer_space parameter, which you can configure, and the buffer block size parameter, which is not customer configurable. For example, if a server had 128MB of shared_buffered space and the size of each block was 8 KB, then there would be 16,384 buffers total in the system. With this extension, you can see what tables/relations are cached on the server. Data cached on the server allows queries or other actions to be performed faster because the data is cached in memory and doesn’t need to be loaded from the disk.

  • Index Types

    • btree_gist - Provides GiST index operator classes that implement B-tree.

  • Supported PL languages include:

    • PL/pgSQL

    • PL/Tcl

    • PL/Perl

    • PL/V8 (Version 9.3.5 and later)

The current list of extensions supported by Amazon RDS can be found in the default DB parameter group for PostgreSQL, called "default.postgres9.3." You can see the current extensions list using psql by showing the rds.extensions parameter like in the following example:

SHOW rds.extensions; 

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.

Database Version Upgrades

Amazon RDS supports major and minor version upgrades for PostgreSQL DB instances.

You can initiate a major version upgrade manually by modifying your instance. However, there are recommended steps to follow when performing a major version upgrade. For details, see Major Version Upgrades.

You can initiate a minor version upgrade manually by modifying your instance, or select the Auto Minor Version Upgrade option when creating or modifying a DB instance to have your instance automatically upgraded once the new version is tested and approved by Amazon RDS.

If you set the Auto Minor Version Upgrade option to true, your DB Instance will automatically be upgraded to new PostgreSQL minor versions as they are supported by Amazon RDS. This patching will occur during your scheduled maintenance window, and it will be announced on the Amazon RDS Community Forum in advance.

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.

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 public key 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 public key, 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:


$ psql postgres -h SOMEHOST.amazonaws.com -p 8192 -U someuser
psql (9.3.1, 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:

Password for user master: 
psql (9.3.1) 
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.


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:


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:


$ 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
$