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.
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:
- Get the parameters from the Team Dashboard
- Retrieve database credentials from AWS Secret Manager
- Connecting to the EC2 bastion instance with Systems Manager
- 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:
|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 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.
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.
3. Connect to the EC2 workstation using AWS Systems Manager¶
Open the Systems Manager: Session Manager service console and click Start session.
Select the instance named
auroralab-postgres-bastion, then click Start session.
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:
Next, update the
$PATH environment variable with the location of the PostgreSQL client tools:
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:
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: