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

Creating a PostgreSQL DB Instance and Connecting to a Database on a PostgreSQL DB Instance

The easiest way to create a DB instance is to use the RDS console. Once you have created the DB instance, you can use standard SQL client utilities to connect to the DB instance such as the pgAdmin utility. In this example, you create a DB instance running the PostgreSQL database engine called west2-postgres1, with a db.m1.small DB instance class, 10 GB of storage, and automated backups enabled with a retention period of one day.

Important

You must complete the tasks in the Setting Up for Amazon RDS section before you can create or connect to a DB instance.

Creating a PostgreSQL DB Instance

To create a DB Instance Running the PostgreSQL DB Engine

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the top right corner of the AWS Management Console, choose the region in which you want to create the DB instance.

  3. In the navigation pane, choose Instances.

  4. Choose Launch DB Instance to start the Launch DB Instance Wizard.

    The wizard opens on the Select Engine page.

    Engine selection

  5. On the Select Engine page, choose the PostgreSQL icon, and then choose Select.

  6. Next, the Production? page asks if you are planning to use the DB instance you are creating for production. If you are, choose PostgreSQL under Production. If you choose this option, the failover option Multi-AZ and the Provisioned IOPS storage option will be preselected in the following step. Choose Next Step when you are finished.

  7. On the Specify DB Details page, specify your DB instance information. Choose Next Step when you are finished.

    For This ParameterDo This

    License Model

    PostgreSQL has only one license model. Choose postgresql-license to use the general license agreement for PostgreSQL.

    DB Engine Version

    Choose the version of PostgreSQL you want to use.

    DB Instance Class

    Choose db.t2.small for a configuration that equates to 2 GB memory, 1 ECU (1 virtual core with 1 ECU), 64-bit platform, and moderate I/O capacity. For more information about all the DB instance class options, see DB Instance Class.

    Multi-AZ Deployment

    Choose Yes to have a standby replica of your DB instance created in another Availability Zone for failover support. We recommend Multi-AZ for production workloads to maintain high availability. For development and testing, you can choose No.

    For more information, see High Availability (Multi-AZ).

    Storage Type

    Choose the storage type General Purpose (SSD). For more information about storage, see Storage for Amazon RDS.

    Allocated Storage

    Type 5 to allocate 5 GB of storage for your database. In some cases, allocating a higher amount of storage for your DB instance than the size of your database can improve I/O performance. For more information about storage allocation, see Amazon Relational Database Service Features.

    DB Instance Identifier

    Type a name for the DB instance that is unique for your account in the region you chose. You can add some intelligence to the name, such as including the region and DB engine you chose, for example postgreSQL-test.

    Master Username

    Type a name using alphanumeric characters that you will use as the master user name to log on to your DB instance. For information on the default privileges granted to the master user name, see Amazon RDS PostgreSQL Planning Information

    Master Password and Confirm Password

    Type a password that contains from 8 to 128 printable ASCII characters (excluding /,", and @) for your master password, then type the password again in the Confirm Password box.

    DB instance details

  8. On the Configure Advanced Settings page, provide additional information that RDS needs to launch the PostgreSQL DB instance. The table shows settings for an example DB instance. Specify your DB instance information, then choose Launch DB Instance.

    For This ParameterDo This

    VPC

    This setting depends on the platform you are on. If you are a new customer to AWS, choose the default VPC shown. If you are creating a DB instance on the previous E2-Classic platform that does not use a VPC, choose Not in VPC. For more information about VPC, see Amazon RDS and Amazon Virtual Private Cloud (VPC).

    Subnet Group

    This setting depends on the platform you are on. If you are a new customer to AWS, choose default, which will be the default DB subnet group that was created for your account. If you are creating a DB instance on the previous E2-Classic platform and you want your DB instance in a specific VPC, choose the DB subnet group you created for that VPC. For more information about VPC, see Amazon RDS and Amazon Virtual Private Cloud (VPC).

    Publicly Accessible

    Choose Yes to give the DB instance a public IP address, meaning that it will be accessible outside the VPC; otherwise, choose No, so the DB instance will only be accessible from inside the VPC. For more information about hiding DB instances from public access, see Hiding a DB Instance in a VPC from the Internet.

    Availability Zone

    Use the default value of No Preference unless you want to specify an Availability Zone.

    VPC Security Group

    If you are a new customer to AWS, choose the default VPC. If you created a VPC security group, choose the VPC security group you previously created.

    Database Name

    Type a name for your database of up to 63 alpha-numeric characters. If you do not provide a name, the default "postgres" database is created.

    Database Port

    Specify a port you want to use to access the database. PostgreSQL installations default to port 5432.

    DB Parameter Group

    Use the default value unless you have created your own parameter group.

    Option Group

    Use the default value unless you have created your own option group.

    Copy Tags To Snapshots

    Choose this option to have any DB instance tags copied to a DB snapshot when you create a snapshot. For more information, see Tagging Amazon RDS Resources.

    Enable Encryption

    Choose Yes to enable encryption at rest for this DB instance. For more information, see Encrypting Amazon RDS Resources.

    Backup Retention Period

    Set the number of days you want automatic backups of your database to be retained. For testing purposes, you can set this value to 1.

    Backup Window

    Unless you have a specific time that you want to have your database backup, use the default of No Preference.

    Enable Enhanced Monitoring

    Choose Yes to enable real-time OS monitoring. Amazon RDS provides metrics in real time for the operating system (OS) that your DB instance runs on. You are only charged for Enhanced Monitoring that exceeds the free tier provided by Amazon CloudWatch Logs.

    Monitoring Role

    Choose Default to use the default IAM role.

    Granularity

    Choose 60 to monitor the instance every minute.

    Auto Minor Version Upgrade

    Choose Yes to enable your DB instance to receive minor DB engine version upgrades automatically when they become available.

    Maintenance Window

    Choose the 30 minute window in which pending modifications to your DB instance are applied. If the time period doesn't matter, choose No Preference.

    Additional Configuration panel

  9. On the final page of the wizard, choose View Your DB Instances.

  10. On the Amazon RDS console, the new DB instance appears in the list of DB instances. The DB instance will have a status of creating until the DB instance is created and ready for use. When the state changes to available, you can connect to the DB instance. Depending on the DB instance class and store allocated, it could take several minutes for the new instance to be available.

    My DB instances list

Connecting to a PostgreSQL DB Instance

After Amazon RDS provisions your DB instance, you can use any standard SQL client application to connect to the instance. It is important to note that the security group you assigned to the DB instance when you created it must allow access to the DB instance. If you have difficulty connecting to the DB instance, the problem is most often with the access rules you set up in the security group you assigned to the DB instance.

This section shows two ways to connect to a PostgreSQL DB instance. The first example uses pgAdmin, a popular Open Source administration and development tool for PostgreSQL. You can download and use pgAdmin without having a local instance of PostgreSQL on your client computer. The second example uses psql, a command line utility that is part of a PostgreSQL installation. To use psql, you must have a PostgreSQL installed on your client computer or have installed the psql client on your machine.

In this example, you connect to a PostgreSQL DB instance using pgAdmin.

Using pgAdmin to Connect to a PostgreSQL DB Instance

To connect to a PostgreSQL DB instance using pgAdmin

  1. Launch the pgAdmin application on your client computer. You can install pgAdmin from http://www.pgadmin.org/.

  2. Choose Add Server from the File menu.

  3. In the New Server Registration dialog box, enter the DB instance endpoint (for example, mypostgresql.c6c8dntfzzhgv0.us-west-2.rds.amazonaws.com) in the Host box. Do not include the colon or port number as shown on the Amazon RDS console (mypostgresql.c6c8dntfzzhgv0.us-west-2.rds.amazonaws.com:5432).

    Enter the port you assigned to the DB instance into the Port box. Enter the user name and user password you entered when you created the DB instance into the Username and Password boxes, respectively.

    Postgres connect

  4. Choose OK.

  5. In the Object browser, expand the Server Groups. Choose the Server (the DB instance) you created, and then choose the database name.

    Postgres connect

  6. Choose the plugin icon and choose PSQL Console. The psql command window opens for the default database you created.

    Postgres connect

  7. Use the command window to enter SQL or psql commands. Type \q to close the window.

Using psql to Connect to a PostgreSQL DB Instance

If your client computer has PostgreSQL installed, you can use a local instance of psql to connect to a PostgreSQL DB instance. To connect to your PostgreSQL DB instance using psql, you need to provide host information and access credentials.

The following format is used to connect to a PostgreSQL DB instance on Amazon RDS:

psql --host=<DB instance endpoint> --port=<port> --username=<master user name> --password --dbname=<database name> 

For example, the following command connects to a database called mypgdb on a PostgreSQL DB instance called mypostgresql using fictitious credentials:

psql --host=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=5432 --username=awsuser --password --dbname=mypgdb 

Troubleshooting Connection Issues

By far the most common problem that occurs when attempting to connect to a database on a DB instance is the access rules in the security group assigned to the DB instance. If you used the default DB security group when you created the DB instance, chances are good that the security group did not have the rules that will allow you to access the instance. For more information about Amazon RDS security groups, see Amazon RDS Security Groups

The most common error is could not connect to server: Connection timed out. If you receive this error, check that the host name is the DB instance endpoint and that the port number is correct. Check that the security group assigned to the DB instance has the necessary rules to allow access through any firewall your connection may be going through.

Deleting a DB Instance

Once you have connected to the sample DB instance that you created, you should delete the DB instance so you are no longer charged for it.

To delete a DB instance with no final DB snapshot

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the Instances list, choose the DB instance you wish to delete.

  3. Choose Instance Actions, and then choose Delete from the dropdown menu.

  4. Choose No in the Create final Snapshot? drop-down list box.

  5. Choose Yes, Delete.