Changing database configuration settings in a production environment is not (or should no bet) a common thing.
Having similar staging or test environments helps ruling out most problems before it’s too late, and that applies to configuration changes as well.
But sometimes you can’t really afford any downtime in a production workload. And you still got to introduce some configuration changes.
Often, with Postgres, these changes are in the form of changing a configuration setting in the postgresql.conf file.
Some of these changes are effective after a service reload (or a pg_reload), which does not cause any downtime, while some require a full service restart.
Even if you have a replicated environment, a database restart still troublesome and will generate some sort of downtime or in the best scenario a few retries on your application logic. In the worst scenario you may be looking at a full fail-over operation.
So, if you’re wondering, for a specific configuration, whether it requires a full service restart or a simple reload, there is a quick way to find out.
Just run the following query:
select name, context from pg_settings;You can add a where clause to filter out based on the setting name if you’d like.
The important column is the ‘context’ column. If the context for the setting is “postmaster”, changing that setting will require a restart to be effective.
If not, you’ll get away with a simple reload.
More information on the pg-settings table can be found here.
Note: some Postgres configuration files will already contain comments specifying if a setting does require a service restart.
Be First to Comment