pg_stat_statements extension in Postgres

1

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

2

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:

3

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:)

4

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
5

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
6

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!

7

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
8

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.

9

It is possible to do this now with:
fly postgres config update -a your-db --shared-preload-libraries pg_stat_statements

and then restart with:
fly restart your-db

1 Like
10

Hello all,

Updating the config to load pg_stat_statements is silently failing.

Update command:

fly postgres config update -a chat-pg --shared-preload-libraries pg_stat_statements

Before and after I run the update command, the config view command shows that the shared-preload-libraries has no value.

fly postgres config view -a chat-pg
NAME                      	VALUE  	UNIT	DESCRIPTION                                                                                	PENDING RESTART
...
shared-preload-libraries  	       	    	Lists shared libraries to preload into server.                                             	false

I am on the latest image

fly image show -a chat-pg
Image Details
MACHINE ID    	REGISTRY            	REPOSITORY    	TAG 	VERSION	DIGEST
d568352a74e18e	registry-1.docker.io	flyio/postgres	14.4	v0.0.33	sha256:0aa49da884754d5e46d3d465738a20952d86f75970bc2fd0ef3f654669b9f2e4

For what it’s worth, I also tried update max-connections to see if it was shared-preload-libraries specifically or config updates in general, the max-connections also failed to update.

There don’t seem to be any relevant logs, just this message:

ewr [info]sentinel | 2022-12-22T04:11:24.509Z	WARN	cmd/sentinel.go:276	no keeper info available	{"db": "8dac5307", "keeper": "7d18175c36a2"}

Any thoughts?

11

This is how I resolved my issue:

I ssh’d onto the pg server:

> fly ssh console -a chat-pg

then ran a stolon update similar to kurt’s above

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

afterwards, the config did show as updated:

> fly postgres config view -a chat-pg

Update available 0.0.441 -> 0.0.442.
Run "fly version update" to upgrade.
NAME                      	VALUE                   	UNIT	DESCRIPTION                                                                                	PENDING RESTART
...
shared-preload-libraries  	 -> (pg_stat_statements)	    	Lists shared libraries to preload into server.                                             	true
...

Some changes are awaiting a restart!
To apply changes, run: `fly postgres restart --app chat-pg`

Then I followed the instructions in that last command to restart the server. Done!

12

So I’ve just tried the ssh + stolonctl solutions mentioned above.

I’m receiving the following error when trying the stolonctl patch command:

# stolonctl update --patch "{\"pgParameters\": {\"pg_stat_statements.track\": \"all\"}}"                                                                        
cluster name required

Anyone have an idea of what I’m doing wrong?

13

@jgnieuwhof You are likely running an older version of the image.

You can confirm this by running:

fly image show --app <app-name>
14

Thanks @shaun!

I updated the image on our staging app, and have applied the pg config changes.

The image update on the production postgres app has brought it down, I made a separate topic here.