# Capturing Aurora PostgreSQL execution plans in Replicas
<a name="AuroraPostgreSQL.QPM.Plancapturereplicas"></a>

QPM (Query Plan Management) allows you to capture the query plans generated by Aurora Replicas and stores them on the primary DB instance of the Aurora DB cluster. You can collect the query plans from all the Aurora Replicas, and maintain a set of optimal plans in a central persistent table on the primary instance. You can then apply these plans on other Replicas when needed. This helps you to maintain the stability of execution plans and improve performance of the queries across the DB clusters and engine versions.

**Topics**
+ [

## Prerequisites
](#AuroraPostgreSQL.QPM.Plancapturereplicas.Prereq)
+ [

## Managing plan capture for Aurora Replicas
](#AuroraPostgreSQL.QPM.Plancapturereplicas.managing)
+ [

## Troubleshooting
](#AuroraPostgreSQL.QPM.Plancapturereplicas.Troubleshooting)

## Prerequisites
<a name="AuroraPostgreSQL.QPM.Plancapturereplicas.Prereq"></a>

**Turn on `capture_plan_baselines parameter` in Aurora Replica** - Set `capture_plan_baselines` parameter to automatic or manual to capture plans in Aurora Replicas. For more information, see [apg\$1plan\$1mgmt.capture\$1plan\$1baselines](AuroraPostgreSQL.Optimize.Parameters.md#AuroraPostgreSQL.Optimize.Parameters.capture_plan_baselines).

**Install postgres\$1fdw extension** - You must install `postgres_fdw` foreign data wrapper extension to capture plans in Aurora Replicas. Run the following command in each database, to install the extension. 

```
postgres=> CREATE EXTENSION IF NOT EXISTS postgres_fdw;
```

## Managing plan capture for Aurora Replicas
<a name="AuroraPostgreSQL.QPM.Plancapturereplicas.managing"></a>

**Turn on plan capture for Aurora Replicas**  
You must have `rds_superuser` privileges to create or remove Plan Capture in Aurora Replicas. For more information on user roles and permissions, see [Understanding PostgreSQL roles and permissions](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Roles.html).

To capture plans, call the function apg\$1plan\$1mgmt.create\$1replica\$1plan\$1capture in the writer DB instance, as shown in the following:

```
postgres=> CALL apg_plan_mgmt.create_replica_plan_capture('endpoint', 'password');
```
+ endpoint - Aurora Global Database writer endpoint or cluster\$1endpoint provides failover support for Plan Capture in Aurora Replicas.

  For more information on the Aurora Global Database writer endpoint, see [Viewing the endpoints of an Amazon Aurora global database](aurora-global-database-connecting.md#viewing-endpoints).

  For more information about cluster endpoints, see [Cluster endpoints for Amazon Aurora](Aurora.Endpoints.Cluster.md).
+ password - We recommend you to follow the below guidelines while creating the password to enhance the security:
  + It must contain at least 8 characters.
  + It must contain at least one uppercase, one lowercase letter, and one number.
  + It must have at least one special character (`?`, `!`, `#`, `<`, `>`, `*`, and so on).

**Note**  
If you change the endpoint, password, or port number, you must run `apg_plan_mgmt.create_replica_plan_capture()` again with the endpoint and password to re-initialize the plan capture. If not, capturing plans from Aurora Replicas will fail.

**Turn off plan capture for Aurora Replicas**  
You can turn off `capture_plan_baselines` parameter in Aurora Replica by setting its value to `off` in the Parameter group.

**Remove plan capture for Aurora Replicas**  
You can completely remove Plan Capture in Aurora Replicas but make sure before you do. To remove plan capture, call `apg_plan_mgmt.remove_replica_plan_capture` as shown:

```
postgres=> CALL apg_plan_mgmt.remove_replica_plan_capture();
```

You must call apg\$1plan\$1mgmt.create\$1replica\$1plan\$1capture() again to turn on plan capture in Aurora Replicas with the endpoint and password.

## Troubleshooting
<a name="AuroraPostgreSQL.QPM.Plancapturereplicas.Troubleshooting"></a>

Following, you can find troubleshooting ideas and workarounds if the plan is not captured in Aurora Replicas as expected.
+ **Parameter settings** - Check if the `capture_plan_baselines` parameter is set to proper value to turn on plan capture.
+ **`postgres_fdw` extension is installed** - Use the following query to check if `postgres_fdw` is installed.

  ```
  postgres=> SELECT * FROM pg_extension WHERE extname = 'postgres_fdw'
  ```
+ **create\$1replica\$1plan\$1capture() is called** - Use the following command to check if the user mapping exits. Otherwise, call `create_replica_plan_capture()` to initialize the feature.

  ```
  postgres=> SELECT * FROM pg_foreign_server WHERE srvname = 'apg_plan_mgmt_writer_foreign_server';
  ```
+ **Endpoint and port number** - Check if the endpoint and port number are appropriate. There won't be any error message displayed if these values are incorrect. 

  Use the following command to verify if the endpoint used in the create() and to check which database it resides in:

  ```
  postgres=> SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'apg_plan_mgmt_writer_foreign_server';
  ```
+ **reload()** - You must call apg\$1plan\$1mgmt.reload() after calling apg\$1plan\$1mgmt.delete\$1plan() in Aurora Replicas to make the delete function effective. This ensures that the change has been successfully implemented.
+ **Password** - You must enter password in create\$1replica\$1plan\$1capture() as per the guidelines mentioned. Otherwise, you will receive an error message. For more information,see [Managing plan capture for Aurora Replicas](#AuroraPostgreSQL.QPM.Plancapturereplicas.managing). Use another password that aligns with the requirements.
+ **Cross-Region connection** - Plan capture in Aurora Replicas is also supported in Aurora global database, where writer instance and Aurora Replicas can be in different regions. Ensure that you use the Aurora Global Database writer endpoint to maintain connectivity after failover or switchover events. For more information on the Aurora Global Database endpoints, see [Viewing the endpoints of an Amazon Aurora Global Database](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database-connecting.html#viewing-endpoints). The writer instance and cross-Region Replica must be able to communicate, using VPC Peering. For more information, see [VPC peering](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html). If a cross-Region failover happens, you must reconfigure the endpoint to new primary DB cluster endpoint.
**Note**  
When using a cluster endpoint instead of an Aurora Global Database writer endpoint, you'll need to update the cluster endpoint after performing either a global failover or switchover operation.