Re: [GENERAL] delaying autovacuum freeze via storage params?

2015-05-27 Thread Steve Kehlet 
On Tue, May 26, 2015 at 7:27 PM Alvaro Herrera alvhe...@2ndquadrant.com
wrote:

 See the docs about the freeze max age storage parameter -- the per-table
 setting can decrease the global setting but not increase it.


Thanks Alvaro, that explains it. I found it in the docs: Note that
autovacuum will ignore attempts to set a
per-tableautovacuum_freeze_max_age larger
than the system-wide setting (it can only be set smaller).

[GENERAL] delaying autovacuum freeze via storage params?

2015-05-26 Thread Steve Kehlet 
Hello, I'd like to postpone an autovacuum: VACUUM public.mytable (to
prevent wraparound) and handle it manually at another time. I thought I
could set these storage parameters on the large table in question
(mytable) like this:

ALTER TABLE mytable SET (
  autovacuum_freeze_min_age=1000,
  autovacuum_freeze_table_age=8,
  autovacuum_freeze_max_age=10
);

then kill the running autovacuum process, but unfortunately it just keeps
coming back:

 mydb   | 22734 | 05/26/2015 23:29:10 | autovacuum: VACUUM public.mytable
(to prevent wraparound)

I also tried setting the toast.autovacuum* storage parameters, as well as a
postgres reload, but neither made any difference.

Here is the table's age:

mydb= select age(relfrozenxid) from pg_class where oid =
'mytable'::regclass;
age
---
 203065951
(1 row)

So 203 million, which is just past the 200 million default (which is why I
started looking at this). But it's less than the 1 billion I set the
storage param to.

Here is \d+:

mydb= \d+ mytable
  Table public.mytable
 Column  |Type |   Modifiers
| Storage  | Stats target | Description
-+-+---+--+--+-
 (removed al lthis)
Options: autovacuum_freeze_min_age=1000,
autovacuum_freeze_table_age=8,
autovacuum_freeze_max_age=10, toast.autovacuum_enabled=false,
toast.autovacuum_freeze_min_age=1000,
toast.autovacuum_freeze_table_age=8,
toast.autovacuum_freeze_max_age=10


I'm guessing I'm missing something here, can anyone offer suggestions or
ideas on what I should try next?

Here is my version:

mydb= SELECT version();
version

---
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-44), 64-bit
(1 row)

and I put my [custom settings in a gist](
https://gist.github.com/skehlet/bcdbc1ce1f212d3b7bb2).

Thanks!

Re: [GENERAL] delaying autovacuum freeze via storage params?

2015-05-26 Thread Alvaro Herrera 
Steve Kehlet wrote:
 Hello, I'd like to postpone an autovacuum: VACUUM public.mytable (to
 prevent wraparound) and handle it manually at another time. I thought I
 could set these storage parameters on the large table in question
 (mytable) like this:
 
 ALTER TABLE mytable SET (
   autovacuum_freeze_min_age=1000,
   autovacuum_freeze_table_age=8,
   autovacuum_freeze_max_age=10
 );

See the docs about the freeze max age storage parameter -- the per-table
setting can decrease the global setting but not increase it.  You can
increase the global setting (postgresql.conf) to 2 billion AFAIR which
should give you plenty of room.  Needs a server restart though.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general