Amazon Relational Database Service
User Guide (API Version 2014-09-01)
« 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

Amazon RDS supports DB instances running several versions of PostgreSQL. Currently we support PostgreSQL versions 9.3.1, 9.3.2, and 9.3.3. 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).

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

Common Management Tasks for PostgreSQL on Amazon RDS

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 Planning Your Amazon RDS PostgreSQL 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 Provisioned IOPS Storage.

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

  • 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 you will use to access the DB instance. The security group you need to create will depend on what EC2 platform your DB instance is on, and whether you will be accessing your DB instance from an EC2 instance. For more information about the two EC2 platforms supported by Amazon RDS, EC2-VPC and EC2-Classic, see Determining Whether You are Using the EC2-VPC or EC2-Classic Platform. 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. For more information about security groups, see Amazon RDS Security Groups or the Setting Up section of this guide.

  • 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 Using Amazon RDS with Amazon Virtual Private Cloud (VPC).

  • If your DB instance is going to require specific database parameters, you should create the parameter group before you create the DB instance. For more information on parameter groups, see Working with DB Parameter Groups.

  • 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. 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 Back Up and Restore.

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

There are also several appendices with useful information about working with PostgreSQL DB instances:

Planning Your Amazon RDS PostgreSQL DB Instance

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

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

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

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. 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 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 sslmode=require to your connection string. Use the sslrootcert parameter to reference the public key, for example, sslrootcert=rds-ssl-ca-cert.pem.

  4. Instead of sslmode=require, use sslmode=verify-full to have the SSL connection verify the DB instance endpoint against the endpoint in the SSL certificate.

Note

Prior to August 5, 2014, SSL certificate verification was not available and SSL certificates for PostgreSQL DB instances did not use the DB instance endpoint as the CN for the SSL certificate for the DB instance. If you have a PostgreSQL DB instance that was created before August 5, 2014, and you want to ensure that the instance endpoint is included as the CN for the SSL certificate for that DB instance, then rename the DB instance. When you rename a DB instance, a new certificate is deployed for the DB instance and the instance is rebooted to enable the new certificate.

The SSL certificate verification sslmode=verify-full connection string parameter is not valid for connections prior to August 5, 2014.

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)