postgresqlautovacuum

How to restore all autovacuum settings to their postgres default


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?


Solution

  • 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.