Thread: pg_autovacuum entries
We are running a mix postgres 8.1.2 and 8.1.9 on several boxes. I would like to use the pg_autovacuum table to skip vacuuming certain tables that get dropped regularly (partitions) Is there a way to create an entry for a table that does not exist yet? Reason I ask is we have several "daily" partitions and I will have to modify scripts on several boxes to insert entries after the partitions get created. If I can pre-insert values in pg_autovacuum..it will save me a lot of trouble.
Anj Adu escribió:
> Is there a way to create an entry for a table that does not exist yet?
No, you will have to have "INSERT INTO pg_autovacuum" statements
alongside your table creation. I don't see that this is a lot of
trouble though, you can simply do
CREATE TABLE whatever ( ... );
INSERT INTO pg_autovacuum VALUES ('whatever'::regclass, false, -1, -1, ...);
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Does vacuumdb read pg_autovacuum ?
If not, can I specify multiple comma-separated tables to pass as
arguments to vacuumdb ? The list of tables that need to be vacuumed
is a constant and hence easier to manage the vacuuming process.
On Fri, Oct 30, 2009 at 3:04 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Anj Adu escribió:
>
>> Is there a way to create an entry for a table that does not exist yet?
>
> No, you will have to have "INSERT INTO pg_autovacuum" statements
> alongside your table creation. I don't see that this is a lot of
> trouble though, you can simply do
>
> CREATE TABLE whatever ( ... );
> INSERT INTO pg_autovacuum VALUES ('whatever'::regclass, false, -1, -1, ...);
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
Alvaro,
Why don't you just create a sql script with a separate vacuum command for each table you need to vacuum?
-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Anj Adu
Sent: Sun 11/1/2009 8:03 AM
To: Alvaro Herrera
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_autovacuum entries
Does vacuumdb read pg_autovacuum ?
If not, can I specify multiple comma-separated tables to pass as
arguments to vacuumdb ? The list of tables that need to be vacuumed
is a constant and hence easier to manage the vacuuming process.
On Fri, Oct 30, 2009 at 3:04 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Anj Adu escribió:
>
>> Is there a way to create an entry for a table that does not exist yet?
>
> No, you will have to have "INSERT INTO pg_autovacuum" statements
> alongside your table creation. I don't see that this is a lot of
> trouble though, you can simply do
>
> CREATE TABLE whatever ( ... );
> INSERT INTO pg_autovacuum VALUES ('whatever'::regclass, false, -1, -1, ...);
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
My goal is to reset the XID wraparound counter by performing vacuumdb
(autovacuum is not an option for my for various reasons)
On Sun, Nov 1, 2009 at 8:06 AM, Igor Polishchuk <ipolishchuk@hi5.com> wrote:
> Alvaro,
> Why don't you just create a sql script with a separate vacuum command for
> each table you need to vacuum?
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org on behalf of Anj Adu
> Sent: Sun 11/1/2009 8:03 AM
> To: Alvaro Herrera
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_autovacuum entries
>
> Does vacuumdb read pg_autovacuum ?
>
> If not, can I specify multiple comma-separated tables to pass as
> arguments to vacuumdb ? The list of tables that need to be vacuumed
> is a constant and hence easier to manage the vacuuming process.
>
> On Fri, Oct 30, 2009 at 3:04 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
>> Anj Adu escribió:
>>
>>> Is there a way to create an entry for a table that does not exist yet?
>>
>> No, you will have to have "INSERT INTO pg_autovacuum" statements
>> alongside your table creation. I don't see that this is a lot of
>> trouble though, you can simply do
>>
>> CREATE TABLE whatever ( ... );
>> INSERT INTO pg_autovacuum VALUES ('whatever'::regclass, false, -1, -1,
>> ...);
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/
>> The PostgreSQL Company - Command Prompt, Inc.
>>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
Anj Adu escribió:
> Does vacuumdb read pg_autovacuum ?
>
> If not, can I specify multiple comma-separated tables to pass as
> arguments to vacuumdb ? The list of tables that need to be vacuumed
> is a constant and hence easier to manage the vacuuming process.
No, but you can write a simple script to vacuum a list of tables that
you keep on a separate file or such. Something trivial like
while read table; do
vacuumdb -t $table
done < /path/to/tablelist
with proper error handling and reporting etc.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Anj Adu escribió: > My goal is to reset the XID wraparound counter by performing vacuumdb > (autovacuum is not an option for my for various reasons) Note that if you are on 8.1 (I can't recall) you need to vacuum the whole database in a single command for Xid wraparound. Only on 8.2 and higher you can get away with doing it one table at a time. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
If I were to use autovacuum on 8.1 , and If I specify the "ignore' tables in pg_autovacuum, 1. will the XID wraparound counter be reset after autovacuum finishes 2. If some of the ignore tables are never dropped or deleted in the future...how do the transaction semantics work with the rows in these tables (this is not likely to happen..but I am curious to know how transaction semantics work with the data in these tables) On Sun, Nov 1, 2009 at 9:56 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Anj Adu escribió: >> My goal is to reset the XID wraparound counter by performing vacuumdb >> (autovacuum is not an option for my for various reasons) > > Note that if you are on 8.1 (I can't recall) you need to vacuum the > whole database in a single command for Xid wraparound. Only on 8.2 and > higher you can get away with doing it one table at a time. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Anj Adu escribió: > If I were to use autovacuum on 8.1 , and If I specify the "ignore' > tables in pg_autovacuum, > > 1. will the XID wraparound counter be reset after autovacuum finishes No. I think you really need to get off 8.1. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Thanks..i will stick to vacuumdb in 8.1 (which works fine..just takes a couple of days to finish) Migration is an option..but our databases are enormous..(> 800G) and we have several of them On Wed, Nov 4, 2009 at 12:00 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Anj Adu escribió: >> If I were to use autovacuum on 8.1 , and If I specify the "ignore' >> tables in pg_autovacuum, >> >> 1. will the XID wraparound counter be reset after autovacuum finishes > > No. > > I think you really need to get off 8.1. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >