pg_autovacuum entries

[Anj Adu]

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.

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

[Alvaro Herrera-7]

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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Anj Adu]

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@...> 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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Igor Polishchuk]

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@... on behalf of Anj Adu
Sent: Sun 11/1/2009 8:03 AM
To: Alvaro Herrera
Cc: pgsql-admin@...
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@...> 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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Anj Adu]

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@...> 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@... on behalf of Anj Adu
> Sent: Sun 11/1/2009 8:03 AM
> To: Alvaro Herrera
> Cc: pgsql-admin@...
> 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@...> 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@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>

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

[Alvaro Herrera-7]

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.

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

[Alvaro Herrera-7]

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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Anj Adu]

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@...> 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@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

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

[Alvaro Herrera-7]

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.

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

[Anj Adu]

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@...> 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.
>

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