|« PreviousNext »|
|Did this page help you? Yes | No | Tell us about it...|
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
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
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.
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:
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.
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:
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.
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.
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
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.
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:
Download the public key stored at https://rds.amazonaws.com/doc/rds-ssl-ca-cert.pem.
Import the certificate into your operating system.
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
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)