Creating and connecting to an Aurora PostgreSQL DB cluster

Sign in to the Amazon Web Services Management Console and open the Amazon EC2 console at https://console.amazonaws.cn/ec2/.

In the upper-right corner of the Amazon Web Services Management Console, choose the Amazon Web Services Region in which you want to create the EC2 instance.

Choose EC2 Dashboard, and then choose Launch instance, as shown in the following image.

The Launch an instance page opens.

Choose the following settings on the Launch an instance page.

1. Under Name and tags, for Name, enter ec2-database-connect.

2. Under Application and OS Images (Amazon Machine Image), choose Amazon Linux, and then choose the Amazon Linux 2023 AMI. Keep the default selections for the other choices.

   

3. Under Instance type, choose t2.micro.

4. Under Key pair (login), choose a Key pair name to use an existing key pair. To create a new key pair for the Amazon EC2 instance, choose Create new key pair and then use the Create key pair window to create it.

   For more information about creating a new key pair, see Create a key pair in the Amazon EC2 User Guide for Linux Instances.

5. For Allow SSH traffic in Network settings, choose the source of SSH connections to the EC2 instance.

   You can choose My IP if the displayed IP address is correct for SSH connections. Otherwise, you can determine the IP address to use to connect to EC2 instances in your VPC using Secure Shell (SSH). To determine your public IP address, in a different browser window or tab, you can use the service at https://checkip.amazonaws.com. An example of an IP address is 192.0.2.1/32.

   In many cases, you might connect through an internet service provider (ISP) or from behind your firewall without a static IP address. If so, make sure to determine the range of IP addresses used by client computers.

   Warning

   If you use 0.0.0.0/0 for SSH access, you make it possible for all IP addresses to access your public EC2 instances using SSH. This approach is acceptable for a short time in a test environment, but it's unsafe for production environments. In production, authorize only a specific IP address or range of addresses to access your EC2 instances using SSH.

   The following image shows an example of the Network settings section.

   

6. Leave the default values for the remaining sections.

7. Review a summary of your EC2 instance configuration in the Summary panel, and when you're ready, choose Launch instance.

On the Launch Status page, note the identifier for your new EC2 instance, for example: i-1234567890abcdef0.

Choose the EC2 instance identifier to open the list of EC2 instances, and then select your EC2 instance.

In the Details tab, note the following values, which you need when you connect using SSH:

1. In Instance summary, note the value for Public IPv4 DNS.

   

2. In Instance details, note the value for Key pair name.

   

Wait until the Instance state for your EC2 instance has a status of Running before continuing.

Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

In the upper-right corner of the Amazon RDS console, choose the Amazon Web Services Region in which you want to create the DB cluster.

In the navigation pane, choose Databases.

Choose Create database, and make sure that Easy create is chosen.

In Configuration, choose Aurora (PostgreSQL Compatible) for Engine type.

For DB instance size, choose Dev/Test.

For DB cluster identifier, enter database-test1.

The Create database page should look similar to the following image.

For Master username, enter a name for the user, or keep the default name (postgres).

To use an automatically generated master password for the DB cluster, select Auto generate a password.

To enter your master password, make sure Auto generate a password is cleared, and then enter the same password in Master password and Confirm password.

To set up a connection with the EC2 instance you created previously, open Set up EC2 connection - optional.

Select Connect to an EC2 compute resource. Choose the EC2 instance you created previously.

Open View default settings for Easy create.

You can examine the default settings used with Easy create. The Editable after database is created column shows which options you can change after you create the database.

Choose Create database.

To view the master username and password for the DB cluster, choose View credential details.

You can use the username and password that appears to connect to the DB cluster as the master user.

In the Databases list, choose the name of the new Aurora PostgreSQL DB cluster to show its details.

The writer instance has a status of Creating until the DB cluster is ready to use.

When the status of the writer instance changes to Available, you can connect to the DB cluster. Depending on the DB instance class and the amount of storage, it can take up to 20 minutes before the new DB cluster is available.

Find the endpoint (DNS name) and port number of the writer instance for your DB cluster.

1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

2. In the upper-right corner of the Amazon RDS console, choose the Amazon Web Services Region for the DB cluster.

3. In the navigation pane, choose Databases.

4. Choose the Aurora PostgreSQL DB cluster name to display its details.

5. On the Connectivity & security tab, copy the endpoint of the writer instance. Also, note the port number. You need both the endpoint and the port number to connect to the DB cluster.

   

Connect to the EC2 instance that you created earlier by following the steps in Connect to your Linux instance in the Amazon EC2 User Guide for Linux Instances.

We recommend that you connect to your EC2 instance using SSH. If the SSH client utility is installed on Windows, Linux, or Mac, you can connect to the instance using the following command format:

ssh -i location_of_pem_file ec2-user@ec2-instance-public-dns-name

For example, assume that ec2-database-connect-key-pair.pem is stored in /dir1 on Linux, and the public IPv4 DNS for your EC2 instance is ec2-12-345-678-90.compute-1.amazonaws.com. Your SSH command would look as follows:

ssh -i /dir1/ec2-database-connect-key-pair.pem ec2-user@ec2-12-345-678-90.compute-1.amazonaws.com

Get the latest bug fixes and security updates by updating the software on your EC2 instance. To do so, use the following command.

sudo dnf update -y

Install the psql command line client from PostgreSQL on Amazon Linux 2023, using the following command:

sudo dnf install postgresql15

Connect to the Aurora PostgreSQL DB cluster. For example, enter the following command. This action lets you connect to the Aurora PostgreSQL DB cluster using the psql client.

Substitute the endpoint of the writer instance for endpoint, substitute the database name --dbname that you want to connect to for postgres, and substitute the master username that you used for postgres. Provide the master password that you used when prompted for a password.

psql --host=endpoint --port=5432 --dbname=postgres --username=postgres

After you enter the password for the user, you should see output similar to the following.

psql (14.3, server 14.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> 

For more information about connecting to an Aurora PostgreSQL DB cluster, see Connecting to an Amazon Aurora PostgreSQL DB cluster. If you can't connect to your DB cluster, see Can't connect to Amazon RDS DB instance.

For security, it is a best practice to use encrypted connections. Only use an unencrypted PostgreSQL connection when the client and server are in the same VPC and the network is trusted. For information about using encrypted connections, see Securing Aurora PostgreSQL data with SSL/TLS.

Run SQL commands.

For example, the following SQL command shows the current date and time:

SELECT CURRENT_TIMESTAMP;

Sign in to the Amazon Web Services Management Console and open the Amazon EC2 console at https://console.amazonaws.cn/ec2/.

In the navigation pane, choose Instances.

Select the EC2 instance, and choose Instance state, Terminate instance.

Choose Terminate when prompted for confirmation.

Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

Choose Databases and then choose the DB instance associated with the DB cluster.

For Actions, choose Delete.

Choose Delete.

After all of the DB instances associated with a DB cluster are deleted, the DB cluster is deleted automatically.

Open the Amazon CloudFormation console.

On the Stacks page in the CloudFormation console, select the root stack (the stack without the name VPCStack, BastionStack or APGNS).

Choose Delete.

Select Delete stack when prompted for confirmation.