Getting started with Aurora DSQL

To create a new cluster in Aurora DSQL

1. Sign in to the AWS Management Console and open the Aurora DSQL console at https://console.aws.amazon.com/dsql.

2. Choose Create cluster. Configure any of the settings that you want, such as deletion protection or tags.

3. Choose Create cluster.

To connect to the cluster with an authentication token

1. Choose the cluster that you want to connect to. Choose Connect.

2. Copy the endpoint.

3. Use the following command to use psql to start a connection to your cluster. Replace your_cluster_endpoint with the cluster endpoint.

   PGSSLMODE=require \
    psql --dbname postgres \
   --username admin \
   --host your_cluster_endpoint

   You should see a prompt to provide a password. Generate an authentication token and use it as your password.

4. Make sure that you chose Connect as admin.

5. Copy the generated authentication token and paste it into the prompt to connect to Aurora DSQL from your SQL client.

6. Press Enter. You should see a PostgreSQL prompt.

   postgres=>

   If you get an access denied error, make sure that your IAM identity has the dsql:DbConnectAdmin permission. If you have the permission and continue to get access deny errors, see Troubleshoot IAM and How can I troubleshoot access denied or unauthorized operation errors with an IAM policy?.

1. Start by creating a schema named example.

   CREATE SCHEMA example;

2. Create an invoice table that uses an automatically generated UUID as the primary key.

   CREATE TABLE example.invoice(id UUID PRIMARY KEY DEFAULT gen_random_uuid(), created timestamp, purchaser int, amount float);

3. Create a secondary index that uses the empty table.

   CREATE INDEX invoice_created_idx on example.invoice(created);

4. Create a department table.

   CREATE TABLE example.department(id INT PRIMARY KEY UNIQUE, name text, email text);

5. Use psql \copy to load in some data. Download the data files named department-insert-multirow.sql and invoice.csv from the aws-samples/aurora-dsql-samples repository on GitHub.

6. Use the command psql \include to load the files. These operations create tables and insert sample data.

   \include samples/department-insert-multirow.sql

   \copy example.invoice(created, purchaser, amount) from samples/invoice.csv csv

7. You can then list departments that are sorted by their total sales.

   SELECT name, sum(amount) AS sum_amount
   FROM example.department LEFT JOIN example.invoice ON department.id=invoice.purchaser
   GROUP BY name
   HAVING sum(amount) > 0
   ORDER BY sum_amount DESC;

   Example output:

              name           |     sum_amount     
   --------------------------+--------------------
    Example Department Three |  54061.67752854594
    Example Department Seven |  53869.65965365204
    Example Department Eight |  52199.73742066634
    Example Department One   | 52034.078869900826
    Example Department Six   |  50886.15556256385
    Example Department Two   |  50589.98422247931
    Example Department Five  | 49549.852635496005
    Example Department Four  |  49266.15578027619
   (8 rows)

To create a new cluster and connect in multiple Regions

1. From the Aurora DSQL Clusters page, choose Create cluster.

2. Choose Add linked Regions and choose a Region for your linked cluster Region. The linked cluster Region is a separate Region to create another cluster in. Aurora DSQL replicates all writes to this cluster as well, so you can read and write from any linked cluster.

3. Choose a witness Region. The witness Region receives all data that is written to linked clusters, but you can't write to it. The witness Region stores a limited window of encrypted transaction logs. Aurora DSQL uses these capabilities to provide multi-Region durability and availability.

   Note

   Witness Regions don't host client endpoints and don't provide user data access. A limited window of the encrypted transaction log is maintained in witness Regions. This facilitates recovery and supports transactional quorum in the event of Region unavailability. During preview, you can only choose us-west-2 as the witness Region.

4. Choose Create.

5. While Aurora DSQL is creating your cluster, open two instances of AWS CloudShell in different Regions. Make sure that you have the preview toolkit in both environments. Open one in the environment in us-east-1 and another one in us-east-2.

   Note

   During preview, creating linked clusters takes additional time.

6. Connect to your cluster in us-east-2.

   export PGSSLMODE=require \
    psql --dbname postgres \
   --username admin \
   --host replace_with_your_cluster_endpoint_in_us-east-2

To write in one Region and read from a second Region

1. In your us-east-2 CloudShell environment, go through the steps in Run SQL commands in Aurora DSQL.

   Example transactions

   CREATE SCHEMA example;
   CREATE TABLE example.invoice(id UUID PRIMARY KEY DEFAULT gen_random_uuid(), created timestamp, purchaser int, amount float);
   CREATE INDEX invoice_created_idx on example.invoice(created);
   CREATE TABLE example.department(id INT PRIMARY KEY UNIQUE, name text, email text);
   

2. Use PSQL meta commands to load data. For more information, see Run SQL commands in Aurora DSQL.

   \copy example.invoice(created, purchaser, amount) from samples/invoice.csv csv
   \include samples/department-insert-multirow.sql
   

3. In your us-east-1 CloudShell environment, query the data that you inserted from a different Region:

   SELECT name, sum(amount) AS sum_amount
   FROM example.department 
   LEFT JOIN example.invoice ON department.id=invoice.purchaser 
   GROUP BY name 
   HAVING sum(amount) > 0 
   ORDER BY sum_amount DESC;