How can I resolve the "ERROR: <module/extension> must be loaded via shared_preload_libraries" error?

Short Description

When using Amazon RDS for PostgreSQL, you can preload shared libraries into the server in order to load additional functionality or to achieve performance benefits. However, you can receive the following error when trying to use the module/extension: 

• "ERROR:  <module/extension> must be loaded via shared_preload_libraries"

For example, when querying pg_stat_statements, you can receive the following error:

postgres=> SELECT * FROM pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist

postgres=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION
 
postgres=> SELECT * FROM pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

In order to resolve this error and query pg_stat_statements, set the value of shared_preload_libraries to pg_stat_statements in a custom parameter group associated with your DB instance.

You can use the following modules/extensions on RDS PostgreSQL by modifying the value of the parameter shared_preload_libraries in a custom parameter group:

• auto_explain
• orafce
• pgaudit
• pglogical
• pg_hint_plan
• pg_prewarm
• pg_similarity
• pg_stat_statements
• pg_transport
• plprofiler

Note: You can modify parameter values in a custom parameter group. You can't change the parameter values in a default DB parameter group. If you make changes to parameters in a custom parameter group, then your changes are applied to all DB instances associated with that parameter group.

Resolution

Create a custom parameter group

Note: If you already have a custom parameter group, skip to the next step.

1. Login to the Amazon RDS console.
2. In the navigation pane, choose Parameter groups.
3. Choose Create parameter group.
4. In the Parameter group family list, select a DB parameter group family.
5. In the Group name box, enter the name of the new DB parameter group.
6. In the Description box, enter a description for the new DB parameter group.
7. Choose Create.

Modify the parameter in custom parameter group

1. Login to the Amazon RDS console.
2. In the navigation pane, choose Parameter groups.
3. In the list, choose the parameter group that you want to modify.
4. For Parameter group actions, choose Edit.
5. Change the values of the parameter shared_preload_libraries.
6. Choose Save changes.

Note: The shared_preload_libraries parameter is a static parameter. When you change a static parameter and save the DB parameter group, the parameter change takes effect after you manually reboot the DB instance.

Associate the parameter group to your DB instance

Note: If your custom parameter group is already associated to your DB instance, skip to the next step.

Modify your DB instance to associate the custom parameter group to your DB instance.

Reboot the DB instance

When you change a static parameter and save the DB parameter group, the parameter change takes effect after you manually reboot the DB instance.

When you change the DB parameter group associated with a DB instance, you must manually reboot the DB instance before the DB instance uses the new DB parameter group.

Reboot your DB instance using the Amazon RDS console or by explicitly calling the RebootDBInstance API operation (without failover, if the DB instance is in a Multi-AZ deployment).

Related Information

PostgreSQL documentation for pg_stat_statements

Working with DB Parameter Groups