I made many changes to postgres autovacuum settings such as changing the cost_delay and naptime settings. I'm not confident what all my changes were---and would like to reset autovacuum settings to their default.
Is there a command I can execute to restore them to their defaults?
You will have to reset every setting you changed. If you can't remember which settings you changed, you can query pg_settings
for all non-default values:
select name,
setting as current_value,
reset_val,
boot_val as original_default,
sourcefile,
sourceline
from pg_settings
where source <> 'default'
and name like '%autovacuum%';
reset_val
is the value the setting would revert to if you run reset <name>;
- this applies to changes you have done in the current session (using set <name> = ...;
)
What I called original_default
is the value of that setting which is applied if the parameter is not specified at all.
The columns sourcefile
and sourceline
will help you find where you changed those values. If that is e.g. postgresql.auto.conf
then the setting was changed with alter system
and should be reset using alter system
.
If it's a different config file (e.g. `postgresql.conf), you will need to edit the file where it was changed.