Re: How clear the cache on postgresql?
On Fri, 2017-11-24 at 11:58 -0800, John R Pierce wrote: > On 11/24/2017 11:43 AM, Michael Nolan wrote: > > There are so many different levels of caching going on--within > > Postgresql, within the OS, within a disk array or SAN, and at the > > individual drive--that there may no longer be a meaningful way to > > perform this measurement. > generally, power cycling the server will flush all the hardware > caches AND the OS cache. Given that a real-world application will almost never experience an empty-cache scenario I question the usefulness of clearing the cache(s). I would capture transactions from a production database in order to create a load test that mimics real-world load. -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: update field in jsonb
On 24.11.2017 12:19, Oleg Bartunov wrote: btw, in PG 11 we expect subscription index update test set data[age]= '30'; Out of curiosity: Why not? update test set data->age= 30; It reminds me more of how to query json in PG select data->age from test; Cheer, Sven
Roles and security
Hi I am Nikhil currently working as a MS SQL DBA . Currently my company is shifting from MS SQL to postgres check the roles of user I want to give user roles only select, insert, update, execute and create database to all users Permission The users should not have these permission drop database , delete table permission **(Example --like in MS SQL we have like DB_roles-- ddladmin,data writer,data reader ,etc like these do we have in postgres).*** Thanks
Re: backends stuck in "startup"
Justin Pryzby writes: > We never had any issue during the ~2 years running PG96 on this VM, until > upgrading Monday to PG10.1, and we've now hit it 5+ times. > BTW this is a VM run on a hypervisor managed by our customer: > DMI: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, > BIOS 6.00 06/22/2012 > Linux TS-DB 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 > x86_64 x86_64 GNU/Linux Actually ... I was focusing on the wrong part of that. It's not your hypervisor, it's your kernel. Running four-year-old kernels is seldom a great idea, and in this case, the one you're using contains the well-reported missed-futex-wakeups bug: https://bugs.centos.org/view.php?id=8371 While rebuilding PG so it doesn't use POSIX semaphores will dodge that bug, I think a kernel update would be a far better idea. There are lots of other known bugs in that version. Relevant to our discussion, the fix involves inserting a memory barrier into the kernel's futex call handling: https://github.com/torvalds/linux/commit/76835b0ebf8a7fe85beb03c75121419a7dec52f0 regards, tom lane
Re: Roles and security
On 11/25/2017 12:03 PM, nikhil raj wrote: check the roles of user I want to give user roles only select, insert, update, execute and create database to all users Permission those permissions can be assigned on a table by table basis, except create database, thats a special permission. if you own a database (the creator owns it by default) then you can drop it, if you don't, you can't. The users should not have these permission drop database , delete table permission generally the owners of objects can grant/revoke permissions on said objects. **(Example --like in MS SQL we have like DB_roles-- ddladmin,data writer,data reader ,etc like these do we have in postgres).*** you could create roles like these, make users members of these roles,a nd grant permissions to the roles. for more information, see https://www.postgresql.org/docs/current/static/user-manag.html https://www.postgresql.org/docs/current/static/sql-grant.html https://www.postgresql.org/docs/current/static/sql-revoke.html https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html -- john r pierce, recycling bits in santa cruz
Re: backends stuck in "startup"
On Sat, Nov 25, 2017 at 05:45:59PM -0500, Tom Lane wrote: > Justin Pryzby writes: > > We never had any issue during the ~2 years running PG96 on this VM, until > > upgrading Monday to PG10.1, and we've now hit it 5+ times. > > > BTW this is a VM run on a hypervisor managed by our customer: > > DMI: VMware, Inc. VMware Virtual Platform/440BX Desktop Reference Platform, > > BIOS 6.00 06/22/2012 > > > Linux TS-DB 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 > > x86_64 x86_64 x86_64 GNU/Linux > > Actually ... I was focusing on the wrong part of that. It's not > your hypervisor, it's your kernel. Running four-year-old kernels > is seldom a great idea, and in this case, the one you're using > contains the well-reported missed-futex-wakeups bug: > > https://bugs.centos.org/view.php?id=8371 > > While rebuilding PG so it doesn't use POSIX semaphores will dodge > that bug, I think a kernel update would be a far better idea. > There are lots of other known bugs in that version. > > Relevant to our discussion, the fix involves inserting a memory > barrier into the kernel's futex call handling: Ouch ! Thanks for the heads up and sorry for the noise. I'm still trying to coax 3 customers off centos5.x, so the 2 customers left running centos6.5 weren't on any of my mental lists.. Justin
