Amazon Relational Database Service
User Guide (API Version 2013-09-09)
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.Did this page help you?  Yes | No |  Tell us about it...

PostgreSQL on Amazon RDS

Things You Should Know About PostgreSQL on Amazon RDS

Amazon RDS supports DB instances running PostgreSQL versions 9.3.1 and 9.3.2. You can create DB instances and DB snapshots, point-in-time restores and backups. DB instances running PostgreSQL support Multi-AZ deployments, Provisioned IOPS, and can be created inside a VPC. You can also use SSL to connect to a DB instance running PostgreSQL.

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

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

  • Manage tablespaces, including creating and deleting them

  • 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

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.

You can modify parameters for your PostgreSQL DB instance by editing values in the instance's associated DB parameter group. For more information about setting parameters and other DBA tasks for PostgreSQL, see Appendix: Common DBA Tasks for PostgreSQL

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

DB Engine Features

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.

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.

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

    • 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 UUID's (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.

  • Index Types

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

  • Supported PL languages include:

    • PL/pgSQL

    • PL/Tcl

    • PL/Perl

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.

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 3072 GB for all instances.

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

Minor Version Upgrades

With Amazon RDS, you can control when to upgrade your PostgreSQL instance to new versions supported by Amazon RDS. You can maintain compatibility with specific PostgreSQL versions, test new versions with your application before deploying in production, and perform version upgrades on your own terms and timelines.

Unless you specify otherwise, 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. To turn off automatic version upgrades, set the AutoMinorVersionUpgrade parameter for your DB instance to false.

If you opt out of automatically scheduled upgrades, you can manually upgrade to a supported minor version release by following the same procedure as you would for a major version update. For information, see Upgrading a DB Instance.

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. SSL support is available in all AWS regions for PostgreSQL.

To use a PostgreSQL DB instance over SSL, follow these general steps:

  1. Download the public key stored at https://rds.amazonaws.com/doc/rds-ssl-ca-cert.pem.

  2. Import the certificate into your operating system.

  3. Connect to your PostgreSQL DB instance over SSL by appending "encrypt=true" to your connection string. If you are connecting to a PostgreSQL DB instance in a VPC, you must also append the connection string with "TrustServerCertificate=true".

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

  
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 ssl_is_used() function to determine if SSL is being used. The function returns true (t) if the connection is using SSL, otherwise it returns false (f).

postgres=> create extension sslinfo;
CREATE EXTENSION

postgres=> select ssl_is_used();
 ssl_is_used
-------------
 t
(1 row)
                

If the SSL parameter is set to true (the default) in the associated parameter group, you can also show the parameter value using the following command:

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