Configure the Cloud9 workstation

Connect to the Cloud9 Terminal

We recommend using AWS Cloud9 to connect to the Aurora PostgreSQL cluster for these labs without the need to open inbound ports, maintain bastion hosts, or manage SSH keys.

Open the Cloud Formation console and click on the stack with the description Amazon Aurora PostgreSQL Labs Stackset.


Within stack proceed to Outputs tab and look for Cloud9URL Key and click on the https address in “Value” column.


It will take few seconds for the Cloud9 IDE to open. Once the Cloud9 IDE is open, close some of the default windows as shown below by clicking on the X icons (follow red arrows).


Now, click the Window menu and select New Terminal.


Configure the lab environment

In the new terminal, paste and run the following commands.

sudo yum install -y jq
sudo yum install -y postgresql96-contrib postgresql96 postgresql96-devel sysbench

export AWSREGION=`aws ec2 describe-availability-zones --output text --query 'AvailabilityZones[0].[RegionName]'`

export DBENDP=`aws rds describe-db-clusters --db-cluster-identifier aupg-labs-cluster --region $AWSREGION --query 'DBClusters[*].Endpoint' | jq -r '.[0]'`

# This assumes a "Name" tag was added to your secret with value aupg-labs-DBMasterUser-secret
SECRETARN=`aws secretsmanager list-secrets --filters Key="tag-value",Values="aupg-labs-DBMasterUser-secret" --query 'SecretList[*].ARN' | jq -r '.[0]'`

# If below command doesnt show you the secret ARN, you should manually set the SECRETARN variable by referring it from the AWS Secrets manager console

CREDS=`aws secretsmanager get-secret-value --secret-id $SECRETARN --region $AWSREGION | jq -r '.SecretString'`

export DBUSER="`echo $CREDS | jq -r '.username'`"
export DBPASS="`echo $CREDS | jq -r '.password'`"


Confirm that you have output for the DBENDP and DBUSER variables. If not, you may not have named your Aurora cluster aupg-labs-cluster or tagged your Secret with “Key:Name and Value: aupg-labs-DBMasterUser-secret”. In that case, please set the DBENDP, DBUSER, and DBPASS variables manually in your terminal window before continuing.

Now run these commands to save these variables to your local environment configuration file

export PGDATABASE=mylab

echo "export DBPASS=\"$DBPASS\"" >> /home/ec2-user/.bashrc
echo "export DBUSER=$DBUSER" >> /home/ec2-user/.bashrc
echo "export DBENDP=$DBENDP" >> /home/ec2-user/.bashrc
echo "export AWSREGION=$AWSREGION" >> /home/ec2-user/.bashrc
echo "export PGUSER=$DBUSER" >> /home/ec2-user/.bashrc
echo "export PGPASSWORD=\"$DBPASS\"" >> /home/ec2-user/.bashrc
echo "export PGHOST=$DBENDP" >> /home/ec2-user/.bashrc
echo "export PGDATABASE=mylab" >> /home/ec2-user/.bashrc

Now, you have saved the PostgreSQL specific environment variables in your Cloud9 Bash startup file which will make it convenient to login to Aurora PostgreSQL Cluster using psql. This will be handy in the subsequent labs hosted in this repo.

Run the following commands in your Cloud9 terminal window to stage some scripts and install python packages used in subsequent labs.

curl > /home/ec2-user/
chmod u+x  /home/ec2-user/

curl > /home/ec2-user/clone_setup.sql
chmod u+x /home/ec2-user/clone_setup.sql

curl > /home/ec2-user/
chmod u+x /home/ec2-user/

sudo pip install --upgrade pip
sudo cp /usr/local/bin/pip /usr/bin/ && sudo cp /usr/local/bin/pip3* /usr/bin/
sudo pip install awscli --upgrade --user
sudo pip install boto3
sudo pip install aws_encryption_sdk
sudo pip install pycrypto
sudo pip install psycopg2