snippetsqlCritical
How to view the current settings of Autovacuum in Postgres?
Viewed 0 times
settingsthepostgresviewautovacuumcurrenthow
Problem
I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration.
Postgres 9.1 is the version I'm most interested in.
Postgres 9.1 is the version I'm most interested in.
Solution
For the general settings use:
for table specific settings, check out the column
You will probably want to join that to pg_namespace to limit this to a specific schema. Where joining needs to happen on the hidden col
select *
from pg_settings
where name like '%autovacuum%'for table specific settings, check out the column
reloptions in pg_class:select relname, reloptions
from pg_classYou will probably want to join that to pg_namespace to limit this to a specific schema. Where joining needs to happen on the hidden col
pg_namespace.oid (added 9.3+).select relname, reloptions, pg_namespace.nspname
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relname like 'data%' and pg_namespace.nspname = 'public';Code Snippets
select *
from pg_settings
where name like '%autovacuum%'select relname, reloptions
from pg_classselect relname, reloptions, pg_namespace.nspname
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relname like 'data%' and pg_namespace.nspname = 'public';Context
StackExchange Database Administrators Q#35814, answer score: 92
Revisions (0)
No revisions yet.