3 months ago
Hi,
I was checking out the extensions tab of the Postgres DB hosted in my project. But when I added the pg_stat_statements extension I am unable to query the stats statement table, I am getting the following error as of now.
Query 1 ERROR at Line 1: : ERROR: pg_stat_statements must be loaded via "shared_preload_libraries"
I have already tried restarting and redeploying the db and the create extension command. Has anyone run into this before? Any pointers would be great!
PFA: screenhots of the extensions tab and issue in querying.
4 Replies
3 months ago
Hey there! We've found the following might help you get unblocked faster:
If you find the answer from one of these, please let us know by solving the thread!
3 months ago
The error you're encountering is a PostgreSQL requirement: pg_stat_statements must be loaded via shared_preload_libraries before PostgreSQL starts.
Solution:
You need to configure PostgreSQL to load `pg_stat_statements` in the `shared_preload_libraries` setting before the extension can be used. Here's how:
1. Run this SQL command to modify your PostgreSQL configuration:
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';Then you can run:
SELECT pg_reload_conf();2. Restart your PostgreSQL deployment by clicking the `Restart` button in your database service's 3-dot menu. Configuration changes to `shared_preload_libraries` require a full restart to take effect (not just a reload).
3. Query the statistics as needed after the restart
SELECT * FROM pg_stat_statements;Simply creating the extension without adding it to shared_preload_libraries won't work because PostgreSQL needs to load the extension module at startup time. The restart is necessary because configuration changes to preload libraries can't be applied without restarting the PostgreSQL process.
Reference: Railway PostgreSQL Configuration Guide
mykal
The error you're encountering is a PostgreSQL requirement: pg_stat_statements must be loaded via shared_preload_libraries before PostgreSQL starts.Solution:You need to configure PostgreSQL to load `pg_stat_statements` in the `shared_preload_libraries` setting before the extension can be used. Here's how:1. Run this SQL command to modify your PostgreSQL configuration:ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';Then you can run:SELECT pg_reload_conf();2. Restart your PostgreSQL deployment by clicking the `Restart` button in your database service's 3-dot menu. Configuration changes to `shared_preload_libraries` require a full restart to take effect (not just a reload).3. Query the statistics as needed after the restartSELECT * FROM pg_stat_statements;Simply creating the extension without adding it to shared_preload_libraries won't work because PostgreSQL needs to load the extension module at startup time. The restart is necessary because configuration changes to preload libraries can't be applied without restarting the PostgreSQL process.Reference: Railway PostgreSQL Configuration Guide
3 months ago
Hi Mykal,
Currently this is running a minor prod service, and I have seen these steps cause a database to go down (not able to restart) in some other theads. Planning on taking a backup and trying out this solution on the weekend. Will update here if it works out, thanks for the solution!
rushi-me
Hi Mykal,Currently this is running a minor prod service, and I have seen these steps cause a database to go down (not able to restart) in some other theads. Planning on taking a backup and trying out this solution on the weekend. Will update here if it works out, thanks for the solution!
3 months ago
For sure! always take a database backup before doing anything! Should go without being said (but I should have said it 
)
If it helps, I did try these exact steps in a fresh PostgreSQL database and it worked! regardless; I would still recommend you do a backup and make sure that you're not taking down production.
You can always spin up a new environment in your project and run these steps there as a "trial run" too.
Looking forward to hearing if it helps 