Configure Different Databases on One Server

On 10/29/2015 11:52 AM, Jim Longwill wrote:

I have a question about Postgres management. Our situation is
probably similar to many data shops.

We have one (Linux) server, and one Postgres installation (v9.4),
hence one Postgres process-daemon set running on the server, but we
have multiple databases created: ddev1, ddev2, ddev3.

I would like to do a different vacuum configuration on ddev2 than on
ddev1, such as turning off autovacuum, etc. How do I do this? E.g.
how do I turn off autovacuum on one database .. using different
postgres.conf files(?) or within the database, without affecting the
other database(s) on the same localhost?

Is there a relatively easy way to do this?

alter database dbname set autovacuum = false;

--
john r pierce, recycling bits in santa cruz

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

John R Pierce <pierce@hogranch.com> writes:

On 10/29/2015 11:52 AM, Jim Longwill wrote:

I would like to do a different vacuum configuration on ddev2 than on
ddev1, such as turning off autovacuum, etc. How do I do this? E.g.
how do I turn off autovacuum on one database .. using different
postgres.conf files(?) or within the database, without affecting the
other database(s) on the same localhost?
Is there a relatively easy way to do this?

alter database dbname set autovacuum = false;

Might be trickier than it looks though. I think individual autovacuum
worker processes will pick up such settings, but the launcher will not
since it doesn't ever attach to any individual database. So a lot of
undocumented implementation details would matter as to where particular
settings get used ...

Might be better to settle for configuring specific large tables using
per-table vacuum settings, and not sweat the small stuff at a per-DB
level.

regards, tom lane

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

On 10/29/2015 12:14 PM, Tom Lane wrote:

Might be better to settle for configuring specific large tables using
per-table vacuum settings, and not sweat the small stuff at a per-DB
level.

indeed, since you ALSO need to vacuum the system tables occasionally, or
things go pear-shaped if you do lots of DDL.

--
john r pierce, recycling bits in santa cruz

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

Ok,
Thanks! I think the ALTER DATABASE.. commands will work well for what
I'm doing now. I want to experiment with one database (doing manual
vacuuming, etc.) while leaving the other databases undisturbed.
Regards,
--Jim Longwill

On 10/29/2015 12:36 PM, John R Pierce wrote:

On 10/29/2015 12:14 PM, Tom Lane wrote:

Might be better to settle for configuring specific large tables using
per-table vacuum settings, and not sweat the small stuff at a per-DB
level.

indeed, since you ALSO need to vacuum the system tables occasionally,
or things go pear-shaped if you do lots of DDL.

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

Mr. Pierce, others,

I spoke too soon on this. I'd like to do your alter database.. command
but it isn't working. I've tried:

postgres=# ALTER DATABASE ddev2 SET autovacuum = off;

both as 'postgres' user, and the ddev2 owner user (which has owner
privs), and I'm always getting this error:

ERROR: parameter "autovacuum" cannot be changed now

I've checked some documentation, tried many variations of the command,
(e.g. = off, false, 0, etc.) tried other variations as well. As you can
see in the above example, I'm connected to 'postgres' database. I tried
'ddev2' also, etc. The above error is *always* the response.

I'm certain that there are no other user sessions in this database.

So.. what are the magic words? Thanks again!

--
--o--o--o--o--o--o--o--o--o--o--o--o--
Jim Longwill
JLongwill@psmfc.org
--o--o--o--o--o--o--o--o--o--o--o--o--

On 10/29/2015 12:05 PM, John R Pierce wrote:

On 10/29/2015 11:52 AM, Jim Longwill wrote:

I have a question about Postgres management. Our situation is
probably similar to many data shops.

We have one (Linux) server, and one Postgres installation (v9.4),
hence one Postgres process-daemon set running on the server, but we
have multiple databases created: ddev1, ddev2, ddev3.

I would like to do a different vacuum configuration on ddev2 than on
ddev1, such as turning off autovacuum, etc. How do I do this? E.g.
how do I turn off autovacuum on one database .. using different
postgres.conf files(?) or within the database, without affecting the
other database(s) on the same localhost?

Is there a relatively easy way to do this?

alter database dbname set autovacuum = false;

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

On 10/29/2015 01:35 PM, Jim Longwill wrote:

Mr. Pierce, others,

I spoke too soon on this. I'd like to do your alter database.. command
but it isn't working. I've tried:

postgres=# ALTER DATABASE ddev2 SET autovacuum = off;

both as 'postgres' user, and the ddev2 owner user (which has owner
privs), and I'm always getting this error:

ERROR: parameter "autovacuum" cannot be changed now

I've checked some documentation, tried many variations of the command,
(e.g. = off, false, 0, etc.) tried other variations as well. As you can
see in the above example, I'm connected to 'postgres' database. I tried
'ddev2' also, etc. The above error is *always* the response.

I'm certain that there are no other user sessions in this database.

So.. what are the magic words? Thanks again!

Look for SEE in below:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-autovacuum.html

"autovacuum (boolean)

Controls whether the server should run the autovacuum launcher
daemon. This is on by default; however, track_counts must also be
enabled for autovacuum to work. SEE--> This parameter can only be set in
the postgresql.conf file or on the server command line. <---SEE

Note that even when this parameter is disabled, the system will
launch autovacuum processes if necessary to prevent transaction ID
wraparound. See Section 23.1.5 for more information.
"

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 10/29/2015 01:35 PM, Jim Longwill wrote:

... I'm always getting this error:
ERROR: parameter "autovacuum" cannot be changed now

