I have an Amazon Relational Database Service (Amazon RDS) for a PostgreSQL DB instance. I want to troubleshoot the error "ERROR: <module/extension> must be loaded via shared_preload_libraries".
Short Description
When you use Amazon RDS for PostgreSQL, you can preload shared libraries into the server. This allows you to load additional functionality or to achieve performance benefits. However, you might receive the following error when you use the module/extension:
"ERROR: <module/extension> must be loaded via shared_preload_libraries"
For example, when you query 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
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 that's associated with your DB instance.
Modify the value of the parameter shared_preload_libraries in a custom parameter group to use the following modules/extensions on RDS for PostgreSQL:
- 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 apply to all DB instances associated with that parameter group.
Resolution
Create a custom parameter group
Note: If you already have a custom parameter group, then skip to the next step.
- Login to the Amazon RDS console.
- In the navigation pane, choose Parameter groups.
- Choose Create parameter group.
- In the Parameter group family list, select a DB parameter group family.
- In the Group name box, enter the name of the new DB parameter group.
- In the Description box, enter a description for the new DB parameter group.
- Choose Create.
Modify the parameter in custom parameter group
- Login to the Amazon RDS console.
- In the navigation pane, choose Parameter groups.
- In the list, choose the parameter group that you want to modify.
- For Parameter group actions, choose Edit.
- Change the values of the parameter shared_preload_libraries.
- 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, then skip to the next step.
Modify your DB instance to associate the custom parameter group to your DB instance.
Reboot the DB instance
Important: After you change the DB parameter group associated with a DB instance, you must manually reboot the DB instance. You must do this before the DB instance uses the new DB parameter group.
Use the Amazon RDS console to reboot your DB instance. Or, you can explicitly call the RebootDBInstance API operation (without failover, if the DB instance is in a Multi-AZ deployment).
Related Information
PostgreSQL documentation for pg_stat_statements (on the PostgreSQL website)
Working with parameter groups