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 and MySQL 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 Using Amazon RDS with Amazon Virtual Private Cloud (VPC).
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
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_superuserrole using the
pg_stat_activitycommand and kill their connections using the
Grant and revoke the replication attribute onto all roles that are not the
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 PostgreSQL version 9.4.4 as well as versions 9.4.1, 9.3.1, 9.3.2, 9.3.3, 9.3.5, 9.3.6, and 9.3.9.
PostgreSQL Version 9.4.4
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!.
This release includes updates from previous versions, including the following:
Security fixes added in version 9.4.2. For more information about fixes in version 9.4.2, see PostgreSQL 9.4.2, 9.3.7, 9.2.11, 9.1.16, and 9.0.20 released!.
Data corruption fixes for "multixact wraparound" added in version 9.4.2 (that were subsequently fixed in version 9.4.4).
File permissions fix added in version 9.4.3. For more information about fixes in version 9.4.3, see PostgreSQL 9.4.3, 9.3.8, 9.2.12, 9.1.17 and 9.0.21 Released!.
PostgreSQL Version 9.4.1
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
parameters are not modifiable. Disabling the
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
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. Amazon RDS does not currently support an in-place upgrade from a PostgreSQL 9.3.x DB instance to a PostgreSQL 9.4.1 DB instance. However, you can migrate a database from a PostgreSQL 9.3.5 DB instance to a PostgreSQL 9.4.1 DB instance by using the following steps:
Create a new PostgreSQL 9.4.1 DB instance
Create a backup of your existing PostgreSQL 9.3.5 database using pg_dump
Import the dump file into your PostgreSQL 9.4.1 DB instance using pg_restore
Be sure to test your application against the new version of PostgreSQL before going into production.
PostgreSQL Version 9.3.9
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!.
This release includes updates from previous versions, including the following:
Security fixes added in version 9.3.7. For more information about fixes in version 9.3.7, see PostgreSQL 9.4.2, 9.3.7, 9.2.11, 9.1.16, and 9.0.20 released!.
Data corruption fixes for "multixact wraparound" added in version 9.3.7 (that were subsequently fixed in version 9.3.9).
File permissions fix added in version 9.3.8. For more information about fixes in version 9.3.8, see PostgreSQL 9.4.3, 9.3.8, 9.2.12, 9.1.17 and 9.0.21 Released!.
PostgreSQL Version 9.3.6
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
To upgrade your DB instance to version 9.3.6, modify the DB instance using the RDS console, RDS CLI, or RDS API, and select version 9.3.6 as the new DB engine version. 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
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 and MySQL Read Replicas
rds_superuserrole to set the
session_replication_roleparameter. 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_roleparameter 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_roleparameter, 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_superuserprivileges can view all user queries and can reset all queries tracked by
You can view
pg_stat_statementsby setting the SHARED_PRELOAD_LIBRARIES parameter to
pg_stat_statements. In previous PostgreSQL versions on Amazon RDS, changing this setting was not allowed.
rds_superuserrole includes privileges for the following commands:
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
DROP EXTENSION pg_stat_statements; CREATE EXTENSION pg_stat_statements;
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.
Database 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:
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.
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.
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.
btree_gist - Provides GiST index operator classes that implement B-tree.
Supported PL languages include:
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:
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 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
parameter for your DB instance to
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.
Amazon RDS will rotate all SSL certificates for DB instances on March 23, 2015 but will not initiate a reboot of the instance. If you use SSL to connect to an Amazon RDS DB instance, you must follow the steps in the topic SSL Certificate Rotation to apply a new SSL certificate to your DB instance before March 23, 2015 or you will not be able to connect to the DB instance using SSL.
To use a PostgreSQL DB instance over SSL, follow these general steps:
Download the public key stored at http://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem.
Import the certificate into your operating system.
Connect to your PostgreSQL DB instance over SSL by appending
sslmode=requireto your connection string. Use the
sslrootcertparameter to reference the public key, for example,
sslmode=verify-fullto have the SSL connection verify the DB instance endpoint against the endpoint in the SSL certificate.
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
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
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)