pg_stat_statements extension in Postgres

Hi all,

A quick question regarding the use of the pg_stat_statements extension on Fly’s Postgres. I can enable the extension without issue but when attempting to use it, however further configuration needs to be in place to use the extension:

pg_stat_statements must be loaded via shared_preload_libraries

It would seem a change to the postgresql.conf needs to be in place to make the extension work. Namely, shared_preload_libraries = 'pg_stat_statements'

My (vague) understanding of Postgres on Fly is that it’s more of a recipe then it is a full managed service. If this is the case, what is the best approach to modifying the Postgres config?

You also don’t have to modify the config, startup switches appear to be enough to enable the shared library, i.e something like: postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all

You may be able to SSH in and do this, no fork required. SSH to a postgres VM and try:

export $(cat /data/.env | xargs)
stolonctl update --patch "{ 'pgParameters' : {'shared_preload_libraries' : 'pg_stat_statements' } }"

You’ll have to run this on each VM afterwards:

gosu stolon pg_ctl -D /data/postgres/ restart -m fast

If you do need to fork the app, it’s here: GitHub - fly-apps/postgres-ha: Postgres + Stolon for HA clusters as Fly apps.

You can clone it, change the name in fly.toml, make changes, and then deploy over your app. Just make sure you pass in the version arg for Postgres 13 if you need it. Postgres 14 is the default:

Thanks for that, I’ll give it a try shortly and report back.

Re: your non forked method, I assume it will persist after restarts but what about upgrades to the Postgres service? I.e. looking at my image:

fly image show --app=vodon-db

Current image details
  Registry   = registry-1.docker.io
  Repository = flyio/postgres
  Tag        = 14.1
  Version    = v0.0.8
  Digest     = sha256:83d4f540dc2a1c7936e421a75b333ed8a04050093c7cfaf81f24607d9e1b793f

Latest image details
  Registry   = registry-1.docker.io
  Repository = flyio/postgres
  Tag        = 14.1
  Version    = v0.0.9
  Digest     = sha256:ea54d08a4e97a772ca033cd02d852d4a96d023978669ee148a9d439f30d50685

I’m one point version behind latest. If I make the change above then update the image, does it persists (you can tell me to pound sand and test it myself if you’d like :laughing:)

The stolon updates will persist, it stores those settings on Consul. We’d like to make those easier to change in the future!

2 Likes

I had to make a couple of alterations. One was the supplied JSON patch, I escaped the quotes:

stolonctl update --patch "{ \"pgParameters\" : {\"shared_preload_libraries\": \"pg_stat_statements\" } }"

The second was that it didn’t take effect until I fully restarted the node.

With both of those down, I’m seeing the expected “outliers” in my Phoenix apps dashboard. Thanks @kurt !

1 Like

Hey sam, did you have to run this twice?

Once for the shared_preload_libraries and a second for pg_stat_statements.track?

Would greatly appreciate a step by step guide unless there is an easier way now @kurt ?

We are looking to get query stats working with PG Hero.

Also, any plans to have this automatically enabled in the future? Taking a look at Heroku PG and it might automatically include this for non hobby dbs?

Thanks!

Hmm, I don’t quite remember!

I think, don’t quite me on this, that it was just:

CREATE EXTENSION pg_stat_statements

then the patch command that I ran. I believe once the patch was in place I may have had to reboot the DB instance (as @kurt mentions with the “deploy”). I think instead I just scaled it to 0 then back up again. After that everything seemed to work (I was using this with Elixir’s Live Dashboard library so it may have issued some other commands behind the scenes).

Hope that helps.

1 Like

Can this now be done using the fly postgres config update command, or does that do something else?

Edit: this command seems specific to log-min-duration-statement, log-statement, max-connections, and wal-level configuration. I was able to do it with ssh + stolonctl as described above.