连接Postgres RDS数据库

本节我们在Cloud 9下连接到RDS

先提前在默认VPC下创建一个Cloud 9，并进入控制台环境。

在Cloud 9下安装postgres客户端及后面实验要用到的工具：

sudo amazon-linux-extras install -y postgresql14
sudo yum install -y postgresql-contrib sysbench jq bzip2-devel xz-devel

将DBUSER，DBENDP，DBPASS保存到环境变量：

AWSREGION=`curl -s http://169.254.169.254/latest/meta-data/placement/region`

RDSPGLABS=`aws rds describe-db-instances \
    --db-instance-identifier rds-pg-labs \
    --region $AWSREGION | jq -r '.DBInstances[0]'`

read DBENDP DBPORT < <(echo $(echo $RDSPGLABS | jq -r '.Endpoint.Address, .Endpoint.Port'))


# This assumes you named the secret to be secretPostgresqlMasterUser
# There may be more than one secret that has a name like secretPostgresqlMasterUser, 
# so pick the most recently created one
# If error, you should manually set the SECRETARN variable
SECRETARN=`aws secretsmanager list-secrets \
    --query 'sort_by(SecretList[?contains(Name, \`secretPostgresMasterUser\`) == \`true\`],&CreatedDate)[-1].ARN' \
    --output text`

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'`"


echo DBENDP: $DBENDP
echo DBUSER: $DBUSER

image-20231103215616991

将PGHOST，PGUSER，PGPASSWORD保存到.bashrc:

export PGHOST=$DBENDP
export PGUSER=$DBUSER
export PGPASSWORD="$DBPASS"

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 DBPORT=$DBPORT" >> /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

image-20231103215736599

这样在连接Postgres时，不再需要手动输入数据库集群DNS地址、数据库用户名和密码，直接在控制台执行：

psql pglab  # postgres客户端会自动从环境变量中找到PGHOST，PGUSER，PGPASSWORD。 pglab代表连接的postgres数据库名

# 连接成功后，执行：
select current_user, current_database();

成功获取到当前用户名和当前数据库名：

image-20231103215917276