Controls whether the server should run the autovacuum launcher
daemon. This is on by default; however, track_counts must also be
enabled for autovacuum to work. SEE--> This parameter can only be set in
the postgresql.conf file or on the server command line. <---SEE

Yeah, this is more or less what I feared Jim would run into, though
I'd forgotten the details. "autovacuum" actually controls whether the
launcher is started at all, and so it cannot be set on a per-database
basis.

I believe it might work to adjust many of the other autovac parameters
at the per-database level, but not that one.

regards, tom lane

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

Ok. The reason I wanted to experiment with autovacuum disable for
'ddev2' database is that it is a mostly read-only database -- to support
querying from our web apps. I.e. there are only 1 or 2 small tables
ever updated all day on it, and only about 20 or so records total per
day (compared to 'ddev1' which has much more frequent activity). Also,
it's rebuilt periodically from the other db, so I had the idea that we
didn't need autovacuum running for 'ddev2'.

However, given that it is connected to the Postgres processes (launcher)
and I can't turn it off per database, it may not be worth the trouble to
configure this. I plan to experiment with other parameters as well, but
this is not critical at this time.

Thanks again.
--Jim Longwill :^)

On 10/29/2015 01:52 PM, Adrian Klaver wrote:

On 10/29/2015 01:35 PM, Jim Longwill wrote:

Mr. Pierce, others,

I spoke too soon on this. I'd like to do your alter database.. command
but it isn't working. I've tried:

postgres=# ALTER DATABASE ddev2 SET autovacuum = off;

both as 'postgres' user, and the ddev2 owner user (which has owner
privs), and I'm always getting this error:

ERROR: parameter "autovacuum" cannot be changed now

I've checked some documentation, tried many variations of the command,
(e.g. = off, false, 0, etc.) tried other variations as well. As you can
see in the above example, I'm connected to 'postgres' database. I tried
'ddev2' also, etc. The above error is *always* the response.

I'm certain that there are no other user sessions in this database.

So.. what are the magic words? Thanks again!

Look for SEE in below:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-autovacuum.html

"autovacuum (boolean)

Controls whether the server should run the autovacuum launcher
daemon. This is on by default; however, track_counts must also be
enabled for autovacuum to work. SEE--> This parameter can only be set
in the postgresql.conf file or on the server command line. <---SEE

Note that even when this parameter is disabled, the system will
launch autovacuum processes if necessary to prevent transaction ID
wraparound. See Section 23.1.5 for more information.
"

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

On 10/29/2015 2:43 PM, Jim Longwill wrote:

Ok. The reason I wanted to experiment with autovacuum disable for
'ddev2' database is that it is a mostly read-only database -- to
support querying from our web apps. I.e. there are only 1 or 2 small
tables ever updated all day on it, and only about 20 or so records
total per day (compared to 'ddev1' which has much more frequent
activity). Also, it's rebuilt periodically from the other db, so I
had the idea that we didn't need autovacuum running for 'ddev2'.

However, given that it is connected to the Postgres processes
(launcher) and I can't turn it off per database, it may not be worth
the trouble to configure this. I plan to experiment with other
parameters as well, but this is not critical at this time.

autovacuum of tables that have already been vacuumed and have no changes
is essentially a no-op, it checks some stats and moves on to another table.

--
john r pierce, recycling bits in santa cruz

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

Jim Longwill schrieb am 29.10.2015 um 22:43:

Ok. The reason I wanted to experiment with autovacuum disable for
'ddev2' database is that it is a mostly read-only database -- to
support querying from our web apps. I.e. there are only 1 or 2 small
tables ever updated all day on it, and only about 20 or so records
total per day (compared to 'ddev1' which has much more frequent
activity). Also, it's rebuilt periodically from the other db, so I
had the idea that we didn't need autovacuum running for 'ddev2'.

If the tables don't change, then autovacuum won't do anything.

So no harm in keeping it enabled - plus this smells like premature optimization.
I would not touch this unless you _really_ see a performance problem
that is cause by autovacuum on that database.

I can't imagine any situation that would justify turning autovacuum off.

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

On 10/29/15 5:01 PM, Thomas Kellerer wrote:

So no harm in keeping it enabled - plus this smells like premature
optimization.
I would not touch this unless you _really_ see a performance problem
that is cause by autovacuum on that database.

Moreso, if you think the problem is autovacuum (in this case) it's
probably something else.

If I can toot my own horn, you might want to watch the recording of
http://www.pgcon.org/2015/schedule/events/829.en.html.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

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

Thanks to Mr. Nasby & others for these references & input.

Indeed. I'm rather sure we don't have tables updated heavily enough to
warrant any adjustments to autovacuum, or to do extra 'vacuuming' of the
database. So I'll be leaving it alone (i.e. there's nothing broke so no
fixes needed!)
--Jim :^)

On 10/30/2015 11:52 AM, Jim Nasby wrote:

On 10/29/15 5:01 PM, Thomas Kellerer wrote:

So no harm in keeping it enabled - plus this smells like premature
optimization.
I would not touch this unless you _really_ see a performance problem
that is cause by autovacuum on that database.

Moreso, if you think the problem is autovacuum (in this case) it's
probably something else.

If I can toot my own horn, you might want to watch the recording of
http://www.pgcon.org/2015/schedule/events/829.en.html.

--
--o--o--o--o--o--o--o--o--o--o--o--o--
Jim Longwill
JLongwill@psmfc.org
--o--o--o--o--o--o--o--o--o--o--o--o--

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