Postgres config management is now available!

1

Hey everyone,

We have just released a new cli feature that will allow you to easily manage common Postgres configuration settings!

$ fly pg config 
View and manage Postgres configuration.

Usage:
  flyctl postgres config [command]

Available Commands:
  update      Update Postgres configuration.
  view        View your Postgres configuration

Flags:
  -h, --help   help for config

Global Flags:
  -t, --access-token string   Fly API Access Token
  -j, --json                  json output
      --verbose               verbose output

Config view

The config view will display the list of configurable settings. Most of the information is pretty self-explanatory, with the exception of maybe “PENDING RESTART”, which is something I will speak more on below.

$ fly pg config view --app <app-name>
NAME                      	VALUE  	DESCRIPTION                                                                                	PENDING RESTART
log-min-duration-statement	-1     	Sets the minimum execution time above which all statements will be logged. (-1, 2147483647)	false
log-statement             	none   	Sets the type of statements logged. [none, ddl, mod, all]                                  	false
max-connections           	400    	Sets the maximum number of concurrent connections. (1, 262143)                             	false
wal-level                 	replica	Set the level of information written to the WAL. [minimal, replica, logical]               	false

Config update

To show you how this works, let’s go ahead and adjust our clusters max-connections value from 400 to 500.

$ fly pg config update --max-connections 500  --app <app-name>
NAME           	VALUE	TARGET VALUE	RESTART REQUIRED
max-connections	400  	500         	true

? Are you sure you want to apply these changes? Yes
Performing update...
Update complete!

Please note that some of your changes will require a cluster restart before they will be applied.
To review the state of your changes, run: `fly postgres config view`

Per the instructions specified at the bottom, let’s take another look at our config view.

$ fly pg config view --app <app-name>

NAME                      	VALUE       	DESCRIPTION                                                                                	PENDING RESTART
log-min-duration-statement	-1          	Sets the minimum execution time above which all statements will be logged. (-1, 2147483647)	false
log-statement             	none        	Sets the type of statements logged. [none, ddl, mod, all]                                  	false
max-connections           	400 -> (500)	Sets the maximum number of concurrent connections. (1, 262143)                             	true
wal-level                 	replica     	Set the level of information written to the WAL. [minimal, replica, logical]               	false

Some changes are awaiting a restart!
To apply changes, run: `fly postgres restart --app <app-name>`

We can now see a pending change to max-connections which will not finalized until we’ve restarted the cluster.

You can issue a quick restart by running the command below:

$ fly postgres restart --app <app-name>
Restarting Postgres
 Restarting 3d1fe6cb-6e91-c373-af9a-074d2a309d2e
 Restarting ef4f2b66-7781-8bdc-3361-030a02543c75
Restart complete

Note - This restart command only restarts the underlying Postgres process, so it should be quite fast.

Now that we have issued our restart, we can take one last look at our config view and see that our max-connections value has now officially been applied!

$ fly pg config view --app <app-name>
NAME                      	VALUE  	DESCRIPTION                                                                                	PENDING RESTART
log-min-duration-statement	-1     	Sets the minimum execution time above which all statements will be logged. (-1, 2147483647)	false
log-statement             	none   	Sets the type of statements logged. [none, ddl, mod, all]                                  	false
max-connections           	500    	Sets the maximum number of concurrent connections. (1, 262143)                             	false
wal-level                 	replica	Set the level of information written to the WAL. [minimal, replica, logical]               	false

That’s it!

As you may have noticed, the current list of configurable settings is quite short… If there are additional settings you’d like to see configurable, let us know!

If you have any feedback on the feature, or if you encounter any issues, let us know!

11 Likes
2

@shaun, this is great! I’d love to be able to configure shared_preload_libraries and pg_stat_statements.track - here’s a related thread on the topic: pg_stat_statements extension in Postgres

I was able to do it with ssh + stolonctl as described in that thread, but I was definitely a little bit afraid of breaking things. A way to do it via flyctl would be awesome.

3

This will be available within flyctl v0.0.382.

Here’s a link to the PR for some additional information about the feature: https://github.com/superfly/flyctl/pull/1210

@donald

1 Like
4

Wow, that was quick. Awesome. Thanks!!

5

Love this feature! Would it possible to add all the settings that are set here:

1 Like
6

Hey guys I have been using PGHero to inspect my DB and it has suggested that I make query stats available by:

adding the following lines to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements' 
pg_stat_statements.track = all

image
image1920Ă—929 31 KB

Any idea how I go about doing this?

7

Hey @mike-cann ,

You should be able to set shared_preload_libraries using the fly pg config tool.

We have not added pg_stat_statements.track yet, so you could pull down the flyctl repo and add it yourself, or you can just do the following:

  1. SSH into your PG VM.
    fly ssh console --app <app-name>

  2. Use stolonctl to set the parameter.
    stolonctl update --patch '{"pgParameters": {"pg_stat_statements.track": "all"} }'

  3. Monitor your logs to ensure things go through properly.

I’d recommend testing in a staging environment first, but should work just fine.

8

Awesome thanks @shaun .

Unfortunately im still getting the message that I need to enable those features.

Do I have to restart the server after doing stolonctl update? If so how do I do that?

9

This should be fixed in the latest version of flyctl.

10

Thats fantastic thanks @shaun this now works great

1 Like
11

Is there a way to update max_wal_size and min_wal_size through the config command? Thanks!