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.
Add a comment
|
For the general settings use:
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_class
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 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';
answered Mar 2 '13 at 20:48
-
3Alternative query:
select * from pg_settings where category like 'Autovacuum'– João Portela Apr 14 '14 at 11:52 -
-
The
like 'autovacuum%'is wrong it should belike '%autovacuum%', not all autovacuum settings start with the wordautovacuum. – Giacomo Alzetta Jun 5 '19 at 9:58
If you want to see the vacuum settings for a specific table:
SELECT relname, reloptions FROM pg_class WHERE relname='tablename';
The general vacuum settings can be seen in postgresql.conf.
-
1I just tried this but it showed the field as null? the field is autovaccuuming – Luffydude Jun 26 '20 at 13:59
-
@Luffydude The field is autovacuuming?? Can you be more clear? In any case I guess that if reloptions is null it means that you are using the default vacuum settings (defined in postgresql.conf) for that table. – collimarco Nov 2 '20 at 13:41
