Connect to your Aurora PostgreSQL DB cluster

To interact with the Aurora database cluster, you will use an Amazon EC2 Linux instance that acts like a workstation to interact with the AWS resources in the labs on this website. All necessary software packages and scripts have been installed and configured on this EC2 instance for you. To ensure a unified experience, you will be interacting with this workstation using AWS Systems Manager Session Manager. With Session Manager you can interact with your workstation directly from the management console, without the need to install any software on your own devices.

Note

At this time, you have completed the Aurora MySQL portion of this workshop. you may close the command line windows and tabs used for those labs, if you like, and to eliminate any confusion. The following Aurora PostgreSQL labs use a different set of resources, in order to provide a consistent experience.

This lab contains the following tasks:

  1. Get the parameters from the Team Dashboard
  2. Retrieve database credentials from AWS Secret Manager
  3. Connecting to the EC2 bastion instance with Systems Manager
  4. Connect to the DB cluster

This lab requires the following prerequisites:

1. Get the parameters from the Team Dashboard

At the end of the getting started step, you have reviewed the parameters for your lab environment. For the following Aurora PostgreSQL labs, you will need the following values for lab resources:

Resource name Value
Cluster Parameter Group Refer to: [postgresClusterParamGroup] on the Team Dashboard
Database Parameter Group Refer to: [postgresNodeParamsGroup] on the Team Dashboard
Cluster Endpoint Refer to: [postgresClusterEndpoint] on the Team Dashboard
Reader Endpoint Refer to: [postgresReaderEndpoint] on the Team Dashboard
Secret Name Refer to: [secretArn] on the Team Dashboard
DB name mylab
DB username masteruser
DB password See below for steps how to retrieve

2. Retrieve database credentials from AWS Secrets Manager

Open the AWS Secrets Manager service console, and search for the secret name shown in the output of the stack (normally there is only one secret listed in the correct region). Click on the name of that secret.

Listing of Secrets

On the secret's detail page, click on the Retrieve secret value button. Note down the username and password, you will need them in later labs.

Secret Details

Secret Credentials

3. Connect to the EC2 workstation using AWS Systems Manager

Open the Systems Manager: Session Manager service console and click Start session.

SSM Listing

Select the instance named auroralab-postgres-bastion, then click Start session.

SSM Select Instance

By default Session Manager connects using the login ssm-user. You need to switch to ec2-user using the following command:

sudo su -l ec2-user

You can verify that you are using the correct ec2-user account by using the following command:

whoami

SSM Terminal

Next, update the $PATH environment variable with the location of the PostgreSQL client tools:

export PATH=/home/ec2-user/postgresql-10.7/src/bin/:/home/ec2-user/postgresql-10.7/src/bin/pgbench:$PATH

4. Connect to the DB cluster

Connect to the Aurora database just like you would to any other PostgreSQL database, using a compatible client tool. In this lab you will be using the psql command line tool to connect.

Run the command below, replacing the [postgresClusterEndpoint] placeholder with the cluster endpoint of your DB cluster. You will find the value for the cluster endpoint parameter on the Team Dashboard web page.

psql -h [postgresClusterEndpoint] -p 5432 -U masteruser -d mylab

Once connected you can issue SQL commands to the database. List the available databases using:

\l

You should see a result similar to the example below:

                                     List of databases
   Name    |   Owner    | Encoding |   Collate   |    Ctype    |     Access privileges
-----------+------------+----------+-------------+-------------+---------------------------
 mylab     | masteruser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | masteruser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin              +
           |            |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | masteruser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/masteruser            +
           |            |          |             |             | masteruser=CTc/masteruser
(5 rows)

Quit out of the database client using the following command:

\q