Postgresql – How to view the current settings of Autovacuum in Postgres

postgresqlpostgresql-9.1vacuum

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.

Best Answer

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';

Related Solutions

Postgresql – How to import the data of postgres 8.x to postgres 9.x

What you need is a dump / restore. However, for this to work, you need the old PostgreSQL 8.4 server running. The new version 9.1 is not binary compatible and cannot use the files in postgres/8.4/data directly.

If you have no backup and cannot run PostgreSQL 8.4 on your machine any more, you are out of luck. Why is it that you cannot install PostgreSQL 8.4 any more? You'll have to, if you want your data.

On a side note:
It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of any enhancements that may have been made in these programs.

Postgresql nested row_to_json not working

What about:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select row_to_json(row(array_agg(tweets))) from tweets;

This is actually one of the really cool things about PostgreSQL, arrays, and complex types. One can aggregate them and then convert. You should get a structure which is effectively:

tweets[] (i.e. an array of tweet tuples) which then get should converted to JSON properly).

Now, if you need a more complex structure than this, you probably want to define types separately. Something like:

CREATE TYPE tweet_set AS (
    set_id int,
    tweets tweet[],
    generated_at timestamp, 
    ....
);

And then come up with a query to generate that, and then convert it to JSON.

One of the basic issues is that since row_to_json expects a record type, you have to pass in a record, not an anyarray. You could also do:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select array_to_json(array_agg(tweets)) from tweets;

Best Answer

Related Solutions

Postgresql – How to import the data of postgres 8.x to postgres 9.x

Postgresql nested row_to_json not working

Related Question