Thread: Configure Different Databases on One Server
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? --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 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
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
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
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. > > >
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; > > >
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
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
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. > " >
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
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.
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
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--