Re: Best options for new PG instance
First - NEVER USE NFS TO STORE DATA YOU DON'T WANT TO LOSE. That said, what you want to host on depends a lot on whether your system is typically CPU bound or I/O bound. A VM for the computational side is generally quite fine. If you're seriously CPU bound then you're likely to want to cluster the thing and/or use PG10 if you can take advantage of parallel requests. Once you get I/O bound things get trickier. AWS has horrible I/O characteristics compared to any "bare metal" solution out there for example. Yes, you can buy I/Oops but now you have incredibly expensive slow I/O characteristics. If you're I/O bound your best solution is to host elsewhere if possible. We have clients who cannot and they're paying a lot more as a result sadly. A great way to host PG is inside docker containers and there's some excellent kubernetes solutions coming around. It is best if you can mount your data on a host file system rather than a data volume container. The reasons for that may be less strong than before (that was one area where early Docker had defects) but we still see better I/O performance when pushed. That said, I am aware of people happy with their deployments using volume containers although I don't know their I/O profiles so much. Anyway - Docker can be run within VMs or directly on bare metal quite easily and is a great way to compare the impact of the two. Oh - and lots of memory is always good no matter what as others have said. Good luck, -- Ben On Mon, Mar 5, 2018 at 11:53 PM, David Gauthier wrote: > Hi: > > I'm going to be requesting a PG DB instance (v9.6.7) from an IT dept in a > large corp setting. I was wondering if anyone could comment on the > pros/cons of getting this put on a virtual machine vs hard metal ? Locally > mounted disk vs nfs ? > > Thanks ! > > > > > >
Re: When use triggers?
I've always found it most useful to consider the difference between "what the system is" vs. "what the system does". The core data entities and their stable relationships comprise most of what the system is. These are the things that should be enforced at the lowest level possible (in a db schema) and can be supported by other tools preferably as close to the database as possible - such as triggers and stored procedures. The purpose of these items should be strictly regulated to keeping the integrity of these entities intact. They should not presume a particular usage model nor should they implement application-specific business logic (which is something that changes more frequently). The various applications and businesses logic comprise "what the system does". Those entities now play various (often dynamic) roles in the context of your use cases. This is what should be implemented in your various programming languages and should be independent of any specifics of your database (or any particular UI as well if possible). This makes testing the application much easier and your application is also easier to write if it can assume that integrity constraints are already enforced at a lower level by the DB itself. This business functionality generally should not be implemented within the DB as it tightly couples the DB and the app which gets really painful as the app evolves. Hope that general set of policies helps guide your thinking and makes your efforts more effective. - - Ben Scherrey On Thu, May 17, 2018, 5:20 AM hmidi slim wrote: > HI, > > I'm working on a microservice application and I avoid using triggers > because they will not be easy to maintain and need an experimented person > in database administration to manage them. So I prefer to manage the work > in the application using ORM and javascript. > However I want to get some opinions and advices about using triggers: when > should I use them? How to manage them when there are some problems? >
Re: Installing PostgreSQL as non-root by Extracting the binaries from RPMs
You know this is something super easy to do if you do it within Docker. Advise you to go ahead and map the data directories to the host file system rather than use a storage container until you get a lot more experience with Docker. Otherwise it's crazy easy and, in fact, there already exist Docker images with Postgres installed that you could just download and use. Postgres will be entirely under local user control and requires no system level access. -- Ben On Mon, May 21, 2018 at 9:23 PM, Thiagarajan Lakshminarayanan < thiagu1...@gmail.com> wrote: > Hello, > > In our environment, our standard RHEL OS install comes with all the > PostgreSQL required packages / libraries installed. So, can I just extract > the binaries from PostgreSQL RPMs via rpm2cpio and create a tar file and > install it as non-root user in an alternate location? I tested the above > approach, created a new PostgreSQL instance and it is working fine. Please > let me know if there would be any issues with the above approach? > > Thanks > Raj >
Re: Code of Conduct plan
Is there some archive of the discussion that brought on this effort and the considerations of the committee itself? I wish I had seen the earlier announcements in 2016 as I would have definitely participated. Another more specific factual question - have there been incidents within the active Postgresql community where behaviour by individuals who are participants in the community have conducted themselves in a manner that brought on the actual need for such a code of conduct to exist in the first place? I'm curious about the specific impetus that brought about Postgresql's efforts to consider one. I've read the other comments in the general list but I'm more interested in the specifics motivations and efforts by the CoC committee. thanks, -- Ben Scherrey On Mon, Jun 4, 2018 at 1:29 AM, Tom Lane wrote: > Two years ago, there was considerable discussion about creating a > Code of Conduct for the Postgres community, as a result of which > the core team announced a plan to create an exploration committee > to draft a CoC [1]. That process has taken far longer than expected, > but the committee has not been idle. They worked through many comments > and many drafts to produce a version that seems acceptable in the view > of the core team. This final(?) draft can be found at > > https://wiki.postgresql.org/wiki/Code_of_Conduct > > We are now asking for a final round of community comments. > Please send any public comments to the pgsql-general list (only). > If you wish to make a private comment, you may send it to > c...@postgresql.org. > > The initial membership of the CoC committee will be announced separately, > but shortly. > > Unless there are substantial objections, or nontrivial changes as a result > of this round of comments, we anticipate making the CoC official as of > July 1 2018. > > regards, tom lane > > [1] https://www.postgresql.org/message-id/56a8516b.8000...@agliodbs.com > >
Re: Code of Conduct plan
On Mon, Jun 4, 2018 at 4:47 AM, Tom Lane wrote: > Benjamin Scherrey writes: > > Is there some archive of the discussion that brought on this effort and > the > > considerations of the committee itself? I wish I had seen the earlier > > announcements in 2016 as I would have definitely participated. > > If you poke around in our mailing list archives for early 2016 (Jan/Feb), > you'll find a number of threads about it. Mostly on the -general list, > IIRC. > I did go back and read through the 2016 content rather thoroughly. But where has all the discussion been going on for the last two years? Am I to understand that this effort has been going on in an entirely undocumented manner? I find that difficult to fathom such a thing happening in this community so I'm sure my understanding is mistaken. Where can we see the details of what was considered and what drove the committee to its apparently final proposal? > > Another more specific factual question - have there been incidents within > > the active Postgresql community where behaviour by individuals who are > > participants in the community have conducted themselves in a manner that > > brought on the actual need for such a code of conduct to exist in the > first > > place? > > I believe there were a couple of unfortunate incidents at conferences. > Now, conferences are generally expected to have their own CoCs and enforce > them themselves; this CoC is meant more to cover on-line interactions. > You could argue that we shouldn't create such a CoC until something bad > happens on-line; but I'd prefer to think that having a CoC might prevent > that from ever happening at all, which is surely better. > > In any case, we went over all these sorts of arguments at excruciating > length in 2016. It's quite clear to the core team that a majority of > the community wants a CoC. I don't think any useful purpose will be > served by re-litigating that point. > I also don't want to re-litigate anything and I do trust that core members and people involved with the effort are acting in good faith for their efforts. I'd just like to see what that consisted of so that I can consider it from a fully informed basis and not waste anyone else's time. I've cc'd Stacey in hopes that perhaps this can be clarified soon. I would like to review what was considered before I finalize any opinion about what's been proposed. thanx & best regards, -- Ben Scherrey
Re: Code of Conduct plan
On Tue, Jun 5, 2018 at 10:37 PM, Peter Geoghegan wrote: > It is of course possible that a member of the committee could act in > bad faith for any number of reasons. You can say the same thing about > any position of leadership or authority within the community, though. > That hasn't really been much of a problem in my experience, and I see > no reason for particular concern about it here. > I thought the same thing as a member of the Django community. It adopted a CoC that I vocally warned was dangerous and far more likely to be abused than provide any benefit. I was shocked when the very first time it was ever invoked it was by one of the founders of the project (whom I previously personally respected) and it was absolutely used in the manner that I had feared which was to shut someone up whose opinion he did not like rather than any legitimate concern. Unfortunately this is not such an unusual circumstance as one might hope in these projects or conferences. It is impossible to separate the concept of political correctness from these CoCs I find and they are much more dangerous things than they appear. We should tread with extreme cautious about adopting such a thing. -- Ben Scherrey
Re: Code of Conduct plan
On Tue, Jun 5, 2018 at 10:48 PM, Joshua D. Drake wrote: > On 06/05/2018 08:41 AM, Lutz Horn wrote: > >> Am 05.06.2018 17:26 schrieb Joshua D. Drake: >> >>> As one of the people that interacts with external members of the >>> community more than most, I can tell you that a CoC is something the >>> wider community wants. I have sat in feedback meetings with hundreds >>> of people who are potential community members. These people have >>> ranged in age, gender, sexual orientation and technical capability on >>> all realms of the spectrum. The majority of them aren't interested if >>> we do not have a written Code of Conduct. >>> >> >> May I ask what the context of these meetings was? Where where they held? >> For which country or part of the broader community where the participants >> representative? >> > > Happy to discuss offlist. I don't want to distract from this thread. > I want to know as well. I've been asking for this kind of backgrounder information and nothing has been forthcoming.
Re: Code of Conduct plan
On Tue, Jun 5, 2018 at 11:36 PM, Joshua D. Drake wrote: > On 06/05/2018 09:32 AM, Tom Lane wrote: > >> James Keener writes: >> >>> I don't participate too much here, but I've never see a group implement >>> a code of conduct go well. >>> >> >> Yeah, personally I'm a bit worried about this too. The proposed CoC >> does contain provisions to try to prevent misusing it, but whether those >> are strong enough remains to be seen --- and it'll depend a good deal >> on the judgment of the committee members. We have a provision in there >> for periodic review of the CoC, and it'll be important to adjust it if >> we see abuses. >> > > A community that has an exceedingly reasonable and popular CoC is Ubuntu: > > https://www.ubuntu.com/community/code-of-conduct > > A community that is the most successful open project in history and didn't > need a CoC is the Linux kernel project. I'd say we more better resemble the > later than the former.:-) >
Re: Code of Conduct plan
On Wed, Jun 6, 2018 at 12:01 AM, James Keener wrote: > I'm sorry for the double post. > > > If you read the reporting guidelines, it is requested that someone > filing a > report provides as much evidence as possible, and that is a really > important provision, both for the person reporting and for the committee > to review and adjudicate fairly. > > What does fairly mean? > > Let's role play. I'll be a homophobic person. > > You've just submitted a proposal suggesting that we change master-master > replication to be multi-partner replication. I've told you I don't like the > wording because of it's implication of supporting homosexual marriage, > which I believe to be a personal offense to me, my marriage, and my "deeply > held religious beliefs". You tell me that's not your intent and that you do > not plan to change your proposed wording. You continue to use the term in > all correspondences on the list and I continually tell you that supporting > gay marriage is offensive and that you need to not be so deeply offensive. > I submit all our correspondences to the CoC committee and complain that > you're purposely using language that is extremely offensive. > > What is a "fair" outcome? Should you be banned? Should you be forced to > change the wording of your proposal that no one else has complained about > and others support? What is a fair, just outcome? > > Jim God I love you , Jim!! Again, just roleplaying of course. :-)
Re: Code of Conduct plan
On Wed, Jun 6, 2018 at 12:36 AM, Joshua D. Drake wrote: > On 06/05/2018 10:26 AM, Chris Travers wrote: > > Let's role play. I'll be a homophobic person. >> >> You've just submitted a proposal suggesting that we change >> master-master replication to be multi-partner replication. I've told >> you I don't like the wording because of it's implication of >> supporting homosexual marriage, which I believe to be a personal >> offense to me, my marriage, and my "deeply held religious beliefs". >> You tell me that's not your intent and that you do not plan to >> change your proposed wording. You continue to use the term in all >> correspondences on the list and I continually tell you that >> supporting gay marriage is offensive and that you need to not be so >> deeply offensive. I submit all our correspondences to the CoC >> committee and complain that you're purposely using language that is >> extremely offensive. >> >> What is a "fair" outcome? Should you be banned? Should you be forced >> to change the wording of your proposal that no one else has >> complained about and others support? What is a fair, just outcome? >> >> >> I think the fundamental outcome is likely to be that people who cause >> trouble are likely to get trouble. This sort of case really doesn't worry >> me. I am sure whoever is stirring the pot will be asked at least to cease >> doing so. >> > > Your example is flawed because: > > Multi-Partner has nothing to do with sexuality unless you want to make the > argument that your belief is that a relationship should be between one > person and another and in this argument a man and a woman which has > literally nothing to do with the word multi or partner in a technical > context. > > Your example would carry better wait if you used master-master replication > to be man-man or woman-woman neither of which makes any sense in the > context of replication. > > Since man-man or woman-woman makes zero sense in the context of > replication it would immediately be -1 from all the -hackers of any sense > which for the most part is all of them. > > In short the fundamental outcome is that the community wouldn't let it get > that far. We have 20 years of results to show in that one. > > Doesn't that 20 years of results pretty clearly demonstrate that this community does not gain an advantage for adopting a CoC?
Re: Code of Conduct plan
On Wed, Jun 6, 2018 at 2:12 AM, Christophe Pettus wrote: > > > On Jun 5, 2018, at 12:06, Benjamin Scherrey > wrote: > > Doesn't that 20 years of results pretty clearly demonstrate that this > community does not gain an advantage for adopting a CoC? > > Not at all. The need for a CoC is not theoretical. Real people, > recently, have left the community due to harassment, and there was no > system within the community to report and deal with that harassment. > I keep hearing this claim. I've followed up and tried to verify them. Sorry but "trust me" doesn't cut it here any more than "trust me this will make Postgres go faster" would on a code change. What's the context for this? What evidence do we have that indicates this CoC would have likely resulted in a different outcome? Without that then your claim does not even rise up to the standard of theoretical. Frankly this claim does not seem very plausible to me at all. Let's try to keep our standards here. I'm not trying to harp on you personally, it's just that you're the unlucky umpteenth time I've seen this claim made with zero satisfaction. -- Ben Scherrey
Re: What does Natvie Posgres mean?
In my experience it refers to development directly via SQL against the Postgres server rather than via an ORM like Django or the like. A remarkably high percentage of applications backed by Postgres have been written by developers that have never actually seen or written SQL code directly. It's all generated (often quite naively) by the object relational mapper. Requesting "native" developers means that they want you to understand how the DB actually behaves and to be able to generate optimal SQL code and proper DDLs that fit the application domain correctly. - - Ben Scherrey On Wed, Jun 13, 2018, 12:59 AM David G. Johnston wrote: > On Tuesday, June 12, 2018, bto...@computer.org > wrote: > >> >> When someone, e.g., as appeared in a recent and some older pgsql-jobs >> messages, says "Native Postgres", what do you suppose that means? >> >> Does it mean something different than just "PostgreSQL"? >> > > Likely it means the open source product built directly from the source > code published here (or packages derived there-from). As opposed to say > AWS RDS or EnterpriseDB or various other forks of the product available in > the wild. > > David J. > >
Re: High WriteLatency RDS Postgres 9.3.20
I would also add that AWS' I/O capabilities are quite poor and expensive. I assume that you have tried purchasing additional IOOPs on that setup to see whether you got an expected speed up? If not you should try that as a diagnostic tool even if you wouldn't want to pay that on an ongoing basis. We have a client that is I/O write bound and it has taken us significant efforts to get it to perform well on AWS. We definitely run our own instances rather than depend on RDS and have always been able to outperform RDS instances which seem to really be focused to provide a PAAS capability for developers who really don't want to have to understand how a db works. Running our identical environment on bare metal is like night & day under any circumstances when compared to AWS. Client's requirement is AWS so we keep working on it and we like AWS for many things but understand it will always underperform on I/O. Post actual measurements with and without IOOPs or create your own PG server instance and then people might be able to give you additional insights. - - Ben Scherrey On Tue, Jun 19, 2018, 5:24 AM Andres Freund wrote: > On 2018-06-18 18:43:06 -0300, Juan Manuel Cuello wrote: > > I'm experiencing high WriteLatency levels in a Postgres server 9.3.20 > > hosted in Amazon RDS. > > A lot of performance improvements have been made since 9.3, and it'll > soon-ish be out of support. > > If you can reproduce the issue on postgres proper, rather than a > modified version in an environment that precludes getting detailed data, > we might be able to sensibly help you further. > > > > So far it's been almost two months of investigation > > and people at AWS technical support don't seem to find the cause. I think > > it could be related to Postgres and the number of schema/tables in the > > database, that's why I post this issue here. > > There've been improvements made since 9.3. Upgrade. > > Greetings, > > Andres Freund > >
Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?
Linux compatibility. :-) Seriously. Our organization does not use Windows anywhere and we're a serious Postgres shop. -- Ben Scherrey On Sun, Jul 15, 2018 at 11:59 PM, Dmitry Igrishin wrote: > Hello all, > > Colleagues. There is an idea to develop a commercial IDE for PostgreSQL > under Windows. > At the initial stage, not so much an IDE, as an assistant for the server > side development. > What features would you like to see in such an instrument? Thanks. >
Re: User documentation vs Official Docs
On Tue, Jul 17, 2018, 3:33 AM Joshua D. Drake wrote: > -general. > > Over the last year as I have visited many meetups and interacted with > people at conferences etc... There are three prevailing issues that > continue to come up in contributing to the community. This email is > about one of them. Where is the "user" documentation? The official > documentation is awesome, if you know what you are doing. It is not > particularly useful for HOWTO style docs. There is some user > documentation in the wiki but let's be honest, writing a > blog/article/howto in a wiki is a pain in the butt. > > What does the community think about a community run, community > organized, sub project for USER documentation? This type of > documentation would be things like, "10 steps to configure replication", > "Dumb simple Postgres backups", "5 things to NEVER do with Postgres". I > imagine we would sort it by version (9.6/10.0 etc...) as well as break > it down via type (Administration, Tuning, Gotchas) etc... > > What do we think? > > Thanks! > > JD > > > -- > Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc > *** A fault and talent of mine is to tell it exactly how it is. *** > PostgreSQL centered full stack support, consulting and development. > Advocate: @amplifypostgres || Learn: https://postgresconf.org > * Unless otherwise stated, opinions are my own. * > > One thing I recall very fondly about the early days of the Lamp stack was that the official documentation of PHP and MySQL was augmented with user created practical examples. It was still reference documentation organized by command or function, but in a comment-like section underneath the formal docs were user provided short practical examples of how the command would be used in real situations. One was able to teach themselves how to build a dynamic website front ending a database just by exploring the core docs and reading the examples. -- Ben Scherrey
Re: A Timeseries Case Study: InfluxDB VS PostgreSQL
Interesting and useful article, Fabio. I'm actually quite curious about your evaluation of MongoDB & Postgres. I've been operating under the opinion that MongoDB has been obsoleted in every respect by Postgres and am curious as to whether there are any credible use cases where, given the opportunity to choose from the beginning which technology to build a new product on, I would ever select MongoDB over Postgres given the choice between them. best regards, - - Ben Scherrey On Fri, Sep 7, 2018, 5:16 PM Fabio Pardi wrote: > Hi Achilleas, > > I'm glad you like the article. > > Probably I will find the time to come back to the topic when I'm done > comparing Mongodb with PostgreSQL > > regards, > > fabio pardi > > > On 07/09/18 11:18, Achilleas Mantzios wrote: > > Nice read! Wonder if you could repeat the tests on pgsql 10.5 and > btree/BRIN. > > >
Re: DB size difference after restore
If you're talking about space on drive then you can expect the new one to be smaller generally as it has been straight efficient writes rather than a bunch of updates and deletes which create "holes" in the physical file space. It helps if you are more detailed as to what you've observed if you want a more specific answer. - - Ben Scherrey On Wed, Oct 3, 2018, 7:43 PM Sonam Sharma wrote: > I have restored the database from backup dump but the size of source and > target databases are different. What can be the reason for this ? > > Regards, > Sonam >
Re: Requirement PA-DSS 1.1.4
Postgres has spent several decades creating a data store that does its best to make sure data is accessible and never accidentally lost. The cobc8of a secure forensic wipe is anathama to the product goals. To have any hope of implementing such a thing with confidence you'd need to drop most of those resilience features and write directly to block storage avoiding any file system that uses journals or copy on write semantics (like btrfs). I've built some of the early leading forensic discovery and decommissioning software so I know what's involved. You should never store such information in a database product unless you plan of decommissioning ALL of the media that stores the information once you're supposed to lose custody. If you're allowed to do this on a periodic basis (say monthly or quarterly) you could, as a policy, make a logical dump of the database and then restore it on a fresh server periodically and then fully decommission the data stores on the prior server. If you have a clustered system for resiliency then round robbin this in an N+1 configuration would work. Outside of the above - if your custody latency allowences are too short then never store this information in a database product of any sort or even a modern file system. I'd write block storage in fixed sizes to a dedicated partition and then write over the data securely multiple times when you release custody. Hope this helps, - - Ben Scherrey On Tue, Jun 4, 2019, 5:57 AM Jan Bilek wrote: > Hi, > > We've build a Payments Authorisation system (Box solution) on Postgresql > database and now we are hitting following issue with our PA:DSS audit - > requirement PA-DSS 1.1.4: > > <> > 1.1.4 Securely delete any track data (from the magnetic stripe or > equivalent data contained on a chip), card verification values or codes, > and PINs or PIN block data stored by previous versions of the payment > application, in accordance with industry-accepted standards for secure > deletion, as defined, for example by the list of approved products > maintained by the National Security Agency, or by other State or National > standards or regulations. > > > All of these elements of sensitive authentication data are not permitted > to be stored post-authorization. If older versions of payment applications > stored this information, the payment application vendor is required to > provide instructions in the PA-DSS Implementation Guide as well as a secure > wipe tool or procedure. If not securely deleted, this data could remain > hidden on customer systems, and malicious individuals who obtain access to > this information could use it to produce counterfeit payment cards, and/or > to perform fraudulent transactions. > Unfortunately, description is too ambiguous and our QSA claims that stored > is stored regardless of form. Tokens he can live with, but encryption not. > But we do encryption (regardless it is happening with a key stored on HSM). > > Actual trouble comes with forensics: > > <> > 1.1.4.c Verify, through the use of forensic tools and/or methods, that the > secure wipe tool or procedure provided by vendor securely removes the data, > in accordance with industry-accepted standards for secure deletion of data. > > > Similar with: > <> > 2.6 Provide a mechanism to render irretrievable any cryptographic key > material or cryptogram stored by the payment application, in accordance > with industry-accepted standards. > > > These are cryptographic keys (Host stored HSM keys) used to encrypt or > verify cardholder data. > > At this stage our QSA was able to identify that data remain on a > persistence device (DB files) even after deleting those from our > application. > > Checking SQLite database, it comes with pragma secure_delete - which is > very much what we are looking for. > https://www.sqlite.org/pragma.html#pragma_secure_delete > > I would appreciate your input on this. Is there any solution already I > haven't been able to find in documentation. If not, is there any way we can > put this on a road map or even contribute to your code? > > Thank you in advance & Kind Regards, > Jan > > -- > > EFTlab CTO >
Re: Postgres install
One option you should seriously consider is using Docker to containerize your postgres installation. The installation thinks it has root access but you don't need any rights beyond the standard user role that way you can use the standard builds without changes. This assumes you're running under Linux. - - Ben Scherrey On Feb 6, 2018 9:42 AM, "Azimuddin Mohammed" wrote: > Hello, > I need to install postgres without root privileges. I have a postgres user > in our companies AD. > Below are my questions n concerns: > > 1. Is there a website you will refer other the postgres documentation, the > one on postgres website is confusing > > 2. What is the default location of postgress install from a source code? > i.e if I run rmp -ivh where will it install by default? can this > be changed ? > > 3. Important parameters I need to change ? I am planning to have > different drive location for my data directory or any important parameter, > please let me know. > > > Thanks in Advance > > -- > > Regards, > Azim > >
