# Working with DB shard groups You perform the following tasks to add and manage a DB shard group for Aurora PostgreSQL Limitless Database. **Topics** + [Connecting to your Aurora PostgreSQL Limitless Database DB cluster](#limitless-endpoint) + [Finding the number of routers and shards in a DB shard group](#limitless-shard.number) + [Describing DB shard groups](#limitless-describe) + [Rebooting a DB shard group](#limitless-reboot) + [Changing the capacity of a DB shard group](limitless-capacity.md) + [Splitting a shard in a DB shard group](limitless-shard-split.md) + [Adding a router to a DB shard group](limitless-add-router.md) + [Deleting a DB shard group](limitless-shard-delete.md) + [Adding a DB shard group to an existing Aurora PostgreSQL Limitless Database DB cluster](limitless-shard-add.md) ## Connecting to your Aurora PostgreSQL Limitless Database DB cluster To work with Aurora PostgreSQL Limitless Database, you connect to the cluster writer or reader endpoint. You can use `psql` or any other connection utility that works with PostgreSQL: ``` $ psql -h DB_cluster_endpoint -p port_number -U database_username -d postgres_limitless ``` The following example uses the endpoint for the DB cluster that you created in [CLI](limitless-create-cluster.md#limitless-create-CLI). ``` $ psql -h my-limitless-cluster.cluster-ckifpdyyyxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d postgres_limitless ``` **Note** The default database for the DB shard group in Aurora PostgreSQL Limitless Database is `postgres_limitless`. ### Using the Limitless Connection Plugin When connecting to Aurora PostgreSQL Limitless Database, clients connect using the cluster endpoint, and are routed to a transaction router by Amazon Route 53. However, Route 53 is limited in its ability to load balance, and can allow uneven workloads on transaction routers. The [Limitless Connection Plugin](https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/using-plugins/UsingTheLimitlessConnectionPlugin.md) for the [AWS JDBC Driver](https://github.com/awslabs/aws-advanced-jdbc-wrapper) addresses this by performing client-side load balancing with load awareness. For more information on the [AWS JDBC Driver](https://github.com/awslabs/aws-advanced-jdbc-wrapper), see [Connecting to Aurora PostgreSQL with the Amazon Web Services (AWS) JDBC Driver](Aurora.Connecting.md#Aurora.Connecting.JDBCDriverPostgreSQL). ## Finding the number of routers and shards in a DB shard group You can use the following query to find the number of routers and shards: ``` SELECT * FROM rds_aurora.limitless_subclusters; subcluster_id | subcluster_type ---------------+----------------- 1 | router 2 | router 3 | shard 4 | shard 5 | shard 6 | shard ``` ## Describing DB shard groups Use the `describe-db-shard-groups` AWS CLI command to describe your DB shard groups. The following parameter is optional: + `--db-shard-group-identifier` – The name of a DB shard group. The following example describes a specific DB shard group. ``` aws rds describe-db-shard-groups --db-shard-group-identifier my-db-shard-group ``` The output resembles the following example. ``` { "DBShardGroups": [ { "DBShardGroupResourceId": "shardgroup-8986d309a93c4da1b1455add17abcdef", "DBShardGroupIdentifier": "my-shard-group", "DBClusterIdentifier": "my-limitless-cluster", "MaxACU": 1000.0, "ComputeRedundancy": 0, "Status": "available", "PubliclyAccessible": false, "Endpoint": "my-limitless-cluster.limitless-ccetp2abcdef.us-east-1.rds.amazonaws.com" } ] } ``` ## Rebooting a DB shard group Sometimes you have to reboot your DB shard group, for example when the `max_connections` parameter changes because of a maximum capacity change. You can use the AWS Management Console or AWS CLI to change the capacity of a DB shard group. ### Console Use the following procedure. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Navigate to the **Databases** page. 1. Select the DB shard group that you want to reboot. 1. For **Actions**, choose **Reboot**. 1. Choose **Confirm**. ### CLI To reboot a DB shard group, use the `reboot-db-shard-group` AWS CLI command with the following parameter: + `--db-shard-group-identifier` – The name of a DB shard group. The following example reboots a DB shard group. ``` aws rds reboot-db-shard-group --db-shard-group-identifier my-db-shard-group ``` # Changing the capacity of a DB shard group You can use the AWS Management Console or AWS CLI to change the capacity of a DB shard group. ## Console Use the following procedure. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Navigate to the **Databases** page. 1. Select the DB shard group that you want to modify. 1. For **Actions**, choose **Modify**. The **Modify DB shard group** page displays. ![\[Modify DB shard group page.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_modify_shard_group.png) 1. Enter a new **Minimum capacity (ACUs)** value, for example **100**. 1. Enter a new **Maximum capacity (ACUs)** value, for example **1000**. 1. Choose **Continue**. The confirmation page displays, with a summary of your changes. 1. Review your changes, then choose **Modify DB shard group**. ## CLI Use the `modify-db-shard-group` AWS CLI command with the following parameters: + `--db-shard-group-identifier` – The name of the DB shard group. + `--max-acu` – The new maximum capacity of the DB shard group. You can set the maximum capacity of the DB shard group to 16–6144 ACUs. For capacity limits higher than 6144 ACUs, contact AWS. The number of routers and shards doesn't change. + `--min-acu` – The new minimum capacity of your DB shard group. It must be at least 16 ACUs, which is the default value. The following CLI example changes the capacity range of a DB shard group to 100–1000 ACUs. ``` aws rds modify-db-shard-group \ --db-shard-group-identifier my-db-shard-group \ --min-acu 100 \ --max-acu 1000 ``` # Splitting a shard in a DB shard group You can split a shard in a DB shard group manually into two smaller shards. This is called a *user-initiated* shard split. Aurora PostgreSQL Limitless Database can also split shards when they have very large amounts of data or very high usage. This is called a *system-initiated* shard split. **Topics** + [Prerequisites](#limitless-shard-split.prereqs) + [Splitting a shard](#limitless-shard-split.proc) + [Tracking shard splits](#limitless-shard-split.track) + [Finalizing shard splits](#limitless-shard-split.finalize) + [Canceling a shard split](#limitless-shard-split.cancel) ## Prerequisites User-initiated shard splits have the following prerequisites: + You must have a DB shard group. + The DB shard group can't be empty: it must contain at least one sharded table. + A user must have the `rds_aurora_limitless_cluster_admin` privilege. The `rds_superuser` has this privilege; therefore the master user also has it. The `rds_superuser` can grant the privilege to other users: ``` /* Logged in as the master user or a user with rds_superuser privileges */ CREATE USER username; GRANT rds_aurora_limitless_cluster_admin to username; ``` + You must know the subcluster (node) ID of the shard that you want to split. You can obtain the ID by using the following query: ``` SELECT * FROM rds_aurora.limitless_subclusters; subcluster_id | subcluster_type ---------------+----------------- 1 | router 2 | router 3 | shard 4 | shard 5 | shard 6 | shard ``` To enable system-initiated shard splits, set the following DB cluster parameters in a custom DB cluster parameter group associated with your DB cluster: | Parameter | Value | | --- | --- | | `rds_aurora.limitless_enable_auto_scale` | `on` | | `rds_aurora.limitless_auto_scale_options` | Either `split_shard`or `add_router,split_shard` | | `rds_aurora.limitless_finalize_split_shard_mode` | This parameter determines how *system-initiated* shard splits are finalized. The value can be one of the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/limitless-shard-split.html) For more information, see [Finalizing shard splits](#limitless-shard-split.finalize). This parameter applies only to system-initiated shard splits. | For more information, see [DB cluster parameter groups for Amazon Aurora DB clusters](USER_WorkingWithDBClusterParamGroups.md). ## Splitting a shard To split a shard in a DB shard group, use the `rds_aurora.limitless_split_shard` function. This function starts a shard-split job that runs asynchronously. ``` SELECT rds_aurora.limitless_split_shard('subcluster_id'); ``` Wait for the return of a job ID upon successful submission of the job, for example: ``` SELECT rds_aurora.limitless_split_shard('3'); job_id --------------- 1691300000000 (1 row) ``` **Note** Concurrent shard split operations are not supported. Execute each operation sequentially and complete each operation before initiating another addition operation. ## Tracking shard splits You can use the job ID to track a shard-split job. To describe a particular job and get more details about it, run the following query: ``` SELECT * FROM rds_aurora.limitless_list_shard_scale_jobs(job_id); ``` For example: ``` SELECT * FROM rds_aurora.limitless_list_shard_scale_jobs(1691300000000); job_id | action | job_details | status | submission_time | message ---------------+-------------+-----------------------+---------+------------------------+------------------------------------------- 1691300000000 | SPLIT_SHARD | Split Shard 3 by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New shard instance with ID 7 was created. (1 row) ``` The query returns an error when you pass a nonexistent job as the input. ``` SELECT * from rds_aurora.limitless_list_shard_scale_jobs(1691300000001); ERROR: no job found with the job ID provided ``` You can track the status of all shard-split jobs by using the same query without a job ID, for example: ``` SELECT * FROM rds_aurora.limitless_list_shard_scale_jobs(); job_id | action | job_details | status | submission_time | message ---------------+-------------+-----------------------+-------------+------------------------+-------------------------------------------------------------- 1691200000000 | SPLIT_SHARD | Split Shard 3 by User | IN_PROGRESS | 2023-08-05 01:46:40+00 | 1691300000000 | SPLIT_SHARD | Split Shard 4 by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New shard instance with ID 7 was created. 1691400000000 | SPLIT_SHARD | Split Shard 5 by User | FAILED | 2023-08-07 09:20:00+00 | Error occurred for the add shard job 1691400000000. | | | | | Retry the command. If the issue persists, contact AWS Support. 1691500000000 | SPLIT_SHARD | Split Shard 5 by User | CANCELED | 2023-08-07 09:20:00+00 | Scaling job was cancelled. (4 rows) ``` The job status can be one of the following: + `IN_PROGRESS` – The shard-split job has been submitted and is in progress. You can have only one job in progress at a time. + `PENDING` – The shard-split job is waiting for you to finalize it. For more information, see [Finalizing shard splits](#limitless-shard-split.finalize). + `CANCELLATION_IN_PROGRESS` – The shard-split job is being canceled by the user. + `CANCELED` – The shard-split job has been successfully canceled by the user or by the system. + `SUCCESS` – The shard-split job completed successfully. The `message` field contains the instance ID of the new shard. + `FAILED` – The shard-split job failed. The `message` field contains the details of the failure and any actions that can be taken as a followup to the failed job. ## Finalizing shard splits Finalizing is the last step of the shard-split process. It causes some downtime. If you start a shard-split job, then finalizing happens immediately after the job completes successfully. Sometimes the system splits shards based on workload, when you've enabled system-initiated shard splits by using the `rds_aurora.limitless_enable_auto_scale` parameter. In this case, you can choose whether finalizing happens immediately, or at a time that you choose. You use the `rds_aurora.limitless_finalize_split_shard_mode` DB cluster parameter to choose when it happens: + If you set the value to `immediate`, it happens immediately. + If you set the value to `user_initiated`, you have to finalize the shard-split job manually. An RDS event is sent to you, and the status of the shard-split job is set to `PENDING`. When set to `user_initiated`, you use the `rds_aurora.limitless_finalize_split_shard` function to finalize the shard-split job: ``` SELECT * FROM rds_aurora.limitless_finalize_split_shard(job_id); ``` **Note** This function applies only to shard splits that are initiated by the system, not by you. ## Canceling a shard split You can cancel a user-initiated or system-initiated shard split that's `IN_PROGRESS` or `PENDING`. You need the job ID to cancel it. ``` SELECT * from rds_aurora.limitless_cancel_shard_scale_jobs(job_id); ``` No output is returned unless there's an error. You can track the cancellation using a job-tracking query. # Adding a router to a DB shard group You can add a router to a DB shard group. **Topics** + [Prerequisites](#limitless-add-router.prereqs) + [Adding a router](#limitless-add-router.proc) + [Tracking router additions](#limitless-add-router.track) + [Canceling a router addition](#limitless-add-router.cancel) ## Prerequisites Adding a router has the following prerequisites: + You must have a DB shard group. + A user must have the `rds_aurora_limitless_cluster_admin` privilege. The `rds_superuser` has this privilege; therefore the master user also has it. The `rds_superuser` can grant the privilege to other users: ``` /* Logged in as the master user or a user with rds_superuser privileges */ CREATE USER username; GRANT rds_aurora_limitless_cluster_admin to username; ``` **Note** If you change your AWS account's default CA certificate after the DB shard group is created, the new router will use the new CA certificate, which is different from the existing router's CA certificate. Depending on your trust store, some connections might fail. + To enable system-initiated router addition, set the following DB cluster parameters in a custom DB cluster parameter group associated with your DB cluster: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/limitless-add-router.html) For more information, see [DB cluster parameter groups for Amazon Aurora DB clusters](USER_WorkingWithDBClusterParamGroups.md). ## Adding a router To add a router, use the `rds_aurora.limitless_add_router` function. This function starts a router-addition job that runs asynchronously. ``` SELECT rds_aurora.limitless_add_router(); ``` Wait for the return of a job ID upon successful submission of the job, for example: ``` job_id --------------- 1691300000000 (1 row) ``` **Note** Concurrent router addition operations are not supported. Execute operations sequentially and complete each operation before initiating another addition operation. ## Tracking router additions You can use the job ID to track a router-addition job. To describe a particular job and get more details about it, run the following query: ``` SELECT * FROM rds_aurora.limitless_list_router_scale_jobs(job_id); ``` For example: ``` SELECT * FROM rds_aurora.limitless_list_router_scale_jobs(1691300000000); job_id | action | job_details | status | submission_time | message ---------------+------------+--------------------------+---------+------------------------+------------------------------------------- 1691300000000 | ADD_ROUTER | Add 1 new Router by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New router instance with ID 7 was created. (1 row) ``` The query returns an error when you pass a nonexistent job as the input. ``` SELECT * from rds_aurora.limitless_list_router_scale_jobs(1691300000001); ERROR: no job found with the job ID provided ``` You can track the status of all router-addition jobs by using the same query without a job ID, for example: ``` SELECT * FROM rds_aurora.limitless_list_router_scale_jobs(); job_id | action | job_details | status | submission_time | message ---------------+------------+--------------------------+-------------+------------------------+------------------------------------------- 1691200000000 | ADD_ROUTER | Add 1 new Router by User | IN_PROGRESS | 2023-08-05 01:46:40+00 | 1691300000000 | ADD_ROUTER | Add 1 new Router by User | SUCCESS | 2023-08-06 05:33:20+00 | Scaling job succeeded. + | | | | | New router instance with ID 7 was created. 1691400000000 | ADD_ROUTER | Add 1 new Router by User | FAILED | 2023-08-07 09:20:00+00 | Error occurred for the add router job 1691400000000. | | | | | Retry the command. If the issue persists, contact AWS Support. 1691500000000 | ADD_ROUTER | Add 1 new Router by User | CANCELED | 2023-08-07 09:20:00+00 | Scaling job was cancelled. (4 rows) ``` The job status can be one of the following: + `IN_PROGRESS` – The router-addition job has been submitted and is in progress. You can have only one job in progress at a time. + `CANCELLATION_IN_PROGRESS` – The router-addition job is being canceled by the user. + `CANCELED` – The router-addition job has been successfully canceled by the user or by the system. + `SUCCESS` – The router-addition job completed successfully. The `message` field contains the instance ID of the new router. + `FAILED` – The router-addition job failed. The `message` field contains the details of the failure and any actions that can be taken as a followup to the failed job. **Note** There's no `PENDING` status because router additions don't need to be finalized. They incur no downtime. ## Canceling a router addition You can cancel a router addition that's `IN_PROGRESS`. You need the job ID to cancel it. ``` SELECT * from rds_aurora.limitless_cancel_router_scale_jobs(job_id); ``` No output is returned unless there's an error. You can track the cancellation using a job-tracking query. # Deleting a DB shard group You can delete a DB shard group if necessary. Deleting the DB shard group deletes the compute nodes (shards and routers), but not the storage. ## Console Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Navigate to the **Databases** page. 1. Select the DB shard group that you want to delete. 1. For **Actions**, choose **Delete**. 1. Enter **delete me** in the box, then choose **Delete**. The DB shard group is deleted. ## AWS CLI To delete your DB shard group, use the `delete-db-shard-group` AWS CLI command with the following parameter: + `--db-shard-group-identifier` – The name of the DB shard group. The following example deletes a DB shard group in the Aurora PostgreSQL DB cluster that you created previously. ``` aws rds delete-db-shard-group --db-shard-group-identifier my-db-shard-group ``` # Adding a DB shard group to an existing Aurora PostgreSQL Limitless Database DB cluster You can create a DB shard group in an existing DB cluster, for example if you're restoring a DB cluster or you had deleted the DB shard group. For more information on primary DB cluster and DB shard group requirements, see [Aurora PostgreSQL Limitless Database requirements and considerationsAurora PostgreSQL Limitless Database requirements and considerations](limitless-reqs-limits.md). **Note** You can have only one DB shard group per cluster. The Limitless Database DB cluster must be in the `available` state before you can create a DB shard group. ## Console You can use the AWS Management Console to add a DB shard group to an existing DB cluster. **To add a DB shard group** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Navigate to the **Databases** page. 1. Select the Limitless Database DB cluster to which you want to add a DB shard group. 1. For **Actions**, choose **Add a DB shard group**. ![\[Add a DB shard group.\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/images/limitless_add_shard_group.png) 1. Enter a **DB shard group identifier**. **Important** After you create the DB shard group, you can't change the DB cluster identifier or the DB shard group identifier. 1. Enter the **Minimum capacity (ACUs)**. Use a value of at least 16 ACUs. 1. Enter the **Maximum capacity (ACUs)**. Use a value from 16–6144 ACUs. For more information, see [Correlating DB shard group maximum capacity with the number of routers and shards created](limitless-cluster.md#limitless-capacity-mapping). 1. For **DB shard group deployment**, choose whether to create standbys for the DB shard group: + **No compute redundancy** – Creates a DB shard group without standbys for each shard. This is the default value. + **Compute redundancy with a single failover target** – Creates a DB shard group with one compute standby in a different Availability Zone (AZ). + **Compute redundancy with two failover targets** – Creates a DB shard group with two compute standbys in two different AZs. 1. Choose whether to make the DB shard group publicly accessible. **Note** You can't modify this setting after you create the DB shard group. 1. Choose **Add a DB shard group**. ## AWS CLI Use the `create-db-shard-group` AWS CLI command to create a DB shard group. The following parameters are required: + `--db-cluster-identifier` – The DB cluster to which the DB shard group belongs. + `--db-shard-group-identifier` – The name of the DB shard group. The DB shard group identifier has the following constraints: + It must be unique in the AWS account and AWS Region where you create it. + It must contain 1–63 letters, numbers, or hyphens. + The first character must be a letter. + It can't end with a hyphen or contain two consecutive hyphens. **Important** After you create the DB shard group, you can't change the DB cluster identifier or the DB shard group identifier. + `--max-acu` – The maximum capacity of the DB shard group. Use a value from 16–6144 ACUs. The following parameters are optional: + `--compute-redundancy` – Whether to create standbys for the DB shard group. This parameter can have the following values: + `0` – Creates a DB shard group without standbys for each shard. This is the default value. + `1` – Creates a DB shard group with one compute standby in a different Availability Zone (AZ). + `2` – Creates a DB shard group with two compute standbys in two different AZs. **Note** If you set the compute redundancy to a nonzero value, the total number of nodes will be doubled or tripled. This will incur extra costs. + `--min-acu` – The minimum capacity of your DB shard group. It must be at least 16 ACUs, which is the default value. + `--publicly-accessible|--no-publicly-accessible` – Whether to assign publicly accessible IP addresses to the DB shard group. Access to the DB shard group is controlled by the security groups used by the cluster. The default is `--no-publicly-accessible`. **Note** You can't modify this setting after you create the DB shard group. The following example creates a DB shard group in an Aurora PostgreSQL DB cluster. ``` aws rds create-db-shard-group \ --db-cluster-identifier my-db-cluster \ --db-shard-group-identifier my-new-shard-group \ --max-acu 1000 ``` The output resembles the following example. ``` { "Status": "CREATING", "Endpoint": "my-db-cluster.limitless-ckifpdyyyxxx.us-east-1.rds.amazonaws.com", "PubliclyAccessible": false, "DBClusterIdentifier": "my-db-cluster", "MaxACU": 1000.0, "DBShardGroupIdentifier": "my-new-shard-group", "DBShardGroupResourceId": "shardgroup-8986d309a93c4da1b1455add17abcdef", "ComputeRedundancy": 0 } ```