[ADMIN] Upgrade from 9.1.6 to 9,1.7 seems to have caused a major slowdown

2012-12-20 Thread Greg Williamson
Dear peoples, Today we upgraded a small (7.5 megabytes) but hyperactive database from postgresql 9.1.6 to 9.1.7 (we use INDEX CONCURRENTLY a lot) and after restarting it looks as if we have a slower system -- fewer queries, but more long ones, fewer checkpoints. I am still manually comparing c

[ADMIN] Autovacuum issues with truncate and create index ...

2012-12-20 Thread Baptiste LHOSTE
Hi everybody, We are having issues with the autovacuum process. Our database is composed by two kinds of tables : - the first ones are partitions, - the second ones are classic tables. Each five minutes we execute the following process : - we drop constraint of the target partition - we drop

Re: [ADMIN] Upgrade from 9.1.6 to 9,1.7 seems to have caused a major slowdown

2012-12-20 Thread Rafael Martinez
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/20/2012 12:06 PM, Greg Williamson wrote: > Dear peoples, > > > Today we upgraded a small (7.5 megabytes) but hyperactive database > from postgresql 9.1.6 to 9.1.7 (we use INDEX CONCURRENTLY a lot) > and after restarting it looks as if we have

[ADMIN] create role?

2012-12-20 Thread Ray Stell
I have a app where the install guide says to give a db user the ability to create roles. "Ensure that the user has permission to create database objects and can create roles." How is that done...elegantly? Seems like the oracle idea of a role. -- Sent via pgsql-admin mailing list (pgsql-adm

Re: [ADMIN] create role?

2012-12-20 Thread Martin French
> > I have a app where the install guide says to give a db user the > ability to create roles. > "Ensure that the user has permission to create database objects and > can create roles." > How is that done...elegantly? Seems like the oracle idea of a role. > CREATE ROLE LOGIN ENCRYPTED PASSW

Re: [ADMIN] create role?

2012-12-20 Thread Lou Picciano
Ray, PostgreSQL has a very powerful implementation of the ROLE/privileges model. Its control can be very granular, and very intricate! You really want to review your application's privileges/security needs before doing GRANTs with abandon. To get what you need, though, the 'db user' simply

Re: [ADMIN] create role?

2012-12-20 Thread Ray Stell
On Dec 20, 2012, at 9:45 AM, Lou Picciano wrote: > To get what you need, though, the 'db user' simply needs the CREATEROLE > privilege set. > createrole is really hard to find when you search for "create role." Thanks.

Re: [ADMIN] Autovacuum issues with truncate and create index ...

2012-12-20 Thread Kevin Grittner
Baptiste LHOSTE wrote: >  - finally we delete old data of the second kind of tables > Then the autovacuum process starts to work on the second kind of > tables, but our process blocks into step 3 (truncate) or step 5 > (create index). > > As soon as I reset the autovacuum thresholds for the seco

Re: [ADMIN] create role?

2012-12-20 Thread Gary Stainburn
On Thursday 20 December 2012 14:57:31 Ray Stell wrote: > createrole is really hard to find when you search for "create role." > Thanks. I always prefix any google search with 'postgresql' then whatever I need. Try googling postgresql create role postgresql alter role The second one is to amend

Re: [ADMIN] Autovacuum issues with truncate and create index ...

2012-12-20 Thread Baptiste LHOSTE
> Would it be possible for you to create such a situation and capture > the contents of pg_stat_activity and pg_locks while it is going on? > What messages related to autovacuum or deadlocks do you see in the > server log while this is going on? Before the change we can only see only automatic a

Re: [ADMIN] Autovacuum issues with truncate and create index ...

2012-12-20 Thread Baptiste LHOSTE
>> Would it be possible to update your 8.4 installation to the latest >> bug fix (currently 8.4.15) to rule out the influence of any bugs >> which have already been fixed? > Is there a way to upgrade without having to dump all data and restore them > after the upgrade ? I have check but debian

Re: [ADMIN] Autovacuum issues with truncate and create index ...

2012-12-20 Thread Kevin Grittner
Baptiste LHOSTE wrote: > Here's the pg_stat_activity during the issue : > [no processes waiting] > Here's the pg_locks during the issue : > [all locks granted] Was the blocking you described occurring at the time you captured this? It doesn't seem to be showing any problem. > Is there a way to

Re: [ADMIN] Autovacuum issues with truncate and create index ...

2012-12-20 Thread Baptiste LHOSTE
> Was the blocking you described occurring at the time you captured > this? It doesn't seem to be showing any problem. Yes indeed. We have noticed that any process seems to be in waiting situation but : - before the autovacuum process starts to work on the both kind of tables, truncate and ind

Re: [ADMIN] create role?

2012-12-20 Thread Ray Stell
On Dec 20, 2012, at 10:22 AM, Gary Stainburn wrote: > On Thursday 20 December 2012 14:57:31 Ray Stell wrote: >> createrole is really hard to find when you search for "create role." >> Thanks. > > I always prefix any google search with 'postgresql' then whatever I need. > > Try googling > > po

Re: [ADMIN] Upgrade from 9.1.6 to 9,1.7 seems to have caused a major slowdown

2012-12-20 Thread Greg Williamson
Rafael -- <...> >> Has anyone else seen anything like this ? >> > > Hello > > The release notes for 9.2.2 say this: > > "... However, you may need to perform REINDEX operations to correct > problems in concurrently-built indexes, as described in the first > changelog item below." > > Bu

Re: [ADMIN] Autovacuum issues with truncate and create index ...

2012-12-20 Thread Kevin Grittner
Baptiste LHOSTE wrote: >> Was the blocking you described occurring at the time you >> captured this? It doesn't seem to be showing any problem. > > Yes indeed. We have noticed that any process seems to be in > waiting situation but : >  - before the autovacuum process starts to work on the both k