I have lots of Postgres instances to manage. And it's very annoying and a huge waste of time when I have to change some postgresql.conf setting in all of them.
But Postgres 9.3 does not have a function to persist settings values directly to the postgresql.conf file (it was introduced in 9.4)
So, I decided to make my own:
create or replace function f_save_postgresql_conf(pname text, pvalue text)
RETURNS text AS
$BODY$
DECLARE
vTEXT TEXT;
vCONF_FILE_LOCATION TEXT;
vRECORD RECORD;
vCATEGORY TEXT;
vMAXLENGTH INTEGER;
vSETTING TEXT;
vSETTING_DEFAULT TEXT;
BEGIN
-- ===================================================================================
-- The intent of this function is to fulfill the lack of ways of changing postgresql.conf
-- "programatically" (using nothing but a "query" - not external or 3party tools).
-- The way it does it is
-- - loading parameter values from original postgresql.conf (discards any comments)
-- - rewriting a fresh new postgresql.conf with all parameters present in pg_settings
-- Parameters:
-- - pname : the name of the setting to be changed (if empty, no setting will be changed)
-- - pvalue : the value of the setting to be changed
-- Result:
-- text : the new content of the file "postgresql.conf"
-- ===================================================================================
-- ===================================================================================
-- gets the max length name parameters (just for padding)
-- ===================================================================================
SELECT MAX(LENGTH(NAME))
INTO vMAXLENGTH
FROM PG_SETTINGS;
-- ===================================================================================
-- creates a temp table (temp_pg_settings) which will hold settings present in postgresql.conf
-- ===================================================================================
create temporary table temp_pg_settings (name text primary key, setting text) on commit drop;
-- ===================================================================================
-- load postgresql.conf lines into a temp table (temp_postgresql_conf)
-- ===================================================================================
vCONF_FILE_LOCATION = ((select setting from pg_settings where name = 'data_directory') || '/postgresql.conf');
create temporary table temp_postgresql_conf (line text) on commit drop;
execute 'copy temp_postgresql_conf from ''' || vCONF_FILE_LOCATION || ''' delimiter ''º'' ';
-- ===================================================================================
-- searchs temp_postgresql_conf (postgresql.conf lines) to find uncommented settings
-- ===================================================================================
for vRECORD IN (
select trim(substr(line, 1, strpos(line,'=')-1)) as name,
trim(substr(line,strpos(line,'=')+1, length(line))) as setting
from (
select trim(replace(
case when strpos(line,'#') > 0 then substr(line,1, strpos(line,'#')-1)
else line
end
,' ',' ')) as line
from temp_postgresql_conf
) a
where line not like '#%'
and line <> ''
order by 1
)
loop
-- raise notice '% = %', vRECORD.name, vrecord.setting;
-- insert into temp_pg_settings the parameter and it's value (present in postgresql.conf)
insert into temp_pg_settings (name, setting) values (vrecord.name, vrecord.setting);
end loop;
-- ===================================================================================
-- if function's parameter "pname" is not empty, then sets the setting with new value
-- ===================================================================================
if (coalesce(pname,'') <> '') then
if (select count(*) from pg_settings where name = pname) = 0 then
raise exception 'Settings name = "%" does not exist!', pname;
end if;
delete from temp_pg_settings where name = pname;
insert into temp_pg_settings (name, setting) values (pname,pvalue);
end if;
-- ===================================================================================
-- creates another temp table just to generate the fresh new content of postgresql.conf
-- ===================================================================================
create temporary table temp_new_postgresql_conf (seq serial primary key, line text) on commit drop;
-- ===================================================================================
-- iterates over pg_settings settings, generating postgresql.conf content based on settings present in temp_pg_settings
-- ===================================================================================
vCATEGORY = '';
for vRECORD in (select ps.*, tps.name as tname, tps.setting as tsetting
from pg_settings ps
left join temp_pg_settings tps on ps.name = tps.name
order by ps.category, case when tps.name is not null then 0 else 1 end, ps.name
)
loop
-- if category has changed since last record
if (vCATEGORY <> vRECORD.category) then
-- insert category name
insert into temp_new_postgresql_conf (line) values
(''),
('#------------------------------------------------------------------------------------------------------------------------------------------------------------'),
('# ' || upper(vRECORD.category) ),
('#------------------------------------------------------------------------------------------------------------------------------------------------------------');
vCATEGORY = vRECORD.category;
end if;
-- if parameters is commented in original postgresql.conf file, then insert it commented as well
if (vRECORD.tname is null) then
vTEXT = '#';
-- otherwise, insert it uncommented
else
vTEXT = '';
end if;
-- gets the parameter name
vTEXT = vTEXT || vRECORD.NAME;
vTEXT = rpad(vTEXT, vMAXLENGTH) || ' = ';
-- gets the parameter value
if (vRECORD.tname is null) then
vSETTING = coalesce(vRECORD.boot_val,'');
else
vSETTING = coalesce(vRECORD.tsetting,'');
end if;
if (vRECORD.vartype = 'string' and strpos(vSETTING,'''') = 0) then
vSETTING = '''' || vSETTING || '''';
end if;
vTEXT = vTEXT || vSETTING;
-- appends extra information (comments)
vTEXT = rpad(vTEXT,GREATEST(length(vTEXT),75)) || ' #';
-- values range/types/etc...
case when coalesce(vRECORD.vartype,'') = 'bool'
then vTEXT = vTEXT || ' on/off';
when vRECORD.enumvals is not null
then vTEXT = vTEXT || ' ' || (vRECORD.enumvals::text);
when coalesce(vRECORD.min_val,'') <> '' or coalesce(vRECORD.max_val,'') <> ''
then vTEXT = vTEXT || ' ' || coalesce(vRECORD.min_val,'') || ' to ' || coalesce(vRECORD.max_val,'')
|| (case when coalesce(vRECORD.unit,'') <> '' then ' (' || coalesce(vRECORD.unit,'') || ')' else '' end);
else vTEXT = vTEXT;
end case;
-- default value (if different from current one)
vSETTING_DEFAULT = coalesce(vRECORD.boot_val,'');
if (vRECORD.vartype = 'string' and strpos(vSETTING_DEFAULT,'''') = 0) then
vSETTING_DEFAULT = '''' || vSETTING_DEFAULT || '''';
end if;
if (vSETTING_DEFAULT <> vSETTING) then
vTEXT = vTEXT || ' (default='|| vSETTING_DEFAULT || ')';
end if;
-- appends setting's description
vTEXT = rpad(vTEXT,GREATEST(length(vTEXT),140)) || ' ' || coalesce(vRECORD.short_desc,'');
if coalesce(vRECORD.extra_desc,'') <> '' then
vTEXT = vTEXT || coalesce(vRECORD.extra_desc,'');
end if;
-- insert the "line" into the table
insert into temp_new_postgresql_conf (line) values (vTEXT);
end loop;
-- saves the new text over the old postgresql.conf
execute 'copy (select line from temp_new_postgresql_conf) to ''' || vCONF_FILE_LOCATION || ''' delimiter ''º'' ';
-- reload the postgresql.conf settings
perform pg_reload_conf();
-- mounts the postgresql.conf new text just to return it
vTEXT = '';
for vRECORD in (select * from temp_new_postgresql_conf order by seq)
loop
--raise notice '%', vRECORD.line;
vTEXT = vTEXT || vRECORD.line || chr(13);
end loop;
return vTEXT;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
And this is how postgresql.conf looks like after changes:
#------------------------------------------------------------------------------------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------------------------------------------------------------------------------------
#autovacuum = on # on/off Starts the autovacuum subprocess.
#autovacuum_analyze_scale_factor = 0.1 # 0 to 100 Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
#autovacuum_analyze_threshold = 50 # 0 to 2147483647 Minimum number of tuple inserts, updates, or deletes prior to analyze.
#autovacuum_freeze_max_age = 200000000 # 100000000 to 2000000000 Age at which to autovacuum a table to prevent transaction ID wraparound.
#autovacuum_max_workers = 3 # 1 to 8388607 Sets the maximum number of simultaneously running autovacuum worker processes.
#autovacuum_multixact_freeze_max_ag = 400000000 # 10000000 to 2000000000 Multixact age at which to autovacuum a table to prevent multixact wraparound.
#autovacuum_naptime = 60 # 1 to 2147483 (s) Time to sleep between autovacuum runs.
#autovacuum_vacuum_cost_delay = 20 # -1 to 100 (ms) Vacuum cost delay in milliseconds, for autovacuum.
#autovacuum_vacuum_cost_limit = -1 # -1 to 10000 Vacuum cost amount available before napping, for autovacuum.
#autovacuum_vacuum_scale_factor = 0.2 # 0 to 100 Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
#autovacuum_vacuum_threshold = 50 # 0 to 2147483647 Minimum number of tuple updates or deletes prior to vacuum.
#------------------------------------------------------------------------------------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS / LOCALE AND FORMATTING
#------------------------------------------------------------------------------------------------------------------------------------------------------------
default_text_search_config = 'pg_catalog.english' # (default='pg_catalog.simple') Sets default text search configuration.
lc_messages = 'C' # (default='') Sets the language in which messages are displayed.
lc_monetary = 'C' # Sets the locale for formatting monetary amounts.
lc_numeric = 'C' # Sets the locale for formatting numbers.
lc_time = 'C' # Sets the locale for formatting date and time values.
#DateStyle = 'ISO, MDY' # Sets the display format for date and time values.Also controls interpretation of ambiguous date inputs.
#IntervalStyle = postgres # {postgres,postgres_verbose,sql_standard,iso_8601} Sets the display format for interval values.
#TimeZone = 'GMT' # Sets the time zone for displaying and interpreting time stamps.
#client_encoding = 'SQL_ASCII' # Sets the client's character set encoding.
#extra_float_digits = 0 # -15 to 3 Sets the number of digits displayed for floating-point values.This affects real, double precision, and geometric data types. The parameter value is added to the standard number of digits (FLT_DIG or DBL_DIG as appropriate).
#lc_collate = 'C' # Shows the collation order locale.
#lc_ctype = 'C' # Shows the character classification and case conversion locale.
#server_encoding = 'SQL_ASCII' # Sets the server (database) character set encoding.
#timezone_abbreviations = '' # Selects a file of time zone abbreviations.
Usage:
SELECT f_save_postgresql_conf('work_mem','10MB');
Final considerations:
There are some settings that need postgres to be restarted to get in production (like max_connections), but the function will persists the new setting's value, and is up to you restart the server later.
The postgresql.conf file will be replaced by a new one, so any comment made directly in the file will be lost.
Feel free to use it (and improve it) and leave a comment if you find something wrong.
alter systemcommand: postgresql.org/docs/current/static/sql-altersystem.htmlpg_upgradeworks fine for me (since 8.4)